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

The following examples show how to use java.sql.CallableStatement#setBigDecimal() . 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 File: CallableTest.java From gemfirexd-oss with Apache License 2.0

6 votes

/**
 * 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 File: DAProcedures.java From gemfirexd-oss with Apache License 2.0

6 votes

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 File: DAProcedures.java From gemfirexd-oss with Apache License 2.0

6 votes

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 File: CallableTest.java From gemfirexd-oss with Apache License 2.0

6 votes

/**
 * 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 File: DAProcedures.java From gemfirexd-oss with Apache License 2.0

6 votes

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 File: DAProcedures.java From gemfirexd-oss with Apache License 2.0

6 votes

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

Source File: CallableStatementTest.java From jTDS with GNU Lesser General Public License v2.1

6 votes

/**
 * 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

Source File: CallableTest.java From spliceengine with GNU Affero General Public License v3.0

6 votes

/**
 * 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 File: ParameterMetaDataJdbc30Test.java From gemfirexd-oss with Apache License 2.0

5 votes

/**
        *  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 10

Source File: CallableTest.java From gemfirexd-oss with Apache License 2.0

5 votes

/**
 * 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 11

Source File: CallableTest.java From gemfirexd-oss with Apache License 2.0

5 votes

/**
 * 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

Source File: ParameterMetaDataJdbc30Test.java From gemfirexd-oss with Apache License 2.0

5 votes

/**
        *  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

Source File: ParameterMetaDataJdbc30Test.java From spliceengine with GNU Affero General Public License v3.0

5 votes

/**
        *  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 14

Source File: SWCallableStatementTest.java From skywalking with Apache License 2.0

5 votes

@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 15

Source File: CallableTest.java From spliceengine with GNU Affero General Public License v3.0

5 votes

/**
 * 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 16

Source File: ParameterMetaDataJdbc30Test.java From spliceengine with GNU Affero General Public License v3.0

4 votes

/**
        * 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();
}

Example 17

Source File: JDBCExecutor.java From amforeas with GNU General Public License v3.0

4 votes

/**
 * 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 18

Source File: ParameterMetaDataJdbc30Test.java From gemfirexd-oss with Apache License 2.0

4 votes

/**
        * 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 19

Source File: ParameterMetaDataJdbc30Test.java From gemfirexd-oss with Apache License 2.0

4 votes

/**
        * 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();
}