Use macro to execute SQL command

Keyboard macros or custom scripts

Use macro to execute SQL command

Postby idachan » Fri Jul 29, 2011 10:28 am

HI!
I would like to ask that how to use macro to execute SQL command?
I want to execute
Code: Select all   Expand viewCollapse view
shutdown script
when I close the file.
Thank you!
OpenOffice 3.0 on Windows XP
idachan
 
Posts: 44
Joined: Tue Jul 05, 2011 11:33 am

Re: Use macro to execute SQL command

Postby Charlie Young » Fri Jul 29, 2011 10:06 pm

idachan wrote:HI!
I would like to ask that how to use macro to execute SQL command?
I want to execute
Code: Select all   Expand viewCollapse view
shutdown script
when I close the file.
Thank you!


In Basic from within Base

Code: Select all   Expand viewCollapse view
Dim oStatement As Object
   
   oStatement = ThisComponent.CurrentController.ActiveConnection.createStatement()
   oStatement.execute("shutdown script")
Apache OpenOffice 4.0.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1437
Joined: Fri May 14, 2010 1:07 am

Re: Use macro to execute SQL command

Postby rudolfo » Fri Jul 29, 2011 11:59 pm

Ida,
I hope you are aware what you are going to do. I guess the idea behind running shutdown script upon closing the OOo Base File is to get a more stable (or more compact) embedded database. You'd better run a search for the two phrases shutdown and script in the search functionality of the forum (upper right). There are several posts from those who are familiar with HSQLDB that explain that the database is written from a temporary cache location to the .odb zip archive. I picked out one example by r4zoli.
In short the process of writing back the cached database into the archive file is not really robust. Processing a radical statement as "shutdown script" direclty before (or in parallel -- who of us can know?) to this sensitive process has a good chance to make it all worse.

Another related issue is that you need to retrieve a handle of the active connection in the macro. The way how to do this differs depending if you are in the main Base window or in a form. And yes the file is about to close. Maybe the first operation when closing the file is to close the connection before any on-close macros are executed.

Make sure that you run tests with a database that doesn't hurt you when you loose it. And be prepared for debbuging and strange timing issues.

Frankly if you are looking for stability (and/or multiuser access) look for the external databases.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
rudolfo
Volunteer
 
Posts: 1415
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Use macro to execute SQL command

Postby idachan » Mon Aug 01, 2011 3:56 am

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.

Dear rudolfo,
Yup,I know what am I doing^^
I was new to Base and database,and going to handle a huge database.Then I chose Base to help me. I discovered that the file size was increased rapidly, so I found that Base would backup data.Therefore I came hereto search any method and I got a SQL command "shutdown script".It's really reduced the size!However I don't want to execute this command every time,so I ask for help^^
I want to execute this command in event "document is going to close".
Thank you for you reply and suggestion.
OpenOffice 3.0 on Windows XP
idachan
 
Posts: 44
Joined: Tue Jul 05, 2011 11:33 am

Re: Use macro to execute SQL command

Postby Charlie Young » Mon Aug 01, 2011 5:43 am

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.


That means you don't have an ActiveConnection. :D

An ActiveConnection is normally established when you view a table or execute a query or some such activity, but you can also make it happen in your macro.

Code: Select all   Expand viewCollapse view
   
   if IsNull(ThisComponent.CurrentController.ActiveConnection) then
      ThisComponent.CurrentController.connect
   endif



I don't think the IsNull check is really necessary, but it's good form and it doesn't hurt.
Apache OpenOffice 4.0.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1437
Joined: Fri May 14, 2010 1:07 am

Re: Use macro to execute SQL command

Postby idachan » Mon Aug 01, 2011 6:29 am

Dear Charlie,
I just try the macro, but same error exists again but point to "if IsNull(ThisComponent.CurrentController.ActiveConnection) then"@@
Here is the completed macro:
Code: Select all   Expand viewCollapse view
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

Thank you.
OpenOffice 3.0 on Windows XP
idachan
 
Posts: 44
Joined: Tue Jul 05, 2011 11:33 am

Re: Use macro to execute SQL command

Postby Charlie Young » Mon Aug 01, 2011 7:06 am

Are you sure you are running this macro from within a Base document? what pops up when you run this?

Put it in before any references to ActiveConnection.

Code: Select all   Expand viewCollapse view

   if ThisComponent.supportsService("com.sun.star.sdb.OfficeDatabaseDocument") then
      MsgBox("I'm in Base")
   else
      MsgBox("I'm somewhere else.")
   endif
Apache OpenOffice 4.0.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1437
Joined: Fri May 14, 2010 1:07 am

Re: Use macro to execute SQL command

Postby RPG » Mon Aug 01, 2011 9:36 am

openSuse version of LibreOffice 4.1.5.3 stable on openSUSE 12.2
RPG
Volunteer
 
