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

The following examples show how to use java.sql.CallableStatement#getParameterMetaData() . 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
/**
        * test ParameterMetaData for Java procedures with CHAR parameters
 *
 * @exception SQLException if error occurs
        */
 public void testParameterMetadataWithCHARParameters () throws SQLException {

	Statement stmt = createStatement();
      		stmt.execute("CREATE PROCEDURE PMDC(IN pmdI_1 CHAR(10), IN pmdI_2 VARCHAR(25), INOUT pmdI_3 CHAR(19), OUT pmdI_4 VARCHAR(32)) language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.jdbcapi.ParameterMetaDataJdbc30Test.dummyString'");
     		CallableStatement cs = prepareCall("CALL PMDC(?, ?, ?, ?)");
	// parameters 1 and 2 are input only	
	cs.setString(1, "TEST0");
	cs.setString(2, "TEST1");
	// parameter 3 is input and output
               Object x = new String("TEST");
               cs.setObject(3,x, Types.CHAR);
               cs.registerOutParameter(3,Types.CHAR);
               //parameter 4 is output only
               cs.registerOutParameter(4,Types.CHAR);
		//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", "false", "10", "0", "1", "CHAR", "java.lang.String", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "false", "25", "0", "12", "VARCHAR", "java.lang.String", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "false", "19", "0", "1", "CHAR", "java.lang.String", "PARAMETER_MODE_IN_OUT"},
               {"PARAMETER_NULLABLE", "false", "32", "0", "12", "VARCHAR", "java.lang.String", "PARAMETER_MODE_OUT"}};

	testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray0);

      		cs.close();
      		stmt.execute("DROP PROCEDURE PMDC");
           stmt.close();
}
 
Example 2
public void testDataAwareProcedureWithoutResultSets()
throws Exception {
  
  setup();
  CallableStatement cs = prepareCall("CALL PROCEDURE_WITHOUT_RESULTSET(?, ?) ON TABLE EMP.PARTITIONTESTTABLE WHERE SECONDID=4 AND THIRDID='3'");
  cs.registerOutParameter(2, java.sql.Types.VARCHAR, 20);
  int number=2;
  String name="INOUTPARAMETER";
  cs.setInt(1, number);
  cs.setString(2, name);
 
  
  cs.execute();
  
  ResultSet rs=cs.getResultSet();
  if(rs!=null || cs.getMoreResults()) {
    fail("no dynamic result set for the procedure!");
  }
  
  ParameterMetaData pmd=cs.getParameterMetaData();  
  int numParameters=pmd.getParameterCount();
  assertTrue(" the number of parameter is 2", numParameters==2);
  try {
    cs.getInt(1);
    fail("the in parameteter cannot be read!");
  } catch (Exception e) {
    
  }
  Object parameter2=cs.getObject(2);    
  assertTrue("the second inout parameter is "+name+number, parameter2.equals(name+number));
    
  

}
 
Example 3
public void testDataAwareProcedureWithoutResultSetsUsingGlobalIndex()
throws Exception {
  
  setup();
  CallableStatement cs = prepareCall("CALL PROCEDURE_WITHOUT_RESULTSET(?, ?) ON TABLE EMP.PARTITIONTESTTABLE1 WHERE SECONDID=4 AND THIRDID='3'");
  cs.registerOutParameter(2, java.sql.Types.VARCHAR, 20);
  int number=2;
  String name="INOUTPARAMETER";
  cs.setInt(1, number);
  cs.setString(2, name);
 
  
  cs.execute();
  
  ResultSet rs=cs.getResultSet();
  if(rs!=null || cs.getMoreResults()) {
    fail("no dynamic result set for the procedure!");
  }
  
  ParameterMetaData pmd=cs.getParameterMetaData();  
  int numParameters=pmd.getParameterCount();
  assertTrue(" the number of parameter is 2", numParameters==2);
  try {
    cs.getInt(1);
    fail("the in parameteter cannot be read!");
  } catch (Exception e) {
    
  }
  Object parameter2=cs.getObject(2);    
  assertTrue("the second inout parameter is "+name+number, parameter2.equals(name+number));
    
  

}
 
