Java Code Examples for java.sql.CallableStatement#registerOutParameter()

/**
 * Tests the SYSIBM.BLOBRELEASELOCATOR stored procedure.
 *
 * @throws SQLException
 */
public void testBlobReleaseLocatorSP() throws SQLException {
    CallableStatement cs  = prepareCall
        ("CALL SYSIBM.BLOBRELEASELOCATOR(?)");
    cs.setInt(1, 1);
    cs.execute();
    cs.close();

    //once the locator has been released the BLOBGETLENGTH on that
    //locator value will throw an SQLException. This assures that
    //the locator has been properly released.

    cs  = prepareCall("? = CALL SYSIBM.BLOBGETLENGTH(?)");
    cs.registerOutParameter(1, java.sql.Types.BIGINT);
    cs.setInt(2, 1);
    try {
        cs.executeUpdate();
    } catch(SQLException sqle) {
        //on expected lines. The test was successful.
        return;
    }
    //The exception was not thrown. The test has failed here.
    fail("Error the locator was not released by SYSIBM.BLOBRELEASELOCATOR");
    cs.close();
}
 

private void useCase1MatchWorkload() throws SQLException {
  SharedCounters counters = UseCase1Blackboard.getInstance().getSharedCounters();
  int throttleMs = UseCase1Prms.getWorkloadThrottleMs();
  while (true) {
    // check with traffic cop
    terminateIfNeeded(counters);
    pauseIfNeeded(counters);

    // initialize the match stored procedure call
    CallableStatement callableStmt = this.connection.prepareCall(MATCH_CALL);
    callableStmt.registerOutParameter(3, Types.INTEGER);

    // run a workload batch
    long batchEnd = System.currentTimeMillis() + this.batchSeconds * 1000;
    while (System.currentTimeMillis() < batchEnd) {
      if (throttleMs != 0) { // throttle was set so choose each time at random
        MasterController.sleepForMs(UseCase1Prms.getWorkloadThrottleMs());
      }
      int index = this.rng.nextInt(0, clientAccounts.size() - 1);
      String clientAccount = clientAccounts.get(index);
      doSingleMatchWithOnePrimaryKey(callableStmt, clientAccount);
    }
    Log.getLogWriter().info("Completed a workload batch");
  }
}
 

/**
 * Tests the SYSIBM.BLOBRELEASELOCATOR stored procedure.
 *
 * @throws SQLException
 */
public void testBlobReleaseLocatorSP() throws SQLException {
    CallableStatement cs  = prepareCall
        ("CALL SYSIBM.BLOBRELEASELOCATOR(?)");
    cs.setInt(1, 1);
    cs.execute();
    cs.close();

    //once the locator has been released the BLOBGETLENGTH on that
    //locator value will throw an SQLException. This assures that
    //the locator has been properly released.

    cs  = prepareCall("? = CALL SYSIBM.BLOBGETLENGTH(?)");
    cs.registerOutParameter(1, java.sql.Types.BIGINT);
    cs.setInt(2, 1);
    try {
        cs.executeUpdate();
    } catch(SQLException sqle) {
        //on expected lines. The test was successful.
        return;
    }
    //The exception was not thrown. The test has failed here.
    fail("Error the locator was not released by SYSIBM.BLOBRELEASELOCATOR");
    cs.close();
}
 

@Override
public SqlTable query(String dataSource, String sql, Object[] args) {
    try {
        long time = System.currentTimeMillis();
        CallableStatement pstmt = newPreparedStatement(dataSource, Mode.Read, sql);
        setArgs(pstmt, args);
        int index = (validator.isEmpty(args) ? 0 : args.length) + 1;
        pstmt.registerOutParameter(index, Types.REF_CURSOR);
        pstmt.execute();
        SqlTable sqlTable = query((ResultSet) pstmt.getObject(index));
        pstmt.close();

        if (logger.isDebugEnable())
            logger.debug("执行SQL[{}:{}:{}:{}]检索操作。", dataSource, sql, converter.toString(args), System.currentTimeMillis() - time);

        return sqlTable;
    } catch (SQLException e) {
        logger.warn(e, "执行SQL[{}:{}:{}]检索时发生异常！", dataSource, sql, converter.toString(args));

        throw new RuntimeException(e);
    }
}
 

