package examples.jdbc.mssqlserver4;

import java.io.*;
import java.sql.*;
import java.util.Properties;

/**
 *
 * This example creates a large table, retrieves its contents, and
 * then deletes the table.  
 * <p> 
 * The application demonstrates how to: 
 * <ul>
 * <li>create a table using a Statement 
 * <li>insert rows individually using separate Statements
 * <li>insert rows using PreparedStatements
 * <li>retrieve rows using Statements
 * <li>retrieve rows using a stored procedure (CallableStatement)
 * </ul>
 * <p>
 * <p>
 * The test creates a stored procedure, so you must use an account that has 
 * the required privileges.
 *<h3>To set up this example:</h3>
 *<ol>
 *<li>Set up your development shell as described in <a
 *href=Package-examples.jdbc.mssqlserver4.html>Package examples.jdbc.mssqlserver4</a>.
 *<p>
 *<li> Change the database URL, login, and password connection parameters
 *to correspond to your Microsoft SQL Server configuration.  If you need
 *more help, check the section on connecting to a database in the
 *Developers Guide, <a href=../../../classdocs/API_jmsq4.html>Using
 *WebLogic jDriver for Microsoft SQL Server</a>.
 *
 *<p>
 *
 *<li>Compile this example by executing the following command in your development shell:
 *
 *<p>
 *
 *<font face="Courier New"><b>$ javac -d c:/weblogic/mssqlserver4/classes LargeTableTests.java</b></font>
 *<p>	
 *
 *<li>Run this example by executing the following command in your development shell:
 *
 *<p>
 *
 *<font face="Courier New"><b>$ java examples.jdbc.mssqlserver4.LargeTableTests</b></font>
 *
 *</ol> * 
 * @author Copyright (c) 1997-2000 by BEA Systems, Inc. All Rights Reserved.  
 */

public class LargeTableTests
{
  // Database connection URL. Change to your own database and host.
  static String url = "jdbc:weblogic:mssqlserver4";             
  
  // Microsoft SQL Server driver class name.
  static String driverName = "weblogic.jdbc.mssqlserver4.Driver";
        
  // The number of rows to insert into the table.
  static int kNumberOfRows = 300;
  