Example 4
Source Project: gemfirexd-oss   File: ProcedureTest.java    License: Apache License 2.0 5 votes vote down vote up
public void testDataAwareProcedureWithoutResultSets()
throws SQLException {
  
  setup();
  CallableStatement cs = prepareCall("CALL PROCEDURE_WITHOUT_RESULTSET(?, ?) ON TABLE EMP.PARTITIONTESTTABLE WHERE SECONDID=4 AND THIRDID='3'");
  cs.registerOutParameter(2, java.sql.Types.VARCHAR, 20);
  int number=2;
  String name="INOUTPARAMETER";
  cs.setInt(1, number);
  cs.setString(2, name);
 
  
  cs.execute();
  
  ResultSet rs=cs.getResultSet();
  if(rs!=null || cs.getMoreResults()) {
    fail("no dynamic result set for the procedure!");
  }
  
  ParameterMetaData pmd=cs.getParameterMetaData();  
  int numParameters=pmd.getParameterCount();
  assertTrue(" the number of parameter is 2", numParameters==2);
  try {
    cs.getInt(1);
    fail("the in parameteter cannot be read!");
  } catch (Exception e) {
    
  }
  Object parameter2=cs.getObject(2);    
  assertTrue("the second inout parameter is "+name+number, parameter2.equals(name+number));
    
  

}
 
Example 5
Source Project: gemfirexd-oss   File: ProcedureTest.java    License: Apache License 2.0 5 votes vote down vote up
public void testDataAwareProcedureWithoutResultSetsUsingGlobalIndex()
throws SQLException {
  
  setup();
  CallableStatement cs = prepareCall("CALL PROCEDURE_WITHOUT_RESULTSET(?, ?) ON TABLE EMP.PARTITIONTESTTABLE1 WHERE SECONDID=4 AND THIRDID='3'");
  cs.registerOutParameter(2, java.sql.Types.VARCHAR, 20);
  int number=2;
  String name="INOUTPARAMETER";
  cs.setInt(1, number);
  cs.setString(2, name);
 
  
  cs.execute();
  
  ResultSet rs=cs.getResultSet();
  if(rs!=null || cs.getMoreResults()) {
    fail("no dynamic result set for the procedure!");
  }
  
  ParameterMetaData pmd=cs.getParameterMetaData();  
  int numParameters=pmd.getParameterCount();
  assertTrue(" the number of parameter is 2", numParameters==2);
  try {
    cs.getInt(1);
    fail("the in parameteter cannot be read!");
  } catch (Exception e) {
    
  }
  Object parameter2=cs.getObject(2);    
  assertTrue("the second inout parameter is "+name+number, parameter2.equals(name+number));
    
  

}
 
Example 6
/**
        * test ParameterMetaData for Java procedures with INTEGER parameters 
 * 
 * @exception SQLException if error occurs
        */
public void testParameterMetadataWithINTParameters () throws SQLException {

	Statement stmt = createStatement();
      		stmt.execute("CREATE PROCEDURE PMDI(IN pmdI_1 INTEGER, IN pmdI_2 INTEGER, INOUT pmdI_3 INTEGER, OUT pmdI_4 INTEGER) language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.jdbcapi.ParameterMetaDataJdbc30Test.dummyint'");
     		CallableStatement cs = prepareCall("CALL PMDI(?, ?, ?, ?)");

	// parameters 1 and 2 are input only
              	cs.setInt(1,1);
       	cs.setInt(2,1);
	// parameter 3 is input and output
               Object x = new Integer(1);
               cs.setObject(3,x, Types.INTEGER);
               cs.registerOutParameter(3,Types.INTEGER);
              	//parameter 4 is output only
              	cs.registerOutParameter(4,Types.INTEGER);

              	//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", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN_OUT"},
               {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_OUT"}};

               testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray0);
		
      		cs.close();
      		stmt.execute("DROP PROCEDURE PMDI");
           stmt.close();
}
 
