How to disconnect and reconnect from a HSQL back end?

[gkick]

WIN10, LO7.3.7.3, Hsql 2.51

Hi,

Not sure if this post should go to Base or here?
A SHUTDOWN/COMPACT will disconnect the back end and base is still open. With base still being open how can one reconnect ie prompt for id and pw by way of a macro?
Calling the original Setup macro contained in the SplitWizard does not do the trick.
The current connection string being

jdbc:hsqldb:file:///C:\LDS\zMPQ\database/zMPQ;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false

Thanks for your thoughts

[Sliderule]

G'day:

I really have no idea what you wrote above SHUTDOWN/COMPACT , unless you meant SHUTDOWN COMPACT.

However, since you are using SINGLE USER rather than SERVER ( where the command can be run outside of OpenOffice / LibreOffice Base ) . . . if you run the SQL DDL statement: checkpoint defrag , per HSQL Documentation:

CHECKPOINT

checkpoint statement

<checkpoint statement> ::= CHECKPOINT [DEFRAG]

Closes the database files, rewrites the script file, deletes the log file and reopens the database.

If DEFRAG is specified, also shrinks the *.data file to its minimum size. CHECKPOINT DEFRAG time depends on the size of the database and can take a long time with huge databases.

A checkpoint on a multi-user database waits until all other sessions have committed or rolled back. While the
checkpoint is in progress other sessions are kept waiting. Checkpoint does not close any sessions.

Only a user with the DBA role can execute this statement.

Therefore, perhaps the above statement ( checkpoint defrag ) can be executed within the:

Tools -> Sql... box assuming the User had DBA authority.

Alternatively, in a Macro ( I would never do it from a macro but . . . that is up to you ) you could issued a macro command:

Code: Select all

executeUpdate(checkpoint defrag)

A better option is to upgrade to HSQL Version 2.6.1 ( as of the date I am writing this, 2022-07-05 ), soon to be released HSQL 2.7.0 . . . and, check documentation for the command below, and, read about automatic defrag when the free space exceeds the desired percentage space.

SET FILES DEFRAG

Furthermore, after the OpenOffice / LibreOffice Base file closed ( since you are running this as a single user ) . . . you could always run an instance of SqlTool from a Windows / Linux batch ( cmd ) file . . . at any computer designated time to issue the command checkpoint defrag . . . therefore accomplishing you desired task.

Sliderule

I hope this helps, please be sure to let me / us know.

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[gkick]

Hi Sliderule and thank you.

Well, I have a form which auto closes at x seconds and SHUTDOWN COMPACT terminates the connection. So I am looking for a way to reconnect without having to close and reopen the base file.

[Sliderule]

And, ¿ did you try to use your form and issue the command CHECKPOINT DEFRAG rather than SHUTDOWN COMPACT ?

[gkick]

Yes, checkpoint does not close the connection but I need the password prompt to come up up again