/**
 * Calls a SQL function that takes no input parameter and returns one 
 * output.
 * @throws SQLException 
 */
public void testNoInOneOutFunc() throws SQLException
{
    CallableStatement cs = prepareCall
        ("? = call NO_IN_ONE_OUT_FUNC()");
    cs.registerOutParameter (1, java.sql.Types.INTEGER);
    cs.execute();
    assertEquals("NO_IN_ONE_OUT_FUNC output value", 55, cs.getInt(1));

}
 

/**
 * Calls a SQL function that takes no input parameter and returns one 
 * output.
 * @throws SQLException 
 */
public void testNoInOneOutFunc() throws SQLException
{
    CallableStatement cs = prepareCall
        ("? = call NO_IN_ONE_OUT_FUNC()");
    cs.registerOutParameter (1, java.sql.Types.INTEGER);
    cs.execute();
    assertEquals("NO_IN_ONE_OUT_FUNC output value", 55, cs.getInt(1));

}
 

/**
 * Tests the SYSIBM.CLOBGETPOSITIONFROMLOCATOR stored procedure.
 *
 * @throws SQLException.
 */
public void testClobGetPositionFromLocatorSP() throws SQLException {
    int locator = 0;

    String newStr = "simple";

    CallableStatement cs  = prepareCall
        ("? = CALL SYSIBM.CLOBCREATELOCATOR()");
    cs.registerOutParameter(1, java.sql.Types.INTEGER);
    cs.executeUpdate();
    locator = cs.getInt(1);
    cs.close();

    cs  = prepareCall("CALL SYSIBM.CLOBSETSTRING(?,?,?,?)");
    cs.setInt(1, locator);
    cs.setInt(2, 1);
    cs.setLong(3, newStr.length());
    cs.setString(4, newStr);
    cs.execute();

    cs.close();
    cs  = prepareCall
        ("? = CALL SYSIBM.CLOBGETPOSITIONFROMLOCATOR(?,?,?)");
    cs.registerOutParameter(1, java.sql.Types.BIGINT);
    cs.setInt(2, 1);
    //find the position of the bytes corresponding to
    //the String simple in the test string.
    cs.setInt(3, locator);
    cs.setLong(4, 1L);
    cs.executeUpdate();
    //check to see that the returned position and the expected position
    //of the substring simple in the string are matching.
    assertEquals("Error SYSIBM.CLOBGETPOSITIONFROMLOCATOR returns " +
        "the wrong value for the position of the Clob", 8, cs.getLong(1));
    cs.close();
}
 

/**
 * Creates the native call graph view of a module.
 * 
 * @param connection Connection to the database.
 * @param moduleId ID of the BinNavi module where the view is created.
 * 
 * @return The ID of the created view.
 * 
 * @throws SQLException Thrown if creating the view failed.
 */
public static int createNativeCallgraphView(final CConnection connection, final int moduleId)
    throws SQLException {
  Preconditions.checkNotNull(connection, "IE00706: connection argument can not be null");
  final String query = "{ ? = call create_native_call_graph_view(?) }";
  final CallableStatement call = connection.getConnection().prepareCall(query);
  call.registerOutParameter(1, Types.INTEGER);
  call.setInt(2, moduleId);
  call.execute();
  return call.getInt(1);
}
 