Example 7
/**
        *  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 8
/**
        *  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 9
public void testDataAwareProcedureWithoutResultSets()
throws Exception {
  
  setup();
  CallableStatement cs = prepareCall("CALL PROCEDURE_WITHOUT_RESULTSET(?, ?) ON TABLE EMP.PARTITIONTESTTABLE WHERE SECONDID=4 AND THIRDID='3'");
  cs.registerOutParameter(2, java.sql.Types.VARCHAR, 20);
  int number=2;
  String name="INOUTPARAMETER";
  cs.setInt(1, number);
  cs.setString(2, name);
 
  
  cs.execute();
  
  ResultSet rs=cs.getResultSet();
  if(rs!=null || cs.getMoreResults()) {
    fail("no dynamic result set for the procedure!");
  }
  
  ParameterMetaData pmd=cs.getParameterMetaData();  
  int numParameters=pmd.getParameterCount();
  assertTrue(" the number of parameter is 2", numParameters==2);
  try {
    cs.getInt(1);
    fail("the in parameteter cannot be read!");
  } catch (Exception e) {
    
  }
  Object parameter2=cs.getObject(2);    
  assertTrue("the second inout parameter is "+name+number, parameter2.equals(name+number));
    
  

}
 
Example 10
/**
        * test ParameterMetaData for Java procedures with INTEGER parameters 
 * 
 * @exception SQLException if error occurs
        */
public void testParameterMetadataWithINTParameters () throws SQLException {

	Statement stmt = createStatement();
      		stmt.execute("CREATE PROCEDURE PMDI(IN pmdI_1 INTEGER, IN pmdI_2 INTEGER, INOUT pmdI_3 INTEGER, OUT pmdI_4 INTEGER) language java parameter style java external name 'com.splicemachine.dbTesting.functionTests.tests.jdbcapi.ParameterMetaDataJdbc30Test.dummyint'");
     		CallableStatement cs = prepareCall("CALL PMDI(?, ?, ?, ?)");

	// parameters 1 and 2 are input only
              	cs.setInt(1,1);
       	cs.setInt(2,1);
	// parameter 3 is input and output
               Object x = new Integer(1);
               cs.setObject(3,x, Types.INTEGER);
               cs.registerOutParameter(3,Types.INTEGER);
              	//parameter 4 is output only
              	cs.registerOutParameter(4,Types.INTEGER);

              	//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", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN_OUT"},
               {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_OUT"}};

               testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray0);
		
      		cs.close();
      		stmt.execute("DROP PROCEDURE PMDI");
           stmt.close();
}
 
Example 11
Source Project: gemfirexd-oss   File: ProcedureTest.java    License: Apache License 2.0 5 votes vote down vote up
public void testDataAwareProcedureWithoutResultSetsUsingGlobalIndex()
throws SQLException {
  
  setup();
  CallableStatement cs = prepareCall("CALL PROCEDURE_WITHOUT_RESULTSET(?, ?) ON TABLE EMP.PARTITIONTESTTABLE1 WHERE SECONDID=4 AND THIRDID='3'");
  cs.registerOutParameter(2, java.sql.Types.VARCHAR, 20);
  int number=2;
  String name="INOUTPARAMETER";
  cs.setInt(1, number);
  cs.setString(2, name);
 
  
  cs.execute();
  
  ResultSet rs=cs.getResultSet();
  if(rs!=null || cs.getMoreResults()) {
    fail("no dynamic result set for the procedure!");
  }
  
  ParameterMetaData pmd=cs.getParameterMetaData();  
  int numParameters=pmd.getParameterCount();
  assertTrue(" the number of parameter is 2", numParameters==2);
  try {
    cs.getInt(1);
    fail("the in parameteter cannot be read!");
  } catch (Exception e) {
    
  }
  Object parameter2=cs.getObject(2);    
  assertTrue("the second inout parameter is "+name+number, parameter2.equals(name+number));
    
  

}
 
