Activity 2: Run SQL using the client driver

This activity uses Derby within a client/server configuration by using the Network Server. The ij tool is the client application that connects to the Derby Network Server. In this activity, you create a database called seconddb and run some basic SQL statements.

1. Open a command window (Shell-1) and change to the DERBYTUTOR directory.
2. Start the Network Server, as shown in the following table.

   Table 1. Command to start the Network Server

   Operating System	Command
   UNIX (Korn Shell)	java -jar $DERBY_HOME/lib/derbyrun.jar server start Thu Sep 13 16:45:36 EDT 2012 : Security manager installed using the Basic server security policy. Thu Sep 13 16:45:37 EDT 2012 : Apache Derby Network Server - 10.10.0.0 - (1384314) started and ready to accept connections on port 1527
   Windows	java -jar %DERBY_HOME%\lib\derbyrun.jar server start Thu Sep 13 16:45:36 EDT 2012 : Security manager installed using the Basic server security policy. Thu Sep 13 16:45:37 EDT 2012 : Apache Derby Network Server - 10.10.0.0 - (1384314) started and ready to accept connections on port 1527

   A Network Server startup message appears in the Shell-1 command window.
3. Open another command window (Shell-2). Change to the DERBYTUTOR directory.
4. Start ij.
   If you included the DERBY_HOME/bin directory in your PATH environment variable, type:

   ij

   Otherwise, you can use the java command to start the ij tool, as shown in the following table.

   Table 2. Command to start the ij tool

   Operating System	Command
   UNIX (Korn Shell)	java -jar $DERBY_HOME/lib/derbyrun.jar ij ij version 10.10
   Windows	java -jar %DERBY_HOME%\lib\derbyrun.jar ij ij version 10.10

   You will enter all subsequent commands from the network client, so you will type the commands in the Shell-2 command window.
5. Create and open a connection to the database using the client driver.

   CONNECT 'jdbc:derby://localhost:1527/seconddb;create=true';
   

   Remember: A client connection URL contains a hostname and a port number. For example: //localhost:1527/
6. Create a table with two columns (ID and NAME) using the following SQL statement:

   CREATE TABLE SECONDTABLE
       (ID INT PRIMARY KEY,
       NAME VARCHAR(14));
   0 rows inserted/updated/deleted
   

7. Insert three records into the table.

   INSERT INTO SECONDTABLE VALUES 
       (100,'ONE HUNDRED'),(200,'TWO HUNDRED'),(300,'THREE HUNDRED');
   3 rows inserted/updated/deleted
   

8. Select all of the records in the table.

   SELECT * FROM SECONDTABLE;
   ID         |NAME
   ------------------------
   100        |ONE HUNDRED
   200        |TWO HUNDRED
   300        |THREE HUNDRED
   
   3 rows selected

9. Select a subset of records from the table by specifying a WHERE clause.

   SELECT * FROM SECONDTABLE WHERE ID=200;
   ID         |NAME
   ------------------------
   200        |TWO HUNDRED
   
   1 row selected

10. Exit ij.

    exit;

11. Shut down the Derby Network Server, as shown in the following table.

    Table 3. Command to shut down the Network Server

    Operating System	Command
    UNIX (Korn Shell)	java -jar $DERBY_HOME/lib/derbyrun.jar server shutdown Thu Sep 13 16:50:37 EDT 2012 : Apache Derby Network Server - 10.10.0.0 - (1384314) shutdown
    Windows	java -jar %DERBY_HOME%\lib\derbyrun.jar server shutdown Thu Sep 13 16:50:37 EDT 2012 : Apache Derby Network Server - 10.10.0.0 - (1384314) shutdown

    The server shutdown confirmation appears in both command windows.