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

The following examples show how to use java.sql.CallableStatement#setBigDecimal() . These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source Project: gemfirexd-oss   File: CallableTest.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * Calls a SQL procedure with BigDecimal IN and OUT parameters.
 * Excluded from JSR169/j2ME, which doesn't support get/set BigDecimal yet.
 * @throws SQLException 
 */
public void xtestBigDecimalInAndOutProc() throws SQLException
{
    CallableStatement cs = prepareCall
        ("CALL BIGDECIMAL_IN_AND_OUT_PROC (?, ?, ?, ?, ?, ?, ?, ?, ?)");
    cs.setBigDecimal(1, new BigDecimal("33.333"));
    cs.registerOutParameter (2, java.sql.Types.DECIMAL);
    cs.setBigDecimal(3, new BigDecimal("-999.999999"));
    cs.registerOutParameter (4, java.sql.Types.DECIMAL);
    cs.registerOutParameter (5, java.sql.Types.DECIMAL);
    cs.registerOutParameter (6, java.sql.Types.DECIMAL);
    cs.registerOutParameter (7, java.sql.Types.DECIMAL);
    cs.registerOutParameter (8, java.sql.Types.DECIMAL);
    cs.registerOutParameter (9, java.sql.Types.DECIMAL);
    cs.execute();

    assertDecimalSameValue("OUT 2", "33.3330",        cs.getBigDecimal(2));
    assertDecimalSameValue("OUT 4", "-33332.9966",    cs.getBigDecimal(4));
    assertDecimalSameValue("OUT 5", "-966.6669",      cs.getBigDecimal(5));
    assertDecimalSameValue("OUT 6", "0.0000",         cs.getBigDecimal(6));
    assertDecimalSameValue("OUT 7", "0.0000",         cs.getBigDecimal(7));
    assertDecimalSameValue("OUT 8", "99999999.0000",  cs.getBigDecimal(8));
    assertDecimalSameValue("OUT 9", "-99999999.0000", cs.getBigDecimal(9));
}
 
Example 2
Source Project: gemfirexd-oss   File: DAProcedures.java    License: Apache License 2.0 6 votes vote down vote up
protected void callProceduresUpdateGfxdPortfolioWrongSG(Connection conn, 
    String proc) {
  //String sql = update trade.portfolio set subTotal=? where qty > ?
  String wrongSG = SQLDAPTest.getWrongServerGroups("portfolio");
  // wrong SG is null only occurs when schema created on default servers
  // and the table inherits from schema server groups
  if (wrongSG == null) {
    Log.getLogWriter().info("Not able to perform, as porfolio is created on default server groups");
    return; 
  }
  int qty = SQLTest.random.nextInt(2000);
  BigDecimal subTotal = new BigDecimal (Double.toString(( SQLTest.random.nextInt(10000)+1) 
      * .01)).multiply(new BigDecimal(String.valueOf(qty)));
  String sql = "{call " + proc + "(?, ?)" +
    " ON SERVER GROUPS (" + wrongSG + ")}";
  Log.getLogWriter().info("call gfxd procedure " + sql + " with subtotal: " + subTotal +
     " and qty:" + qty);
  try {
    CallableStatement cs = null;
    cs = conn.prepareCall(sql);
    cs.setBigDecimal(1, subTotal);
    cs.setInt(2, qty);
  } catch (SQLException se) {
    checkGFGFXDException(se);
  }  
}
 
Example 3
Source Project: gemfirexd-oss   File: DAProcedures.java    License: Apache License 2.0 6 votes vote down vote up
protected static void callProcedureByCidRangePortfolioUpdate(Connection conn, String sql, 
    int cid1, int cid2, BigDecimal subTotal, int tid) throws SQLException { 
  CallableStatement cs = null;
  cs = conn.prepareCall(sql);
  Log.getLogWriter().info(sql + " with cid1: " + cid1 + " and with cid2: " + cid2  +
      " with subTotal: " + subTotal + " and with tid: " + tid );
  cs.setInt(1, cid1);
  cs.setInt(2, cid2);
  cs.setBigDecimal(3, subTotal);
  cs.setInt(4, tid);
  cs.execute();
 
  SQLWarning warning = cs.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
}
 
Example 4
Source Project: gemfirexd-oss   File: CallableTest.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * Calls a SQL procedure with BigDecimal IN and OUT parameters.
 * Excluded from JSR169/j2ME, which doesn't support get/set BigDecimal yet.
 * @throws SQLException 
 */