Example 12
Source Project: r-course   File: CallableStatementTest.java    License: MIT License 4 votes vote down vote up
/**
 * Tests the new parameter parser that doesn't require "BEGIN" or "\n" at
 * end of parameter declaration
 * 
 * @throws Exception
 */
public void testParameterParser() throws Exception {

    if (!versionMeetsMinimum(5, 0)) {
        return;
    }

    CallableStatement cstmt = null;

    try {

        createTable("t1", "(id   char(16) not null default '', data int not null)");
        createTable("t2", "(s   char(16),  i   int,  d   double)");

        createProcedure("foo42", "() insert into test.t1 values ('foo', 42);");
        this.conn.prepareCall("{CALL foo42()}");
        this.conn.prepareCall("{CALL foo42}");

        createProcedure("bar", "(x char(16), y int, z DECIMAL(10)) insert into test.t1 values (x, y);");
        cstmt = this.conn.prepareCall("{CALL bar(?, ?, ?)}");

        ParameterMetaData md = cstmt.getParameterMetaData();
        assertEquals(3, md.getParameterCount());
        assertEquals(Types.CHAR, md.getParameterType(1));
        assertEquals(Types.INTEGER, md.getParameterType(2));
        assertEquals(Types.DECIMAL, md.getParameterType(3));

        createProcedure("p", "() label1: WHILE @a=0 DO SET @a=1; END WHILE");
        this.conn.prepareCall("{CALL p()}");

        createFunction("f", "() RETURNS INT NO SQL return 1; ");
        cstmt = this.conn.prepareCall("{? = CALL f()}");

        md = cstmt.getParameterMetaData();
        assertEquals(Types.INTEGER, md.getParameterType(1));
    } finally {
        if (cstmt != null) {
            cstmt.close();
        }
    }
}
 
Example 13
/**
 * Tests the new parameter parser that doesn't require "BEGIN" or "\n" at
 * end of parameter declaration
 * 
 * @throws Exception
 */
public void testParameterParser() throws Exception {

    if (!versionMeetsMinimum(5, 0)) {
        return;
    }

    CallableStatement cstmt = null;

    try {

        createTable("t1", "(id   char(16) not null default '', data int not null)");
        createTable("t2", "(s   char(16),  i   int,  d   double)");

        createProcedure("foo42", "() insert into test.t1 values ('foo', 42);");
        this.conn.prepareCall("{CALL foo42()}");
        this.conn.prepareCall("{CALL foo42}");

        createProcedure("bar", "(x char(16), y int, z DECIMAL(10)) insert into test.t1 values (x, y);");
        cstmt = this.conn.prepareCall("{CALL bar(?, ?, ?)}");

        ParameterMetaData md = cstmt.getParameterMetaData();
        assertEquals(3, md.getParameterCount());
        assertEquals(Types.CHAR, md.getParameterType(1));
        assertEquals(Types.INTEGER, md.getParameterType(2));
        assertEquals(Types.DECIMAL, md.getParameterType(3));

        createProcedure("p", "() label1: WHILE @a=0 DO SET @a=1; END WHILE");
        this.conn.prepareCall("{CALL p()}");

        createFunction("f", "() RETURNS INT NO SQL return 1; ");
        cstmt = this.conn.prepareCall("{? = CALL f()}");

        md = cstmt.getParameterMetaData();
        assertEquals(Types.INTEGER, md.getParameterType(1));
    } finally {
        if (cstmt != null) {
            cstmt.close();
        }
    }
}
 
Example 14
/**
 * Tests fix for Bug#84324 - CallableStatement.extractProcedureName() not work when catalog name with dash.
 */
