Page 1 of 1

TIP: Exploring Servoy's InMem HSQLDB Table Data

PostPosted: Fri Aug 16, 2019 9:01 pm
by sbutler
If you are using an application that makes use of Servoy's InMem datasources, it can be difficult to understand whats going on under-the-hood. You might want to poke around and look at the tables, and explore the data. This tip will show you how to do that. At a high level, we are going to modify our Servoy Developer install to spin up HSQLDB in server-mode, and have Servoy connect to that instead of an isolated embedded mode. Then we'll launch the HSQL Database Manager to explore the structure and data.
NOTE: This is only something you would do in Developer, and not on an application server.

One Time Setup
1. Add these 4 files to your application_server folder (See attachments, and make sure to rename ones where necessary):
sqltool.jar : This is for the HSQL Database Manager GUI
sqltool.rc.txt: This is for the saved settings for the HSQL Database Manager GUI. It specifies the DB name and credentials. Rename this to sqltool.rc
starthsqldb.bat.txt: This stats up HSLQDB as a server. Rename this to starthsqldb.bat
stophsqldb.bat.txt: This stops the HSLQDB a server. Rename this to stophsqldb.bat

2. Edit your application_server/servoy.properties file

Modify the nativeShutdownLauncher and add this to the front of the current value. If you are using Postgres, it will have a value already, so add this before the current value:
Code: Select all
%%user.dir%%/stophsqldb.bat|&|
So a full example might look like this:
nativeShutdownLauncher=%%user.dir%%/stophsqldb.bat|&|%%user.dir%%/postgres_db/bin/pg_ctl|stop|-D|database|-l|postgres_db/postgres_log.txt

If there is no value, then just add/set this property, like:
Code: Select all
nativeShutdownLauncher=%%user.dir%%/stophsqldb.bat


Do the same thing for startup, and modify the nativeStartupLauncher by adding this before the current value:
Code: Select all
%%user.dir%%/starthsqldb.bat|&|
So a full example might look like this:
nativeStartupLauncher=%%user.dir%%/starthsqldb.bat|&|%%user.dir%%/startpostgres.bat

If there is no value, then just add/set this property, like:
Code: Select all
nativeStartupLauncher=%%user.dir%%/starthsqldb.bat


Lastly, add this to the properties file. It overrides what Servoy will use for its InMem DB.
Code: Select all
server.inMemory.URL=jdbc\:hsqldb\:hsql\://localhost/xdb;shutdown\=true


Save the property file.


Usage
When you launch Servoy Developer, it will now start HSQLDB up in server mode, and launch the HSQL Database Manager GUI. When you close Servoy Developer, it will close the HSQL Database Manager GUI and stop the HSQLDB server. The process to startup and stop will take an extra 20 seconds or so, but that will vary by machine.
Once the HSQL Database Manager GUI is open, and you've done some debugging in your Servoy client to generate some InMem data, you can choose View=>Refresh Tree to refresh the list of tables. Then expand the tables to see the columns, and right click on a table and choose the SELECT option to preview the data:
hsqldb.PNG
hsqldb.PNG (39.76 KiB) Viewed 3669 times


Other Thoughts
- Mac users: The .bat files could be adjusted to shell scripts for equivalent use
- It would be handy if this was integrated into the SQL Explorer perspective. I didn't see an obvious way to do that, but I may still look into it. Alternatively it would be a nice feature request for Servoy to automatically show the InMem DB in the SQL Explorer perspective, as well as the Data tab enabled when opening/editing the InMem table.

Re: TIP: Exploring Servoy's InMem HSQLDB Table Data

PostPosted: Mon Aug 19, 2019 5:43 pm
by huber
Thanks for this! I agree it would be nice to have it in the SQL Explorer perspective or somehow integrated in Servoy Developer.

Regards,

Re: TIP: Exploring Servoy's InMem HSQLDB Table Data

PostPosted: Tue Sep 17, 2019 8:57 am
by Joas
Good stuff. Thanks for sharing!