Using ij to issue SQL commands

ij, the interactive SQL scripting tool provided with Derby, allows you to issue ad-hoc queries against a Derby database. Running ij from within Eclipse speeds application development by testing and running SQL statements prior to coding JDBC calls.

To launch ij

• Select the project and bring up the context menu. Select the menu item, Apache Derby, ij (Interactive SQL).

• The Console view will show the ij prompt. For this example we assume the Derby Network Server has been started; if you haven't started it, go ahead and start it up now.
  
  
• The first step to using Derby is to connect to the database using a database JDBC connection URL.
  The database connection URL we'll use for this example will connect to our Network Server using the Derby Network Client driver on the localhost. We'll create a database called myDB as the user 'me' with a password of 'mine.'
  
  To connect to the database from ij we need to issue the connect command, so the entire command to connect to our database looks like this:

  connect 'jdbc:derby://localhost:1527/myDB;create=true;user=me;password=mine';
  

• Cut and paste the above connection URL into the ij console window. It should create a database in the current workspace, under the current Java project, called myDB.
  
  
• We'll also create a table in our myDB database, insert some rows and select all rows from the table. Here is the SQL to do this:

  create table restaurants(id integer, name varchar(20), city varchar(50));
  insert into restaurants values (1, 'Irifunes', 'San Mateo');
  insert into restaurants values (2, 'Estradas', 'Daly City');
  insert into restaurants values (3, 'Prime Rib House', 'San Francisco');
  select * from restaurants;
  

• Cut and paste this SQL (one line at a time) into the ij console window.

Sample output from our ij session which runs the sql commands listed above is shown below.

The database connection URL shown above is used to connect to the Derby Network Server. If you want to connect to the database using the Derby JDBC embedded driver the connection URL would look like jdbc:derby:myDB;create=true and the ij command would be this:

connect 'jdbc:derby:myDB;create=true';

For detailed information on the proper syntax to use for the Connection URL refer to the Derby Tools & Utility Guide. The section called Getting started with ij provides the necessary information to start using ij.