public void testBug84324() throws Exception {
    createDatabase("`testBug84324-db`");

    /*
     * Test procedure.
     */
    createProcedure("`testBug84324-db`.`testBug84324-proc`", "(IN a INT, INOUT b VARCHAR(100)) BEGIN SELECT a, b; END");

    final CallableStatement cstmtP = this.conn.prepareCall("CALL testBug84324-db.testBug84324-proc(?, ?)");
    ParameterMetaData pmd = cstmtP.getParameterMetaData();

    assertEquals(2, pmd.getParameterCount());
    // 1st parameter
    assertEquals("INT", pmd.getParameterTypeName(1));
    assertEquals(Types.INTEGER, pmd.getParameterType(1));
    assertEquals(Integer.class.getName(), pmd.getParameterClassName(1));
    assertEquals(ParameterMetaData.parameterModeIn, pmd.getParameterMode(1));
    // 2nd parameter
    assertEquals("VARCHAR", pmd.getParameterTypeName(2));
    assertEquals(Types.VARCHAR, pmd.getParameterType(2));
    assertEquals(String.class.getName(), pmd.getParameterClassName(2));
    assertEquals(ParameterMetaData.parameterModeInOut, pmd.getParameterMode(2));

    cstmtP.setInt(1, 1);
    cstmtP.setString(2, "foo");
    assertThrows(SQLException.class, new Callable<Void>() {
        public Void call() throws Exception {
            cstmtP.execute();
            return null;
        }
    }); // Although the procedure metadata could be obtained, the end query actually fails due to syntax errors.
    cstmtP.close();

    /*
     * Test function.
     */
    createFunction("`testBug84324-db`.`testBug84324-func`", "(a INT, b VARCHAR(123)) RETURNS INT DETERMINISTIC BEGIN RETURN a + LENGTH(b); END");

    final CallableStatement cstmtF = this.conn.prepareCall("{? = CALL testBug84324-db.testBug84324-func(?, ?)}");
    pmd = cstmtF.getParameterMetaData();

    assertEquals(3, pmd.getParameterCount());
    // 1st parameter
    assertEquals("INT", pmd.getParameterTypeName(1));
    assertEquals(Types.INTEGER, pmd.getParameterType(1));
    assertEquals(Integer.class.getName(), pmd.getParameterClassName(1));
    assertEquals(ParameterMetaData.parameterModeOut, pmd.getParameterMode(1));
    // 2nd parameter
    assertEquals("INT", pmd.getParameterTypeName(2));
    assertEquals(Types.INTEGER, pmd.getParameterType(2));
    assertEquals(Integer.class.getName(), pmd.getParameterClassName(2));
    assertEquals(ParameterMetaData.parameterModeIn, pmd.getParameterMode(2));
    // 3rd parameter
    assertEquals("VARCHAR", pmd.getParameterTypeName(3));
    assertEquals(Types.VARCHAR, pmd.getParameterType(3));
    assertEquals(String.class.getName(), pmd.getParameterClassName(3));
    assertEquals(ParameterMetaData.parameterModeIn, pmd.getParameterMode(3));

    cstmtF.registerOutParameter(1, Types.INTEGER);
    cstmtF.setInt(2, 1);
    cstmtF.setString(3, "foo");
    assertThrows(SQLException.class, new Callable<Void>() {
        public Void call() throws Exception {
            cstmtF.execute();
            return null;
        }
    }); // Although the function metadata could be obtained, the end query actually fails due to syntax errors.
    cstmtP.close();
    cstmtF.close();
}
 
Example 15
/**
    	* Testing a callable statement by calling a SQL procedure with 
* IN parameters, OUT parameters and IN_OUT parameters.
       *
* @exception SQLException if error occurs
    	*/
