public ResultSet getResultSet(CallableStatement ps) throws SQLException {
   boolean isResultSet = ps.execute();
   while (!isResultSet && ps.getUpdateCount() != -1) {
     isResultSet = ps.getMoreResults();
   }
   return ps.getResultSet();
 }
  @Override
  public void performAction(Connection connection, OperationObserver observer)
      throws SQLException, Exception {

    ProcedureTranslator transl = createTranslator(connection);
    CallableStatement statement = (CallableStatement) transl.createStatement();

    try {
      // stored procedure may contain a mixture of update counts and result sets,
      // and out parameters. Read out parameters first, then
      // iterate until we exhaust all results
      boolean hasResultSet = statement.execute();

      // local observer to cache results and provide them to the external observer
      // in the order consistent with other adapters.

      Observer localObserver = new Observer(observer);

      // read query, using local observer

      while (true) {
        if (hasResultSet) {
          ResultSet rs = statement.getResultSet();
          try {
            RowDescriptor descriptor = describeResultSet(rs, processedResultSets++);
            readResultSet(rs, descriptor, query, localObserver);
          } finally {
            try {
              rs.close();
            } catch (SQLException ex) {
            }
          }
        } else {
          int updateCount = statement.getUpdateCount();
          if (updateCount == -1) {
            break;
          }
          dataNode.getJdbcEventLogger().logUpdateCount(updateCount);
          localObserver.nextCount(query, updateCount);
        }

        hasResultSet = statement.getMoreResults();
      }

      // read out parameters to the main observer ... AFTER the main result set
      // TODO: I hope SQLServer does not support ResultSets as OUT parameters,
      // otherwise
      // the order of custom result descriptors will be messed up
      readProcedureOutParameters(statement, observer);

      // add results back to main observer
      localObserver.flushResults(query);
    } finally {
      try {
        statement.close();
      } catch (SQLException ex) {

      }
    }
  }
 public ResultSet getResultSet(CallableStatement ps) throws SQLException {
   boolean isResultSet = ps.execute();
   //		 This assumes you will want to ignore any update counts
   while (!isResultSet && ps.getUpdateCount() != -1) {
     isResultSet = ps.getMoreResults();
   }
   //		 You may still have other ResultSets or update counts left to process here
   //		 but you can't do it now or the ResultSet you just got will be closed
   return ps.getResultSet();
 }
  /** @since 3.0 */
  @Override
  protected long longPkFromDatabase(DataNode node, DbEntity entity) throws Exception {
    // handle CAY-588 - get connection that is separate from the connection in the
    // current transaction.

    // TODO (andrus, 7/6/2006) Note that this will still work in a pool with a single
    // connection, as PK generator is invoked early in the transaction, before the
    // connection is grabbed for commit... So maybe promote this to other adapters in
    // 3.0?

    Transaction transaction = Transaction.getThreadTransaction();
    Transaction.bindThreadTransaction(null);

    try {

      Connection connection = node.getDataSource().getConnection();
      try {
        CallableStatement statement = connection.prepareCall("{call auto_pk_for_table(?, ?)}");
        try {
          statement.setString(1, entity.getName());
          statement.setInt(2, super.getPkCacheSize());

          // can't use "executeQuery"
          // per http://jtds.sourceforge.net/faq.html#expectingResultSet
          statement.execute();
          if (statement.getMoreResults()) {
            ResultSet rs = statement.getResultSet();

            try {
              if (rs.next()) {
                return rs.getLong(1);
              } else {
                throw new CayenneRuntimeException(
                    "Error generating pk for DbEntity " + entity.getName());
              }
            } finally {
              rs.close();
            }
          } else {
            throw new CayenneRuntimeException(
                "Error generating pk for DbEntity "
                    + entity.getName()
                    + ", no result set from stored procedure.");
          }
        } finally {
          statement.close();
        }
      } finally {
        connection.close();
      }
    } finally {
      Transaction.bindThreadTransaction(transaction);
    }
  }
  public void testProc1() throws Exception {
    Statement stmt = con.createStatement();
    dropProcedure(stmt, "#spTestExec");
    dropProcedure(stmt, "#spTestExec2");

    stmt.executeUpdate(" create procedure #spTestExec2 as " + "select 'Did it work?' as Result");
    stmt.executeUpdate(
        "create procedure #spTestExec as "
            + "set nocount off "
            + "create table #tmp ( Result varchar(50) ) "
            + "insert #tmp execute #spTestExec2 "
            + "select * from #tmp");
    stmt.close();

    CallableStatement cstmt = con.prepareCall("#spTestExec");
    assertFalse(cstmt.execute());
    assertEquals(1, cstmt.getUpdateCount());

    // The JDBC-ODBC driver does not return update counts from stored
    // procedures so we won't, either.
    //
    // SAfe Yes, we will. It seems like that's how it should work. The idea
    //      however is to only return valid update counts (e.g. not from
    //      SET, EXEC or such).
    assertTrue(cstmt.getMoreResults());

    boolean passed = false;
    ResultSet rs = cstmt.getResultSet();
    while (rs.next()) {
      passed = true;
    }
    assertTrue("Expecting at least one result row", passed);
    assertTrue(!cstmt.getMoreResults() && cstmt.getUpdateCount() == -1);
    cstmt.close();
    // stmt.executeQuery("execute spTestExec");
  }
  /**
   * Execute the desired Callable statement and then call dispResultSet to display the rows and
   * columns
   *
   * @param stmt CallableStatement object to be processed
   * @exception SQLException .
   */
  public void displayRows(CallableStatement stmt) throws SQLException {

    boolean results = stmt.execute();
    int rsnum = 0; // Number of Result Sets processed
    int rowsAffected = 0;
    do {
      if (results) {
        ResultSet rs = stmt.getResultSet();
        output("\n\nDisplaying ResultSet: " + rsnum + "\n");
        dispResultSet(rs);
        rsnum++;
        rs.close();
      } else {
        rowsAffected = stmt.getUpdateCount();
        if (rowsAffected >= 0) output(rowsAffected + " rows Affected.\n");
      }
      results = stmt.getMoreResults();
    } while (results || rowsAffected != -1);
  }
  /** @param args */
  public static void main(String[] args) throws Exception {
    // Chargement du driver
    Class.forName("com.mysql.jdbc.Driver");
    //		Driver driver = new com.mysql.jdbc.Driver();
    //		DriverManager.registerDriver(driver);

    // Connexion au SGBD
    java.sql.Connection connection =
        DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "");
    // Creation de la procedure
    Statement statement = connection.createStatement();
    statement.executeUpdate("DROP PROCEDURE IF EXISTS rechercherNom");
    statement.executeUpdate(
        "CREATE PROCEDURE rechercherNom(IN leNom VARCHAR(50))\n"
            + "BEGIN\n"
            + " SELECT * FROM Annuaire WHERE nom = leNom;\n"
            + " SELECT COUNT(*) FROM Annuaire WHERE nom = leNom;\n"
            + "END\n");
    // Appel a la procedure stockee
    CallableStatement callableStatement = connection.prepareCall("{call rechercherNom(?)}");
    // Passage de kane comme valeur du premier parametre
    callableStatement.setString(1, "kane");
    boolean resultOK = callableStatement.execute();
    if (resultOK) {
      // Recuperation des ResultSet
      ResultSet resultSet1 = callableStatement.getResultSet();
      callableStatement.getMoreResults(Statement.KEEP_CURRENT_RESULT);
      ResultSet resultSet2 = callableStatement.getResultSet();
      // Traitement des informations
      while (resultSet1.next()) {
        for (int i = 0; i < resultSet1.getMetaData().getColumnCount(); i++) {
          System.out.print(resultSet1.getObject(i + 1) + " ,");
        }
        System.out.println("");
      }
      resultSet2.next();
      System.out.println("Nombre de lignes = " + resultSet2.getObject(1));
      resultSet1.close();
      resultSet2.close();
    }
  }
  public static void main(String[] args) {
    Connection conn = null;
    ResultSet rs = null;
    CallableStatement cStmt = null;
    String dbURL = "jdbc:mysql://localhost/data_mining_assignment";

    try {
      // Load our driver into memory
      Class.forName("com.mysql.jdbc.Driver");

      // load credentials for SQL connection
      Properties config = new Properties();
      FileInputStream in = new FileInputStream("C:\\data-minning-config.properties");
      config.load(in);

      // establish connection to DB
      conn = DriverManager.getConnection(dbURL, config);

      // Create our procedure call and execute
      cStmt = conn.prepareCall("{CALL getBinnedPoints()}");
      boolean hadResults = cStmt.execute();

      // while we have results to process
      while (hadResults) {
        // load the results into a result set
        rs = cStmt.getResultSet();
        // while data still in the result set
        while (rs.next()) {
          // read in each column
          String team = rs.getString("Team");
          String league = rs.getString("League");
          String season = rs.getString("Season");
          int spend = rs.getInt("Spend");
          int income = rs.getInt("Income");
          int nett = rs.getInt("Nett");
          String points = rs.getString("Points");
          int position = rs.getInt("Position");

          // just print the data for now
          System.out.print("Team: " + team);
          System.out.print(" League: " + league);
          System.out.print(" Season: " + season);
          System.out.print(" Spend: " + spend);
          System.out.print(" Income: " + income);
          System.out.print(" Nett: " + nett);
          System.out.print(" Points: " + points);
          System.out.println(" Position: " + position);
        }
        // check if the procedure returns any more results... it never will :p
        hadResults = cStmt.getMoreResults();
      }

    }
    // catch any SQL exceptions
    catch (SQLException ex) {
      System.out.println("SQLException: " + ex.getMessage());
      System.out.println("SQLState: " + ex.getSQLState());
      System.out.println("VendorError: " + ex.getErrorCode());
    }
    // catch any exception with loading driver
    catch (Exception e) {
      e.printStackTrace();
    }
    // clean up
    finally {
      // close the result set
      if (rs != null) {
        try {
          rs.close();
          System.out.println("Closed Result Set");
        } catch (SQLException sqlEx) {
        }
      }
      rs = null;
      // close our procedure
      if (cStmt != null) {
        try {
          cStmt.close();
          System.out.println("Closed Statment");
        } catch (SQLException sqlEx) {
        }
        cStmt = null;
      }
      // close our connection
      if (conn != null) {
        try {
          conn.close();
          System.out.println("Closed Connection");
        } catch (SQLException sqlEx) {
        }
        conn = null;
      }
    }
  }
 /** @see java.sql.Statement#getMoreResults(int) */
 public boolean getMoreResults(int current) throws SQLException {
   return original.getMoreResults(current);
 }
 /** @see java.sql.Statement#getMoreResults() */
 public boolean getMoreResults() throws SQLException {
   return original.getMoreResults();
 }
  public void testProc2() throws Exception {
    Statement stmt = con.createStatement();
    String sqlwithcount =
        "create procedure #multi1withcount as "
            + "  set nocount off "
            + "  select 'a' "
            + "  select 'b' "
            + "  create table #multi1withcountt (A VARCHAR(20)) "
            + "  insert into #multi1withcountt VALUES ('a') "
            + "  insert into #multi1withcountt VALUES ('a') "
            + "  insert into #multi1withcountt VALUES ('a') "
            + "  select 'a' "
            + "  select 'b' ";
    String sqlnocount =
        "create procedure #multi1nocount as "
            + "  set nocount on "
            + "  select 'a' "
            + "  select 'b' "
            + "  create table #multi1nocountt (A VARCHAR(20)) "
            + "  insert into #multi1nocountt VALUES ('a') "
            + "  insert into #multi1nocountt VALUES ('a') "
            + "  insert into #multi1nocountt VALUES ('a') "
            + "  select 'a' "
            + "  select 'b' ";
    dropProcedure(stmt, "#multi1withcount");
    dropProcedure(stmt, "#multi1nocount");
    stmt.executeUpdate(sqlwithcount);
    stmt.executeUpdate(sqlnocount);
    stmt.close();

    CallableStatement cstmt = con.prepareCall("#multi1nocount");
    assertTrue(cstmt.execute());
    ResultSet rs = cstmt.getResultSet();
    assertTrue(rs.next());
    assertTrue(rs.getString(1).equals("a"));
    assertTrue(!rs.next());
    assertTrue(cstmt.getMoreResults());
    rs = cstmt.getResultSet();
    assertTrue(rs.next());
    assertTrue(rs.getString(1).equals("b"));
    assertTrue(!rs.next());
    assertTrue(cstmt.getMoreResults());
    rs = cstmt.getResultSet();
    assertTrue(rs.next());
    assertTrue(!rs.next());
    assertTrue(cstmt.getMoreResults());
    rs = cstmt.getResultSet();
    assertTrue(rs.next());
    assertTrue(!rs.next());
    assertTrue(!cstmt.getMoreResults() && cstmt.getUpdateCount() == -1);
    cstmt.close();

    cstmt = con.prepareCall("#multi1withcount");

    // The JDBC-ODBC driver does not return update counts from stored
    // procedures so we won't, either.
    //
    // SAfe Yes, we will. It seems like that's how it should work. The idea
    //      however is to only return valid update counts (e.g. not from
    //      SET, EXEC or such).
    assertTrue(cstmt.execute());
    rs = cstmt.getResultSet();
    assertTrue(rs.next());
    assertTrue(rs.getString(1).equals("a"));
    assertTrue(!rs.next());
    assertTrue(cstmt.getMoreResults());
    rs = cstmt.getResultSet();
    assertTrue(rs.next());
    assertTrue(rs.getString(1).equals("b"));
    assertTrue(!rs.next());
    assertTrue(!cstmt.getMoreResults() && cstmt.getUpdateCount() == 1); // insert
    assertTrue(!cstmt.getMoreResults() && cstmt.getUpdateCount() == 1); // insert
    assertTrue(!cstmt.getMoreResults() && cstmt.getUpdateCount() == 1); // insert
    assertTrue(cstmt.getMoreResults()); // select
    rs = cstmt.getResultSet();
    assertTrue(rs.next());
    assertTrue(!rs.next());
    assertTrue(cstmt.getMoreResults());
    rs = cstmt.getResultSet();
    assertTrue(rs.next());
    assertTrue(!rs.next());
    assertTrue(!cstmt.getMoreResults() && cstmt.getUpdateCount() == -1);
    cstmt.close();
  }
