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.

Open a command window that we'll call Shell-1. Change directory (cd) to the DERBYDBS directory and set the DERBY_HOME environment variable.

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.

Open another command window that we'll call Shell-2. Change directory (cd) to the DERBYDBS directory and set the DERBY_HOME environment variable.
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.
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/.

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

Insert three records into the table.

INSERT INTO SECONDTABLE VALUES 
    (100,'ONE HUNDRED'),(200,'TWO HUNDRED'),(300,'THREE HUNDRED');

3 rows inserted/updated/deleted

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

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

Exit ij.
```
exit;
```

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.