public void testCallableStatement () throws SQLException {

       	Statement stmt = createStatement();

       	stmt.executeUpdate("create procedure dummyint( " + 
			   "in a integer, in b integer, " + 
			   "out c integer, inout d integer) " + 
			   "language java external name "+ 
			   "'org.apache.derbyTesting.functionTests." +
			   "tests.jdbcapi.ParameterMetaDataJdbc30Test.dummyint' " +
			   "parameter style java");
      		CallableStatement cs = prepareCall("CALL dummyint(?,?,?,?)");
	// parameters 1 and 2 are input only
     		cs.setInt(1,1);
     		cs.setInt(2,1);
     		//parameter 3 is output only
     		cs.registerOutParameter(3,Types.INTEGER);
     		// parameter 4 is input and output
     		Object x = new Integer(1);
     		cs.setObject(4,x, Types.INTEGER);
     		cs.registerOutParameter(4,Types.INTEGER);

     		//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", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"},
	{"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"},
	{"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_OUT"},
	{"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN_OUT"}};
		
	testParameterMetaData(paramMetaData, parameterMetaDataArray0);

     		cs.execute();

	/*
	/* bug 4450 - parameter meta data info for the return parameter was giving
     		/* null pointer exception. In the past, we didn't need to keep the return
     		/* parameter info for callable statement execution and hence we never
     		/* generated the meta data for it. To fix the problem, at the parsing time,
     		/* I set a flag if the call statement is of ? = form. If so, the first
     		/* parameter is a return parameter and save it's meta data rather than
     		/* discarding it.
 	 */	

     		cs = prepareCall("? = call RDB(?)");
     		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", "127", "0", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_OUT"},
               {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"}};

               testParameterMetaData(paramMetaData, parameterMetaDataArray1);

	stmt.close();
	cs.close();
}
 
Example 16
@Test
public void testCallableStatementConfig() throws SQLException {
    CallableStatement callableStatement = swConnection.prepareCall("INSERT INTO test VALUES( ? , ?)", 1, 1);
    callableStatement.setInt(1, 1);
    callableStatement.setString(2, "a");
    callableStatement.getUpdateCount();
    callableStatement.setFetchDirection(1);
    callableStatement.getFetchDirection();
    callableStatement.getResultSetConcurrency();
    callableStatement.getResultSetType();
    callableStatement.isClosed();
    callableStatement.setPoolable(false);
    callableStatement.isPoolable();
    callableStatement.getWarnings();
    callableStatement.clearWarnings();
    callableStatement.setCursorName("test");
    callableStatement.setMaxFieldSize(11);
    callableStatement.getMaxFieldSize();
    callableStatement.setMaxRows(10);
    callableStatement.getMaxRows();
    callableStatement.getParameterMetaData();
    callableStatement.setEscapeProcessing(true);
    callableStatement.setFetchSize(1);
    callableStatement.getFetchSize();
    callableStatement.setQueryTimeout(1);
    callableStatement.getQueryTimeout();
    Connection connection = callableStatement.getConnection();

    callableStatement.execute();

    callableStatement.getMoreResults();
    callableStatement.getMoreResults(1);
    callableStatement.getResultSetHoldability();
    callableStatement.getMetaData();
    callableStatement.getResultSet();

    callableStatement.close();
    verify(mysqlCallableStatement).getUpdateCount();
    verify(mysqlCallableStatement).getMoreResults();
    verify(mysqlCallableStatement).setFetchDirection(anyInt());
    verify(mysqlCallableStatement).getFetchDirection();
    verify(mysqlCallableStatement).getResultSetType();
    verify(mysqlCallableStatement).isClosed();
    verify(mysqlCallableStatement).setPoolable(anyBoolean());
    verify(mysqlCallableStatement).getWarnings();
    verify(mysqlCallableStatement).clearWarnings();
    verify(mysqlCallableStatement).setCursorName(anyString());
    verify(mysqlCallableStatement).setMaxFieldSize(anyInt());
    verify(mysqlCallableStatement).getMaxFieldSize();
    verify(mysqlCallableStatement).setMaxRows(anyInt());
    verify(mysqlCallableStatement).getMaxRows();
    verify(mysqlCallableStatement).setEscapeProcessing(anyBoolean());
    verify(mysqlCallableStatement).getResultSetConcurrency();
    verify(mysqlCallableStatement).getResultSetConcurrency();
    verify(mysqlCallableStatement).getResultSetType();
    verify(mysqlCallableStatement).getMetaData();
    verify(mysqlCallableStatement).getParameterMetaData();
    verify(mysqlCallableStatement).getMoreResults(anyInt());
    verify(mysqlCallableStatement).setFetchSize(anyInt());
    verify(mysqlCallableStatement).getFetchSize();
    verify(mysqlCallableStatement).getQueryTimeout();
    verify(mysqlCallableStatement).setQueryTimeout(anyInt());
    verify(mysqlCallableStatement).getResultSet();
    assertThat(connection, CoreMatchers.<Connection>is(swConnection));
}
 
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
/**
        * 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
/**
    * Testing a callable statement by calling a SQL procedure with 
    * boolean IN parameters, OUT parameters and IN_OUT parameters.
    */