  public static void main(String [] args)
  {
      // Change the username, password, and server to your database login account.  
      Properties props = new Properties();
      props.put("user", "sa");
      props.put("password", "secret");
      props.put("server", "myHost:1433");

      Driver myDriver = null;
      
    try
      {
        System.out.println("\n\nLarge table tests...\n");
        
        // Instantiate the JDBC driver, then create the database connection.
        myDriver = (Driver) Class.forName(driverName).newInstance();
        Connection connection = myDriver.connect(url, props);   
        
        System.out.println("Connected to " + url + " as " + connection + "\n");

        // Create a table with two columns, an integer and a string.

        try
          {
            String sql = "create table largeTable(number int,string varchar(255))";
            System.out.println("Creating largeTable table:\n\n" + sql + "\n");

            Statement s = connection.createStatement();                     

            // An exception is raised here if table can't be created.
            s.execute(sql);

            System.out.println("Created table.\n");

            // Close the statement, so its resources can be reclaimed. 
            s.close();
          }

        catch (SQLException se) 
          { 
            System.out.println("The table could not be created because " + se); 
          }

        // Insert rows in the table using a Statement object and a SQL
        // INSERT for each row.  In a real-world application it would
        // be more efficient to group a bunch of rows into a single
        // statement.

        int index = 1;
        try
          {
            System.out.println("Inserting rows in the table using a Statement object.");

            // Create a timer to see how long it takes.
            Timer aTimer = new Timer("Inserted in ");                       

            Statement s = connection.createStatement();                     

            for ( ; index <= (kNumberOfRows / 2) ; index++)
              {
                String sql = "insert into largeTable values(" + index + 
                  ",'" + String.valueOf(index) + "')";

                // Execute the INSERT statement. An exception is raised if the row wasn't added.
                // Should return 1 (the update count).
                int count = s.executeUpdate(sql);

                if (count != 1) 
                  throw new SQLException("One row should have been added. Count was " + 
                                         count + " instead of 1.");

                if (index % 20 == 0) 
                  System.out.println("  added " + index + " rows...");
              }

            s.close();
            
            // Print timer result.
            System.out.println("\n" + aTimer + "\n");
          }

        catch (SQLException se) 
          { 
            System.out.println("Failed while inserting rows because " + se); 
          }

        // Insert rows using a PreparedStatement and the SQL INSERT
        // command.

        try
          {
            System.out.println("Inserting entries in the table using a PreparedStatement.\n");

            Timer aTimer = new Timer("Inserted in ");

            PreparedStatement ps = 
              connection.prepareStatement("insert into largeTable values(?,?)");

            for ( ; index <= kNumberOfRows ; index++)
              {
                // Replace parameters with and integer and a string.
                ps.setInt(1,index);
                ps.setString(2,String.valueOf(index));

                // Execute the statement and get the update count. 
                int count = ps.executeUpdate();

                if (count != 1) 
                  throw new SQLException("One row should have been added. Count was " + 
                                         count + " instead of 1.");
                if (index % 20 == 0) 
                  System.out.println("  added " + index + " rows.");
              }
            System.out.println("\n" + aTimer + "\n");
            ps.close();
          }

        catch (SQLException se) 
          { 
            System.out.println("Failed while inserting rows because " + se); 
          }

        // Retrieve rows from the table using a single SELECT to get
        // the entire table at once.
        try
          {
            System.out.println("Selecting all rows from table using a Statement.\n");

            Timer aTimer = new Timer("Selected in ");                       
            // This is pretty fast because it's a single call to
            // executeQuery.

            Statement st = connection.createStatement(); 

            ResultSet rs = st.executeQuery("select number,string from largeTable order by number");

            for (index = 1 ; rs.next() && index <= kNumberOfRows ; index++) 
              {
                // Retrieve two columns from current row. 
                int number = rs.getInt(1);
                String string = rs.getString(2).trim();

                if (number != index || Integer.parseInt(string) != index)
                  {
                    throw new SQLException("Expecting " + index + ", but got " + 
                                           number + " " + string);
                  }
                if (index % 20 == 0 || index == kNumberOfRows) 
                  System.out.println("  selected " + index + " rows.");
              }
            
            // Check that all rows where read.
            if (index < (kNumberOfRows + 1))
              {
                System.out.println("Only " + index + " rows where selected. Expected " + 
                                   kNumberOfRows + ".");
              }

            st.close();
            System.out.println("\n" + aTimer + "\n");
          }

        catch (SQLException se) 
          { 
            System.out.println("Failed while selecting rows because " + se); 
          }

        // Create a stored procedure that returns the integer column
        // value given the string value. Use the stored procedure to
        // retrieve rows one per call.

        try
          {
            Statement s = connection.createStatement();

            s.execute("create procedure " + 
                      "largeTableProcedure(@parm1 integer output, " +
                      "@parm2 varchar(255)) as " + 
                      "select @parm1 = number from largeTable where string = @parm2");
            s.close();

            System.out.println("Created stored procedure.\n");

            // Now fetch each row in the table using the procedure.
            // This will take much longer since since each row is
            // selected individually.
            
            System.out.println("Selecting rows from table using CallableStatement.\n");

            Timer aTimer = new Timer("Selected in ");                       


            CallableStatement call = 
              connection.prepareCall("{call largeTableProcedure(?,?)}");

            // Register the the first parameter as an integer output parameter.
            call.registerOutParameter(1, Types.INTEGER);

            for (index = 1 ; index <= kNumberOfRows ; index++)
              {
                // Set the input parameter to the row index. 
                call.setString(2,String.valueOf(index));
                call.execute();
                // Process results. (There shouldn't be any.)
                dumpResults(call);

                // Get the output parameter value from the procedure.
                int number = call.getInt(1);

                // Output parameter should be the same sas the row index. 
                if (number != index)
                  {
                    throw new SQLException("Expecting " + index + " got " + number + " instead.");
                  }

                if (index % 20 == 0 || index == kNumberOfRows) 
                  System.out.println("  selected " + index + " rows.");
              }

            System.out.println("\n" + aTimer + "\n");

            call.close();
          }

        catch(SQLException se) 
          { 
            System.out.println("Failed while selecting rows because " + se); 
          }


        // Drop the table from the database.
        try
          {
            Statement s = connection.createStatement();
            s.execute("drop table largeTable");
            s.execute("drop procedure largeTableProcedure");
            s.close();

            System.out.println("largeTable was dropped.\n");
          }

        catch(SQLException se) 
          { 
            System.out.println("SQL exception raised: " + se);
          }

        // End of tests.
      }
     
    catch (Exception e) 
      { 
        System.out.println("An exception was raised " + e); 
      }
    catch (UnknownError e) 
      { 
        System.out.println("An exception was raised " + e + "\n"); 
        e.printStackTrace(); 
      }
    System.out.println("\n\n(Press Enter to quit)");

    // Wait for user input.
    try 
      { 
        System.in.read(); 
      }

    catch (IOException e) 
      { 
        return; 
      }
  }

  /** 
   * Drain result sets and update counts.
   *
   * @param statement, the Statement object, which has already been executed.
   * @exception SQLException
   */

  static void dumpResults(Statement statement) throws SQLException
  {
    for (boolean hasMore = true ; hasMore ; )
      {
        ResultSet resultSet = statement.getResultSet();
        if (statement.getMoreResults() == false)
          {
            hasMore = (statement.getUpdateCount() != -1);
          }
      }
  }
}

/**
 * Timer objects track elapsed time. This class is used by the LargeTableTests
 * sample application.
 *
 * @author Copyright (c) 1998-2000 by BEA Systems, Inc. All Rights Reserved.  
 **/

class Timer
{
  /*
   * Create a timer object to remember the time it was created.
   * @param iLabel, a String to describe the object.
   **/
  public Timer(String iLabel)
  {
    label = iLabel; 
    time = System.currentTimeMillis();
  }

  String label; 
  long time;

  /** 
   * Return number of milliseconds since the time was created.
   **/
  long getTime()
  {
    return System.currentTimeMillis() - time;
  }

  /**
   * Return timer label and elapsed time. 
   **/
  public String toString()
  {
    return label + getTime() + " ms";
  }
}