protected static ResultSet[] callProcedureByCidRangePortfolio(Connection conn, String sql, 
    int cid1, int cid2, int sid, int tid, int[] data) throws SQLException { 
  ResultSet[] rs = new ResultSet[4];
  CallableStatement cs = null;
  cs = conn.prepareCall(sql);
  Log.getLogWriter().info(sql + " with cid1: " + cid1 + " and with cid2: " + cid2  +
      " with sid: " + sid + " and with tid: " + tid );
  cs.setInt(1, cid1);
  cs.setInt(2, cid2);
  cs.setInt(3, sid);
  cs.setInt(4, tid);
  cs.registerOutParameter(5, Types.INTEGER);
  cs.execute();
  data[0] = new Integer(cs.getInt(5));

  rs[0] = cs.getResultSet();
  int i=1;
  while (cs.getMoreResults(Statement.KEEP_CURRENT_RESULT)) {
    Log.getLogWriter().info("has more results");
    rs[i] = cs.getResultSet();
    i++;
  }
  if (rs == null) Log.getLogWriter().info("could not get result sets in callProcedureByCidRangePortfolio");
  
  SQLWarning warning = cs.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
  return rs;
}
 

public void testCallableStatementWithOutputParamBatch()
throws SQLException {

    println("Negative Callable Statement: " +
        "callable statement with output parameters in the batch");
    Statement s = createStatement();

    s.execute("CREATE PROCEDURE " +
        "takesString(OUT P1 VARCHAR(40), IN P2 INT) " +
        "EXTERNAL NAME '" + this.getClass().getName() + ".takesString'" +
    " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");

    CallableStatement cs = prepareCall("call takesString(?,?)");
    cs.registerOutParameter(1, Types.CHAR);
    cs.setInt(2, Types.INTEGER);
    try
    {
        cs.addBatch();
        if (usingEmbedded())
            fail("Expected to see error XJ04C");
        else if (usingDerbyNetClient()) {
            executeBatchCallableStatement(cs);
        }
    }
    catch (SQLException sqle)
    {
        // Check to be sure the exception is callback related
        assertSQLState("XJ04C", sqle);
    }

    cs.close();
    s.execute("drop procedure takesString");
    s.close();
    rollback();
}
 

/**
 * Tests fix for BUG#60816 - Cannot pass NULL to an INOUT procedure parameter
 * 
 * @throws Exception
 */
public void testBug60816() throws Exception {
    createProcedure("test60816_1", "(INOUT x INTEGER)\nBEGIN\nSET x = x + 1;\nEND");
    createProcedure("test60816_2", "(x INTEGER, OUT y INTEGER)\nBEGIN\nSET y = x + 1;\nEND");
    createProcedure("test60816_3", "(INOUT x INTEGER)\nBEGIN\nSET x = 10;\nEND");

    CallableStatement call = this.conn.prepareCall("{ call test60816_1(?) }");
    call.setInt(1, 1);
    call.registerOutParameter(1, Types.INTEGER);
    call.execute();
    assertEquals(2, call.getInt(1));

    call = this.conn.prepareCall("{ call test60816_2(?, ?) }");
    call.setInt(1, 1);
    call.registerOutParameter(2, Types.INTEGER);
    call.execute();
    assertEquals(2, call.getInt(2));

    call = this.conn.prepareCall("{ call test60816_2(?, ?) }");
    call.setNull(1, Types.INTEGER);
    call.registerOutParameter(2, Types.INTEGER);
    call.execute();
    assertEquals(0, call.getInt(2));
    assertTrue(call.wasNull());

    call = this.conn.prepareCall("{ call test60816_1(?) }");
    call.setNull(1, Types.INTEGER);
    call.registerOutParameter(1, Types.INTEGER);
    call.execute();
    assertEquals(0, call.getInt(1));
    assertTrue(call.wasNull());

    call = this.conn.prepareCall("{ call test60816_3(?) }");
    call.setNull(1, Types.INTEGER);
    call.registerOutParameter(1, Types.INTEGER);
    call.execute();
    assertEquals(10, call.getInt(1));

}
 

/**
 * Tests the stored procedure SYSIBM.BLOBSETBYTES
 * @throws UnsupportedEncodingException 
 *
 * @throws SQLException.
 */
