package examples.jdbc.informix4;

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

/**
 *
 *This simple example shows how to create and execute a stored procedure
 * using an INFORMIX On-Line DBMS using JDBC.
 * <p>
 *<h3>To set up this example:</h3>
 *<ol>
 *<li>Set up your development shell as described in <a
 *href=Package-examples.jdbc.informix4.html>Package examples.jdbc.informix4</a>.
 *<p>
 *<li> Change connection parameters to correspond to your Informix
 *Dynamic Server configuration.  If you need more help, check the section on
 *connecting to a database in the Developers Guide, <a
 *href=../../../classdocs/API_jinf4.html>Using WebLogic jDriver for Informix</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/informix4/classes sprocs.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.informix4.sprocs</b></font>
 *<p>The program waits after displaying output and requires you to press enter to continue.
 *</ol>
 * 
 *  @author      Copyright (c) 1997-2000 by BEA Systems, Inc. All Rights Reserved.
 */
public class sprocs {

  /**
   * Runs the example from the command line.
   */
  public static void main(String argv[]) throws Exception {
    // Make a Connection

    Properties props = new Properties();
    props.put("user",     "informix");
    props.put("password", "secret");
    props.put("server",   "myDBHost"); // hostname on which Informix server runs
    props.put("port",     "1493");       // port on which Informix server listens
    props.put("db",       "myDB");  // name of database on Informix server
    
    Driver myDriver = (Driver) Class.forName("weblogic.jdbc.informix4.Driver").newInstance();
    Connection conn = myDriver.connect("jdbc:weblogic:informix4", props);

    // Create a Statement for executing queries
    Statement stmt = conn.createStatement();

    try {
      // Try dropping the table we want to create, in case it already
      // exists
      stmt.execute("drop table demo");
      System.out.println("Table demo dropped.");
    }
    catch (SQLException e) {
      System.out.println("Table demo doesn't need to be dropped.");
    }

    try {
      // Try dropping the table we want to create, in case it already
      // exists
      stmt.execute("drop procedure sp_demo");
      System.out.println("procedure sp_demo dropped.");
    }
    catch (SQLException e) {
      System.out.println("procedure sp_demo doesn't need to be dropped.");
    }

    // Create a table by executing a query
    stmt.execute("create table demo " +
      " ( int_col INT, v_col VARCHAR(5) )");
    get_line("Table demo created."+ " (press ENTER to continue...)");

    // The results of this query aren't a ResultSet, but rather the
    // number of rows inserted into the new table.
    int numrows = stmt.executeUpdate("insert into demo " +
      " values ( 1, '11111' )");
    get_line("Number of rows inserted = " + numrows + " (press ENTER to continue...)");

    numrows = stmt.executeUpdate("insert into demo " +
      " values ( 2, '22222' )");
    get_line("Number of rows inserted = " + numrows + " (press ENTER to continue...)");

    // now create a stored procedure
    numrows = stmt.executeUpdate("create procedure sp_demo(col1 INT, col2 VARCHAR(5)) " +
      "RETURNING INT; " +
      "DEFINE temp_int int; " +
      "insert into demo values(col1, col2); " +
      "let temp_int = col1; " +
      "return temp_int; " +
      "END PROCEDURE;");

    // prepare the sproc call
    // note that this sproc returns a parameter, but we do not retrieve
    // it as an OUTPUT parameter.  It will go into a ResultSet instead
    // INFORMIX sprocs do not have JDBC CallableStatement OUT Parameters
    CallableStatement cstmt = conn.prepareCall("{ call sp_demo('6', '66666')}");

    // execute the sproc
    boolean retbool = cstmt.execute();

    if (retbool == true) {
      get_line("sproc returned result set" + " (press ENTER to continue...)");
      ResultSet rrs = cstmt.getResultSet();
      if (rrs.next()) {
        String rrs_col1 = rrs.getString(1);
        get_line("retrieved result =" + rrs_col1 + " (press ENTER to continue...)");
      } else {
        get_line("expected to get result from sproc !");
      }
    } else {
      get_line("sproc did not return result set" + " (press ENTER to continue...)");
    }

    // Always close the Statement and the Connection when you've
    // finished working with them.
    stmt.close();
    cstmt.close();
    conn.close();
  }
  
    private static void get_line(String s)
  {
    int key;
    System.out.print(s);
    System.out.flush();
    try
      {
        while ((key=System.in.read()) != 10){}
      }
    catch (Exception ex){}
  }

}

