| ## Java 1.1 Unleashed # - 43 - Getting Started with JDBC by Shelley Powers IN THIS CHAPTER Connecting to a Database and Making Simple Queries More Complex Uses of JDBC Working with Multiple Databases The Java Database Connectivity (JDBC) API includes classes that can handle most database access needs. From simple select statements to processing the results of a complex stored procedure to accessing more than one database at a time, the JDBC provides the functionality most applications need. This chapter provides examples that use the main JDBC classes. For general information about the classes, review Chapter 19, "The SQL Package." Additionally, Chapter 42, "Databases and Java," provides information about accessing data using techniques other than the JDBC. To use the JDBC, you must have one or more JDBC drivers written specifically to connect to the database types you are using. A list of these drivers can be found at the Sun JavaSoft site at `http://www.javasoft.com/jdbc`. ## Connecting to a Database and Making Simple Queries One very attractive aspect of the JDBC is how easy it is to use. To connect to a database, make a query, and process the results takes no more than a few lines of code, as the following sections demonstrate. THE SAMPLES IN THIS CHAPTER The samples in this chapter use either the JDBC-ODBC bridge that comes with the Java Development Kit or the Imaginary mSQL-JDBC driver. Three databases are used: a Sybase SQL Anywhere database, a Microsoft Access 97 database, and an mSQL database. The databases and the script to make the databases can be found on the CD-ROM that accompanies this book. ### Database Connections The database connection string is the only JDBC component that references the particular database being accessed. You must provide a specific driver connection string that uses the protocol defined for the driver. These protocols, currently maintained by JavaSoft, are used by the JDBC `DriverManager` class to determine which driver to load. Remember to establish the database connection with the ODBC Administrator before using these sample programs. For example, to connect to a Sybase SQL Anywhere database using the JDBC-ODBC bridge, use the following connection string and connection method:
    //connect to ODBC database
    String url = "jdbc:odbc:Zoo";
    // connect
    Connection con = DriverManager.getConnection(url,"dba", "sql");
The second line of the code creates a `String` object that contains the URL for the database. The string also includes the protocol `jdbc`, the subprotocol `odbc`, and the location of the database `Zoo`. The fourth line creates the `Connection` object for the database. Because this example uses the JDBC-ODBC bridge, the database access is through the name defined with the ODBC Administration tool, found in the Control Panel group for Windows NT or 95. Compare this example with one using the mSQL-JDBC driver and note that the URL string differs in format as well as in content:
    //connect to database
    Class.forName("imaginary.sql.iMsqlDriver");
    String url = "jdbc:msql://yasd.com:1112/test";
    // connect
    Connection con = DriverManager.getConnection(url);
With this driver, the subprotocol is `msql`, and the database connection information required by the driver is a double slash followed by the name of the site, the port, and the name of the database. The port number `1112` specifically tells the mSQL-JDBC driver that the database is being run by `root` rather than by some other user name. Specifying a port number of `4333` states that the database is running under another user name, which should also be specified in the connection. In these two examples, note that the second example loads the driver classes explicitly using the `Class.forName()` method. With this method, the class loader checks for the driver classes in the local path designated by the `CLASSPATH` variable. If the driver classes are not found and the method is called from an applet, the class loader attempts to download them from the network. Chapter 44, "Building Database Applets with JDBC," provides more information about classes and applets. Another difference between the two examples is that the first connection, for the JDBC-ODBC bridge, uses three parameters: The connection string, the database user name, and the password. The second example, for the mSQL-JDBC driver, passes just the connection string and assumes that the connection is open to all. A third example, which follows (and which also uses the JDBC-ODBC bridge), shows a connection that uses the third `getConnection()` method, which uses a `Properties` class:
    //connect to ODBC database
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String url = "jdbc:odbc:Zoo";
            // connect
            Properties p = new Properties();
            p.put("user", "dba");
            p.put("password","sql");
            Connection con = DriverManager.getConnection(url,p);
