Begin change

Example: Using JDBC 2.0 standard extensions

//////////////////////////////////////////////////////////////////////////////////
//
// JDBC 2.0 Standard Extension example.  This program uses an AS400JDBCRowSet object, an 
// AS400JDBCDataSource object, and an AS400JDBCConnectionPool object to create 
// and update a table.
//
// Command syntax:
//    JDBCStdExtExample system userId password collectionName tableName
//
// For example,
//    JDBCStdExtExample MySystem MyUserId MyPassword MyCollectionName MyTable
//
// Note: JDBC 2.0 standard extension classes and JNDI classes must be included 
// in your classpath to run this example.  These are not part of the Toolbox and 
// are available from Sun.
//
//////////////////////////////////////////////////////////////////////////////////
//
// This source is an example of AS/400 Toolbox for Java JDBC driver.
// IBM grants you a nonexclusive license to use this as an example
// from which you can generate similar function tailored to
// your own specific needs.
//
// This sample code is provided by IBM for illustrative purposes
// only. These examples have not been thoroughly tested under all
// conditions. IBM, therefore, cannot guarantee or imply
// reliability, serviceability, or function of these programs.
//
// All programs contained herein are provided to you "AS IS"
// without any warranties of any kind.  The implied warranties of
// merchantablility and fitness for a particular purpose are
// expressly disclaimed.
//
// AS/400 Toolbox for Java
// (C) Copyright IBM Corp. 2000
// All rights reserved.
// US Government Users Restricted Rights -
// Use, duplication, or disclosure restricted
// by GSA ADP Schedule Contract with IBM Corp.
//
//////////////////////////////////////////////////////////////////////////////////

import com.ibm.as400.access.*;
import java.sql.*;

public class JDBCStdExtExample
{
   // Strings to be added in the WORD column of the table.
   private static final String words[] 
   = { "One",      "Two",      "Three",    "Four",     "Five",
       "Six",      "Seven",    "Eight",    "Nine",     "Ten",
       "Eleven",   "Twelve",   "Thirteen", "Fourteen", "Fifteen",
       "Sixteen",  "Seventeen","Eighteen", "Nineteen", "Twenty"};

   // Strings to be added in the WORD column of the table.
   private static final String moreWords[]
   = { "Anteater", "Bat",      "Cat",    "Dog",     "Elephant",
       "Fox",      "Giraffe",  "Hippo",  "Iguana",  "Just",
       "Lion",     "Monkey",   "None",   "Or",      "Possum",
       "Quite",    "Rabbit",   "So",     "Tiger",   "Bye"};

   public static void main (String[] parameters)
   {
      // Check the input parameters.
      if (parameters.length != 5)
      {
         System.out.println("");
         System.out.println("Usage:");
         System.out.println("");
         System.out.println("   JDBCStdExtExample system userId password collectionName tableName");
         System.out.println("");
         System.out.println("");
         System.out.println("For example:");
         System.out.println("");
         System.out.println("");
         System.out.println("   JDBCStdExtExample MySystem MyUserId MyPassword MyLibrary MyTable");
         System.out.println("");
         return;
      }

      String system           = parameters[0];
      String userId           = parameters[1];
      String password         = parameters[2];
      String collectionName   = parameters[3];
      String tableName        = parameters[4];

      Connection connection   = null;

      try
      {
         // Create an AS400JDBCConnectionPoolDataSource with system.  Since we do not
         // provide a userId or password, a prompt will appear.  

         // A data source object can be created and used to create multiple AS400JDBCConnectionPool 
         // and AS400JDBCRowSet objects.  Data source objects can also be stored and 
         // retrieved using JNDI.

         AS400JDBCConnectionPoolDataSource dataSource = new AS400JDBCConnectionPoolDataSource(system, userId, password);

         // Set the JDBCDriver implementation to Toolbox.
         dataSource.setDriver("toolbox");

         // Set the datasource database name.
         dataSource.setDatabaseName(system);

         // Create a AS400JDBCConnectionPool.
         AS400JDBCConnectionPool pool = new AS400JDBCConnectionPool(dataSource);

         // Allow a maximum of 128 connections in the pool.
         pool.setMaxConnections(128);

         // Add 5 connections to the pool that can be used by the application.
         pool.fill(5);

         // Get a handle to a database connection from the pool.
         connection = pool.getConnection();

         // Drop the table if it already exists.

         try
         {
            Statement dropTable = connection.createStatement ();
            dropTable.executeUpdate ("DROP TABLE " + collectionName + "." + tableName);
         }
         catch (SQLException e)
         {
            // Ignore.
         }

         // Create the table.
         Statement createTable = connection.createStatement ();
         createTable.executeUpdate ("CREATE TABLE " + collectionName + "." + tableName
                              + " (I INTEGER, WORD VARCHAR(20), SQUARE INTEGER, "
                              + " SQUAREROOT DOUBLE)");

         // Prepare a statement for inserting rows.  Since we
         // execute this multiple times, it is best to use a
         // PreparedStatement and parameter markers.
         PreparedStatement insert = connection.prepareStatement ("INSERT INTO " + collectionName + "."
                                                   + tableName + " (I, WORD, SQUARE, SQUAREROOT) "
                                                   + " VALUES (?, ?, ?, ?)");
         // Populate the table.
         for (int i = 1; i <= words.length; ++i)
         {
            insert.setInt (1, i);
            insert.setString (2, words[i-1]);
            insert.setInt (3, i*i);
            insert.setDouble (4, Math.sqrt(i));
            insert.executeUpdate ();
         }

         // Output a completion message.
         System.out.println ("Table " + collectionName + "." + tableName
                        + " has been populated.");
         
         // Create an AS400JDBCRowSet.
              AS400JDBCRowSet rowset = new AS400JDBCRowSet("jdbc:as400://" + system, userId, password);

         // Set concurrency so updates can be made.
         rowset.setConcurrency(ResultSet.CONCUR_UPDATABLE);

         // Set the command used to select rows from the table.
         rowset.setCommand("SELECT * FROM " + collectionName + "." + tableName + " for update");

         // Populate the rowset.
         rowset.execute();

         // Update the words.
         for (int i=0; rowset.next(); i++)
         {
            String newWord = moreWords[i];
            rowset.updateString("WORD", newWord);
            rowset.updateRow();
         }

         // Output a completion message.
         System.out.println ("Table " + collectionName + "." + tableName
                        + " has been updated.");

         //   Close the connection handle to return it to the pool.
         connection.close();

         // Close the pool to release all resources.
         pool.close();
      }
      catch (Exception e)
      {
         System.out.println ();
         System.out.println ("ERROR: " + e.getMessage());
         e.printStackTrace();
      }

      finally
      {
         // Clean up.
         try
         {
            if (connection != null)
               connection.close ();
         }
         catch (SQLException e)
         {
            // Ignore.
         }
      }

      System.exit (0);
   }
}
End change