public void testBooleanProcedureArgs() throws Exception
   {
       Statement stmt = createStatement();

       stmt.executeUpdate("create procedure booleanProc" + 
                          "( in inarg boolean, out outarg boolean, inout inoutarg boolean )" + 
                          "language java external name "+ 
                          "'com.splicemachine.dbTesting.functionTests." +
                          "tests.jdbcapi.ParameterMetaDataJdbc30Test.booleanProc' " +
                          "parameter style java");
       
       CallableStatement cs = prepareCall("CALL booleanProc(?,?,?)");
       cs.registerOutParameter(2,Types.BOOLEAN);
       cs.registerOutParameter(3,Types.BOOLEAN);

       //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", "false", "1", "0", "16", "BOOLEAN", "java.lang.Boolean", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "false", "1", "0", "16", "BOOLEAN", "java.lang.Boolean", "PARAMETER_MODE_OUT"},
               {"PARAMETER_NULLABLE", "false", "1", "0", "16", "BOOLEAN", "java.lang.Boolean", "PARAMETER_MODE_IN_OUT"},
           };
       
	testParameterMetaData(paramMetaData, parameterMetaDataArray0);

       cs.setBoolean( 1,  true );
       cs.setBoolean( 3, true );
       cs.execute();
       assertEquals( true, cs.getBoolean( 2 ) );
       assertEquals( false, cs.getBoolean( 3 ) );

       cs.setObject( 1,  Boolean.FALSE );
       cs.setObject( 3, Boolean.FALSE );
       cs.execute();
       assertEquals( false, cs.getBoolean( 2 ) );
       assertEquals( true, cs.getBoolean( 3 ) );

	stmt.close();
	cs.close();
}
 
Example 20
/**
    	* Testing a callable statement by calling a SQL procedure with 
* IN parameters, OUT parameters and IN_OUT parameters.
       *
* @exception SQLException if error occurs
    	*/
public void testCallableStatement () throws SQLException {

       	Statement stmt = createStatement();

       	stmt.executeUpdate("create procedure dummyint( " + 
			   "in a integer, in b integer, " + 
			   "out c integer, inout d integer) " + 
			   "language java external name "+ 
			   "'org.apache.derbyTesting.functionTests." +
			   "tests.jdbcapi.ParameterMetaDataJdbc30Test.dummyint' " +
			   "parameter style java");
      		CallableStatement cs = prepareCall("CALL dummyint(?,?,?,?)");
	// parameters 1 and 2 are input only
     		cs.setInt(1,1);
     		cs.setInt(2,1);
     		//parameter 3 is output only
     		cs.registerOutParameter(3,Types.INTEGER);
     		// parameter 4 is input and output
     		Object x = new Integer(1);
     		cs.setObject(4,x, Types.INTEGER);
     		cs.registerOutParameter(4,Types.INTEGER);

     		//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", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"},
	{"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"},
	{"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_OUT"},
	{"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN_OUT"}};
		
	testParameterMetaData(paramMetaData, parameterMetaDataArray0);

     		cs.execute();

	/*
	/* bug 4450 - parameter meta data info for the return parameter was giving
     		/* null pointer exception. In the past, we didn't need to keep the return
     		/* parameter info for callable statement execution and hence we never
     		/* generated the meta data for it. To fix the problem, at the parsing time,
     		/* I set a flag if the call statement is of ? = form. If so, the first
     		/* parameter is a return parameter and save it's meta data rather than
     		/* discarding it.
 	 */	

     		cs = prepareCall("? = call RDB(?)");
     		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", "127", "0", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_OUT"},
               {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"}};

               testParameterMetaData(paramMetaData, parameterMetaDataArray1);

	stmt.close();
	cs.close();
}