[Solved] How do I get Base to remember log file information?

[SpeedySteedy]

I would like my database to have a log file to audit any changes and which user made these changes.

Having followed the excellent tutorials on here to split the database, I have created a log file either by either using the Tools > SQL console to issue the SQL command

Code: Select all

SET DATABASE EVENT LOG SQL LEVEL 3

or by appending ‘hsqldb.sqllog=3’ on the JDBC URL used to connect the database.
This is successful and creates a <databasename>.sql.log file in the database folder containing all the SQL statements with time and session information which is retained when Base is shut down.

However, when I shut Base down and restart, the link to the log file is missing and nothing is recorded - until I manually issue the SQL command or edit the JDBC URL again.

So although there is a manual workaround, how do I get Base to remember the log file information?
I'm assuming it's something to do with the macro used to create the JDBC URL to make the split database portable but macros are beyond my current knowledge.

Thanks in advance for any help

[Greengiant224]

SpeedySteedy:
Having followed the excellent tutorials on here to split the database, I have created a log file either by either using the Tools > SQL console to issue the SQL command

Code: Select all Expand view
SET DATABASE EVENT LOG SQL LEVEL 3

or by appending ‘hsqldb.sqllog=3’ on the JDBC URL used to connect the database.
This is successful and creates a <databasename>.sql.log file in the database folder containing all the SQL statements with time and session information which is retained when Base is shut down.

Close the database down first.
You would need to edit the <databaseName>.script file that is in the database folder.
Find the line :
SET DATABASE EVENT LOG SQL LEVEL 0 which is set as a default of 0 (no logging) and
change to 3, save the file. Your database should now remember your logging requirements.

Hope this helps.

Greengiant224

[SpeedySteedy]

That doesn't seem to work.

I edited the script file as suggested, saved, restarted LibreOffice, made changes to my database and exited, but the log file hasn't recorded my changes.
Checked the script file again and it still says SET DATABASE EVENT LOG LEVEL 3, so at least the changes I made in there have been saved, just not acted on.

Log file still works correctly if I execute SET DATABASE EVENT LOG LEVEL 3 in the SQL console, but I don't want to do this manually each time.

I even tried creating a new split database using the Split_HSQLDB_2.3.2_Wizard_v3d.odb file, and have the same issue with that.

I would appreciate any other suggestions.
Thanks in advance

[Sliderule]

Log file still works correctly if I execute SET DATABASE EVENT LOG LEVEL 3 in the SQL console, but I don't want to do this manually each time.

Since, you are using HSQL 2.3.3 . . . this information is for you ( not for those running an Embedded Database . . . HSQL Version 1.8.0.10 ).

The log information is written to a file YOUR_DB_NAME.app.log . . . and . . . since you want log information to ALWAYS be written to that file . . . add to your database connection string . . . whether connecting from OpenOffice / LibreOffice Base, or, any other external HSQL front-end ( for example, SqlTool, SQLWorkbench/J etc )

Code: Select all

;hsqldb.applog=3

For example,

1. From the Base Menu: Edit -> Database -> Properties
2. For your Database URL, at the end, include the hsqldb.applog property, I use the following ( file bldtest.app.log is now defined/written to ):
   

   Code: Select all

   hsqldb:file:C:\Users\Sliderule\Documents\HSQL\data\bldtest;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false;hsqldb.applog=3

3. Of course, if running your HSQL connection as a server, change the server start-up connection accordingly.

Check out HSQL 2.3.3 Documentation at the link below, and search for applog :

http://www.hsqldb.org/doc/2.0/guide/dbp ... _props_url

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

Sliderule

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

[SpeedySteedy]

Thanks Sliderule but I had already tried appending hsqldb.sqllog=3 to the URL (see first message) Whatever I changed the URL to, it didn't save this information when Base was closed and restarted.

BUT!! I think I've solved it.
I was having a nosey around the Macros (for a completely unrelated reason) and noticed that one of them included the JDBC URL that I was unsuccessfully trying to edit. I changed the Macro, saved it, closed Base, re-opened & bingo!! the URL is exactly what I want it to be and the log file works.

For anyone else having this issue -
From the Base menu: Tools > Macros > Organise Macros > LibreOffice Basic
Expand the macros in <YourDB>.odb > Standard > Embedded

I edited the IsInstalled macro although changing one seems to change them all.

This is my first experience with macros so if anyone else wants to check out my instructions then I would be very grateful.