public void testBlobSetBytes() throws SQLException, UnsupportedEncodingException {
    String newString = "123456789012345";
    byte [] newBytes = newString.getBytes("US-ASCII");
    //initialize the locator to a default value.
    int locator = -1;
    //call the stored procedure to return the created locator.
    CallableStatement cs  = prepareCall
        ("? = CALL SYSIBM.BLOBCREATELOCATOR()");
    cs.registerOutParameter(1, java.sql.Types.INTEGER);
    cs.executeUpdate();
    locator = cs.getInt(1);
    cs.close();

    //use this new locator to test the SETBYTES function
    //by inserting the new bytes and testing whether it has
    //been inserted properly.

    //Insert the new substring.
    cs  = prepareCall("CALL SYSIBM.BLOBSETBYTES(?,?,?,?)");
    cs.setInt(1, locator);
    cs.setLong(2, 1L);
    cs.setInt(3, newString.length());
    cs.setBytes(4, newBytes);
    cs.execute();
    cs.close();

    //check the new locator to see if the value has been inserted correctly.
    cs  = prepareCall("? = CALL " +
        "SYSIBM.BLOBGETBYTES(?,?,?)");
    cs.registerOutParameter(1, java.sql.Types.VARBINARY);
    cs.setInt(2, locator);
    cs.setLong(3, 1);
    cs.setInt(4, newString.length());
    cs.executeUpdate();
    byte [] retVal = cs.getBytes(1);
    //compare the new bytes and the bytes returned by the stored
    //procedure to see of they are the same.
    for (int i=0;i<newString.length();i++){
        assertEquals
            ("The Stored procedure SYSIBM.BLOBGETBYTES " +
            "returns the wrong bytes"
            , newBytes[i], retVal[i]);
    }
    cs.close();
}
 

/**
 * Tests fix for BUG#15464 - INOUT parameter does not store IN value.
 * 
 * @throws Exception
 *             if the test fails
 */

public void testBug15464() throws Exception {
    if (!serverSupportsStoredProcedures()) {
        return;
    }

    createProcedure("testInOutParam",
            "(IN p1 VARCHAR(255), INOUT p2 INT)\nbegin\n DECLARE z INT;\n" + "SET z = p2 + 1;\nSET p2 = z;\nSELECT p1;\nSELECT CONCAT('zyxw', p1);\nend\n");

    CallableStatement storedProc = null;

    storedProc = this.conn.prepareCall("{call testInOutParam(?, ?)}");

    storedProc.setString(1, "abcd");
    storedProc.setInt(2, 4);
    storedProc.registerOutParameter(2, Types.INTEGER);

    storedProc.execute();

    assertEquals(5, storedProc.getInt(2));
}
 

/**
 * Test for bug [983432] Prepared call doesn't work with jTDS 0.8
 */
public void testCallableRegisterOutParameter4()
   throws Exception
{
   // cleanup remains from last run
   dropProcedure( "rop4" );
   dropType( "T_INTEGER" );

   CallableStatement cstmt = con.prepareCall( "{call sp_addtype T_INTEGER, int, 'NULL'}" );
   Statement stmt = con.createStatement();

   try
   {
      cstmt.execute();
      cstmt.close();

      stmt.execute( "create procedure rop4 @data T_INTEGER OUTPUT as\r\n " + "begin\r\n" + "set @data = 1\r\n" + "end" );
      stmt.close();

      cstmt = con.prepareCall( "{call rop4(?)}" );

      cstmt.registerOutParameter( 1, Types.VARCHAR );
      cstmt.execute();

      assertEquals( cstmt.getInt( 1 ), 1 );
      assertTrue( !cstmt.wasNull() );
      cstmt.close();

      cstmt = con.prepareCall( "rop4 ?" );

      cstmt.registerOutParameter( 1, Types.VARCHAR );
      cstmt.execute();

      assertEquals( cstmt.getInt( 1 ), 1 );
      assertTrue( !cstmt.wasNull() );
      cstmt.close();
   }
   finally
   {
      // cleanup
      dropProcedure( "rop4" );
      dropType( "T_INTEGER" );
   }
}
 

