import java.sql. DriverManager ; import java.sql. Connection ; import java.sql. Statement ;

EXAMPLE IAn applicationshowingJDBC access to Cloudscape.http://www-306.ibm.com/software/data/cloudscape/

import java.sql.DriverManager; import java.sql.Connection; import java.sql.Statement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Properties; import com.ibm.db2j.drda.DB2jServer;

public class SimpleApp{ /* the default framework is embedded*/ public String framework = "embedded"; public String driver = "com.ibm.db2j.jdbc.DB2jDriver"; public String protocol = "jdbc:db2j:"; public static void main(String[] args) { new SimpleApp().go(args); } void go(String args[]) { /* parse the arguments to determine which framework is desired*/ parseArguments(args); System.out.println("SimpleApp starting in " + framework + " mode.");

try{ /* The driver is installed by loading its class. In an embedded environment, this will start up Cloudscape, since it is not already running. */ Class.forName(driver).newInstance(); System.out.println("Loaded the appropriate driver."); Connection conn = null; Properties props = new Properties(); props.put("user", "user1"); props.put("password", "user1"); /*The connection specifies create=true to cause the database to be created. To remove the database, remove the directory db2jDB and its contents.The directory db2jDB will be created under the directory that the system property db2j.system.home points to, or the currentdirectory if db2j.system.home is not set. */

if (framework.equals("embedded")) { conn = DriverManager.getConnection(protocol + "db2jDB;create=true", props); } else {// jccjdbc framework DB2jServer server = new DB2jServer(null); server.testConnection("db2jDB;create=true", null, null, "localhost", 1527); conn = DriverManager.getConnection(protocol + "//localhost:1527/db2jDB", props); } System.out.println("Connected to and created database db2jDB"); conn.setAutoCommit(false); /* Creating a statement lets us issue commands against the connection. */

Statement s= conn.createStatement(); // We create a table, add a few rows, and update one. s.execute("create table db2jDB(num int, addr varchar(40))"); System.out.println("Created table db2jDB"); s.execute("insert into db2jDB values (1956,'Webster St.')"); System.out.println("Inserted 1956 Webster"); s.execute("insert into db2jDB values (1910,'Union St.')"); System.out.println("Inserted 1910 Union"); s.execute( "update db2jDB set num=180, addr='Grand Ave.' where num=1956"); System.out.println("Updated 1956 Webster to 180 Grand"); s.execute( "update db2jDB set num=300, addr='Lakeshore Ave.' where num=180"); System.out.println("Updated 180 Grand to 300 Lakeshore");

// We select the rows and verify the results. ResultSetrs= s.executeQuery("SELECT num, addr FROM db2jDB ORDER BY num"); if (!rs.next()) throw new Exception("Wrong number of rows"); if (rs.getInt(1)!=300) throw new Exception("Wrong row returned"); if (!rs.next()) throw new Exception("Wrong number of rows"); if (rs.getInt(1)!=1910) throw new Exception("Wrong row returned") if (rs.next()) throw new Exception("Wrong number of rows");

System.out.println("Verified the rows"); s.execute("drop table db2jDB"); System.out.println("Dropped table db2jDB"); // We release the result and statement resource rs.close(); s.close(); System.out.println("Closed result set and statement"); // We end the transaction and the connection. conn.commit(); conn.close(); System.out.println("Committed transaction and closed connection");

/* In embedded mode, an application should shut down Cloudscape. If the application fails to shut down Cloudscape explicitly, the Cloudscape doesnot perform a checkpoint when the JVM shuts down, which means that the nextconnection will be slower. Explicitly shutting down Cloudscape with the URL is preferred.This style of shutdown will always throw an "exception". */ boolean gotSQLExc = false; if (framework.equals("embedded")) { try { DriverManager.getConnection("jdbc:db2j:;shutdown=true"); } catch (SQLException se) { gotSQLExc = true; } if (!gotSQLExc) System.out.println("Database did not shut downnormally"); else System.out.println("Database shut down normally"); }}

catch (Throwable e) { System.out.println("exception thrown:"); if (e instanceof SQLException) printSQLError((SQLException)e); else e.printStackTrace(); } System.out.println("SimpleApp finished"); } static void printSQLError(SQLException e) { while (e != null) { System.out.println (e.toString()) e = e.getNextException(); } }

private void parseArguments(String[] args) { int length = args.length; for (int index = 0; index < length; index++) { if (args[index].equalsIgnoreCase("jccjdbcclient")) { framework = "jccjdbc"; driver = "com.ibm.db2.jcc.DB2Driver"; protocol = "jdbc:db2j:net:"; // localhost:1527 } } } }

This program accomplishes the following tasks: • starts up the Cloudscape engine, if necessary • creates and connects to a database • creates a table • inserts data • updates data • selects data • drops a table • disconnects • shuts down Cloudscape, if necessary

Run in the Embedded Environment • The simplest Cloudscape environment. • The application starts up an instance of Cloudscape within the current JVM and shuts down the instance before it completes. • No network access is involved. In an embedded environment, only one application at a time can access a database.

New Files and Directories: after running the application • db2jDB(directory) The directory that makes up the db2jDBdatabase. • We must not modify anything in this directory, or we will corrupt the database. • The directory was created when the application connected with Cloudscape, using the attribute create=true in the database connection URL. • The database name, db2jDB, was also set in the database connection URL. • db2jDB\log (directory) The directory that holds the database log for the db2jDBdatabase. • db2jDB\seg0(directory) The directory that holds the data for the db2jDBdatabase. • db2jDB\service.properties An internal file that holds boot-time configuration parameters for the db2jDB database; do not edit. • db2j.LOGThe log file with Cloudscape progress and error messages.

Setting Class Path for an Embedded Environment • Cloudscape provides a script to help us get started setting class path in %DB2J_INSTALL%bin • This script is calledsetCP and • comes for Windows environment with .bat • For users working in this environment, copying the commands in this file will help us get started setting the class path.

Running the Application in an Embedded Environment • Open a command window and change directory in which the application files are copied For example: C:\Cloudscape_5.1\bin • DB2J_INSTALL environment variable is set to the the directory we installed the Cloudscape software (C:\Cloudscape_5.1) • Run Cloudscape's utility for testing the class path for an embedded environment java com.ibm.db2j.tools.sysinfo -cp arguments or java com.ibm.db2j.tools.sysinfo -cp embedded SimpleApp.class

com.ibm.db2j.tools public classsysinfoextends java.lang.Object This class (sysinfo) displays system information to system out. • This class displays system information to system out. To run from the command-line, enter the following: java com.ibm.db2j.tools.sysinfo • Also available on this class are methods which allow us to determine the version of the code for the system without actually booting a database. • This is the Cloudscape version of the .jar files, not of our databases.

If the environment is set up correctly • The utility shows output indicating success. • It looks like the following: FOUND IN CLASS PATH: Cloudscape primary library (db2j.jar) Valid Cloudscape license (Cloudscape primary library, or for evaluation copies of the software only, license.jar) user-specified class (SimpleApp) SUCCESS: All Cloudscape-Related classes for embedded environment found in class path.

If something is missing from class path environment • The utility indicates what is missing • For example, if we neglected to add the directory containing the SimpleApp class to our class path, the utility would indicate as such: Testing for presence of Cloudscape-related libraries for embedded environment. FOUND IN CLASS PATH: Cloudscape primary library (db2j.jar) Valid Cloudscape license (Cloudscape primary library, or for evaluation copies of the software only, license.jar) NOT FOUND IN CLASS PATH: user-specified class (SimpleApp) (SimpleApp not found.)

If we have our environment set up correctly • we can execute the application fromthe same directory java SimpleApp • A successful run produces the following output: SimpleApp starting in embedded mode. Loaded the appropriate driver. Connected to and created database db2jDB Created table db2jDB Inserted 1956 Webster Inserted 1910 Union Updated 1956 Webster to 180 Grand Updated 180 Grand to 300 Lakeshore Verified the rows Dropped table db2jDB Closed result set and statement Committed transaction and closed connection Database shut down normally

create=truefunction • Creates the standard database specified within the database connection URL • Cloudscape system and then connects to it. • If the database cannot be created, theerror appears in the error log and the connection attempt fails with an SQLExceptionindicating that the database cannot be found.

EXAMPLE II An applicationshowingJDBC access to MySQL

import java.sql.*; public class JdbcExam2 { public static void main(String args[]) { Connection con= null; Statement st = null; ResultSet rs = null; try { Class.forName("com.mysql.jdbc.Driver").newInstance(); con = DriverManager.getConnection("jdbc:mysql:///examples", "root", "secret"); st = con.createStatement(); rs = st.executeQuery("SELECT user_id, first_name, last_name, country_code FROM users"); while(rs.next()) { int userId = rs.getInt(1); String firstName = rs.getString(2); String lastName = rs.getString(3); String countryCode = rs.getString(4); System.out.println(userId + ". " + lastName + ", " + firstName + " (" + countryCode + ")"); }

} catch (Exception e) { System.err.println("Exception: " + e.getMessage()); } finally { try { if(rs != null) rs.close(); if(st != null) st.close(); if(con != null) con.close(); } catch (SQLException e) { } } } }

Step by Step Explanations • We create 3 variables to hold Connection, Statement and ResultSet objects for us which we'll create later in a try/catch/finally block. Connection con = null; Statement st = null; ResultSet rs = null; • Then,we enter a try/catch/finally block. • Our data access code will reside in the try block • Exception notification code in the catch block • Code to close the connection in the finally block

Step by Step Explanations cont’d • First thing we do to display records is to obtain a connection to MySQL database • we do that my first loading it's JDBC (Connector/J) driver and then using DriverManager to obtain a connection to our "examples" database. • We might want to change the password from "secret" to whatever is our password for 'root' account. Class.forName("com.mysql.jdbc.Driver").newInstance(); con =DriverManager.getConnection ("jdbc:mysql:///examples", "root", "secret");

Step by Step Explanations cont’d • Once we are connected, we create a new SQL Statement object. st = con.createStatement(); • We then execute this statement to obtain a ResultSet which contains all the records from the "users" table and we do that by executing a SELECT SQL statement. rs = st.executeQuery("SELECT user_id, first_name, last_name, " + "country_code FROM users");

Step by Step Explanations cont’d • We then iterate through that ResultSet to obtain values for each field of the "users" table. We then print these values on the user console • In the finally block we close off ourResultSet, Statement and Connectionobjects. Finally: • Compile the example, • Before running the Java program, make sure that MySQL server is running • properly created and setup "examples" database and "users" table. • Now to run our Java program, execute following command at the command prompt from the folder

import java.sql. DriverManager ; import java.sql. Connection ; import java.sql. Statement ;