Using SQuirreL SQL Client with Derby. Configuring the SQuirreL Drivers window to use the Derby JDBC drivers Creating aliases to new or existing Derby databases. Use the JDBC driver from a Java application to submit Hive queries to Hadoop on HDInsight. Connect programmatically and from the SQuirrel SQL client.
JDBC 3.0 driver Driver version Size v8.1 FP0 (GA) s021023 1.0.581 663346 v8.1 FP1 s030130 1.2.34 798931 v8.1 FP2 s030508 1.2.117 845603 v8.1 FP3 s030728 1.3.70 872718 v8.1 FP4a s040122 1.5.54 900501 v8.1 FP5 s040212 1.9. V8.1 FP6 s040616 2.2. V8.1 FP6a s040914 2.2. V8.1 FP6c OD14073 2.2. V8.1 FP7 s040812 2.3. V8.1 FP7b OD14080 2.3. V8.1 FP8 s041221 2.5.
V8.1 FP8a OD14081 2.5. V8.1 FP9 s050422 2.6. V8.1 FP9a OD14086 2.6. V8.1 FP10 s050811 2.7. V8.1 FP11 s060120 2.8. V8.1 FP12 s060429 2.9.
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 Prerequisites. Download and install the following software to use SQuirreL SQL Client with Derby: Software Minimum Release Level Required Release Used in this Example Download Location Java JRE or JDK or SDK 1.4.x 1.5.x Any J2SE-compliant JVM, Java's reference implementation is at SQuirreL SQL Client Version 2.0 Version 2.2 Final Apache Derby Version 10.1.x Version 10.1.3 Additionally, if you would like to follow some of the examples for creating tables and loading data, download the file,. After downloading SQuirreL, follow the instructions on the installation page, 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. Configuring SQuirreL for the Derby Embedded JDBC Driver. When you first launch SQuirreL it will create a directory called.squirrel-sql in your C: Documents and Settings 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. 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.
Translations
On my machine I browsed to C: derbyhome derby10.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. Creating an Alias to a Derby database using the Embedded Driver. 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 DerbyEmbeddedFirstDB. 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.
Creating an alias for the Embedded Driver Test the connection by clicking the Test button. The Connect to: DerbyEmbeddedFirstDB 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.
Configuring the Derby Client Driver in the Drivers window. 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, 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:/derbyhome/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:/derbyhome/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. Creating an Alias for an existing Derby database using the Client Driver. Select the blue cross from the Aliases window and name the Alias something descriptive - I've chosen DerbyClientClientDB. 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:/derbyhome/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 of the Derby Developer's Guide, on configuring user authentication. Creating an Alias to an existing database using the Client Driver Once the test connection is successful, at the main screen for the DerbyClientClientDB 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. Creating a session to issue SQL and browse the database.
Double click the DerbyClientClientDB 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. Creating a session for the DerbyClientClientDB 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, squirrelderby.zip to a convenient location and locate the file tablesinserts.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 tablesinserts.sql file and pastes the output into the SQL editor area so we can run it. 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.
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 citiesinserts.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 createcitiestable.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! SQuirreL and Derby Resources.
The web site and 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, quickstart.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 mailing list.
Last updated: July 14, 2006.