Using SQuirreL SQL Client with Derby

SQuirreL SQL Client is a useful tool for browsing and manipulating SQL databases via JDBC. This paper discusses setting up SQuirreL to access Derby databases via the Derby Embedded Driver and Client Drivers with the 10.1 version of Derby.

Some of the features of SQuirreL that are discussed in relation to working with Derby databases are:

• Configuring the SQuirreL Drivers window to use the Derby JDBC drivers
• Creating aliases to new or existing Derby databases
• Executing, editing and running SQL scripts and commands
• Browsing the objects in a database using the session window
• Extracting and loading data to and from a Derby table via a local file
• Generating DDL
• Dropping tables

Download and install the following software to use SQuirreL SQL Client with Derby:

Additionally, if you would like to follow some of the examples for creating tables and loading data, download the file, squirrel_derby.zip.

After downloading SQuirreL, follow the instructions on the installation page, squirrel.org/#installation, on how to install SQuirreL using the executable jar file. On my system, this command worked to launch the install wizard:

java -jar squirrel-sql-2.2final-install.jar

The wizard lets you choose the location where you wish to install SQuirreL, as well as which plugins you would like to install. I selected the Standard plugins because this paper will make use of the SQL Scripts standard plugin. Note that some of the Optional Plugins are considered Beta quality. After the installation is complete go to the directory where you installed SQuirreL and launch (or double-click on Windows) the squirrel-sql.bat file, or the squirrel-sql.sh file on Linux.

When you first launch SQuirreL it will create a directory called .squirrel-sql in your C:\Documents and Settings\<username> directory on Windows, or $HOME on Linux. This is where configuration settings and history are stored. The first screen that appears in the desktop will show two windows - the Drivers and the Aliases windows. The Drivers window will show the Apache Derby Client and the Apache Derby Embedded drivers in the list. If the jar files for either of these drivers, derbyclient.jar or derby.jar, are in your system CLASSPATH then they will have a blue check next to them. If not, a red 'X' will appear in front of them as shown below.

Figure 1. The SQuirreL SQL Client desktop prior to configuration

Although you need to install Derby prior to going further, I'll assume you don't have the jar files in your CLASSPATH and we'll set up SQuirreL correctly so you don't need to.

Configuring the Derby Embedded Driver in the Drivers window

Select the Apache Derby Embedded option from the Drivers window and click the pencil icon, which allows you to Modify the Selected Driver. We need to point to the location of the derby.jar file on the file system, which contains the Derby Embedded Driver. Click the Extra Class Path tab, and then the Add button. Browse to this directory and select derby.jar. On my machine I browsed to C:\derby_home\derby_10.1.3\lib\derby.jar.

Now, click the List Drivers button and the Class Name pulldown list will be automatically populated. Select the Class Name of org.apache.derby.jdbc.EmbeddedDriver from the list and click OK.

Don't modify the Example URL in the Drivers window since this is just a listing of available drivers, and does not represent a connection to a specific database. That is what the Aliases window is for. Now you should be returned to the main desktop of SQuirreL and if the driver was added successfully, you will see this message in the status window, with green highlighting;

Driver class org.apache.derby.jdbc.EmbeddedDriver successfully registered for driver definition: Apache Derby Embedded

Also, there should be a blue check mark next to the Apache Derby Embedded listing in the Drivers window now.

Now go to the Aliases window and click the blue '+' mark. Name the Alias something descriptive. I'm going to create a database using SQuirreL called FirstDB so I'll name the Alias Derby_Embedded_FirstDB. For the driver, pull down the list to select Apache Derby Embedded. For the URL, I want to create the FirstDB database so the URL will look like this: jdbc:derby:FirstDB;create=true.

A User Name and Password are not required to make a connection using the Embedded Derby driver, so I will not enter any values for this. Also, at this time I do not want to automatically logon or connect at startup, so I'll leave those boxes unchecked.

Figure 2. Creating an alias for the Embedded Driver

Test the connection by clicking the Test button. The Connect to: Derby_Embedded_FirstDB window will appear and prompt again for a username and password, but they are not required. Just click the Connect button to test. If all goes well the FirstDB database will be created in the directory where you launched SQuirreL from, and a popup box will tell you the Connection was successful. Say OK to dismiss this window, and from the next window click the OK button too. Close the next window instead of connecting, so we can set up the Derby Client driver first, before browsing the database.

