package examples.jdbc.mssqlserver4;

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

/** This example shows how to create, call and remove a stored
 * procedure from a Microsoft SQL Server.
 *
 * <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. The example creates a stored procedure, so make sure
 *your account has privileges to do that. 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 ProcedureTests.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.ProcedureTests</b></font>
 *
 *</ol>
 *  
 */
public class ProcedureTests
{
  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
      {
        String driverName = "weblogic.jdbc.mssqlserver4.Driver";
 
        // Connection URL. Change to your own database, host, and port.
        String url = "jdbc:weblogic:mssqlserver4";    

        System.out.println("\n\nStored procedure tests...\n");

        // Instantiate the jdbc driver then connect to the database
        myDriver = (Driver) Class.forName(driverName).newInstance();
        Connection connection = myDriver.connect(url, props);   

        System.out.println("Connection established with " + url + " as " + connection + "\n");

        /*
          Create the stored procedure with this SQL text:

          procedure simpleProcedure(@par1 varchar(255) output, 
          @par2 varchar(255) output, @par3 varchar(255)) as
          select @par1 = @par1 + ' plus something'
          select @par2 = @par3 + ' plus something different'
          return 5 */
        try
          {
            String sql = "create procedure simpleProcedure"
              + "(@par1 varchar(255) output, @par2 varchar(255) output, @par3 varchar(255)) as\n"
              + "select @par1 = @par1 + ' plus something'\n"
              + "select @par2 = @par3 + ' plus something different'\n"
              + "return 5\n";

            System.out.println("Creating simpleProcedure. Here's the SQL:\n\n" + sql);

            Statement s = connection.createStatement();
            s.execute(sql);
            System.out.println("Stored procedure was created.\n");
          }

        catch(SQLException se)
          {
            System.out.println("The procedure was not created because " + se);
          }

        try
          {
            // Call the procedure with a result and 3 parameters
            System.out.println("Calling simpleProcedure with result only...\n");

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

            call.registerOutParameter(1,Types.INTEGER);     // Result is out parameter 1
            call.registerOutParameter(2,Types.VARCHAR);     // Parameter 2 is in and out
            call.setString(2,"Some text");                  // Set value for parameter 2
            call.registerOutParameter(3,Types.VARCHAR);     // Parameter 3 is out only (no in value)
            
            call.setString(4,"3.14159265358979312"); // Parameter 4 is
            // in only (let's use a number with plenty of digits)
            call.execute();

            // Notice that before we can get the output parameters using
            // any of the getXXX methods we make sure all update counts
            // and/or results sets returned by the statement were read.
            // To do this we call dumpResults which will just skip through
            // them. We don't care about the actual results for this test.

            // Dump result counts and result sets (we're only testing
            // procedure parameters)
            dumpResults(call); 
            
            System.out.println("Procedure result is: " + call.getInt(1));
            System.out.println("Procedure @par1 is '" + call.getString(2) + "'");
            System.out.println("Procedure @par2 is '" + call.getString(3) + "'\n");
          }

        catch(SQLException se)
          {
            System.out.println("Failed while calling the procedure because " + se);
          }

        try
          {
            // Call the procedure with a result and 3 parameters
            System.out.println("Calling simpleProcedure with 3 parameters and a result...\n");

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

            call.registerOutParameter(1,Types.INTEGER);     // Result is out parameter 1
            call.registerOutParameter(2,Types.VARCHAR);     // Parameter 2 is in and out
            call.setString(2,"Some text");                  // Set value for parameter 2
            // Parameter 3 is out only (no in value)
            call.registerOutParameter(3,Types.VARCHAR); 
            call.setString(4,"3.14159265358979312");        // Parameter 4 is in only

            call.execute();

            // Before we can get the output parameters using any of
            // the getXXX methods we make sure all update counts
            // and/or results sets returned by the statement were
            // read.  To do this we call dumpResults which just skips
            // through them. We don't care about the actual results.

            // Dump result counts and result sets.  We're only testing
            // procedure parameters.
            dumpResults(call); 
            
            System.out.println("Procedure result is: " + call.getInt(1));
            System.out.println("Procedure @par1 is '" + call.getString(2) + "'");
            System.out.println("Procedure @par2 is '" + call.getString(3) + "'\n");
          }

        catch(SQLException se)
          {
            System.out.println("Failed while calling the procedure because " + se);
          }

        try
          {
            // Call the procedure with a result and 3 parameters. One
            // of the parameters comes from a stream as well.
            System.out.println("Calling simpleProcedure with 3 parameters and no result...\n");

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

            // Parameter 1 is both IN and OUT
            call.registerOutParameter(1,Types.VARCHAR); 
            
            StringBufferInputStream stream = 
              new StringBufferInputStream("Some text coming from a stream");

            // Let's use a string stream for it
            call.setAsciiStream(1,stream,stream.available()); 

            // Parameter 2 is an OUT parameter only. It returns @par3
            // + some string.
            call.registerOutParameter(2,Types.VARCHAR); 

            // Parameter 3 is an IN parameter only (no OUT or return
            // value).
            call.setString(3,"something"); 

            call.execute();

            // Dump result counts and result sets (we're only testing procedure parameters)
            dumpResults(call);                                      
            
            System.out.println("Procedure @par1 is '" + call.getString(1) + "'");
            System.out.println("Procedure @par2 is '" + call.getString(2) + "'\n");
          }

        catch(SQLException se)
          {
            System.out.println("Failed while calling the procedure because " + se);
          }

        // Remove the stored procedure from the database.
        try
          {
            Statement s = connection.createStatement(); 
            
            // This fails if procedure was not defined
            s.execute("drop procedure simpleProcedure");            
            s.close();

            System.out.println("Procedure was dropped from the database.\n");
          }

        catch(SQLException se) { }
      }

    catch(Exception e)
      {
        System.out.println("An exception was raised " + e);
      }

    System.out.println("\n\n(press Enter to exit)");

    try { System.in.read(); }
    catch (IOException e) { return; }
  }

  /** 
   * Skips through result sets and/or update counts. 
   * @param statement, the executed statement.
   */
  static void dumpResults(Statement statement) throws SQLException
  {
    for(boolean hasMore = true ; hasMore ; )
      {
        ResultSet resultSet = statement.getResultSet();

        if(statement.getMoreResults() == false)
          {
            hasMore = (statement.getUpdateCount() != -1);
          }
      }
  }
}