The benefit of this third technique is that the application can prompt the user for the user name and password so that the password can be entered into an encrypted field and not be hard coded into the application. Listing 43.1 shows the complete code for connecting to an ODBC database and processing a simple query. Note that, at the end of the application, the `Connection` instance is closed. Unless resources are limited, closing the `Connection` instance is usually not necessary. When the object is no longer in scope (as is the case when the application is no longer running, the applet is unloaded, or the object is contained within a procedure or control structure that ends), normal Java garbage collection frees up the resources. #### Listing 43.1. The sample1 application using JDBC.
    import java.lang.*;
    import java.sql.*;
    import sun.jdbc.odbc.*;
    public class sample1
    {
    public static void main(String arg[]) {
    int id;
    float amount;
    Date dt;
    String status;
    String result;
         try {
            //connect to ODBC database
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String url = "jdbc:odbc:Zoo";
            // connect
            Connection con = DriverManager.getConnection(url,"dba", "sql");
            // create Statement object
            Statement stmt = con.createStatement();
            String sqlselect = "Select company_id, order_dt, po_amount, status_cd"
            + " from dba.purchase_order";
            // run query
            ResultSet rs = stmt.executeQuery(sqlselect);
            // process results
            while(rs.next()) {
                    result  = "";
                    id      = rs.getInt(1);
                    amount  = rs.getFloat(3);
                    dt      = rs.getDate(2);
                    status  = rs.getString(4);
                    result = result.valueOf(id) + " ";
                    result+= result.valueOf(amount) + " ";
                    result+= dt.toString() + " " + status;
                    System.out.println("Values are: " + result);
                    }

            //close connection
            con.close();
            }
         catch(Exception e) {
               System.out.println(e.getMessage());
            }
    }
    }
Once a connection is established to the database, it is used to create some kind of statement using the `Statement` class or one of its extensions. ### The Statement Class A statement for simple querying of a database takes the following form:
    // create Statement object
    Statement stmt = con.createStatement();
This type of statement can be used for single-use updates or queries to the database that do not return multiple result sets or update counts. After the statement is created, it can be used to issue a simple query, as shown here:
    String sqlselect = "Select company_id, order_dt, po_amount, status_cd"
            + " from dba.purchase_order";
    // run query
    ResultSet rs = stmt.executeQuery(sqlselect);
This example shows how a `String` object is used to hold the select statement, which is then used in the `executeQuery()` method call. This method, in turn, generates a `ResultSet` object containing the results of the query. The preceding example shows a select statement that accesses all the data in a table. Parameters passed to the Java application can be appended to the SQL statement for a more dynamic query, as shown here:
    String srch = arg[0];
    String sqlselect = "Select company_id, order_dt, po_amount, status_cd"
            + " from dba.purchase_order where company_id = " + srch;
    // run query
    ResultSet rs = stmt.executeQuery(sqlselect);
In addition to searching for specific values, wildcard characters (also referred to as escape or pattern match characters) can be used in a search. In Java, an underscore character (`_`) is used to search for a single character, and a percent sign (`%`) is used to search for zero or more characters, as shown in Listing 43.2. #### Listing 43.2. The sample2 application using escape characters.
    import java.lang.*;
    import java.sql.*;
    import sun.jdbc.odbc.*;
    public class sample2
    {
    public static void main(String arg[]) {
    int id;
    float amount;
    Date dt;
    String status;
    String result;
         try {
            //connect to ODBC database
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String url = "jdbc:odbc:Zoo";
          // connect
            Connection con = DriverManager.getConnection(url,"dba", "sql");
            // create Statement object
            Statement stmt = con.createStatement();
            String srch = arg[0];
            String sqlselect = "Select company_id, order_dt, po_amount, status_cd"
            + " from dba.purchase_order where company_id = " + srch;
            // run query
            ResultSet rs = stmt.executeQuery(sqlselect);
            // process results
            while(rs.next()) {
                    result  = "";
                    id      = rs.getInt(1);
                    amount  = rs.getFloat(3);
                    dt      = rs.getDate(2);
                    status  = rs.getString(4);
                    result = result.valueOf(id) + " ";
                    result+= result.valueOf(amount) + " ";
                    result+= dt.toString() + " " + status;
                    System.out.println("Values are: " + result);
                    }

            //close connection
            con.close();
            }
         catch(Exception e) {
               System.out.println(e.getMessage());
            }
    }
    }
