I would like to ask that how to use macro to execute SQL command?
I want to execute
Code: Select all
shutdown script
Thank you!
Code: Select all
shutdown script
In Basic from within Baseidachan wrote:HI!
I would like to ask that how to use macro to execute SQL command?
I want to executewhen I close the file.Code: Select all
shutdown script
Thank you!
Code: Select all
Dim oStatement As Object
oStatement = ThisComponent.CurrentController.ActiveConnection.createStatement()
oStatement.execute("shutdown script")
That means you don't have an ActiveConnection.idachan wrote:Dear Charlie Young,
Thank you for your reply,but there exists an error when running the macro.
"Property or method not found:ActiveConnection"
How to deal with it?Thank you.
Code: Select all
if IsNull(ThisComponent.CurrentController.ActiveConnection) then
ThisComponent.CurrentController.connect
endif
Code: Select all
sub executeSQLcmd
Dim oStatement As Object
if IsNull(ThisComponent.CurrentController.ActiveConnection) then
ThisComponent.CurrentController.connect
endif
oStatement = ThisComponent.CurrentController.ActiveConnection.createStatement()
oStatement.execute("shutdown script")
end sub
Code: Select all
if ThisComponent.supportsService("com.sun.star.sdb.OfficeDatabaseDocument") then
MsgBox("I'm in Base")
else
MsgBox("I'm somewhere else.")
endif
Code: Select all
if ThisComponent.supportsService("com.sun.star.sdb.OfficeDatabaseDocument") then
MsgBox("I'm in Base")
else
MsgBox("I'm somewhere else.")
endif
Then I guess you're in Base. OK, dispose of that piece of code.idachan wrote:Dear Charlie,
Sorry that reply you late.
I run your macro,A message box written "I'm in Base" was shown.Code: Select all
if ThisComponent.supportsService("com.sun.star.sdb.OfficeDatabaseDocument") then MsgBox("I'm in Base") else MsgBox("I'm somewhere else.") endif
What should I do next step?
Thank you!
The last macro was just to be sure you were running from Base and not some other app. Yes, it was correct.idachan wrote:Dear Charlie,
I'm sorry that I cannot get your meaning...
could u give me some examples to try?
btw, is it correct for my previous macro?
thank you!
Ida
Code: Select all
Sub executeSQL()
Dim oStatement As Object
Dim oDoc As Object
oDoc = ThisComponent
if IsNull(oDoc.CurrentController.ActiveConnection) then
oDoc.CurrentController.connect
endif
oStatement = oDoc.CurrentController.ActiveConnection.createStatement()
oStatement.execute("SELECT * INTO ""SomeTableBackup"" FROM ""SomeTable""")
End Sub
Code: Select all
Sub executeSQL()
Dim oStatement As Object
Dim oDoc As Object
Dim oMRI As Object
Globalscope.BasicLibraries.LoadLibrary("MRILib")
oMRI = CreateUnoService("mytools.Mri")
oDoc = ThisComponent
oMRI.inspect( oDoc.CurrentController )
if IsNull(oDoc.CurrentController.ActiveConnection) then
oDoc.CurrentController.connect
endif
oStatement = oDoc.CurrentController.ActiveConnection.createStatement()
oStatement.execute("SELECT * INTO ""SomeTableBackup"" FROM ""SomeTable""")
End Sub
Code: Select all
sub executeSQLcmd
' This can work direct in the IDE and with a button in a form
Dim oStatement As Object
if IsNull(Thisdatabasedocument.CurrentController.ActiveConnection) then
Thisdatabasedocument.CurrentController.connect
endif
oStatement = Thisdatabasedocument.CurrentController.ActiveConnection.createStatement()
dim resultset
resultset=oStatement.executequery("call CURRENt_TIME")
resultset.next
print resultset.getstring(1)
end sub
Code: Select all
sub executeSQLcmd(oEvent as object)
' This must be activated by a button
' I assume this can work in all forms but you can not run it in the IDE
dim oButton ,oForm
oButton=oEvent.source.model
'When the line above is blue then you run it possible in the IDE
oForm=oButton.parent ' We assumed that when you come here there is always an activ connection
Dim oStatement As Object
oStatement = oForm.ActiveConnection.createStatement()
dim resultset
resultset=oStatement.executequery("call CURRENt_TIME")
resultset.next
print resultset.getstring(1)
end sub
Code: Select all
'This works only for a form in a database document and not for a standalone form
'It works when it is activate from the IDE and assume there is a connectio to the database
sub executeSQLcmd
' This must be activated by a button or on an other way
' I assume this can work in all forms who are stored in the database. but you can not run it in the IDE
dim oDoc,oDatabaseDocument
Dim oStatement As Object
oDoc=Thiscomponent
oDatabaseDocument=oDoc.parent
oStatement = oDatabaseDocument.getCurrentController.ActiveConnection.createStatement()
dim resultset
resultset=oStatement.executequery("call CURRENt_TIME")
resultset.next
print resultset.getstring(1)
end sub