public void xtestBigDecimalInAndOutProc() throws SQLException
{
    CallableStatement cs = prepareCall
        ("CALL BIGDECIMAL_IN_AND_OUT_PROC (?, ?, ?, ?, ?, ?, ?, ?, ?)");
    cs.setBigDecimal(1, new BigDecimal("33.333"));
    cs.registerOutParameter (2, java.sql.Types.DECIMAL);
    cs.setBigDecimal(3, new BigDecimal("-999.999999"));
    cs.registerOutParameter (4, java.sql.Types.DECIMAL);
    cs.registerOutParameter (5, java.sql.Types.DECIMAL);
    cs.registerOutParameter (6, java.sql.Types.DECIMAL);
    cs.registerOutParameter (7, java.sql.Types.DECIMAL);
    cs.registerOutParameter (8, java.sql.Types.DECIMAL);
    cs.registerOutParameter (9, java.sql.Types.DECIMAL);
    cs.execute();

    assertDecimalSameValue("OUT 2", "33.3330",        cs.getBigDecimal(2));
    assertDecimalSameValue("OUT 4", "-33332.9966",    cs.getBigDecimal(4));
    assertDecimalSameValue("OUT 5", "-966.6669",      cs.getBigDecimal(5));
    assertDecimalSameValue("OUT 6", "0.0000",         cs.getBigDecimal(6));
    assertDecimalSameValue("OUT 7", "0.0000",         cs.getBigDecimal(7));
    assertDecimalSameValue("OUT 8", "99999999.0000",  cs.getBigDecimal(8));
    assertDecimalSameValue("OUT 9", "-99999999.0000", cs.getBigDecimal(9));
}
 
Example 5
Source Project: gemfirexd-oss   File: DAProcedures.java    License: Apache License 2.0 6 votes vote down vote up
protected void callProceduresUpdateGfxdPortfolioWrongSG(Connection conn, 
    String proc) {
  //String sql = update trade.portfolio set subTotal=? where qty > ?
  String wrongSG = SQLDAPTest.getWrongServerGroups("portfolio");
  // wrong SG is null only occurs when schema created on default servers
  // and the table inherits from schema server groups
  if (wrongSG == null) {
    Log.getLogWriter().info("Not able to perform, as porfolio is created on default server groups");
    return; 
  }
  int qty = SQLTest.random.nextInt(2000);
  BigDecimal subTotal = new BigDecimal (Double.toString(( SQLTest.random.nextInt(10000)+1) 
      * .01)).multiply(new BigDecimal(String.valueOf(qty)));
  String sql = "{call " + proc + "(?, ?)" +
    " ON SERVER GROUPS (" + wrongSG + ")}";
  Log.getLogWriter().info("call gfxd procedure " + sql + " with subtotal: " + subTotal +
     " and qty:" + qty);
  try {
    CallableStatement cs = null;
    cs = conn.prepareCall(sql);
    cs.setBigDecimal(1, subTotal);
    cs.setInt(2, qty);
  } catch (SQLException se) {
    checkGFGFXDException(se);
  }  
}
 
Example 6
Source Project: gemfirexd-oss   File: DAProcedures.java    License: Apache License 2.0 6 votes vote down vote up
protected static void callProcedureByCidRangePortfolioUpdate(Connection conn, String sql, 
    int cid1, int cid2, BigDecimal subTotal, int tid) throws SQLException { 
  CallableStatement cs = null;
  cs = conn.prepareCall(sql);
  Log.getLogWriter().info(sql + " with cid1: " + cid1 + " and with cid2: " + cid2  +
      " with subTotal: " + subTotal + " and with tid: " + tid );
  cs.setInt(1, cid1);
  cs.setInt(2, cid2);
  cs.setBigDecimal(3, subTotal);
  cs.setInt(4, tid);
  cs.execute();
 
  SQLWarning warning = cs.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
}
 
Example 7
/**
 * Test for bug [1236078] Procedure doesn't get called for some BigDecimal
 * values - invalid bug.
 */
