Examples with ParameterMetaData java.sql.ParameterMetaData used on opensource projects

Example 91 with ParameterMetaData

use of java.sql.ParameterMetaData in project derby by apache.

the class ParameterMetaDataJdbc30Test method testPreparedStatement.

/**
 * Testing a prepared statement.
 *
 * @exception SQLException if database access errors or other errors occur
 */
public void testPreparedStatement() throws SQLException {
    // next testing a prepared statement
    PreparedStatement ps = prepareStatement("insert into t values(?, ?, ?, ?, ?)");
    ps.setNull(1, java.sql.Types.CHAR);
    ps.setInt(2, 1);
    ps.setNull(3, java.sql.Types.INTEGER);
    ps.setBigDecimal(4, new BigDecimal("1"));
    ps.setNull(5, java.sql.Types.DATE);
    ParameterMetaData paramMetaData = ps.getParameterMetaData();
    assertEquals("Unexpected parameter count", 5, paramMetaData.getParameterCount());
    // expected values to be stored in a 2dim. array
    String[][] parameterMetaDataArray0 = { // isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
    { "PARAMETER_NULLABLE", "false", "5", "0", "1", "CHAR", "java.lang.String", "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" }, { "PARAMETER_NULLABLE", "true", "5", "0", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN" }, { "PARAMETER_NULLABLE", "false", "10", "0", "91", "DATE", "java.sql.Date", "PARAMETER_MODE_IN" } };
    testParameterMetaData(paramMetaData, parameterMetaDataArray0);
    /*
      		 *  JCC seems to report these parameters as MODE_UNKNOWN, where as Derby uses MODE_IN
      		 *  JCC behaviour with network server matches its behaviour with DB2
      		 *  getPrecision() returns 0 for CHAR/DATE/BIT types for Derby. JCC shows maxlen
		 */
    ps.execute();
    /*
		 * bug 4533 - associated parameters should not be included in the parameter meta data list
      		 * Following statement systab will generate 4 associated parameters for the 2
      		 * user parameters. This results in total 6 parameters for the prepared statement
      		 * internally. But we should only show 2 user visible parameters through
      		 * getParameterMetaData().
		 */
    ps = prepareStatement("select * from sys.systables where " + " CAST(tablename AS VARCHAR(128)) like ? and CAST(tableID AS CHAR(36)) like ?");
    ps.setString(1, "SYS%");
    ps.setString(2, "8000001%");
    paramMetaData = ps.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", "false", "128", "0", "12", "VARCHAR", "java.lang.String", "PARAMETER_MODE_IN" }, { "PARAMETER_NULLABLE", "false", "36", "0", "1", "CHAR", "java.lang.String", "PARAMETER_MODE_IN" } };
    testParameterMetaData(paramMetaData, parameterMetaDataArray1);
    ps.execute();
    ps.close();
}

Example 92 with ParameterMetaData

use of java.sql.ParameterMetaData in project derby by apache.

the class ParameterMetaDataJdbc30Test method testBooleanParameters.

/**
 * Test boolean parameters
 *
 * @exception SQLException if database access errors or other errors occur
 */
public void testBooleanParameters() throws SQLException {
    Statement stmt = createStatement();
    stmt.executeUpdate("create function booleanValue( b boolean )\n" + "returns varchar( 100 ) language java parameter style java no sql\n" + "external name 'org.apache.derbyTesting.functionTests.tests.lang.BooleanValuesTest.booleanValue'\n");
    stmt.executeUpdate("create table t_boolean( b boolean )");
    stmt.close();
    // 
    // Test metadata for parameters to boolean-valued functions.
    // 
    PreparedStatement ps = prepareStatement("values( booleanValue( ? ) )");
    ParameterMetaData paramMetaData = ps.getParameterMetaData();
    assertEquals("Unexpected parameter count", 1, 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" } };
    testParameterMetaData(paramMetaData, parameterMetaDataArray0);
    ps.close();
    // 
    // Test metadata for boolean-valued columns.
    // 
    ps = prepareStatement("insert into t_boolean values(?)");
    ps.setBoolean(1, true);
    paramMetaData = ps.getParameterMetaData();
    assertEquals("Unexpected parameter count", 1, paramMetaData.getParameterCount());
    parameterMetaDataArray0 = new String[][] { // isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
    { "PARAMETER_NULLABLE", "false", "1", "0", "16", "BOOLEAN", "java.lang.Boolean", "PARAMETER_MODE_IN" } };
    testParameterMetaData(paramMetaData, parameterMetaDataArray0);
    ps.close();
}

Example 93 with ParameterMetaData

use of java.sql.ParameterMetaData in project derby by apache.

the class ParameterMetaDataJdbc30Test method testSelectStatementUsingBoolean.

/**
 * testing SELECT statements for BOOLEANs (1 for true, 0 for false)
 *
 * @exception SQLException if error occurs
 */
public void testSelectStatementUsingBoolean() throws SQLException {
    /*
		 * Bug 4654 - Null Pointer exception while executuing a select with a
         	 * where clause parameter of type 'TRUE' or 'FALSE' constants. The existing prior to
         	 * exposing parameter metadata didn't need to fill in metadata information for where
         	 * clause parameter in the example above.
         	 * This no longer makes sense, for we cannot take BOOLEANs anymore.
         	 * replace with a simple where 1 = ?. Which would take either 1 for true, or 0 for false
		 */
    PreparedStatement ps = prepareStatement("select * from t " + "where 1=? for update");
    ParameterMetaData paramMetaData = ps.getParameterMetaData();
    assertEquals("Unexpected parameter count", 1, 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" } };
    testParameterMetaData(paramMetaData, parameterMetaDataArray0);
    dumpParameterMetaDataNegative(paramMetaData);
    ps.setInt(1, 1);
    ps.execute();
    ps.close();
}

Example 94 with ParameterMetaData

use of java.sql.ParameterMetaData in project derby by apache.

the class CaseExpressionTest method testParameters.

/**
 * Test that parameters can be used in CASE expressions.
 */
public void testParameters() throws SQLException {
    // If all of the result expressions are untyped parameters, the
    // type cannot be determined, and an error should be raised.
    assertCompileError("42X87", "values case when true then ? else ? end");
    // If at least one result expression is typed, the parameter should
    // get its type from it.
    PreparedStatement ps = prepareStatement("values case when true then ? else 1 end");
    // DERBY-6567: The result should be nullable, since the parameter
    // could be set to null. It used to be reported as not nullable.
    assertEquals(ResultSetMetaData.columnNullable, ps.getMetaData().isNullable(1));
    ps.setNull(1, Types.INTEGER);
    JDBC.assertSingleValueResultSet(ps.executeQuery(), null);
    ps.setInt(1, 1);
    JDBC.assertSingleValueResultSet(ps.executeQuery(), "1");
    ps = prepareStatement("values case when true then ? else cast(? as integer) end");
    ParameterMetaData params = ps.getParameterMetaData();
    assertEquals(Types.INTEGER, params.getParameterType(1));
    assertEquals(Types.INTEGER, params.getParameterType(2));
    ps.setInt(1, 1);
    ps.setInt(2, 2);
    JDBC.assertSingleValueResultSet(ps.executeQuery(), "1");
    // Parameters in the WHEN clause can be untyped. They will
    // implicitly get the BOOLEAN type.
    ps = prepareStatement("values case when ? then 1 else 0 end");
    assertEquals(Types.BOOLEAN, ps.getParameterMetaData().getParameterType(1));
    ps.setBoolean(1, true);
    JDBC.assertSingleValueResultSet(ps.executeQuery(), "1");
    ps.setBoolean(1, false);
    JDBC.assertSingleValueResultSet(ps.executeQuery(), "0");
    ps.setNull(1, Types.BOOLEAN);
    JDBC.assertSingleValueResultSet(ps.executeQuery(), "0");
}

Example 95 with ParameterMetaData

use of java.sql.ParameterMetaData in project derby by apache.

the class UDTTest method checkPMD.

/**
 * Check the ParameterMetaData for a statement whose first parameter is a UDT.
 */
private void checkPMD(Connection conn, String query, String expectedClassName, int expectedJDBCType, String expectedSQLTypeName, int expectedPrecision, int expectedScale) throws Exception {
    PreparedStatement ps = conn.prepareStatement(query);
    ParameterMetaData pmd = ps.getParameterMetaData();
    assertEquals(pmd.getParameterClassName(1), expectedClassName);
    assertEquals(pmd.getParameterType(1), expectedJDBCType);
    assertEquals(pmd.getParameterTypeName(1), expectedSQLTypeName);
    assertEquals(pmd.getPrecision(1), expectedPrecision);
    assertEquals(pmd.getScale(1), expectedScale);
    ps.close();
}