package examples.jdbc.mssqlserver4;

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

/**
 * This simple example shows how to create and execute stored
 * procedures, and get the results.
 * <p>
 * This example is run on the command line and is output to
 * System.out.
 * <p>
 * Please note that this example uses a simple method for obtaining
 * result sets, update counts, and/or the output parameter values it may
 * generate. This is always possible with simple, known queries.
 * When executing complex or unknown possibly multi-statement SQL
 * or stored procedures, you should use the example in <tt>complexproc.java</tt>
 * to ensure that all results become available.
 *<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 storedprocs.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.storedprocs</b></font>
 *
 *</ol>
 *
 * @author         Copyright (c) 1997-2000 by BEA Systems, Inc. All Rights Reserved.
 */
public class storedprocs {
  
  public static void main(String argv[])
       throws Exception
  {
      Properties props = new Properties();
      props.put("user", "sa");
      props.put("password", "secret");
      props.put("server", "myHost:1433");

    Driver myDriver = (Driver)
        Class.forName("weblogic.jdbc.mssqlserver4.Driver").newInstance();
    Connection conn = myDriver.connect("jdbc:weblogic:mssqlserver4", props);
    
    Statement stmt = conn.createStatement();
    try {stmt.execute("drop procedure proc_squareInt");} catch (SQLException e) {;}
    try {stmt.execute("drop procedure func_squareInt");} catch (SQLException e) {;}
    try {stmt.execute("drop procedure proc_getResults");} catch (SQLException e) {;}
    stmt.close();
    
    // Create a stored procedure
    Statement stmt1 = conn.createStatement();
    stmt1.execute("create procedure proc_squareInt (@field1 int, @field2 int output) as " + 
                  "begin select @field2 = @field1 * @field1 end");
    stmt1.close();
    
    CallableStatement cstmt1 = conn.prepareCall("{call proc_squareInt(?, ?)}");
    
    cstmt1.registerOutParameter(2, java.sql.Types.INTEGER);
    for (int i = 0; i < 100; i++) {
      cstmt1.setInt(1, i);
      cstmt1.execute();
      System.out.println(i + " " + cstmt1.getInt(2));
    }
    cstmt1.close();
    
    // Create a procedure with a return value
    Statement stmt2 = conn.createStatement();
    stmt2.execute("create procedure func_squareInt (@field1 int) as " +
                  "begin return @field1 * @field1 end");
    stmt2.close();
    
    CallableStatement cstmt2 = conn.prepareCall("{? = call func_squareInt(?)}");
    
    cstmt2.registerOutParameter(1, java.sql.Types.INTEGER);
    for (int i = 0; i < 100; i++) {
      cstmt2.setInt(2, i);
      cstmt2.execute();
      System.out.println(i + " " + cstmt2.getInt(1));
    }
    cstmt2.close();
    
    // Create a procedure with results
    Statement stmt3 = conn.createStatement();
    stmt3.execute("create procedure proc_getResults as " +
                  "begin select name from sysusers \n" +
                  "select gid from sysusers end");
    stmt3.close();
    
    CallableStatement cstmt3 = conn.prepareCall("{call proc_getResults()}");
    
    boolean hasResultSet = cstmt3.execute();
    while (hasResultSet) {
      ResultSet rs = cstmt3.getResultSet();
      while (rs.next())
        System.out.println("Value: " + rs.getString(1));
      rs.close();
      hasResultSet = cstmt3.getMoreResults();
    }
    
    cstmt3.close();
    conn.close();
  }
}
