How to connect to the HSQL database using its built-in GUI query tool called DatabaseManager?

[kc2001]

I am new to Talend Studio. I am doing data profiling using Talend Studio enterprise version 7.3. Generating PDF reports has gone smoothly.

Our group would like to produce some output that is different than what is automatically provided, so we would like to query the underlying reporting database. If I go to the Generated Report Settings pane, there is a Database Connection Settings area with a Db Type of HSQL. Clicking the Check button causes a popup that says "Connection successful and Datamart well configured", as shown below.

 

Great! Unfortunately, I have not been able to find the name of the database, so that I could connect to it and inspect it using an IDE. For example, I've read that I should be able to connect to the HSQL database using its built-in GUI query tool called DatabaseManager like so:

java -cp ../lib/hsqldb.jar org.hsqldb.util.DatabaseManager 

and then setting the config to:

jdbc:hsqldb:file:databaseName

But what is the "databaseName"? Is it specified in a properties file or XML file somewhere?

[Anonymous]

Hi @Not defined Not defined​,

I have spoken to a couple of colleagues about your question and I am afraid there is no easy answer. It looks like the HSQL db is an in-memory db that is not intended to be queryable. When the Studio starts, it runs the following code as part of the create process for the in-memory DB....

CREATE USER SA PASSWORD DIGEST 'd41d8cd98f00b204e9800998ecf8427e'

ALTER USER SA SET LOCAL TRUE

Although this doesn't necessarily render access impossible, it does not really make it very easy. The password digest doesn't give us any clues to the password.

However, you have the Enterprise Edition. You can therefore get around this limitation quite easily (it is not recommended to use the in-memory DB with the Enterprise Edition).

If you go to your preferences in Studio, then select Talend --> Profiling --> Reporting, you can configure a proper database which will allow you to query as you wish. It will also preserve the data (the in-memory db does not preserve it).

 

I have set this up quite quickly on my machine using Docker. Let me know if you have any further questions

[kc2001]

Thanks! I've handed this off to a colleague. I believe he succeeded, using the method you described. Cheers, Keith