The Derby Client driver is used with the Derby network server, so we'll discuss the network server prior to configuring the client driver.

Starting the Network Server and creating a database using ij

The Derby Network server should be used in environments where multiple JVMs are connecting to a single Derby database. In the context of using SQuirreL to browse a Derby database this configuration is required if other users will be connecting to the same database via other clients, for instance, ij or some other application.

Let's start the network server now, and create the database using ij prior to creating the Alias for this database.

Start the Derby Network server on your localhost. If you don't know how to start the network server refer to the Derby tutorial, which also shows you how to set up your environment to do so.

This is the command I used to start the network server on my host with derbynet.jar in my CLASSPATH:

java org.apache.derby.drda.NetworkServerControl start

In another command window, with derbyclient.jar and derbytools.jar in my classpath, I issued this command to launch ij, and then created a database called ClientDB under my C:/derby_home/databases/ directory. Notice the difference in the URL when connecting to the network server versus when making an embedded Derby connection.

java org.apache.derby.tools.ij ij version 10.1 ij> connect 'jdbc:derby://localhost:1527/C:/derby_home/databases/ClientDB;create=true';

Leave the network server and ij windows active. Next we'll configure SQuirreL for use with the Client driver and then create an alias to the ClientDB database.

The Derby Client Driver configuration in the Drivers window

Select the Apache Derby Client driver from the Drivers window in SQuirreL, and then click the pencil icon again. Click the Extra Class Path tab and then the Add button to browse to the derbyclient.jar file. The client driver only contains one Class of driver, so the Class Name category should be populated with the correct value, org.apache.derby.jdbc.ClientDriver. Click OK and the blue check mark will appear next to the Apache Derby Client value in the list now.

In the next section we'll configure an Alias to use the Derby Client Driver to connect to our database, ClientDB.

Select the blue cross from the Aliases window and name the Alias something descriptive - I've chosen Derby_Client_ClientDB. Make sure the Driver is Apache Derby Client and for the URL, we will point to our database ClientDB we created earlier. For my environment the correct value for the URL is:

jdbc:derby://localhost:1527/C:/derby_home/databases/ClientDB;

Here is what the values for my environment look like, just before clicking the Test button. A user name and password is required in the SQuirreL environment although it is not required for ij. Any value for the user name and password will do, but realize that the user name sets the default schema for the connection. Note: If you would like to require a specific username and password to connect to Derby, refer to this section of the Derby Developer's Guide, on configuring user authentication.

Figure 3. Creating an Alias to an existing database using the Client Driver

Once the test connection is successful, at the main screen for the Derby_Client_ClientDB window, select the OK button to close the window.

Remember how we left our ij session active? When we clicked the test button, and later on when we connect to and browse the ClientDB database, we are taking advantage of the Derby Network Servers' ability to allow multiple client applications, running in different JVMs to connect to the same database.

Double click the Derby_Client_ClientDB entry in the Aliases window which brings up another window. Click the Connect button to connect to the database and bring up the session window. The session window is shown below with the Alias name listed and the username I connected with, in this case, as user slc.

Figure 4. Creating a session for the Derby_Client_ClientDB alias

Executing, editing and running SQL scripts using the SQL tab

Before we explore the Objects tab, it will be more interesting if we create some tables and insert data into our database. Unzip the zip file, squirrel_derby.zip to a convenient location and locate the file tables_inserts.sql. Select the SQL tab of the session window and then use the folder icon in the session window to Open a file. This allows you to browse to the tables_inserts.sql file and pastes the output into the SQL editor area so we can run it. The CREATE TABLE statements included in this file are shown below (the inserts are omitted.)