public void testBigDecimal()
   throws Exception
{
   dropProcedure( "dec_test2" );
   dropTable( "dec_test" );

   Statement stmt = con.createStatement();
   assertEquals( 0, stmt.executeUpdate( "CREATE TABLE dec_test (ColumnVC varchar(50) NULL, ColumnDec decimal(18,4) NULL)" ) );
   assertEquals( 0, stmt.executeUpdate( "CREATE PROCEDURE dec_test2 (@inVc varchar(32), @inBd decimal(18,4)) AS begin update dec_test set columnvc = @inVc, columndec = @inBd end" ) );
   assertEquals( 1, stmt.executeUpdate( "insert dec_test (columnvc, columndec) values (null, null)" ) );
   stmt.close();

   CallableStatement cstmt = con.prepareCall( "{call dec_test2 (?,?)}" );
   cstmt.setString( 1, "D: " + new java.util.Date() );
   cstmt.setBigDecimal( 2, new BigDecimal( "2.9E+7" ) );
   assertEquals( 1, cstmt.executeUpdate() );
   cstmt.close();
}
 
Example 8
/**
 * Calls a SQL procedure with BigDecimal IN and OUT parameters.
 * Excluded from JSR169/j2ME, which doesn't support get/set BigDecimal yet.
 * @throws SQLException 
 */
public void xtestBigDecimalInAndOutProc() throws SQLException
{
    CallableStatement cs = prepareCall
        ("CALL BIGDECIMAL_IN_AND_OUT_PROC (?, ?, ?, ?, ?, ?, ?, ?, ?)");
    cs.setBigDecimal(1, new BigDecimal("33.333"));
    cs.registerOutParameter (2, java.sql.Types.DECIMAL);
    cs.setBigDecimal(3, new BigDecimal("-999.999999"));
    cs.registerOutParameter (4, java.sql.Types.DECIMAL);
    cs.registerOutParameter (5, java.sql.Types.DECIMAL);
    cs.registerOutParameter (6, java.sql.Types.DECIMAL);
    cs.registerOutParameter (7, java.sql.Types.DECIMAL);
    cs.registerOutParameter (8, java.sql.Types.DECIMAL);
    cs.registerOutParameter (9, java.sql.Types.DECIMAL);
    cs.execute();

    assertDecimalSameValue("OUT 2", "33.3330",        cs.getBigDecimal(2));
    assertDecimalSameValue("OUT 4", "-33332.9966",    cs.getBigDecimal(4));
    assertDecimalSameValue("OUT 5", "-966.6669",      cs.getBigDecimal(5));
    assertDecimalSameValue("OUT 6", "0.0000",         cs.getBigDecimal(6));
    assertDecimalSameValue("OUT 7", "0.0000",         cs.getBigDecimal(7));
    assertDecimalSameValue("OUT 8", "99999999.0000",  cs.getBigDecimal(8));
    assertDecimalSameValue("OUT 9", "-99999999.0000", cs.getBigDecimal(9));
}
 
Example 9
Source Project: gemfirexd-oss   File: CallableTest.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * Calls a SQL procedure that takes numeric IN and OUT parameters.
 * Excluded from JSR169/j2ME, which doesn't support get/set BigDecimal yet.
 * @throws SQLException 
 */
public void xtestNumericTypesInAndOutProc() throws SQLException
{
    CallableStatement cs = prepareCall
        ("call NUMERIC_TYPES_IN_AND_OUT_PROC(?,?,?,?,?,?,?,?,?,?,?,?)");

    cs.setShort(1, (short) 3);
    cs.setInt(2, 4);
    cs.setLong(3, 5);
    cs.setFloat(4, (float) 6.0);
    cs.setDouble(5, 7.0);
    cs.setBigDecimal(6, new BigDecimal("88.88"));

    cs.registerOutParameter (7, java.sql.Types.SMALLINT);
    cs.registerOutParameter (8, java.sql.Types.INTEGER);
    cs.registerOutParameter (9, java.sql.Types.BIGINT);
    cs.registerOutParameter (10, java.sql.Types.REAL);
    cs.registerOutParameter (11, java.sql.Types.DOUBLE);
    cs.registerOutParameter (12, java.sql.Types.DECIMAL);

    cs.execute();

    assertEquals("OUT short", (short) 3, cs.getShort(7));
    assertEquals("OUT int"  , 4, cs.getInt(8));
    assertEquals("OUT long" , 5, cs.getLong(9));
    assertEquals("OUT float" , (float) 6.0, cs.getFloat(10), .0001);
    assertEquals("OUT double" , 7.0, cs.getDouble(11), .0001);
    assertDecimalSameValue("OUT decimal", "88.88", cs.getBigDecimal(12));
}
 
Example 10
/**
        *  test ParameterMetaData for Java procedures with DECIMAL parameters
 *
 * @exception SQLException if error occurs
        */