This code in Listing 43.2 looks for any company name that contains the value specified by the variable `srch`. Notice that the results are pulled from the row using the data type specific to the column. These values are then converted to `String` values using the `valueOf()` method. A more efficient method would have been to pull the values in directly as strings using the `getString()` method, but the example does demonstrate some of the other `ResultSet` get methods. CAUTION: If you are using the escape characters, don't forget to include the surrounding quotation marks (") for the pattern-match sequence. Pattern matching is for character-based or like fields; if you forget the quotation marks, you will get an error. You can also run update statements with the JDBC. Instead of using `executeQuery()`, your application must use `executeUpdate()`. The update statement can be any valid data modification statement: `UPDATE`, `DELETE`, or `INSERT`. The result is the count of rows impacted by the change. Listing 43.3 shows an example of each of the different types of data manipulation statements. #### Listing 43.3. The sample3 application demonstrating database updates.
    import java.lang.*;
    import java.sql.*;
    import sun.jdbc.odbc.*;
    public class sample3
    {
    public static void main(String arg[]) {
         try {
            //connect to ODBC database
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String url = "jdbc:odbc:Zoo";
            // connect
            Connection con = DriverManager.getConnection(url,"dba", "sql");
            con.setAutoCommit(false);

            // create Statement object
            Statement stmt = con.createStatement();
            String updatestring = "INSERT INTO RETAIL_ITEM(item_nbr,item_desc, qty_per_pkg, "
                    + "wholesale_cost, retail_cost, company_id, color, size) "
                    + "VALUES(25,'some item', 1, 10.00, 15.00, 1, 'orange', 'M')";
            // run query
            int ct = stmt.executeUpdate(updatestring);
            // process results
            System.out.println("Insert row: " + updatestring.valueOf(ct));
            updatestring = "UPDATE PERSON "
                           + "set zip_cd = '97228' where "
                           + "zip_cd = '97229'";
            // run query
            ct = stmt.executeUpdate(updatestring);
            // process results
            System.out.println("Updated rows: " + updatestring.valueOf(ct));
            //back out modifications
            updatestring = "DELETE FROM RETAIL_ITEM "
                         + "where item_nbr = 25 and company_id = 1";
            // run query
            ct = stmt.executeUpdate(updatestring);
            // process results
            System.out.println("Delete row: " + updatestring.valueOf(ct));
            updatestring = "UPDATE PERSON "
                           + "set zip_cd = '97229' where "
                           + "zip_cd = '97228'";
            // run query
            ct = stmt.executeUpdate(updatestring);
            // process results
            System.out.println("Updated rows: " + updatestring.valueOf(ct));
            //close connection
            con.commit();
            con.close();
            }
         catch(Exception e) {
               System.out.println(e.getMessage());
               }
    }
    }
TIP: In Listing 43.3, note that `autocommit` is turned off for the set of transactions. If you don't turn `autocommit` off, each of the transactions would be committed as soon as it completed, rather than committing after all the transactions have completed. Because we are backing changes out, we want all the transactions to succeed before issuing a commit; otherwise all the transactions will roll back if an exception occurs (the database implements this functionality by default). The `Statement` object can also be used in a call to a database stored procedure if no dynamic parameters are given. For procedures with `IN`, `OUT`, and `INOUT` parameters, the `CallableStatement` class is used, as explained later in this chapter. If the statement is a query, it always returns a `ResultSet` object unless an exception occurs. The `ResultSet` object is demonstrated in more detail in the following section. ### The ResultSet Class A query returns a `ResultSet` object. This object contains the results of the query in a form that can be accessed by the application. If the query has no results, the `ResultSet` object contains no rows; otherwise, it contains rows of data matching the query (up to the limit specified for the database). If the database supports doing so, you can set the maximum number of rows using the `Statement` class method `setMaxRows()`. Once the result set is returned, you can use methods to access and process individual columns as shown in Listing 43.4. #### Listing 43.4. The sample4 application with a result set.
    import java.lang.*;
    import java.sql.*;
    import sun.jdbc.odbc.*;
    public class sample4
    {
    public static void main(String arg[]) {
    int id;
    String name;
    String address;
    String city;
    String state;
    String result;
         String srch = arg[0];
         try {
            //connect to ODBC database
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String url = "jdbc:odbc:Zoo";
            // connect
            Connection con = DriverManager.getConnection(url,"dba", "sql");
            // create Statement object
            Statement stmt = con.createStatement();
             stmt. setMaxRows(10);
            String sqlselect = "Select company_id, company_name, address_1, city, state_cd"
            + " from dba.company where company_name like '%" + srch + "%'";
            // run query
            ResultSet rs = stmt.executeQuery(sqlselect);
            // process results
            while(rs.next()) {
                    result  = "";
                    id      = rs.getInt(1);
                    name    = rs.getString(2);
                    address = rs.getString(3);
                    city    = rs.getString(4);
                    state   = rs.getString(5);
                    result = result.valueOf(id) + " ";
                    result+= name + " " + address + " ";
                    result+= city + " " + state;
                    System.out.println("Values are: " + result);
                    }
            //close connection
            con.close();
            }
         catch(Exception e) {
               System.out.println(e.getMessage());
            }
    }
    }