Posts: 1548
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Use macro to execute SQL command

Postby idachan » Fri Aug 05, 2011 6:16 am

Dear Charlie,

Sorry that reply you late.
I run your macro,
Code: Select all   Expand viewCollapse view
       if ThisComponent.supportsService("com.sun.star.sdb.OfficeDatabaseDocument") then
          MsgBox("I'm in Base")
       else
          MsgBox("I'm somewhere else.")
       endif

A message box written "I'm in Base" was shown.

What should I do next step?
Thank you!
OpenOffice 3.0 on Windows XP
idachan
 
Posts: 44
Joined: Tue Jul 05, 2011 11:33 am

Re: Use macro to execute SQL command

Postby Charlie Young » Fri Aug 05, 2011 6:44 am

idachan wrote:Dear Charlie,

Sorry that reply you late.
I run your macro,
Code: Select all   Expand viewCollapse view
       if ThisComponent.supportsService("com.sun.star.sdb.OfficeDatabaseDocument") then
          MsgBox("I'm in Base")
       else
          MsgBox("I'm somewhere else.")
       endif

A message box written "I'm in Base" was shown.

What should I do next step?
Thank you!


Then I guess you're in Base. OK, dispose of that piece of code.

What happens if you try something other than "Shutdown Script?" Say try to create a backup table (SELECT INTO). It seems strange that you are able to load an .odb but not connect to it.
Apache OpenOffice 4.0.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1437
Joined: Fri May 14, 2010 1:07 am

Re: Use macro to execute SQL command

Postby idachan » Fri Aug 05, 2011 8:55 am

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
OpenOffice 3.0 on Windows XP
idachan
 
Posts: 44
Joined: Tue Jul 05, 2011 11:33 am

Re: Use macro to execute SQL command

Postby Charlie Young » Fri Aug 05, 2011 4:28 pm

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


The last macro was just to be sure you were running from Base and not some other app. Yes, it was correct.

An example, where a valid table name in your database should be substituted for SomeTable, and the placement of quotation marks is important.

Code: Select all   Expand viewCollapse view
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


Note that if this is successful, you will need to delete "SomeTableBackup" before running it again.
Apache OpenOffice 4.0.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1437
Joined: Fri May 14, 2010 1:07 am

Re: Use macro to execute SQL command

Postby rudolfo » Fri Aug 05, 2011 8:56 pm

Sorry for jumping in again. But this thread seems to become a funny interpretation of remote debugging ... with Charlie as the remote debugger. Surely, Charlie is one of the best debuggers you can get, but still posting in this thread introduces a lot of waiting time into the typical programming cycle (writing code, running the macro, inspect the errors with a debugger, modify the code, run the macro again ...).
One of the major rules of macro programming in OpenOffice (and probably other macro enabled programs, as well) is: If you run into an error, inspect the object with that you are working. The Basic function "IsNull" is a very basic object inspection method (and probably one of the rare kind that can be used with human remote debugging).
But it might be easier if you follow the Guide in the Online Help (press F1) and then "Macros and Programming" -> "Guides" -> "Debugging a Basic Program".

If you are running your macro from an event handler (maybe the CloseDocument Event?) you can't use the Basic Debbuger with all its feature. In this case it is easier to get one of the Object inspection tool extensions MRI or Xray.
For MRI your macro code could be:
Code: Select all   Expand viewCollapse view
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

You can click one certain properties and methods and they will be shown in the window. If a property is again a UNO object it will have a list of properties and methods as well. The following screenshot show how it looks for me, if I have a Base document opened.
mri.png
Inspect a Base document with MRI
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
rudolfo
Volunteer
 
Posts: 1415
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Use macro to execute SQL command

Postby RPG » Sat Aug 06, 2011 2:12 am

Hello

Maybe the next example can work but it is not sure. But the macros given by Charlie Young are perfect macros. I do use ThisdatabaseDocument and not ThisComponent. That I use an other example for SQL is not important.

This can work maybe work always in a form what is part of a databasedocument. Also it can be runned in the IDE.
Code: Select all   Expand viewCollapse view
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


I asume this can be runned in ll forms but not from the IDE.
Code: Select all   Expand viewCollapse view
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

This code is an other example what can work but has other problems.
Code: Select all   Expand viewCollapse view
'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


I want make clear that the question seems simple: I want a little macro. But when you not describe in what kind of document you use the macro and also not describe how you activate the macro then it can not work for you.

Maybe an other problem can be where do you store the macros. Most of this macros work only when they are stored in the document and not My Macros. When you store the macros in My macros you can expect other problems. This also depends on what kind of document you use and also how do you call the macro.

Romke
openSuse version of LibreOffice 4.1.5.3 stable on openSUSE 12.2
RPG
Volunteer
 
Posts: 1548
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 2 guests