Exemple #12
0
  /**
   * WL#6406 - Stacked diagnostic areas
   *
   * <p>"STACKED" in "GET [CURRENT | STACKED] DIAGNOSTICS" syntax was added in 5.7.0. Final behavior
   * was implemented in version 5.7.2, by WL#5928 - Most statements should clear the diagnostic
   * area.
   *
   * @throws SQLException
   */
  public void testGetStackedDiagnostics() throws Exception {

    if (!versionMeetsMinimum(5, 7, 2)) {
      return;
    }

    // test calling GET STACKED DIAGNOSTICS outside an handler
    final Statement locallyScopedStmt = this.stmt;
    assertThrows(
        SQLException.class,
        "GET STACKED DIAGNOSTICS when handler not active",
        new Callable<Void>() {
          public Void call() throws Exception {
            locallyScopedStmt.executeQuery("GET STACKED DIAGNOSTICS @num = NUMBER");
            return null;
          }
        });

    // test calling GET STACKED DIAGNOSTICS inside an handler
    // (stored procedure is based on documentation example)
    createTable("testGetStackedDiagnosticsTbl", "(c VARCHAR(8) NOT NULL)");
    createProcedure(
        "testGetStackedDiagnosticsSP",
        "() BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN "
            + "GET CURRENT DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO, @msg = MESSAGE_TEXT; "
            + "SELECT 'current DA before insert in handler' AS op, @errno AS errno, @msg AS msg; " // 1st result
            + "GET STACKED DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO, @msg = MESSAGE_TEXT; "
            + "SELECT 'stacked DA before insert in handler' AS op, @errno AS errno, @msg AS msg; " // 2nd result
            + "INSERT INTO testGetStackedDiagnosticsTbl (c) VALUES('gnitset'); "
            + "GET CURRENT DIAGNOSTICS @num = NUMBER; "
            + "IF @num = 0 THEN SELECT 'INSERT succeeded, current DA is empty' AS op; " // 3rd
                                                                                        // result
            + "ELSE GET CURRENT DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO, @msg = MESSAGE_TEXT; "
            + "SELECT 'current DA after insert in handler' AS op, @errno AS errno, @msg AS msg; END IF; "
            + "GET STACKED DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO, @msg = MESSAGE_TEXT; "
            + "SELECT 'stacked DA after insert in handler' AS op, @errno AS errno, @msg AS msg; END; " // 4th result
            + "INSERT INTO testGetStackedDiagnosticsTbl (c) VALUES ('testing');INSERT INTO testGetStackedDiagnosticsTbl (c) VALUES (NULL); END");

    CallableStatement cStmt = this.conn.prepareCall("CALL testGetStackedDiagnosticsSP()");
    assertTrue(cStmt.execute());

    // test 1st ResultSet
    this.rs = cStmt.getResultSet();
    assertTrue(this.rs.next());
    assertEquals("current DA before insert in handler", this.rs.getString(1));
    assertEquals(1048, this.rs.getInt(2));
    assertEquals("Column 'c' cannot be null", this.rs.getString(3));
    assertFalse(this.rs.next());
    this.rs.close();

    // test 2nd ResultSet
    assertTrue(cStmt.getMoreResults());
    this.rs = cStmt.getResultSet();
    assertTrue(this.rs.next());
    assertEquals("stacked DA before insert in handler", this.rs.getString(1));
    assertEquals(1048, this.rs.getInt(2));
    assertEquals("Column 'c' cannot be null", this.rs.getString(3));
    assertFalse(this.rs.next());
    this.rs.close();

    // test 3rd ResultSet
    assertTrue(cStmt.getMoreResults());
    this.rs = cStmt.getResultSet();
    assertTrue(this.rs.next());
    assertEquals("INSERT succeeded, current DA is empty", this.rs.getString(1));
    assertFalse(this.rs.next());
    this.rs.close();

    // test 4th ResultSet
    assertTrue(cStmt.getMoreResults());
    this.rs = cStmt.getResultSet();
    assertTrue(this.rs.next());
    assertEquals("stacked DA after insert in handler", this.rs.getString(1));
    assertEquals(1048, this.rs.getInt(2));
    assertEquals("Column 'c' cannot be null", this.rs.getString(3));
    assertFalse(this.rs.next());
    this.rs.close();

    // no more ResultSets
    assertFalse(cStmt.getMoreResults());
    cStmt.close();

    // test table contents
    this.rs = this.stmt.executeQuery("SELECT * FROM testGetStackedDiagnosticsTbl");
    assertTrue(this.rs.next());
    assertEquals("testing", this.rs.getString(1));
    assertTrue(this.rs.next());
    assertEquals("gnitset", this.rs.getString(1));
    assertFalse(this.rs.next());
    this.rs.close();
  }
  public void sampleCode() {

    //  Variables for simple test case
    String procname = "sp_timestampSample";

    /* *CREATE PROCEDURE*
       String createQuery1 =
           "create table spt_timestampSample(f1 int, f2 char(5), f3 timestamp )";
       String insertQuery1 =
           "insert spt_timestampSample(f1,f2) values(1, 'Hello')";

       // Sample Stored Procedure
       String dropProc = "drop proc " + procname;
       String createProc =
           "create proc " + procname +
           "(@p1 int, @p2 timestamp out)" +
           " as " +
           "select 'p1='  + convert(varchar(10),@p1) "  +
           "select @p2 = f3 from spt_timestampSample where f1=1"   +
           "select * from spt_timestampSample " +
           "return 21";
    */
    String sproc = "{? = call " + procname + "(?,?)}";
    try {
      /* *CREATE PROCEDURE*
         // We will create a temp table which contains a timestamp column
         // and we will insert a row.  We will then execute a stored
         // procedure which will returnt the timestamp column as an OUTPUT
         // parameter

         // Create our table
         execDDL( createQuery1);

         // Insert our row
         execDDL( insertQuery1);

         // Now create the Proc
         execDDL( createProc);
      */

      // Now execute our Sproc
      CallableStatement cstmt = _con.prepareCall(sproc);
      output("Executing: " + sproc + "\n");

      // Declare the IN Params.  Note, you must skip the Return Status
      cstmt.setInt(2, 1961);

      // Now declare our OUT Params
      cstmt.registerOutParameter(1, Types.INTEGER);
      cstmt.registerOutParameter(3, Types.VARBINARY);

      boolean results = cstmt.execute();
      int rsnum = 0; // Number of Result Sets processed
      int rowsAffected = 0;

      do {
        if (results) {
          ResultSet rs = cstmt.getResultSet();
          output("\n\nDisplaying ResultSet: " + rsnum + "\n");
          dispResultSet(rs);
          rsnum++;
          rs.close();
        } else {
          rowsAffected = cstmt.getUpdateCount();
          if (rowsAffected >= 0) output(rowsAffected + " rows Affected.\n");
        }
        results = cstmt.getMoreResults();
      } while (results || rowsAffected != -1);

      String s = cstmt.getString(1);
      String s2 = cstmt.getString(3);

      // Now grab the same output parameter as VARBINARY
      byte[] ts = cstmt.getBytes(3);

      // Display the Output Parameters
      output("OUT Param1=" + s + "\n");
      output("OUT Param2 as String=" + s2 + "\n");
      output("OUT Param2 as byte[]=" + toHexString(ts) + "\n");

      cstmt.close();

      /* *CREATE PROCEDURE*
         // Drop our sproc
         execDDL( dropProc);
      */
    } catch (SQLException ex) {
      displaySQLEx(ex);
    } catch (java.lang.Exception ex) {
      // Got some other type of exception.  Dump it.
      ex.printStackTrace();
    }
  }