DISCOVER Build your web site, collaborate with your colleagues, manage your content, and more. DEVELOP Build applications that run inside Liferay, extend the features provided out of the box with Liferay's APIs. DISTRIBUTE Let the world know about your app by publishing it in Liferay's marketplace. PARTICIPATE Become a part of Liferay's community, meet other Liferay users, and get involved in the open source project.

Wiki

HSQLInspect

Details

Tags: hsql hsqldb

Table of Contents [-]

This page describes a simple way to inspect the in-memory HSQL database that is used in the default Liferay bundles. You should stop the application server that's accessing the database before inspecting / modifying it with the procedure below.

Obtain HSQL JDBC Driver #

The HSQLDB JDBC driver is bundled in the main HSQL JAR. Download the HSQL distribution. In the lib directory you should find hsql.jar. Make a note of the location.

Obtain SquirrelSQL #

SquirrelSQL is an open-source database explorer that supports any JDBC accessible database.

Configure the HSQL Driver #

In the "drivers" pane in SquirrelSQL, you should see "HSQLDB In-Memory". It will have a red X in front of it meaning that you have not yet configured the JDBC driver location. Edit this driver (the pen icon). In the opened window, click the "extra class path" tab. Click "add" and navigate to hsqldb.jar from the HSQL distribution. The "class name" field below should now be filled in with "org.hsqldb.jdbcDriver".

Create an Alias #

An alias is a pre-configured connection to a particular database. In the alias pane, click new (the + icon). The URL should be of the form jdbc:hsqldb:file:<path>/lportal, where <path> is the current working directory of the application server, which is usually the directory where you executed the application server's start command. Look for lportal.script, lportal.log, etc. to validate that you have the correct path.

For example: jdbc:hsqldb:file:/Users/jtb/glassfish/lportal

The user name should be "sa", and the password "" (blank).

Connect #

Make the connection by double-clicking on the alias. Liferay's tables are under PUBLIC/TABLES.

0 Attachments

47366 Views

Average (3 Votes)

Comments

Threaded Replies	Author	Date
Perfect article! :D	Brian Preuß	September 9, 2008 4:10 AM
Awesome! Thank you	Andreas Minnich	November 12, 2008 6:30 AM
adsf	Ramdas Sawant	December 8, 2008 10:30 PM
Dot even need SquirrelSQL. Use hsqldb builtin...	Ramdas Sawant	December 8, 2008 10:32 PM
Can you please detail "Create an Alias" part......	ilke Muhtaroglu	July 1, 2010 5:38 AM
The information helped a lot.	Ricardo Kehrle Miranda	July 5, 2010 12:40 PM
It failed in the connection to the data base....	Tom I.	November 16, 2010 3:28 AM
Try this: 1. Shutdown your liferay server; 2....	Henrique Fernandes	December 13, 2010 6:47 AM
Hi, I tried to configure the liferay HSQLDB as...	Fatema Rajkotwala	February 16, 2012 7:13 PM
Make sure that the server is stopped while...	Huage Chen	June 18, 2013 7:35 AM
Hi, I get an error when trying to connect:...	David Lee	February 5, 2014 3:18 PM
If you are having trouble seeing the Liferay...	David Benson	June 10, 2014 1:56 AM
Thanks, this worked for me.	Christian Wilkie	July 10, 2014 12:22 PM

Brian Preuß

Perfect article! emoticon

Posted on 9/9/08 4:10 AM.

Andreas Minnich

Awesome!
Thank you

Posted on 11/12/08 6:30 AM.

Ramdas Sawant

adsf

Posted on 12/8/08 10:30 PM.

Ramdas Sawant

Dot even need SquirrelSQL. Use hsqldb builtin db explorer

java -cp <path of hsqldb.jar> org.hsqldb.util.DatabaseManager

Go ahead with "Create an Alias" step described above

Cheers !!!!!

Posted on 12/8/08 10:32 PM in reply to Ramdas Sawant.

ilke Muhtaroglu

Can you please detail "Create an Alias" part...

in Configure the HSQL Driver, it is told that we should provide the hsql.jar (this already exists in /lib/ext of liferay tomcat distribution, ok?)

then we are told to provide an url, which is in form "jdbc:hsqldb:."

there it is told url should be "jdbc:hsqldb:file:<path>/lportal"

( according to this link: http://www.emforge.net/web/liferay-petstore-portlet/wiki/-/wiki/Main/Step1:+From+DB+to+simple+UI )

( according to this link: http://www.liferay.com/community/forums/-/message_boards/message/70471 )

so what I try is to give URL:

jdbc:hsqldb:file:/home/ilke/lportal6/liferay-apache-tomcat-6.0.26/bin/lportal

Then I get the following error by SQuirreL:

( liferay_hsql: The database is already in use by another process: lockFile: org.hsqldb.persist.LockFile@b82b963b[file =/home/ilke/lportal6/liferay-apache-tomcat-6.0.26/bin/lportal.lck, exists=true, locked=false, valid=false, ] method: checkHeartbeat read: 2010-07-01 15:35:24.417 heartbeat - read: -9854 ms.
)

I use Ubuntu, liferay deployed into tomcat.

ilke

Posted on 7/1/10 5:38 AM.

Ricardo Miranda

The information helped a lot.

Posted on 7/5/10 12:40 PM.

Tom I.

It failed in the connection to the data base.
The database is already in use by another process: lockFile: org.hsqldb.persist.LockFile@aadfcab9

Is there a way to inspect HSQL data during debug in Eclipse?

Posted on 11/16/10 3:28 AM.

Henrique Fernandes

Try this:

1. Shutdown your liferay server;
2. Edit [LIFERAY_HOME]\data\hsql\lportal.properties
3. Set hsqldb.lock_file=false

Posted on 12/13/10 6:47 AM in reply to Tamas Imrei.

Fatema Rajkotwala

Hi,
I tried to configure the liferay HSQLDB as mentioned in the above article.
I gave the URL as jdbc:hsqldb:file emoticon

:/<Liferay_HOME>/data/hsql/Iportal
I am not getting any errors and when I am testing the Squirrel connection it is showing as Connection successful.
But still I am not able to view the liferay tables.Is there something more that needs to be configured.Please help me out.

Posted on 2/16/12 7:13 PM.

Huage Chen

Make sure that the server is stopped while access the HSQLDB

Posted on 6/18/13 7:35 AM in reply to Fatema Rajkotwala.

David Lee

Hi,
I get an error when trying to connect:
Caused by: java.sql.SQLException: Unable to create connection. Check your URL.
at net.sourceforge.squirrel_sql.fw.sql.SQLDriverManager.getConnection(SQLDriverManager.java:136)
at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.executeConnect(OpenConnectionCommand.java:167)
... 8 more

I am running Windows. So my URL is:
jdbc:hsqldb:file:c:/liferay/data/hsql/lportal

Is this correct? Please help. Thanks in advance!!

Posted on 2/5/14 3:18 PM.

David Benson

If you are having trouble seeing the Liferay tables, try changing the alias path to jdbc:hsqldb:file:/<liferay location>/data/hsql/lportal

This worked for me. Thanks to this post for pointing me in the right direction:
http://btnkumar.blogspot.co.uk/2011/11/how-to-open-hsql-db-tables-of-liferay_29.html

Posted on 6/10/14 1:56 AM.

Christian Wilkie

Thanks, this worked for me.

Posted on 7/10/14 12:22 PM in reply to David Benson.