TIP: Exploring Servoy's InMem HSQLDB Table Data

Find out how to get things done with Servoy. Post how YOU get things done with Servoy

TIP: Exploring Servoy's InMem HSQLDB Table Data

Postby sbutler » Fri Aug 16, 2019 9:01 pm

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 3675 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.
Attachments
stophsqldb.bat.txt
(200 Bytes) Downloaded 329 times
starthsqldb.bat.txt
(200 Bytes) Downloaded 312 times
sqltool.rc.txt
(84 Bytes) Downloaded 282 times
sqltool.jar
(144.46 KiB) Downloaded 270 times
Scott Butler
iTech Professionals, Inc.
SAN Partner

Servoy Consulting & Development
Servoy University- Training Videos
Servoy Components- Plugins, Beans, and Web Components
Servoy Guy- Tips & Resources
ServoyForge- Open Source Components
User avatar
sbutler
Servoy Expert
 
Posts: 759
Joined: Sun Jan 08, 2006 7:15 am
Location: Cincinnati, OH

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

Postby huber » Mon Aug 19, 2019 5:43 pm

Thanks for this! I agree it would be nice to have it in the SQL Explorer perspective or somehow integrated in Servoy Developer.

Regards,
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
huber
 
Posts: 516
Joined: Mon May 14, 2012 11:31 pm

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

Postby Joas » Tue Sep 17, 2019 8:57 am

Good stuff. Thanks for sharing!
Joas de Haan
Yield Software Development
Need help on your project? yieldsd.com
User avatar
Joas
Site Admin
 
Posts: 842
Joined: Mon Mar 20, 2006 4:07 pm
Location: Leusden, NL


Return to How To

Who is online

Users browsing this forum: No registered users and 8 guests