/**
 * Update the static information about an existing run
 *
 * @param runId
 * @param runName
 * @param osName
 * @param productName
 * @param versionName
 * @param buildName
 * @param userNote
 * @param hostName
 * @param closeConnection
 * @throws DatabaseAccessException
 */
public void updateRun(
                       int runId,
                       String runName,
                       String osName,
                       String productName,
                       String versionName,
                       String buildName,
                       String userNote,
                       String hostName,
                       boolean closeConnection ) throws DatabaseAccessException {

    final String errMsg = "Unable to update run with name '" + runName + "' and id " + runId;

    // then start the run
    final int indexRowsUpdate = 9;

    CallableStatement callableStatement = null;
    try {
        refreshInternalConnection();

        callableStatement = connection.prepareCall("{ call sp_update_run(?, ?, ?, ?, ?, ?, ?, ?, ?) }");
        callableStatement.setInt(1, runId);
        callableStatement.setString(2, productName);
        callableStatement.setString(3, versionName);
        callableStatement.setString(4, buildName);
        callableStatement.setString(5, runName);
        callableStatement.setString(6, osName);
        callableStatement.setString(7, userNote);
        callableStatement.setString(8, hostName);
        callableStatement.registerOutParameter(indexRowsUpdate, Types.INTEGER);

        callableStatement.execute();
        if (callableStatement.getInt(indexRowsUpdate) != 1) {
            throw new DatabaseAccessException(errMsg);
        }
    } catch (Exception e) {
        throw new DatabaseAccessException(errMsg, e);
    } finally {
        if (closeConnection) {
            DbUtils.close(connection, callableStatement);
        } else {
            DbUtils.closeStatement(callableStatement);
        }
    }
}
 

public void testCallProcedureWithInAndOutParameter()
   throws SQLException  {
  
  setup();
  int number=2;
  CallableStatement cs = prepareCall("CALL PROCEDURE_INOUT_PARAMETERS(?, ?, ?)");
  cs.setInt(1, number);
  cs.registerOutParameter(2, java.sql.Types.VARCHAR);
  cs.setString(2, "INOUT_PARAMETER");
  cs.registerOutParameter(3, java.sql.Types.INTEGER);
  cs.execute();
  
  String[][] results=new String[2][1];
  results[0][0]="1";
  results[1][0]="1";
           
  int rsIndex=-1;
  do {
    ++rsIndex;
    int rowIndex=0;
    ResultSet rs = cs.getResultSet();
    ResultSetMetaData metaData = rs.getMetaData();
    int rowCount = metaData.getColumnCount();
    while (rs.next()) {
      String row="";
      for (int i = 1; i <=rowCount; ++i) {
        Object value = rs.getObject(i);
        row+=value.toString();          
      }
      if(rsIndex>1 || rowIndex>1) {
        fail("the result is not correct!");
      }
      if(!row.equals(results[rsIndex][rowIndex])) {
        fail("the result is not correct!");
      }
      ++rowIndex;
    }
  } while (cs.getMoreResults());    
  
  String outValue=cs.getString(2);
  String outParameter="INOUT_PARAMETER"+"Modified";
  if(!outValue.equals(outParameter)) {
    fail("the out parameter is supposed to "+outParameter+" but "+outValue);
  }
  
  int parameter3=cs.getInt(3);    
  if(parameter3!=number) {
    fail("the out parameter is supposed to "+number+" but "+parameter3);
  }
           
}
 