public void testParameterMetadataWithDECIMALParameters () throws SQLException {

	Statement stmt = createStatement();
      		stmt.execute("CREATE PROCEDURE PMDD(IN pmdI_1 DECIMAL(5,3), IN pmdI_2 DECIMAL(4,2), INOUT pmdI_3 DECIMAL(9,0), OUT pmdI_4 DECIMAL(10,2)) language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.jdbcapi.ParameterMetaDataJdbc30Test.dummyDecimal'");
     		CallableStatement cs = prepareCall("CALL PMDD(?, ?, ?, ?)");

	// parameters 1 and 2 are input only
               cs.setBigDecimal(1,new BigDecimal("1"));;
               cs.setBigDecimal(2,new BigDecimal("1"));;
               // parameter 3 is input and output
               Object x = new BigDecimal(1.1);
               cs.setObject(3,x, Types.DECIMAL);
               cs.registerOutParameter(3,Types.DECIMAL);
               //parameter 4 is output only
               cs.registerOutParameter(4,Types.DECIMAL);
	//verify the meta data for the parameters
             	ParameterMetaData paramMetaData = cs.getParameterMetaData();
	assertEquals("Unexpected parameter count", 4, paramMetaData.getParameterCount());

	//expected values to be stored in a 2dim. array
               String parameterMetaDataArray0 [][] = {
               //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
               {"PARAMETER_NULLABLE", "true", "5", "3", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "true", "4", "2", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "true", "9", "0", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN_OUT"},
               {"PARAMETER_NULLABLE", "true", "10", "2", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_OUT"}};

               testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray0);

      		cs.close();
}
 
Example 11
Source Project: gemfirexd-oss   File: CallableTest.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * Calls a SQL procedure that takes numeric IN and OUT parameters.
 * Excluded from JSR169/j2ME, which doesn't support get/set BigDecimal yet.
 * @throws SQLException 
 */
public void xtestNumericTypesInAndOutProc() throws SQLException
{
    CallableStatement cs = prepareCall
        ("call NUMERIC_TYPES_IN_AND_OUT_PROC(?,?,?,?,?,?,?,?,?,?,?,?)");

    cs.setShort(1, (short) 3);
    cs.setInt(2, 4);
    cs.setLong(3, 5);
    cs.setFloat(4, (float) 6.0);
    cs.setDouble(5, 7.0);
    cs.setBigDecimal(6, new BigDecimal("88.88"));

    cs.registerOutParameter (7, java.sql.Types.SMALLINT);
    cs.registerOutParameter (8, java.sql.Types.INTEGER);
    cs.registerOutParameter (9, java.sql.Types.BIGINT);
    cs.registerOutParameter (10, java.sql.Types.REAL);
    cs.registerOutParameter (11, java.sql.Types.DOUBLE);
    cs.registerOutParameter (12, java.sql.Types.DECIMAL);

    cs.execute();

    assertEquals("OUT short", (short) 3, cs.getShort(7));
    assertEquals("OUT int"  , 4, cs.getInt(8));
    assertEquals("OUT long" , 5, cs.getLong(9));
    assertEquals("OUT float" , (float) 6.0, cs.getFloat(10), .0001);
    assertEquals("OUT double" , 7.0, cs.getDouble(11), .0001);
    assertDecimalSameValue("OUT decimal", "88.88", cs.getBigDecimal(12));
}
 
Example 12
/**
        *  test ParameterMetaData for Java procedures with DECIMAL parameters
 *
 * @exception SQLException if error occurs
        */
public void testParameterMetadataWithDECIMALParameters () throws SQLException {

	Statement stmt = createStatement();
      		stmt.execute("CREATE PROCEDURE PMDD(IN pmdI_1 DECIMAL(5,3), IN pmdI_2 DECIMAL(4,2), INOUT pmdI_3 DECIMAL(9,0), OUT pmdI_4 DECIMAL(10,2)) language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.jdbcapi.ParameterMetaDataJdbc30Test.dummyDecimal'");
     		CallableStatement cs = prepareCall("CALL PMDD(?, ?, ?, ?)");

	// parameters 1 and 2 are input only
               cs.setBigDecimal(1,new BigDecimal("1"));;
               cs.setBigDecimal(2,new BigDecimal("1"));;
               // parameter 3 is input and output
               Object x = new BigDecimal(1.1);
               cs.setObject(3,x, Types.DECIMAL);
               cs.registerOutParameter(3,Types.DECIMAL);
               //parameter 4 is output only
               cs.registerOutParameter(4,Types.DECIMAL);
	//verify the meta data for the parameters
             	ParameterMetaData paramMetaData = cs.getParameterMetaData();
	assertEquals("Unexpected parameter count", 4, paramMetaData.getParameterCount());

	//expected values to be stored in a 2dim. array
               String parameterMetaDataArray0 [][] = {
               //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
               {"PARAMETER_NULLABLE", "true", "5", "3", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "true", "4", "2", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "true", "9", "0", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN_OUT"},
               {"PARAMETER_NULLABLE", "true", "10", "2", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_OUT"}};

               testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray0);

      		cs.close();
}
 
Example 13
@Test(expected = SQLException.class)
public void testMultiHostWithException() throws SQLException {
    when(mysqlCallableStatement.executeQuery()).thenThrow(new SQLException());
    try {
        CallableStatement preparedStatement = multiHostConnection.prepareCall("SELECT * FROM test WHERE a = ? OR b = ? OR c=? OR d = ? OR e=?");
        preparedStatement.setBigDecimal(1, new BigDecimal(10000));
        preparedStatement.setBlob(2, inputStream);
        preparedStatement.setBlob(3, inputStream, 1000000L);
        preparedStatement.setByte(3, (byte) 1);
        preparedStatement.setBytes(4, bytesParam);
        preparedStatement.setLong(5, 100L);

        ResultSet resultSet = preparedStatement.executeQuery();

        preparedStatement.close();
    } finally {
        verify(mysqlCallableStatement).executeQuery();
        verify(mysqlCallableStatement, times(0)).close();
        verify(mysqlCallableStatement).setBigDecimal(anyInt(), any(BigDecimal.class));
        verify(mysqlCallableStatement).setBlob(anyInt(), any(InputStream.class));
        verify(mysqlCallableStatement).setBlob(anyInt(), any(InputStream.class), anyLong());
        verify(mysqlCallableStatement).setByte(anyInt(), anyByte());
        assertThat(segmentStorage.getTraceSegments().size(), is(1));
        TraceSegment traceSegment = segmentStorage.getTraceSegments().get(0);
        List<AbstractTracingSpan> spans = SegmentHelper.getSpans(traceSegment);
        assertThat(spans.size(), is(1));
        assertDBSpan(spans.get(0), "Mysql/JDBI/CallableStatement/executeQuery", "SELECT * FROM test WHERE a = ? OR b = ? OR c=? OR d = ? OR e=?");
        List<LogDataEntity> logs = SpanHelper.getLogs(spans.get(0));
        Assert.assertThat(logs.size(), is(1));
        assertDBSpanLog(logs.get(0));
    }
}
 
Example 14
/**
 * Calls a SQL procedure that takes numeric IN and OUT parameters.
 * Excluded from JSR169/j2ME, which doesn't support get/set BigDecimal yet.
 * @throws SQLException 
 */
public void xtestNumericTypesInAndOutProc() throws SQLException
{
    CallableStatement cs = prepareCall
        ("call NUMERIC_TYPES_IN_AND_OUT_PROC(?,?,?,?,?,?,?,?,?,?,?,?)");

    cs.setShort(1, (short) 3);
    cs.setInt(2, 4);
    cs.setLong(3, 5);
    cs.setFloat(4, (float) 6.0);
    cs.setDouble(5, 7.0);
    cs.setBigDecimal(6, new BigDecimal("88.88"));

    cs.registerOutParameter (7, java.sql.Types.SMALLINT);
    cs.registerOutParameter (8, java.sql.Types.INTEGER);
    cs.registerOutParameter (9, java.sql.Types.BIGINT);
    cs.registerOutParameter (10, java.sql.Types.REAL);
    cs.registerOutParameter (11, java.sql.Types.DOUBLE);
    cs.registerOutParameter (12, java.sql.Types.DECIMAL);

    cs.execute();

    assertEquals("OUT short", (short) 3, cs.getShort(7));
    assertEquals("OUT int"  , 4, cs.getInt(8));
    assertEquals("OUT long" , 5, cs.getLong(9));
    assertEquals("OUT float" , (float) 6.0, cs.getFloat(10), .0001);
    assertEquals("OUT double" , 7.0, cs.getDouble(11), .0001);
    assertDecimalSameValue("OUT decimal", "88.88", cs.getBigDecimal(12));

    // test that setObject() does the right thing for BigDecimal. see db-5488.
    cs.setObject(3, new BigDecimal( "10" ) );
    cs.execute();
    assertEquals("OUT long" , 10, cs.getLong(9));

    // test that setObject() does the right thing for BigInteger. see db-5488.
    cs.setObject(3, new BigInteger( "11" ) );
    cs.execute();
    assertEquals("OUT long" , 11, cs.getLong(9));
}
 
Example 15
/**
        *  test ParameterMetaData for Java procedures with DECIMAL parameters
 *
 * @exception SQLException if error occurs
        */
public void testParameterMetadataWithDECIMALParameters () throws SQLException {

     		CallableStatement cs = prepareCall("CALL PMDD(?, ?, ?, ?)");

	// parameters 1 and 2 are input only
               cs.setBigDecimal(1,new BigDecimal("1"));;
               cs.setBigDecimal(2,new BigDecimal("1"));;
               // parameter 3 is input and output
               Object x = new BigDecimal(1.1);
               cs.setObject(3,x, Types.DECIMAL);
               cs.registerOutParameter(3,Types.DECIMAL);
               //parameter 4 is output only
               cs.registerOutParameter(4,Types.DECIMAL);
	//verify the meta data for the parameters
             	ParameterMetaData paramMetaData = cs.getParameterMetaData();
	assertEquals("Unexpected parameter count", 4, paramMetaData.getParameterCount());

	//expected values to be stored in a 2dim. array
               String parameterMetaDataArray0 [][] = {
               //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
               {"PARAMETER_NULLABLE", "true", "5", "3", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "true", "4", "2", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "true", "9", "0", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN_OUT"},
               {"PARAMETER_NULLABLE", "true", "10", "2", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_OUT"}};

               testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray0);

      		cs.close();
}
 
Example 16
/**
        * test ParameterMetaData for Java procedures with some literal parameters
 *
 * @exception SQLException if error occurs
        */
public void testParameterMetadataWithLITERALParameters () throws SQLException {

	Statement stmt = createStatement();
     		CallableStatement cs = prepareCall("CALL PMDD(32.4, ?, ?, ?)");
	// parameters 2 is input only
               cs.setBigDecimal(1,new BigDecimal("1"));;
               // parameter 3 is input and output
               Object x = new BigDecimal(1.1);
               cs.setObject(2,x, Types.DECIMAL);
               cs.registerOutParameter(2,Types.DECIMAL);
               //parameter 4 is output only
               cs.registerOutParameter(3,Types.DECIMAL);

	//verify the meta data for the parameters
               ParameterMetaData paramMetaData = cs.getParameterMetaData();
               assertEquals("Unexpected parameter count", 3, paramMetaData.getParameterCount());

	//expected values to be stored in a 2dim. array
               String parameterMetaDataArray0 [][] = {
               //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
               {"PARAMETER_NULLABLE", "true", "4", "2", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "true", "9", "0", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN_OUT"},
               {"PARAMETER_NULLABLE", "true", "10", "2", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_OUT"}};

               testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray0);

         	cs.close();

     		cs = prepareCall("CALL PMDD(32.4, 47.9, ?, ?)");
	// parameter 3 is input and output
               Object y = new BigDecimal(1.1);
               cs.setObject(1,y, Types.DECIMAL);
               cs.registerOutParameter(1,Types.DECIMAL);
               //parameter 4 is output only
               cs.registerOutParameter(2,Types.DECIMAL);
	paramMetaData = cs.getParameterMetaData();
               assertEquals("Unexpected parameter count", 2, paramMetaData.getParameterCount());

	//expected values to be stored in a 2dim. array
               String parameterMetaDataArray1 [][] = {
               //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
               {"PARAMETER_NULLABLE", "true", "9", "0", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN_OUT"},
               {"PARAMETER_NULLABLE", "true", "10", "2", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_OUT"}};

               testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray1);

         	cs.close();

     		cs = prepareCall("CALL PMDD(?, 38.2, ?, ?)");
	// parameters 1 is input only
               cs.setBigDecimal(1,new BigDecimal("1"));;
               // parameter 3 is input and output
               Object z = new BigDecimal(1.1);
               cs.setObject(2,z, Types.DECIMAL);
               cs.registerOutParameter(2,Types.DECIMAL);
               //parameter 4 is output only
               cs.registerOutParameter(3,Types.DECIMAL);

	//verify the meta data for the parameters
               paramMetaData = cs.getParameterMetaData();
	assertEquals("Unexpected parameter count", 3, paramMetaData.getParameterCount());

	//expected values to be stored in a 2dim. array
               String parameterMetaDataArray2 [][] = {
               //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
               {"PARAMETER_NULLABLE", "true", "5", "3", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "true", "9", "0", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN_OUT"},
               {"PARAMETER_NULLABLE", "true", "10", "2", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_OUT"}};

               testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray2);

         	cs.close();
         	stmt.execute("DROP PROCEDURE PMDD");
           stmt.close();
}
 
Example 17
/**
        * test ParameterMetaData for Java procedures with some literal parameters
 *
 * @exception SQLException if error occurs
        */
public void testParameterMetadataWithLITERALParameters () throws SQLException {

	Statement stmt = createStatement();
     		CallableStatement cs = prepareCall("CALL PMDD(32.4, ?, ?, ?)");
	// parameters 2 is input only
               cs.setBigDecimal(1,new BigDecimal("1"));;
               // parameter 3 is input and output
               Object x = new BigDecimal(1.1);
               cs.setObject(2,x, Types.DECIMAL);
               cs.registerOutParameter(2,Types.DECIMAL);
               //parameter 4 is output only
               cs.registerOutParameter(3,Types.DECIMAL);

	//verify the meta data for the parameters
               ParameterMetaData paramMetaData = cs.getParameterMetaData();
               assertEquals("Unexpected parameter count", 3, paramMetaData.getParameterCount());

	//expected values to be stored in a 2dim. array
               String parameterMetaDataArray0 [][] = {
               //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
               {"PARAMETER_NULLABLE", "true", "4", "2", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "true", "9", "0", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN_OUT"},
               {"PARAMETER_NULLABLE", "true", "10", "2", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_OUT"}};

               testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray0);

         	cs.close();

     		cs = prepareCall("CALL PMDD(32.4, 47.9, ?, ?)");
	// parameter 3 is input and output
               Object y = new BigDecimal(1.1);
               cs.setObject(1,y, Types.DECIMAL);
               cs.registerOutParameter(1,Types.DECIMAL);
               //parameter 4 is output only
               cs.registerOutParameter(2,Types.DECIMAL);
	paramMetaData = cs.getParameterMetaData();
               assertEquals("Unexpected parameter count", 2, paramMetaData.getParameterCount());

	//expected values to be stored in a 2dim. array
               String parameterMetaDataArray1 [][] = {
               //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
               {"PARAMETER_NULLABLE", "true", "9", "0", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN_OUT"},
               {"PARAMETER_NULLABLE", "true", "10", "2", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_OUT"}};

               testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray1);

         	cs.close();

     		cs = prepareCall("CALL PMDD(?, 38.2, ?, ?)");
	// parameters 1 is input only
               cs.setBigDecimal(1,new BigDecimal("1"));;
               // parameter 3 is input and output
               Object z = new BigDecimal(1.1);
               cs.setObject(2,z, Types.DECIMAL);
               cs.registerOutParameter(2,Types.DECIMAL);
               //parameter 4 is output only
               cs.registerOutParameter(3,Types.DECIMAL);

	//verify the meta data for the parameters
               paramMetaData = cs.getParameterMetaData();
	assertEquals("Unexpected parameter count", 3, paramMetaData.getParameterCount());

	//expected values to be stored in a 2dim. array
               String parameterMetaDataArray2 [][] = {
               //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
               {"PARAMETER_NULLABLE", "true", "5", "3", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "true", "9", "0", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN_OUT"},
               {"PARAMETER_NULLABLE", "true", "10", "2", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_OUT"}};

               testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray2);

         	cs.close();
         	stmt.execute("DROP PROCEDURE PMDD");
           stmt.close();
}
 
Example 18
/**
 * Utility method which registers in a CallableStatement object the different {@link amforeas.jdbc.StoredProcedureParam}
 * instances in the given list. Returns a List of {@link amforeas.jdbc.StoredProcedureParam} with all the OUT parameters
 * registered in the CallableStatement
 * @param cs the CallableStatement object where the parameters are registered.
 * @param params a list of {@link amforeas.jdbc.StoredProcedureParam}
 * @return a list of OUT {@link amforeas.jdbc.StoredProcedureParam} 
 * @throws SQLException if we fail to register any of the parameters in the CallableStatement
 * @throws AmforeasBadRequestException 
 */
private List<StoredProcedureParam> addParameters (final CallableStatement cs, final List<StoredProcedureParam> params) throws SQLException, AmforeasBadRequestException {
    final List<StoredProcedureParam> outParams = new ArrayList<StoredProcedureParam>();
    int i = 1;
    for (StoredProcedureParam p : params) {
        final Integer sqlType = p.getSqlType();
        if (p.isOutParameter()) {
            l.debug("Adding OUT parameter " + p.toString());
            cs.registerOutParameter(i++, sqlType);
            outParams.add(p);
        } else {
            l.debug("Adding IN parameter " + p.toString());
            switch (sqlType) {
                case Types.BIGINT:
                case Types.INTEGER:
                case Types.TINYINT:
                    // case Types.NUMERIC:
                    cs.setInt(i++, Integer.valueOf(p.getValue()));
                    break;
                case Types.DATE:
                    cs.setDate(i++, (Date) AmforeasUtils.parseValue(p.getValue()));
                    break;
                case Types.TIME:
                    cs.setTime(i++, (Time) AmforeasUtils.parseValue(p.getValue()));
                    break;
                case Types.TIMESTAMP:
                    cs.setTimestamp(i++, (Timestamp) AmforeasUtils.parseValue(p.getValue()));
                    break;
                case Types.DECIMAL:
                    cs.setBigDecimal(i++, (BigDecimal) AmforeasUtils.parseValue(p.getValue()));
                    break;
                case Types.DOUBLE:
                    cs.setDouble(i++, Double.valueOf(p.getValue()));
                    break;
                case Types.FLOAT:
                    cs.setLong(i++, Long.valueOf(p.getValue()));
                    break;
                default:
                    cs.setString(i++, p.getValue());
                    break;
            }
        }
    }
    return outParams;
}
 
Example 19
/**
        * test ParameterMetaData for Java procedures with some literal parameters
 *
 * @exception SQLException if error occurs
        */
public void testParameterMetadataWithLITERALParameters () throws SQLException {

     		CallableStatement cs = prepareCall("CALL PMDD(32.4, ?, ?, ?)");
	// parameters 2 is input only
               cs.setBigDecimal(1,new BigDecimal("1"));;
               // parameter 3 is input and output
               Object x = new BigDecimal(1.1);
               cs.setObject(2,x, Types.DECIMAL);
               cs.registerOutParameter(2,Types.DECIMAL);
               //parameter 4 is output only
               cs.registerOutParameter(3,Types.DECIMAL);

	//verify the meta data for the parameters
               ParameterMetaData paramMetaData = cs.getParameterMetaData();
               assertEquals("Unexpected parameter count", 3, paramMetaData.getParameterCount());

	//expected values to be stored in a 2dim. array
               String parameterMetaDataArray0 [][] = {
               //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
               {"PARAMETER_NULLABLE", "true", "4", "2", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "true", "9", "0", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN_OUT"},
               {"PARAMETER_NULLABLE", "true", "10", "2", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_OUT"}};

               testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray0);

         	cs.close();

     		cs = prepareCall("CALL PMDD(32.4, 47.9, ?, ?)");
	// parameter 3 is input and output
               Object y = new BigDecimal(1.1);
               cs.setObject(1,y, Types.DECIMAL);
               cs.registerOutParameter(1,Types.DECIMAL);
               //parameter 4 is output only
               cs.registerOutParameter(2,Types.DECIMAL);
	paramMetaData = cs.getParameterMetaData();
               assertEquals("Unexpected parameter count", 2, paramMetaData.getParameterCount());

	//expected values to be stored in a 2dim. array
               String parameterMetaDataArray1 [][] = {
               //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
               {"PARAMETER_NULLABLE", "true", "9", "0", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN_OUT"},
               {"PARAMETER_NULLABLE", "true", "10", "2", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_OUT"}};

               testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray1);

         	cs.close();

     		cs = prepareCall("CALL PMDD(?, 38.2, ?, ?)");
	// parameters 1 is input only
               cs.setBigDecimal(1,new BigDecimal("1"));;
               // parameter 3 is input and output
               Object z = new BigDecimal(1.1);
               cs.setObject(2,z, Types.DECIMAL);
               cs.registerOutParameter(2,Types.DECIMAL);
               //parameter 4 is output only
               cs.registerOutParameter(3,Types.DECIMAL);

	//verify the meta data for the parameters
               paramMetaData = cs.getParameterMetaData();
	assertEquals("Unexpected parameter count", 3, paramMetaData.getParameterCount());

	//expected values to be stored in a 2dim. array
               String parameterMetaDataArray2 [][] = {
               //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
               {"PARAMETER_NULLABLE", "true", "5", "3", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "true", "9", "0", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN_OUT"},
               {"PARAMETER_NULLABLE", "true", "10", "2", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_OUT"}};

               testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray2);

         	cs.close();
}