CREATE TABLE CITIES ( CITY_ID INTEGER NOT NULL CONSTRAINT cities_pk PRIMARY KEY, CITY_NAME VARCHAR(24) NOT NULL, COUNTRY VARCHAR(26) NOT NULL, AIRPORT CHAR(3), LANGUAGE VARCHAR(16), COUNTRY_ISO_CODE CHAR(2) ); CREATE TABLE FLIGHTS ( FLIGHT_ID CHAR(6) NOT NULL, SEGMENT_NUMBER INTEGER NOT NULL, ORIG_AIRPORT CHAR(3), DEPART_TIME TIME, DEST_AIRPORT CHAR(3), ARRIVE_TIME TIME, MEAL CHAR(1) CONSTRAINT MEAL_CONSTRAINT CHECK (meal IN ('B', 'L', 'D', 'S')), FLYING_TIME DOUBLE PRECISION, MILES INTEGER, AIRCRAFT VARCHAR(6), DEPART_DATE DATE, CONSTRAINT FLIGHTS_PK Primary Key (FLIGHT_ID) ); CREATE INDEX DESTINDEX ON FLIGHTS (DEST_AIRPORT) ; CREATE INDEX ORIGINDEX ON FLIGHTS (ORIG_AIRPORT) ; CREATE TABLE USERS ( FIRSTNAME VARCHAR(40) NOT NULL, LASTNAME VARCHAR (40) NOT NULL, USERNAME VARCHAR(20) NOT NULL CONSTRAINT username_pk PRIMARY KEY, PASSWORD VARCHAR(20) NOT NULL, PASSWORD_VERIFY VARCHAR(20), EMAIL VARCHAR(30) NOT NULL ); CREATE TABLE USER_CREDIT_CARD ( ID INT NOT NULL GENERATED ALWAYS AS IDENTITY CONSTRAINT user_cc_pk PRIMARY KEY, USERNAME varchar(20) NOT NULL, LASTNAME varchar(40), CREDIT_CARD_TYPE varchar(15) NOT NULL, CREDIT_CARD_NUMBER varchar(20) NOT NULL, CREDIT_CARD_DISPLAY varchar(25) NOT NULL ); ALTER TABLE USER_CREDIT_CARD ADD CONSTRAINT USERNAME_FK Foreign Key (username) REFERENCES USERS (username); CREATE TABLE FLIGHTHISTORY ( ID INT NOT NULL GENERATED ALWAYS AS IDENTITY CONSTRAINT user_fh_pk PRIMARY KEY, USERNAME VARCHAR(20) NOT NULL, FLIGHT_ID CHAR(6) NOT NULL, ORIG_AIRPORT CHAR(3) NOT NULL, DEST_AIRPORT CHAR(3) NOT NULL, MILES INTEGER, AIRCRAFT VARCHAR(6), DEPARTURE_DATE VARCHAR(25), CREDIT_CARD_TYPE varchar(15) NOT NULL, CREDIT_CARD_DISPLAY varchar(25) NOT NULL ); ALTER TABLE FLIGHTHISTORY ADD CONSTRAINT USERNAME_FH_FK Foreign Key (username) REFERENCES USERS (username);

All of the SQL should be selected (highlighted in grey), if not, select it by issuing a Ctrl + A and then click the icon of the running man (or alternately enter a Ctrl + Enter). The image below shows what the session window looks like after I successfully executed all of the SQL in the file.

Figure 5. Running SQL commands using the SQL tab

We just saw how to import an SQL file to run it, but other options available when running SQL commands or SQL scripts from the SQL tab are:

• New File - the icon with the blank page and the cross at the top.
• Appending a file to the editor contents - the icon with the file and the folder.
• Save editor contents to a file - the disk icon.
• Save editor contents to a new file - the disk icon with the ellipsis.
• Print the editor contents - the printer icon.
• Save the file - the 'X' icon.

Additionally, ad hoc queries and commands can be entered in the SQL editor window.

Browsing the database with the Objects tab

Now that we have data in our ClientDB database, select the Objects tab of the session window. The first thing you need to do to see the tables you just created is to refresh the tree structure. Click the Refresh Database Object Tree icon (the little yellow arrows) or push F5.

Select the schema folder which represents the user you connected as, in my case this is the SLC folder. When connecting to a database in Derby, the default schema is the username you connected with. Expand this folder and then expand the TABLE folder. The five tables you just created should appear. Select the CITIES table and notice how the tabs on the right change. SQuirreL provides a lot of metadata about each database object. Look at some of the info provided by the different tabs and then click the Content tab.