Individual data types have matching `get`XXX`()` methods to retrieve the value in the form the application prefers. You pass to each method the number of the column representing the position it holds in the original select statement, or a text string containing the name of the column. The `next()` method maintains a cursor that points to the current row being processed. Each call to the method moves the cursor to the next row. When no more rows are found, the `next()` method returns a value of `false`. You can access the columns even if you don't know the order in which the columns will be returned. Using the `findColumn()` method, you can look for a column with the same name as the one passed to the method; the method returns the column index. You can then use the index used to access the value, as shown in Listing 43.5. #### Listing 43.5. The sample5 application using the findColumn() method.
    // run query
            ResultSet rs = stmt.executeQuery(sqlselect);
            // process results
            while(rs.next()) {
                    result  = "";
                    id      = rs.getInt(rs.findColumn("company_id"));
                    name    = rs.getString(rs.findColumn("company_name"));
                    address = rs.getString(rs.findColumn("address_1"));
                    city    = rs.getString(rs.findColumn("city"));
                    state   = rs.getString(rs.findColumn("state_cd"));
                    result = result.valueOf(id) + " ";
                    result+= name + " " + address + " ";
                    result+= city + " " + state;
                    System.out.println("Values are: " + result);
                    }
This technique is a little convoluted because you can also use the get methods that accept a string representing the column name and return the result. A partial listing of the results of running the `sample5` Java application are shown here:
    java sample5
    Values are: 1 Portland T-Shirt Company 18050 Industrial Blvd. Portland, OR
    Values are: 2 Tri-State Stuffed Critter 923 Hawthorne Way Vancouver WA
    Values are: 3 Tigard Candy Shop 1900 Mountain Rd Tigard OR
    Values are: 4 LA T-Shirt Company 13090 SW 108th Ave SW Los Angeles CA
    . . .