public void testQueryTimeOutStoredProc_2() throws Exception {
  startVMs(1, 2);
  Connection cxn = TestUtil.getConnection() ;
  Statement stmt = cxn.createStatement();
  
  // create a procedure
  stmt.execute("CREATE PROCEDURE myProc2 " 
      + "(IN timOutOnCallableStmt INTEGER, "
      + " OUT count INTEGER)" 
      + "LANGUAGE JAVA PARAMETER STYLE JAVA "
      + "READS SQL DATA " + "DYNAMIC RESULT SETS 2 " + "EXTERNAL NAME '"
      + QueryTimeOutDUnit.class.getName() + ".myProc2'");
  
  stmt.execute("create table MyTable(x int, y int) partition by column(x)");
  stmt.execute("insert into MyTable values (1, 1), (2, 2), (3, 3), " +
          "(4, 4), (5, 5), (6, 6), (7, 7)");
  
  // set a large timeout on outer callable stmt so that it does not timeout
  int timOutOnCallableStmt = 10; // seconds
  CallableStatement callableStmt = cxn
       // first param to myProc2- timeOut to be set on callable stmt
      // CALL myProc2(10, ?) ON TABLE MyTable 
      .prepareCall("{CALL myProc2(" + timOutOnCallableStmt +  
              ", ?) ON TABLE MyTable}");
  callableStmt.registerOutParameter(1, Types.INTEGER);

  callableStmt.setQueryTimeout(timOutOnCallableStmt);
  
  // suspend the execution to allow query get timed out
  CacheSerializableRunnable csr = new CacheSerializableRunnable(
      "_testTimeOut_") {
    @Override
    public void run2() {
      GemFireXDQueryObserver old = GemFireXDQueryObserverHolder
          .setInstance(new GemFireXDQueryObserverAdapter() {
            @Override
            public void onGetNextRowCoreOfBulkTableScan(
                com.pivotal.gemfirexd.internal.iapi.sql.ResultSet resultSet) {
              try {
                Thread.sleep(1500);
              } catch (InterruptedException e) {
              }
            }
          });
    }
  };
  // clientExecute(1, csr);
  serverExecute(1, csr);
  serverExecute(2, csr);
  
  addExpectedException(new int[] { 1 }, new int[] { 1, 2 },
      SQLException.class);
  try {
    callableStmt.execute();
    fail("This test should have thrown exception "
        + "due to query timeout (exception state XCL52)");
  } catch (SQLException se) {
    if (!se.getSQLState().equals("XCL52")) {
      throw se;
    }
    assertEquals(10, callableStmt.getQueryTimeout());
  } finally {
    removeExpectedException(new int[] { 1 }, new int[] { 1, 2 },
        SQLException.class);
  }
}
 

/**
 * Verify correct mapping of clobs.
 */
public void testClobMapping() throws Exception
{
    Connection conn = getConnection();
    PreparedStatement ps;
    CallableStatement cs;
    Clob outVal;

    //
    // Clob input parameter
    //
    ps = chattyPrepare
        (
         conn,
         "create procedure clobIn\n" +
         "( in c clob, out result varchar( 100 ) )\n" +
         "language java\n" +
         "parameter style java\n" +
         "no sql\n" +
         "external name '" + getClass().getName() + ".clobIn'\n"
         );
    ps.execute();
    ps.close();

    cs = chattyPrepareCall( conn, "call clobIn( cast( 'def' as clob ), ? )" );
    cs.registerOutParameter( 1, Types.VARCHAR );
    cs.execute();
    assertEquals( "def", cs.getString( 1 ) );
    cs.close();

    cs = chattyPrepareCall( conn, "call clobIn( ?, ? )" );
    cs.setClob( 1, new StringColumnVTI.SimpleClob( "ghi" ) );
    cs.registerOutParameter( 2, Types.VARCHAR );
    cs.execute();
    assertEquals( "ghi", cs.getString( 2 ) );
    cs.close();

    //
    // Clob output parameter
    //
    ps = chattyPrepare
        (
         conn,
         "create procedure clobOut\n" +
         "( out c clob )\n" +
         "language java\n" +
         "parameter style java\n" +
         "no sql\n" +
         "external name '" + getClass().getName() + ".clobOut'\n"
         );
    ps.execute();
    ps.close();
    
    cs = chattyPrepareCall( conn, "call clobOut( ? )" );
    cs.registerOutParameter( 1, Types.CLOB );
    cs.execute();
    outVal = cs.getClob( 1 );
    assertEquals( "abc", outVal.getSubString( 1L, (int) outVal.length() ) );
    cs.close();

    //
    // Clob inout parameter
    //
    ps = chattyPrepare
        (
         conn,
         "create procedure clobInOut\n" +
         "( inout c clob )\n" +
         "language java\n" +
         "parameter style java\n" +
         "no sql\n" +
         "external name '" + getClass().getName() + ".clobInOut'\n"
         );
    ps.execute();
    ps.close();
    
    cs = chattyPrepareCall( conn, "call clobInOut( ? )" );
    cs.setClob( 1, new StringColumnVTI.SimpleClob( "ghi" ) );
    cs.registerOutParameter( 1, Types.CLOB );
    cs.execute();
    outVal = cs.getClob( 1 );
    assertEquals( "ihg", outVal.getSubString( 1L, (int) outVal.length() ) );

    Clob inValue = makeBigClob();
    cs.setClob( 1, inValue );
    cs.execute();
    Clob outValue = cs.getClob( 1 );
    compareClobs( inValue, outValue );
    
    cs.close();
}
 