Some of the really nice things to note about the content tab are:

• The ability to sort the table (ascending or descending) by column name. Just click the column name and an up or down arrow will appear and sort it.
• Right-click one row or select all rows to see the variety of options available if you want to copy the data - copy as html, copy with headers, show row number, etc.
• Right-click one row or all rows to Make Editable. This allows you to modify the data, insert or delete rows.

The image below shows what the first row in the CITIES table looks like after I have right-clicked it and then single-clicked in this column. The column that I am about to edit for this row is the CITY_NAME column as shown by the yellow highlighting. By double-clicking the value in this column, several other options are available for updating or saving the value of this row's column, including importing and exporting values to a file or executing an external command. Refer to the SQuirreL help system for a detailed explanation on these options.

Figure 6. Making a row editable by right-clicking in a row

To save an edit issue a Ctrl + S prior to exiting the session, otherwise your changes will be lost. Also, exiting a session reverts the ability to edit the data to the setting which exists in the New Session Properties setting. The place to set this property for new sessions is under the main SQuirreL desktop menu item, File >> New Session Properties >> General tab. Once there, look in the Output area, and the Table Contents setting. To make the output for the table default to Not Editable select Table. To enable editing by default, select Editable Table.

To set the default to enable editing in the existing session, click the farthest left icon on the menu bar and set the property there.

A plethora of options is available when right-clicking one of the tables in the Objects tab. The available options can be grouped into these major categories:

• Modifying the schema object - dropping the table or deleting records.
• Changing the view of the data - refreshing the object tree or specifying a where clause to limit the number of rows retrieved in the content tab.
• Copying the object name - either the fully qualified name or just the short name.
• Creating scripts - create table, create select, drop table, create data or create template data scripts.

Figure 7. Options available for table objects

Extracting and loading data to a table via a local file

Let's "unload" the data from the CITIES into an SQL file so we can demonstrate how to delete the rows, drop the table, recreate the table and then load the rows back. In the current version of SQuirreL, V2.2 Final Release, the only way to "unload" the data is to create insert statements from the existing data. In the 2.3 RC1 version of SQuirreL, there will be a way to export the data into a CSV file.

First select the SQL tab from the session window and click the New File icon. This will clear the current SQL window and allow us to create a new file for the insert statements we are about to create. Now go back to the Objects tab. Right-click the CITIES table and select Create Data Script. This will automatically put you back in the SQL tab. Now click the Save Editor contents to a file, or Ctrl + S. Save the file with an SQL extension. I've called it cities_inserts.sql.

Deleting the rows from a table

Now go back to the Objects tab. Right-click the CITIES table again and select Delete Rows. All of the rows in this table will be removed as well as any rows in other tables that are dependent on rows in the CITIES table.

Generating a script to create a table

Go back to the SQL tab and close the file with the insert statements, then go back to the Objects tab and select Create Table Script when right-clicking the CITIES table. The SQL tab becomes active. Save this script to a new file by issuing a Ctrl + S or clicking the icon. I've called the file create_cities_table.sql. Click the X icon to close the file.

Dropping a table

Back in the Objects table select the CITIES table and drop it by right-clicking it and selecting Drop.

Running the scripts to recreate and "load" the data

Select the SQL tab and select the Open a File icon, the folder icon. Browse to the file you created that contains the create table statement. Then execute the SQL by clicking on the running man icon. The table should be recreated. Close this file and open the SQL file that contained the insert statements and run this file.

Go back to the Objects tab and refresh the database object tree if necessary to see the CITIES table. Once the CITIES table is selected, click the Content tab to see all of the data you just loaded again!

The Derby web site and wiki are the best sources of information about Derby.

The intent of this document was to discuss using SQuirreL in relation to Derby databases and only provides a view which is a subset of the functionality available via SQuirreL. The documentation that comes with SQuirreL is very complete and provides much more information than provided here. The help system, accessible via the main menu item Help > Help, or as a static html page, quick_start.html, is part of the installation under the doc directory where you installed SQuirreL.

Susan Cline wrote this paper and would appreciate any feedback in the form of suggestions, corrections or questions about it by posting to the derby-user mailing list.