Any way to automate/assist maintenance scripts?

[david.karr]

I've built a small prototype of a set of tables and forms using the embedded HSQL database. I've noticed that running "checkpoint defrag" helps to compact space from time to time. I haven't seen any way to run this except by manually entering it in the direct SQL tool (It doesn't even remember it in the "Previous Commands" list). Is there any way I can automate the execution of this, or even assist the semi-manual execution of this?

I noticed that that expression works in the "Tools"->"SQL" window, but it doesn't work in the "Create Query in SQL View" option. It just fails with "syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE".

[rudolfo]

The Queries that you can create in OOo Base are objects that have to follow a certain structure and this is to return a so called "result set". In a generalized way to look at it a result set is just a set of n rows where each row has m columns. A SELECT statement does always return such a construct though n (the number of rows) may be 0. An UPDATE or INSERT statement does never return a result set. And your "checkpoint defrag" statement is not even a Data Manipulation Language statement, but a maintenance statement of the database backend which would rather fall into the class of DDL (Data Definition) statements.

In short you can execute such statements only in the Tools -> SQL window, which is used to directly talk to the database backend. Don't confuse the Tools "SQL Window" with the option to create a query in "SQL View". They might use the same syntax highlighting for SQL, but they are completely different things. As stated above the Queries know what to do with the result sets visually (in the most simple case they display them in a 2 dimensional grid ... or the first 30 or 40 rows if the number of rows is large) while the SQL window knows how to tell the Database backend to make changes to the data in the tables or the tables (the structure of the tables) itself.

The Macro languages or to be more precise the UNO objects of Base are not bound to this kind of separation. Through a macro you can work with result sets (but this is a lot of work that you have to do to get at least a simple visual representation of the result set) and you can also modify your tables. The following code does basically the same as typing "checkpoint defrag" into the SQL Window and clicking [Ok] or [Execute].

Code: Select all

Sub DefragDatabase()
  ' The next line retrieves the currently open database connection
  ' (and requires that a .odb document is openend and active at the moment)
  oCon = ThisComponent.DataSource.getConnection("", "")
  oStmt = oCon.createStatement("checkpoint defrag") ' the macro counterpart for typing in the SQL Window
  oStmt.executeUpdate()                             ' Clicking the button
  oStmt.close()
  ' Don't close the connection oCon here, it is only borrowed from the .odb document
  ' and that document will still need it.
End Sub

Although the .executeUpdate method in the macro code sounds like it is only meant for Update statements, it is not like that. The documentation for Interface XStatement says:

executeUpdate executes an SQL INSERT, UPDATE, or DELETE statement. In addition, SQL statements that return nothing, such as SQL DDL statements, can be executed.

You can store this macro in the "My Macros" area and bind a key to it for a convenient way to execute it, but you still have to execute it manually.

[david.karr]

Hmm. I created a module in "My Macros", copying in this script, and when I ran it, it gave me:

IllegalArgumentException, "arguments len differ!".

[RoryOF]

Without seeing the actual code, how can we diagnose how the arguments' lengths differ? You may perhaps have one argument with a trailing space.

[david.karr]

I said "copying in this script". That referred to the script provided in the previous response.

In any case, I looked at other examples, and they all showed "createStatement()" as a function that takes no arguments, and "executeUpdate()" taking the SQL to execute. When I changed the script in that way, it ran without error.

[rudolfo]

Yes, sorry, your observations are right. I usually work with .prepareStatement instead of .createStatement because it is more robust. .prepareStatement expects the SQL statement as string parameter and consequently the .executeQuery or .executeUpdate methods of the statement are then without parameters.

And just because I am curious. Your original goal was to get the defragmentation of the database executed automatically in the background every hour or every 3 hours (or whatever period would be appropriate). You have now a macro that initiates this for you (so you can save the typing in the Tools->SQL Window), but you still have to click on the macro button or activate the short key combination manually, right?