Apache OpenOffice Community Forum

Posted: **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

shutdown script

when I close the file.
Thank you!

Posted: **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
shutdown script
when I close the file.
Thank you!

In Basic from within Base

Code: Select all

Dim oStatement As Object
	
	oStatement = ThisComponent.CurrentController.ActiveConnection.createStatement()
 	oStatement.execute("shutdown script")

Posted: **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.

Posted: **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.

Posted: **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.

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

	
	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.

Posted: **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

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.

Posted: **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


	if ThisComponent.supportsService("com.sun.star.sdb.OfficeDatabaseDocument") then
		MsgBox("I'm in Base")
	else
		MsgBox("I'm somewhere else.")
	endif

Posted: **Mon Aug 01, 2011 9:36 am**

Hello

Maybe this can help

http://user.services.openoffice.org/en/ ... sh#p150827

Romke

Posted: **Fri Aug 05, 2011 6:16 am**

Dear Charlie,

Sorry that reply you late.
I run your macro,

Code: Select all

       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!

Posted: **Fri Aug 05, 2011 6:44 am**

idachan wrote:Dear Charlie,

Sorry that reply you late.
I run your macro,
Code: Select all
       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.

Posted: **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

Posted: **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

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.

Posted: **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

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.

: Inspect a Base document with MRI

Posted: **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

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

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

'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

Apache OpenOffice Community Forum

Use macro to execute SQL command

Use macro to execute SQL command

Re: Use macro to execute SQL command

Re: Use macro to execute SQL command

Re: Use macro to execute SQL command

Re: Use macro to execute SQL command

Re: Use macro to execute SQL command

Re: Use macro to execute SQL command

Re: Use macro to execute SQL command

Re: Use macro to execute SQL command

Re: Use macro to execute SQL command

Re: Use macro to execute SQL command

Re: Use macro to execute SQL command

Re: Use macro to execute SQL command

Re: Use macro to execute SQL command