Activity 2: Run SQL using the client driver This activity uses within a client/server configuration by using the Network Server. The ij tool is the client application that connects to the Network Server. In this activity, you create a database called seconddb and run some basic SQL statements. Network ServerClient driver tutorialclient driver This activity assumes that you know how to open a command shell and change to the DERBYTUTOR directory.

You use two command windows (referred to as Shell-1 and Shell-2) in this activity. You use Shell-1 to start the Network Server and display Network Server messages. You use Shell-2 to establish a client connection to the Network Server using ij and then perform some basic SQL operations.

 Open a command window (Shell-1) and change to the DERBYTUTOR directory. Start the Network Server, as shown in the following table. Command to start the Network ServerThis table lists the commands to start the Network Server on UNIX and Windows systems. Operating System Command UNIX (Korn Shell) java -jar $DERBY_HOME/lib/derbyrun.jar server start Wed Nov 28 10:08:37 PST 2018 : Apache Derby Network Server - 10.15.0.0 alpha - (1847657M) started and ready to accept connections on port 1527 Windows java -jar %DERBY_HOME%\lib\derbyrun.jar server start Wed Nov 28 10:08:37 PST 2018 : Apache Derby Network Server - 10.15.0.0 alpha - (1847657M) started and ready to accept connections on port 1527
A Network Server startup message appears in the Shell-1 command window. Open another command window (Shell-2). Change to the DERBYTUTOR directory. 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.

Command to start the <codeph>ij</codeph> toolThis table lists the commands to start the ij tool on UNIX and Windows systems. Operating System Command UNIX (Korn Shell) java -jar $DERBY_HOME/lib/derbyrun.jar ij ij version @RELEASE_ID_SHORT@ Windows java -jar %DERBY_HOME%\lib\derbyrun.jar ij ij version @RELEASE_ID_SHORT@
You will enter all subsequent commands from the network client, so you will type the commands 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'; A client connection URL contains a hostname and a port number. For example: //localhost:1527/ 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 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 specifying a WHERE clause. SELECT * FROM SECONDTABLE WHERE ID=200; ID |NAME ------------------------ 200 |TWO HUNDRED 1 row selected Exit ij.exit; Shut down the Network Server, as shown in the following table. Command to shut down the Network ServerThis table lists the commands to shut down the Network Server on UNIX and Windows systems. Operating System Command UNIX (Korn Shell) java -jar $DERBY_HOME/lib/derbyrun.jar server shutdown Wed Nov 28 10:15:54 PST 2018 : Apache Derby Network Server - 10.15.0.0 alpha - (1847657M) shutdown Windows java -jar %DERBY_HOME%\lib\derbyrun.jar server shutdown Wed Nov 28 10:15:54 PST 2018 : Apache Derby Network Server - 10.15.0.0 alpha - (1847657M) shutdown
The server shutdown confirmation appears in both command windows. Activity notes

The client connection URL contains network information (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 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 engine to be embedded in the ij application (an embedded configuration).

In this configuration, multiple client programs can connect to the Network Server and access the database simultaneously. (This activity does not demonstrate this capability.)