# 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. A database called `seconddb` is created and some basic SQL commands are executed.

This activity assumes that you know how to open a command shell, change directory (`cd`) to the `DERBYDBS` directory and set the DERBY_HOME environment variable.
Two command windows (labelled `Shell-1` and `Shell-2`) are used in this activity. `Shell-1` is used to start the Derby Network Server and display Network Server messages. `Shell-2` is used to establish a client connection to the Network Server using `ij` and then perform some basic SQL operations.

1. Open a command window that we'll call `Shell-1`. Change directory (`cd`) to the `DERBYDBS` directory and set the DERBY_HOME environment variable.
2. Start the Network Server.
   On Windows platforms:

         java -jar %DERBY_HOME%\lib\derbynet.jar start

         Apache Derby Network Server - 10.2.1.6 - (452058) started and
          ready to accept connections on port 1527 at 2006-09-22
          00:08:30.049 GMT

   On UNIX Korn Shell platforms:

         java -jar $DERBY_HOME/lib/derbynet.jar start

         Apache Derby Network Server - 10.2.1.6 - (452058) started and
          ready to accept connections on port 1527 at 2006-09-22
          00:08:30.049 GMT

   A Network Server startup message is displayed in the `Shell-1` command window.
3. Open another command window that we'll call `Shell-2`. Change directory (`cd`) to the `DERBYDBS` directory and set the DERBY_HOME environment variable.
4. Start `ij`.
   On Windows platforms:

         java -jar %DERBY_HOME%\lib\derbyrun.jar ij

         ij version 10.2

   On UNIX Korn Shell platforms:

         java -jar $DERBY_HOME/lib/derbyrun.jar ij

         ij version 10.2

   All subsequent commands are entered from the network client, and are therefore entered 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: `//localhost:1527/`.
6. Create a table with two columns (`ID` and `NAME`) using SQL.

       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 qualifying the command.

       ij> 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.
    On Windows platforms:

          java -jar %DERBY_HOME%\lib\derbynet.jar shutdown

          Apache Derby Network Server - 10.2.1.6 - (452058) shutdown
           at 2006-09-22 00:16:44.223 GMT

    On UNIX Korn Shell platforms:

          java -jar $DERBY_HOME/lib/derbynet.jar shutdown

          Apache Derby Network Server - 10.2.1.6 - (452058) shutdown
           at 2006-09-22 00:16:44.223 GMT

    The server shutdown confirmation appears in both command windows.

Activity notes

The client connection URL contains network information (a hostname and portnumber) not found in the URL for an embedded connection. This information tells the client driver the "location" of the Network Server. The client driver sends requests to and receives responses from the Network Server.

In this activity the Derby database engine is embedded in the Network Server and returns data to the `ij` client (a client/server configuration). In contrast, establishing a connection using an embedded URL (one without `//localhost:1527/`) would have caused the Derby engine to be embedded in the `ij` application (an embedded configuration).

Network Server start up and shutdown messages are written to the `derby.log` log file along with the standard database engine messages. For example:

    Apache Derby Network Server - 10.2.1.6 - (452058) started and ready to
     accept connections on port 1527 at 2006-09-22 00:08:30.049 GMT
       ...( database engine messages not shown )...
    Apache Derby Network Server - 10.2.1.6 - (452058) shutdown
     at 2006-09-22 00:16:44.223 GMT

Though not demonstrated here, multiple client programs can connect to Network Server and access the database simultaneously in this configuration.

Previous topic: Activity 1: Run SQL using the Embedded driver
Next topic: Activity 3: Run a JDBC program using the Embedded driver