A Free Database Query Tool
The first step in exploring the ESQ database is to take a quick look at the database schema (the tables and fields). Although there are various tools available, our recommendation is a free tool known as dbVisualizer, which was introduced to us by the folks at LOGICBit, the makers of ESQ. dbVisualizer supports most major operating systems and a wide variety of databases. By default you will install the free version which supports most of the commonly required functionality. For more robust or additional database needs the personal addition adds the following features.


Connecting to the Database
ESQ utilizes the Derby database in “embedded” mode which means that ESQ consumes exclusive access to the database. In other words, outside database connections are strictly prohibited while ESQ is running. This prevents ANY connection to the database other than ESQ. Since ESQ runs as a service, outside connections are normally prohibited because ESQ is always running. At first this may appear problemic for the technologist wishing to make an external connection. Pause to think about the security aspects, if "we" (the technologist) cannot connect, neither can a potential assailant.

What this means is that we simply have to schedule a time to open the database when we can take ESQ offline. In some terms I consider it best practice to perform database operations during downtime anyway. When we explore the reporting capabilities of the BIRT integration I’ll review other options for working with the data offline.

Let’s get back to creating an ESQ database connection with dbVisualizer.

1.  You will need a few pieces of information before we get started.   

a.  The path to your HoudiniESQ installation

b.  The database user and login information if your deployment.  Since Derby is using “embedded mode” you may not have been required to use a database password, it’s optional.

2.  From the dbVisualizer main menu select Database then select Create Database connection. You may alternatively select the main tool bar button as illustrated below.

 
dbVisualizer Main Menu
 

dbVisualizer Toolbar

Either of these options will accomplish the same task. If you are prompted to use a wizard, say no, the wizard makes the process unnecessarily complicated.

3.  You will  now see the following dialog on the right side of the dbVisualizer window.

1 Alias: This is a friendly name for the database connection. I simply use ESQ for my connection name.

2 Database Type: Click the dropdown and select JavaDB/Derby as shown above.

3 Driver (JDBC): Click the dropdown and select JaveDB/Derby embedded as illustrated above.

4 Database URL: This entry may vary from site to site however if you are using Windows yours should look exactly like this entry.

For a Windows 64 Bit OS
jdbc:derby:/Program Files (x86)/HoudiniESQ/webapps/houdini/esb/config/dbms/Houdini;create=false

For a Windows 32 Bit OS
jdbc:derby:/Program Files/HoudiniESQ/webapps/houdini/esb/config/dbms/Houdini;create=false

5 Userid and Password: This may be site specific, the default is shown above.

4.  Now press the connect button to verify your entries are correct. If you experience an error, double- check to insure you have entered the correct settings above. Pay particular attention to entry #4 that you haven’t made a typo in the database URL.

Should you see this error: Another instance of Derby may have already booted the database. Then you should ensure the HoudiniESQ server has been taken offline for service.

 

In our next HoudiniESQ segment we'll cover the basics of the database schema so you can quickly create reports in the BIRT report designer.
 

• < Prev