package examples.jdbc.mssqlserver4;

import java.io.*;
import java.util.*;
import java.math.*;
import java.lang.*;
import java.net.*;
import java.sql.*;

/**
 * This example shows the correct method of retrieving rows,
 * update counts, procedure status, and/or output parameters
 * from complex stored procedures that may have multiples of
 * them.
 *
 *<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 complexproc.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.complexproc</b></font>
 *
 *</ol>
 *
 * @author      Copyright (c) 1997-2000 by BEA Systems, Inc. All Rights Reserved.
 */

public class complexproc
{
  public static void main(String argv[])
       throws Exception
  {
    java.sql.Connection conn  = null;
    java.util.Properties props = new java.util.Properties();
    props.put("user",     "sa");
    props.put("password", "secret");
    props.put("server",   "myHost:1433");
    
    Driver myDriver = (Driver)    
        Class.forName("weblogic.jdbc.mssqlserver4.Driver").newInstance();
    conn = myDriver.connect("jdbc:weblogic:mssqlserver4", props);
    
    String setup_proc =
      " if exists (select * from sysobjects where id = "
      + " object_id('dbo.sp_myname') and sysstat & 0xf = 4) "
      + " drop procedure dbo.sp_myname";
    
    String big_proc =
      " create procedure sp_myname @aname varchar(30) output as "
      + " begin                                                   "
      + "    declare @string varchar(30)                          "
      + "    select @aname = 'no name'                            "
      + "    declare @tmp_return_code INT                         "
      + "    declare str_cursor cursor for                        "
      + "       select name from master.dbo.sysdatabases          "
      + "       for read only                                     "
      + "    IF @@ERROR != 0                                      "
      + "    begin                                                "
      + "        RETURN @@ERROR                                   "
      + "    end                                                  "
      + "    open str_cursor                                      "
      + "    IF @@ERROR != 0                                      "
      + "    begin                                                "
      + "      RETURN @@ERROR                                     "
      + "    end                                                  "
      + "                                                         "
      + "    fetch str_cursor into @string                        "
      + "                                                         "
      + "    SELECT @tmp_return_code = @@ERROR                    "
      + "    IF @tmp_return_code != 0                             "
      + "    begin                                                "
      + "        GOTO WAY_OUT                                     "
      + "    end                                                  "
      + "                                                         "
      + "    WHILE (@@fetch_status = 0)                           "
      + "      BEGIN                                              "
      + "         FETCH str_cursor INTO @string                   "
      + "                                                         "
      + "          IF @@fetch_status = -1                         "
      + "          begin                                          "
      + "            select @tmp_return_code = 88                 "
      + "            GOTO WAY_OUT                                 "
      + "          end                                            "
      + "                                                         "
      + "          IF @@fetch_status = -2                         "
      + "          begin                                          "
      + "            select @tmp_return_code = 77                 "
      + "            GOTO WAY_OUT                                 "
      + "          end                                            "
      + "                                                         "
      + "          select @aname = @string                        "
      + "          SELECT @tmp_return_code = @@ERROR              "
      + "          IF @tmp_return_code != 0                       "
      + "          begin                                          "
      + "              RETURN @tmp_return_code                    "
      + "          end                                            "
      + "                                                         "
      + "       END                                               "
      + "                                                         "
      + " WAY_OUT:                                                "
      + "   CLOSE str_cursor                                      "
      + "   DEALLOCATE  str_cursor                                "
      + "   select @aname                                         "
      + "   return @tmp_return_code                               "
      + "                                                         "
      + " end                                                     ";
    
    Statement stmt = conn.createStatement();
    stmt.executeUpdate ( setup_proc );
    System.out.println("setup_proc executed\n");
    stmt.executeUpdate ( big_proc );
    CallableStatement cstmt1 = conn.prepareCall("{ ? = call sp_myname(?)}");
    
    cstmt1.registerOutParameter(1, java.sql.Types.INTEGER);
    cstmt1.registerOutParameter(2, java.sql.Types.VARCHAR);
    
    boolean hasResultSet = cstmt1.execute();
    
    while (true)
      {
	ResultSet rs    = cstmt1.getResultSet();
	int updateCount = cstmt1.getUpdateCount();
	
        // If there are no more results or counts, we're done.
        if (rs == null && updateCount == -1)
          break; 
      
        // Check to see if there is a ResultSet
        if (rs != null) {
          while (rs.next()) {
            System.out.println("Get first col by id:" + rs.getString(1));
          }
          rs.close();
        } // Otherwise, there will be an update count
        else {
          System.out.println("Update count = " + cstmt1.getUpdateCount());
        }
        cstmt1.getMoreResults();
      }
    
    // Best to retrieve status after all result sets and update counts
    // have been retrieved.
    System.out.println( "Output status: " + cstmt1.getInt(1));
    System.out.println( "Output param:  " + cstmt1.getString(2));

    cstmt1.close();
    
    try {conn.close();}    
    catch (Exception e) {;}
  }
}