public void testBug43576() throws Exception {
    createTable("TMIX91P",
            "(F01SMALLINT         SMALLINT NOT NULL, F02INTEGER          INTEGER,F03REAL             REAL,"
                    + "F04FLOAT            FLOAT,F05NUMERIC31X4      NUMERIC(31,4), F06NUMERIC16X16     NUMERIC(16,16), F07CHAR_10          CHAR(10),"
                    + " F08VARCHAR_10       VARCHAR(10), F09CHAR_20          CHAR(20), F10VARCHAR_20       VARCHAR(20), F11DATE         DATE,"
                    + " F12DATETIME         DATETIME, PRIMARY KEY (F01SMALLINT))");

    this.stmt.executeUpdate("INSERT INTO TMIX91P VALUES (1,1,1234567.12,1234567.12,111111111111111111111111111.1111,.111111111111111,'1234567890',"
            + "'1234567890','CHAR20CHAR20','VARCHAR20ABCD','2001-01-01','2001-01-01 01:01:01.111')");

    this.stmt.executeUpdate("INSERT INTO TMIX91P VALUES (7,1,1234567.12,1234567.12,22222222222.0001,.99999999999,'1234567896','1234567896','CHAR20',"
            + "'VARCHAR20ABCD','2001-01-01','2001-01-01 01:01:01.111')");

    this.stmt.executeUpdate("INSERT INTO TMIX91P VALUES (12,12,1234567.12,1234567.12,111222333.4444,.1234567890,'2234567891','2234567891','CHAR20',"
            + "'VARCHAR20VARCHAR20','2001-01-01','2001-01-01 01:01:01.111')");

    createProcedure("MSQSPR100",
            "\n( p1_in  INTEGER , p2_in  CHAR(20), OUT p3_out INTEGER, OUT p4_out CHAR(11))\nBEGIN "
                    + "\n SELECT F01SMALLINT,F02INTEGER, F11DATE,F12DATETIME,F03REAL \n FROM TMIX91P WHERE F02INTEGER = p1_in; "
                    + "\n SELECT F02INTEGER,F07CHAR_10,F08VARCHAR_10,F09CHAR_20 \n FROM TMIX91P WHERE  F09CHAR_20 = p2_in ORDER BY F02INTEGER ; "
                    + "\n SET p3_out  = 144; \n SET p4_out  = 'CHARACTER11'; \n SELECT p3_out, p4_out; END");

    String sql = "{call MSQSPR100(1,'CHAR20',?,?)}";

    CallableStatement cs = this.conn.prepareCall(sql);

    cs.registerOutParameter(1, Types.INTEGER);
    cs.registerOutParameter(2, Types.CHAR);

    cs.execute();
    cs.close();

    createProcedure("bug43576_1", "(OUT nfact VARCHAR(100), IN ccuenta VARCHAR(100),\nOUT ffact VARCHAR(100),\nOUT fdoc VARCHAR(100))\nBEGIN"
            + "\nSET nfact = 'ncfact string';\nSET ffact = 'ffact string';\nSET fdoc = 'fdoc string';\nEND");

    createProcedure("bug43576_2", "(IN ccuent1 VARCHAR(100), IN ccuent2 VARCHAR(100),\nOUT nfact VARCHAR(100),\nOUT ffact VARCHAR(100),"
            + "\nOUT fdoc VARCHAR(100))\nBEGIN\nSET nfact = 'ncfact string';\nSET ffact = 'ffact string';\nSET fdoc = 'fdoc string';\nEND");

    Properties props = new Properties();
    props.put("jdbcCompliantTruncation", "true");
    props.put("useInformationSchema", "true");
    Connection conn1 = null;
    conn1 = getConnectionWithProps(props);
    try {
        CallableStatement callSt = conn1.prepareCall("{ call bug43576_1(?, ?, ?, ?) }");
        callSt.setString(2, "xxx");
        callSt.registerOutParameter(1, java.sql.Types.VARCHAR);
        callSt.registerOutParameter(3, java.sql.Types.VARCHAR);
        callSt.registerOutParameter(4, java.sql.Types.VARCHAR);
        callSt.execute();

        assertEquals("ncfact string", callSt.getString(1));
        assertEquals("ffact string", callSt.getString(3));
        assertEquals("fdoc string", callSt.getString(4));

        CallableStatement callSt2 = conn1.prepareCall("{ call bug43576_2(?, ?, ?, ?, ?) }");
        callSt2.setString(1, "xxx");
        callSt2.setString(2, "yyy");
        callSt2.registerOutParameter(3, java.sql.Types.VARCHAR);
        callSt2.registerOutParameter(4, java.sql.Types.VARCHAR);
        callSt2.registerOutParameter(5, java.sql.Types.VARCHAR);
        callSt2.execute();

        assertEquals("ncfact string", callSt2.getString(3));
        assertEquals("ffact string", callSt2.getString(4));
        assertEquals("fdoc string", callSt2.getString(5));

        CallableStatement callSt3 = conn1.prepareCall("{ call bug43576_2(?, 'yyy', ?, ?, ?) }");
        callSt3.setString(1, "xxx");
        // callSt3.setString(2, "yyy");
        callSt3.registerOutParameter(2, java.sql.Types.VARCHAR);
        callSt3.registerOutParameter(3, java.sql.Types.VARCHAR);
        callSt3.registerOutParameter(4, java.sql.Types.VARCHAR);
        callSt3.execute();

        assertEquals("ncfact string", callSt3.getString(2));
        assertEquals("ffact string", callSt3.getString(3));
        assertEquals("fdoc string", callSt3.getString(4));
    } finally {
        conn1.close();
    }
}
 

/**
 * Tests fix for BUG#15464 - INOUT parameter does not store IN value.
 * 
 * @throws Exception
 *             if the test fails
 */

public void testBug15464() throws Exception {

    createProcedure("testInOutParam",
            "(IN p1 VARCHAR(255), INOUT p2 INT)\nbegin\n DECLARE z INT;\n" + "SET z = p2 + 1;\nSET p2 = z;\nSELECT p1;\nSELECT CONCAT('zyxw', p1);\nend\n");

    CallableStatement storedProc = null;

    storedProc = this.conn.prepareCall("{call testInOutParam(?, ?)}");

    storedProc.setString(1, "abcd");
    storedProc.setInt(2, 4);
    storedProc.registerOutParameter(2, Types.INTEGER);

    storedProc.execute();

    assertEquals(5, storedProc.getInt(2));
}