In addition, you can also check to see whether a column value is `null` by using the `wasNull()` method after the `get`XXX`()` method call:
    state = rs.getString("state_cd");
    boolean b = rs.wasNull();
    if (b) {
    . . .
The examples in this section introduced and demonstrated the `Connection`, `Statement`, and `ResultSet` classes. The next two sections demonstrate the use of some more complex features of the JDBC and how to access multiple heterogeneous databases. ## More Complex Uses of JDBC Occasionally, a database developer has to program for more complex database access situations. You may want to create a statement and then execute it many times, or call a stored procedure that returns multiple result sets, or issue a dynamic SQL statement. This section covers some techniques for handling these types of statements. Two of the classes discussed, `PreparedStatement` and `CallableStatement`, are extended classes: the former is an extension of `Statement` and the latter is an extension of `PreparedStatement`. In addition to the classes, the following sections also demonstrate and discuss the `execute()` method of the `Statement` class. ### The PreparedStatement Class The `PreparedStatement` class is used to create and compile a statement at the database, and then invoke that statement multiple times. The statement usually has one or more `IN` parameters that change each time the statement is executed. Both the `executeUpdate()` and `executeQuery()` methods work with the `PreparedStatement` class. The class is an extension of the standard `Statement` class; it has the additional flexibility of being stored in a compiled form and run many times. To create the `PreparedStatement` class statement, use a SQL string that contains references to one or more unknown parameters:
    String sqlselect =
                    "Select * from retail_item where company_id = ?";
            // create Statement object
            PreparedStatement stmt = con.prepareStatement(sqlselect);
Before executing the statement (in this example, it is a query), you must set the `IN` parameter value:
    stmt.setInt(1,i);
    rs = stmt.executeQuery();
After executing the query or update, you can process the results in the same way you process a regular statement:
    int colcount = rsmd.getColumnCount();
    // process results
    while(rs.next()) {
           result = "";
           for (int k = 1; k <= colcount; k++) {
                result+= rs.getString(k) + " ";
                }
           System.out.println("Values are: " + result);
          }
The only difference between using the `PreparedStatement` class and the `Statement` class is that the same statement would then be processed for the former with different parameters. A full example of using this class is given in Listing 43.6. #### Listing 43.6. The sample6 application using the PreparedStatement class.
    import java.lang.*;
    import java.sql.*;
    import sun.jdbc.odbc.*;
    public class sample6
    {
    public static void main(String arg[]) {
    String result;
         try {
            //connect to ODBC database
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String url = "jdbc:odbc:Zoo";
            // connect
            Connection con = DriverManager.getConnection(url,"dba", "sql");
            con.setAutoCommit(false);
            String sqlselect =
                    "Select * from retail_item where company_id = ?";
            // create Statement object
            PreparedStatement stmt = con.prepareStatement(sqlselect);
            ResultSet rs;
            ResultSetMetaData rsmd;
            for (int i = 1; i <= 3; i++) {
                    stmt.setInt(1,i);
                    rs = stmt.executeQuery();
                    rsmd = rs.getMetaData();
                    int colcount = rsmd.getColumnCount();
                    // process results
                    while(rs.next()) {
                       result = "";
                       for (int k = 1; k <= colcount; k++) {
                          result+= rs.getString(k) + " ";
                          }
                       System.out.println("Values are: " + result);
                       }
                 }

            //close connection
            con.close();
            }
         catch(Exception e) {
               System.out.println(e.getMessage());
            }
    }
    }
Issuing an update instead of a query is no different except that the `IN` parameters are used to modify data instead of to select it, and that the `executeUpdate()` method is used instead of `executeQuery()`:
    String sqlselect =
          "Update retail_item set company_id = ? where company_id = ?";
    // create Statement object
    PreparedStatement stmt = con.prepareStatement(sqlselect);
    for (int i = 1; i <= 3; i++) {
           stmt.setInt(1,i);
           stmt.setInt(2, i + 1);
           int count = stmt.executeUpdate();
    . . .
> The purpose of the `PreparedStatement` class is to compile the query ahead of time, which cuts back on the time necessary to process each query or update. If you are not planning to run the SQL statement multiple times, using the `PreparedStatement` class is not an effective approach. ### PROBLEMS WITH PreparedStatement The `PreparedStatement` class may not work if the database does not maintain an open connection between transactions; if the database does not support the use of compiled SQL; or if the JDBC drivers or the database drivers do not support this type of statement. If you use the statement with a JDBC driver that doesn't support it, you get a class exception. If you use the statement with a database or driver that does not support it, you get a `SQLException`. In my experience, the `PreparedStatement` class did not work with the ODBC driver for Sybase SQL Anywhere; it also did not work with the driver I had for Microsoft Access at the time this chapter was written. Using the `PreparedStatement` class resulted in the error `Invalid Cursor State`, as shown here:
>     java sample6
>     Values are: 1 Eagle T-Shirt 1 8.50 14.95 1 Black S null
>     Values are: 4 Wolf T-Shirt 1 8.50 14.95 1 Green S null
>     Values are: 5 Wolf T-Shirt 1 8.50 14.95 1 Green M null
>     Values are: 6 Wolf T-Shirt 1 8.50 14.95 1 Green L null
>     Values are: 19 Snake Shirt 1 13.50 22.00 1 Green S null
>     Values are: 20 Snake Shirt 1 13.50 22.00 1 Green M null
>     Values are: 21 Snake Shirt 1 13.50 22.00 1 Green L null
>     Values are: 22 Cat Shirt 1 8.00 13.00 1 Green XLG null
>     Values are: 2 Eagle T-Shirt 1 8.50 14.95 1 Black M null
>     Values are: 3 Eagle T-Shirt 1 8.50 14.95 1 Black L null
>     Values are: 7 Wolf T-Shirt 1 8.50 14.95 1 Blue L ADSDFS
>     Values are: 23 Get Wild Tie Dyed T-Shirt 1 10.50 20.95 1 null XL null
>     Values are: 24 Leopard T-Shirt 1 9.00 14.95 1 Brown L null
>     Values are: 30 Test 1 12.00 13.00 1 Orange S null
>     Values are: 40 Cats of the World T-Shirt 1 8.00 14.00 1 Black L null
>     [Sybase][ODBC Driver]Invalid cursor state
### The execute() Method The `execute()` method is defined for use with the `Statement` class, which also makes it available for use with the `PreparedStatement` and `CallableStatement` classes. This method is used to process an unknown statement, a statement that may return multiple result sets and update counts, or both. For example, you may have an ad-hoc SQL tool that allows the user to enter any valid SQL statement and then process the statement. The user passes in the SQL statement with a program call like this:
    // create Statement object
    Statement stmt = con.createStatement();
    String sqlstmt = arg[0];
    // run statement
    boolean b = stmt.execute(sqlstmt);
The result returned from the `execute()` method is a `boolean` value: it is `false` if there are no results or the statement contains an update; it is `true` if the statement returns at least one result set and no update counts. If your application cares only about processing result sets from a statement, you can process the results as follows:
    // if true, result set
            result = "";
            if (b) {
              // process results
              ResultSet rs = stmt.getResultSet();
              ResultSetMetaData rsmd = rs.getMetaData();
              int colcount = rsmd.getColumnCount();
              while(rs.next()) {
                    result = "";
                    for (int i=1; i <= colcount; i++) {
                       result+= rs.getString(i) + " ";
                       }
                    System.out.println("Values are: " + result);
                    }
               }
    . . .
Normally, however, your application wants to process all the return results--if only to provide feedback to the user. Listing 43.7 provides a complete example of using the `execute()` method. #### Listing 43.7. The sample7 application using the execute() method.
    import java.lang.*;
    import java.sql.*;
    import sun.jdbc.odbc.*;
    public class sample7
    {
    public static void main(String arg[]) {
    String result;
         try {
            //connect to ODBC database
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String url = "jdbc:odbc:Zoo";
            // connect
            Connection con = DriverManager.getConnection(url,"dba", "sql");
            // create Statement object
            Statement stmt = con.createStatement();
            String sqlstmt = arg[0];
            // run statement
            boolean b = stmt.execute(sqlstmt);
            // if true, result set
            result = "";
            if (b) {
              // process results
              ResultSet rs = stmt.getResultSet();
              ResultSetMetaData rsmd = rs.getMetaData();
              int colcount = rsmd.getColumnCount();
              while(rs.next()) {
                    result = "";
                    for (int i=1; i <= colcount; i++) {
                       result+= rs.getString(i) + " ";
                       }
                    System.out.println("Values are: " + result);
                    }
               }
             else {
                int ct = stmt.getUpdateCount();
                result = "Update count is " + result.valueOf(ct);
                System.out.println(result);
                }
            //close connection
            con.close();
            }
         catch(Exception e) {
               System.out.println(e.getMessage());
            }
    }
    }
Running the example and passing in the string `"Select * from retail_item"` returns this result (only the first few lines of the result are shown because the result is fairly lengthy):
    java sample7 "select * from retail_item"
    Values are: 1 Eagle T-Shirt 1 8.50 14.95 1 Black S null
    Values are: 2 Chocolate Tigers 5 1.00 1.50 3 null M null
    Values are: 3 Stuffed Panda 1 13.50 21.00 2 null L null
    Values are: 4 Wolf T-Shirt 1 8.50 14.95 1 Green S null
    Values are: 5 Wolf T-Shirt 1 8.50 14.95 1 Green M null
    Values are: 6 Wolf T-Shirt 1 8.50 14.95 1 Green L null
    Values are: 3 Keychain Zoo Pen 1 .52 1.95 8 null null null
    Values are: 8 Taffy Pulls 3 .75 1.25 3 null null null
    Values are: 9 Chocolate Pandas 5 1.00 1.50 3 null null null
    Values are: 10 Stuffed Giraffe 1 9.95 16.95 2 null null null
An application rarely wants to process an unknown statement, but you may want to run a stored procedure that has multiple result sets. This type of procedure is demonstrated in the next section. PROBLEMS WITH Using Cursors The `execute()` method requires the database to support cursors, which enable processing of multiple result sets. The `execute()` method also requires the database and database driver to support maintaining an open connection after a transaction. If the database does not support these features, the `execute()` method can result in an error such as `invalid cursor state` (as happened when I ran the `sample7` application with both the Sybase SQL Anywhere ODBC driver and the Microsoft Access ODBC driver). Before spending time coding for something that may not work, test your JDBC and database drivers with the `sample7` code in Listing 43.7. ### The CallableStatement Class The `CallableStatement` class is used to accept several parameters for a stored procedure call. The parameters can be defined as input only, output only, or both. `CallableStatement` is an extension of `PreparedStatement`, which is itself an extension of `Statement`. The `CallableStatement` class adds methods to register and access output parameters. To create a `CallableStatement` object, issue the procedure call with question marks (`?`) in place of parameters: `String scall = "call new_po(?,?,?,?,?,?,?,?,?,?)"; // create Statement object CallableStatement stmt = con.prepareCall(scall);` You must first set the `IN` parameters using the appropriate `set`XXX`()` methods:
    stmt.setInt(1, 1);
            stmt.setDate(2, dt);
            stmt.setInt(3,1);
            stmt.setDouble(4, 10.00);
            stmt.setString(5,"OP");
            stmt.setInt(6,1);
            stmt.setInt(7,1);
            stmt.setDouble(8, 10.00);
            stmt.setInt(9,61);
The last parameter is not set because it is an `OUT` parameter only. It must be registered using one of the `RegisterOutParameter()` methods: `stmt.registerOutParameter(10,java.sql.Types.INTEGER);` Because the `new_po()` procedure contains two `INSERT` commands and multiple result sets, in addition to the one output parameter, the execution method to use for this statement is `execute()`:
    stmt.registerOutParameter(10,java.sql.Types.INTEGER);
    // run statement
    stmt.execute();
The result sets and update counts are then processed. Listing 43.8 shows all the code that demonstrates the use of `CallableStatement` with two `insert` commands. #### Listing 43.8. The sample8 application using the CallableStatement class.
    import java.lang.*;
    import java.sql.*;
    import sun.jdbc.odbc.*;
    public class sample8
    {
    public static void main(String arg[]) {
    String result;
    Date dt = new Date(97, 12,1);
         try {
            //connect to ODBC database
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String url = "jdbc:odbc:Zoo";
            // connect
            Connection con = DriverManager.getConnection(url,"dba", "sql");
            String scall = "call new_po(?,?,?,?,?,?,?,?,?,?)";
            // create Statement object
            CallableStatement stmt = con.prepareCall(scall);
            stmt.setInt(1, 1);
            stmt.setDate(2, dt);
            stmt.setInt(3,1);
            stmt.setDouble(4, 10.00);
            stmt.setString(5,"OP");
            stmt.setInt(6,1);
            stmt.setInt(7,1);
            stmt.setDouble(8, 10.00);
            stmt.setInt(9,61);
            stmt.registerOutParameter(10,java.sql.Types.INTEGER);
            // run statement
            stmt.execute();
            // get update counts
            boolean cont = true;
            while(cont) {
                    int colcount = stmt.getUpdateCount();
                    if (colcount > 0) {
                            System.out.println("Updated rows are: " + colcount);
                            stmt.getMoreResults();
                            }
                    else {
                            cont = false;
                            }
                    }
            cont =true;
            result="";
            while(cont) {
                    ResultSet rs = stmt.getResultSet();
                    ResultSetMetaData rsms = rs.getMetaData();
                    int colcount = rsms.getColumnCount();
                    for (int i = 1; i <= colcount; i++) {
                      result+=rs.getString(i) + " ";
                      }
                    System.out.println("results are " + result);
                    result="";
                    cont =  stmt.getMoreResults();
                    }
            int po_count = stmt.getInt(10);
            System.out.println("Number of POS is " + po_count);
            //close connection
            con.close();
            }
         catch(Exception e) {
               System.out.println(e.getMessage());
            }
    }
    }
Unfortunately, when I use this code with the JDBC-ODBC bridge to access a Sybase SQL Anywhere database, I get the `Function Sequence Error` error:
    java sample8
    Updated rows are: 1
    [Microsoft] [ODBC Driver Manager] Function sequence error
This ODBC `DriverManager` error occurs because an asynchronously executing function is called when the function is still running from the first call. According to the ODBC documentation, this error occurs when a procedure call or SQL execution statement occurs while a previous call still requires data to be passed in from parameters. After examining the data in the tables, however, I did find that the updates had occurred. As a workaround to this problem, I modified the code (the modified example is in `sample8b.java`) to call a stored procedure that contains only the two updates and not the multiple result sets, as shown in Listing 43.9. #### Listing 43.9 The sample8b.java application containing the stored procedure call with two updates.
    import java.lang.*;
    import java.sql.*;
    import sun.jdbc.odbc.*;

    public class sample8b
    {

    public static void main(String arg[]) {
    String result;

    Date dt = new Date(97, 12,1);

         try {

            //connect to ODBC database
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String url = "jdbc:odbc:Zoo";

            // connect
            Connection con = DriverManager.getConnection(url,"dba", "sql");

            String scall = "call new_po_copy(?,?,?,?,?,?,?,?,?,?)";

            // create Statement object
            CallableStatement stmt = con.prepareCall(scall);

            stmt.setInt(1, 1);
            stmt.setDate(2, dt);
            stmt.setInt(3,1);
            stmt.setDouble(4, 10.00);
            stmt.setString(5,"OP");
            stmt.setInt(6,1);
            stmt.setInt(7,1);
            stmt.setDouble(8, 10.00);
            stmt.setInt(9,62);

            stmt.registerOutParameter(10,java.sql.Types.INTEGER);

            // run statement
            stmt.executeUpdate();

            int po_count = stmt.getInt(10);
            System.out.println("Number of POS is " + po_count);

            //close connection
            con.close();
            }
         catch(Exception e) {
               System.out.println(e.getMessage());
            }

    }
    }
This procedure is then used in an `executeUpdate()` call. The output parameter is printed after the update occurs. This version of the application worked without error and printed the number of purchase orders in the database, as returned in the `OUT` parameter. TIP: Chapter 42, "Databases and Java," describes a database utility tool, `dbutil`, that you can modify to get information about what the database, driver, and JDBC driver can process (such as multiple result sets). You should modify this tool to check what your database and drivers can do before you code your application. ## Working with Multiple Databases The last Java application created in this chapter is one that refreshes a table in an mSQL database with the contents of a table in a Microsoft Access database. This example demonstrates the ease with which you can open and maintain multiple database connections at the same time. The first part of the code loads the mSQL-JDBC driver classes and opens a connection to the mSQL database: `//connect to mSQL database Class.forName("imaginary.sql.iMsqlDriver"); String url = "jdbc:msql://yasd.com:1112/yasd"; // mSQL connection Connection con = DriverManager.getConnection(url); // mSQL statement Statement stmt = con.createStatement();` Next, an update is issued to delete the contents from the table being refreshed, in this case, the `company` table: `// clean out existing data stmt.executeUpdate("DELETE from company");` The Access database connection and statement is created next. Note that the mSQL database is remote and that the Access database is local to the application:
    //connect to Access database
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    url = "jdbc:odbc:cityzoo";
    // Access Connection
    Connection con2 = DriverManager.getConnection(url,"Admin","sql");
    // Access Statement
    Statement stmt2 = con2.createStatement();
    ResultSet rs = stmt2.executeQuery("Select company_id,"
            + "company_name, address_1, address_2, address_3,"
            + "city, state_cd, country_cd, postal_cd, phone_nbr "
            + "from company");
The data is pulled from the Access database as strings, which are then concatenated in to an insert string for the mSQL database, as shown in Listing 43.10. #### Listing 43.10. The code for sample9, which accesses two different databases at the same time.
    import java.sql.*;
    import sun.jdbc.odbc.*;
    import java.io.*;
    public class sample9
    {
    public static void main(String arg[]) {
    String invalue;
    String outvalue;
    String id, name, add1, add2, add3,city,state,country,post,phone;
    int pos, endpos;
            try {
                     System.out.println("Refreshing company...");
                    //connect to mSQL database
                    Class.forName("imaginary.sql.iMsqlDriver");
                    String url = "jdbc:msql://yasd.com:1112/yasd";
                    // mSQL connection
                    Connection con = DriverManager.getConnection(url);
                    // mSQL statement
                    Statement stmt = con.createStatement();
                    // clean out existing data
                    stmt.executeUpdate("DELETE from company");
                    //connect to Access database
                    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                    url = "jdbc:odbc:cityzoo";
                    // Access Connection
                    Connection con2 = DriverManager.getConnection(url,"Admin","sql");
                    // Access Statement
                    Statement stmt2 = con2.createStatement();
                    ResultSet rs = stmt2.executeQuery("Select company_id,"
                        + "company_name, address_1, address_2, address_3,"
                        + "city, state_cd, country_cd, postal_cd, phone_nbr "
                        + "from company");
                    // for each line, enter into database
                    while (rs.next()) {
                       // get id
                       id = rs.getString(1);
                       name = rs.getString(2);
                            add1= rs.getString(3);
                            add2=rs.getString(4);
                            add3=rs.getString(5);
                            city=rs.getString(6);
                            state=rs.getString(7);
                            country=rs.getString(8);
                            post=rs.getString(9);
                            phone=rs.getString(10);
                            // create and execute insert statement
                            stmt.executeUpdate("insert into company (company_id, company_name,"
                               + "address_1,address_2,address_3, "
                               + "city,state_cd,country_cd,postal_cd,phone_nbr) values("
                               + id + ",'" + name + "','" + add1 + "','"
                               + add2 + "','"
                               + add3 + "','" + city + "','" + state + "','"
                               + country + "','" + post +
                               "','" + phone + "')");
                            }
                            stmt.close();
                            stmt2.close();
                            rs.close();
                    }
                catch(Exception e) {
                     System.out.println(e.getMessage());
                     }
    }
    }
The `PreparedStatement` class would have been ideal for this type of operation, but the mSQL database does not support cursors, and the drivers for Microsoft Access do not maintain an open connection between transactions. However, the technique that was used works very well--and works fairly quickly, considering that the transactions occurred over a modem. As a reminder, if you are working with multiple databases at the same time, you must create separate connections and statements for each of the different databases. ## Summary This chapter demonstrates how to use the most common aspects of the JDBC; all the examples use the JDBC-ODBC bridge or the mSQL-JDBC driver. Most SQL statements can be processed with all drivers; however, some statements (such as cursor-based statements) may not work with all drivers or databases. You can usually find a workaround or use a different JDBC-ODBC driver. Using the JDBC is simple, as this chapter has shown. By using this Java API, you can run simple and complex queries and use all forms of database updates. In addition, the JDBC provides methods and classes that compile statements for later use (the `PreparedStatement` class), execute an unknown SQL statement (the `execute()` method), process multiple results and updates (the `execute()` method), and call procedures with parameters (the `CallableStatement` class). Refer to Chapter 19, "The SQL Package," for an overview of the JDBC API. You may also want to refer to Chapter 42, "Databases and Java," for an introduction to the JDBC and the beginnings of a handy database utility called `dbutil`. Chapter 44, "Building Database Applets with the JDBC," provides examples of using the JDBC in applets.
| ©Copyright, Macmillan Computer Publishing. All rights reserved.