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

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

/**
 * Test dynamic arguments
 */
public void testDynamicArgsMetaData() throws SQLException {

	//since there is no getParameterMetaData() call available in JSR169 
	//implementations, do not run this test if we are running JSR169
	if (JDBC.vmSupportsJSR169()) return;

    PreparedStatement ps = prepareStatement(
        "select * from t1 where a = ? order by b " +
        "offset ? rows fetch next ? rows only");

    ParameterMetaData pmd = ps.getParameterMetaData();
    int[] expectedTypes = { Types.INTEGER, Types.BIGINT, Types.BIGINT };

    for (int i = 0; i < 3; i++) {
        assertEquals("Unexpected parameter type",
                     expectedTypes[i], pmd.getParameterType(i+1));
        assertEquals("Derby ? args are nullable",
                     // Why is that? Cf. logic in ParameterNode.setType
                     ParameterMetaData.parameterNullable,
                     pmd.isNullable(i+1));
    }
    ps.close();
}
 

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

/** 
         * test execute statements that no parameters would be returned if 
         * prepareStatement("execute statement systab using values('SYS%','8000001%')");
  *
  * @exception SQLException if error occurs
         */

public void testExecuteStatementUsing () throws SQLException {

	/*
	 * the test no longer tests 4552, but kept as an interesting test scenario
                * bug 4552 - no parameters would be returned for execute statement using
                * System.out.println("Bug 4552 - no parameters would be returned for execute statement using");
                * orig: ps = con.prepareStatement("execute statement systab using values('SYS%','8000001%')");
	 */
     		PreparedStatement ps = prepareStatement("select * from sys.systables " + 
						"where CAST(tablename AS VARCHAR(128)) like 'SYS%' and " + 
						"CAST(tableID AS VARCHAR(128)) like '8000001%'");

     		ParameterMetaData paramMetaData = ps.getParameterMetaData();
	assertEquals("Unexpected parameter count", 0, paramMetaData.getParameterCount());

	//expected values to be stored in a 2dim. array
               String parameterMetaDataArray0 [][] = null;

               testParameterMetaData(paramMetaData, parameterMetaDataArray0);

     		ps.execute();

	ps.close();
}
 

@Test
public void testDateAdditionExpressionMetaData2() throws Exception {
    String query = "SELECT entity_id,a_string FROM atable where ?+a_date>a_date";
    Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, TestUtil.TEST_PROPERTIES);
    PreparedStatement statement = conn.prepareStatement(query);
    ParameterMetaData pmd = statement.getParameterMetaData();
    assertEquals(1, pmd.getParameterCount());
    assertEquals(BigDecimal.class.getName(), pmd.getParameterClassName(1));
}
 

@Test
public void testDateSubstractExpressionMetaData2() throws Exception {
    String query = "SELECT entity_id,a_string FROM atable where a_date-?=a_date";
    Connection conn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TestUtil.TEST_PROPERTIES));
    PreparedStatement statement = conn.prepareStatement(query);
    ParameterMetaData pmd = statement.getParameterMetaData();
    assertEquals(1, pmd.getParameterCount());
    // FIXME: Should really be Date, but we currently don't know if we're 
    // comparing to a date or a number where this is being calculated 
    // (which would disambiguate it).
    assertEquals(null, pmd.getParameterClassName(1));
}
 

@Test
public void testCoerceToDecimalArithmeticMetaData() throws Exception {
    String[] ops = { "+", "-", "*", "/" };
    for (String op : ops) {
        String query = "SELECT entity_id,a_string FROM atable where a_integer" + op + "2.5" + op + "?=0";
        Connection conn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TestUtil.TEST_PROPERTIES));
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setInt(1, 4);
        ParameterMetaData pmd = statement.getParameterMetaData();
        assertEquals(1, pmd.getParameterCount());
        assertEquals(BigDecimal.class.getName(), pmd.getParameterClassName(1));
    }
}
 

@Test
public void testStringConcatMetaData() throws Exception {
	String query = "SELECT entity_id,a_string FROM atable where 2 || a_integer || ? like '2%'";
	Connection conn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TestUtil.TEST_PROPERTIES));
	PreparedStatement statement = conn.prepareStatement(query);
	statement.setString(1, "foo");
	ParameterMetaData pmd = statement.getParameterMetaData();
	assertEquals(1, pmd.getParameterCount());
	assertEquals(String.class.getName(), pmd.getParameterClassName(1));

}
 

@Test
public void testInListParameterMetaData3() throws Exception {
    String query = "SELECT a_string, b_string FROM atable WHERE ? IN ('foo')";
    Connection conn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TestUtil.TEST_PROPERTIES));
    PreparedStatement statement = conn.prepareStatement(query);
    ParameterMetaData pmd = statement.getParameterMetaData();
    assertEquals(1, pmd.getParameterCount());
    assertEquals(String.class.getName(), pmd.getParameterClassName(1));
}
 

@Test
public void testRowValueConstructorBindParamMetaDataWithBindArgsAtDiffPlacesOnLHSRHS() throws Exception {
    String query = "SELECT a_integer, x_integer FROM aTable WHERE (a_integer, ?) = (?, a_integer)";
    Connection conn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TestUtil.TEST_PROPERTIES));
    PreparedStatement statement = conn.prepareStatement(query);
    ParameterMetaData pmd = statement.getParameterMetaData();
    assertEquals(2, pmd.getParameterCount());
    assertEquals(Integer.class.getName(), pmd.getParameterClassName(1));
    assertEquals(Integer.class.getName(), pmd.getParameterClassName(2));
}
 

@Test
public void testSubstrParameterMetaData() throws Exception {
    String query = "SELECT a_string, b_string FROM atable WHERE substr(a_string,?,?) = ?";
    Connection conn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TestUtil.TEST_PROPERTIES));
    PreparedStatement statement = conn.prepareStatement(query);
    ParameterMetaData pmd = statement.getParameterMetaData();
    assertEquals(3, pmd.getParameterCount());
    assertEquals(Long.class.getName(), pmd.getParameterClassName(1));
    assertEquals(Long.class.getName(), pmd.getParameterClassName(2));
    assertEquals(String.class.getName(), pmd.getParameterClassName(3));
}
 

@Test
public void testParameterMetaData() throws Exception {
    String query = "SELECT a_string, b_string FROM atable WHERE organization_id=? and (a_integer = ? or a_date = ? or b_string = ? or a_string = 'foo')";
    Connection conn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TestUtil.TEST_PROPERTIES));
    PreparedStatement statement = conn.prepareStatement(query);
    ParameterMetaData pmd = statement.getParameterMetaData();
    assertEquals(4, pmd.getParameterCount());
    assertEquals(String.class.getName(), pmd.getParameterClassName(1));
    assertEquals(Integer.class.getName(), pmd.getParameterClassName(2));
    assertEquals(Date.class.getName(), pmd.getParameterClassName(3));
    assertEquals(String.class.getName(), pmd.getParameterClassName(4));
}
 

/** 
 * DERBY-44 added support for SELECT ... WHERE column LIKE ? ESCAPE ?
        * This test case tests
        *   a) that such a statement compiles, and
        *   b) that we get the correct error message if the escape
        *      sequence is an empty string (at one point this would
        *      lead to a StringIndexOutOfBoundsException)`
 *
 * @exception SQLException if error occurs
 */
public void testLikeEscaleStatement () throws SQLException {

     		//variation 1, testing DERBY-44 
     		PreparedStatement ps = prepareStatement("select * from sys.systables " +
						"where CAST(tablename AS VARCHAR(128)) like ? escape CAST(? AS VARCHAR(128))");
     		ps.setString (1, "SYS%");
     		ps.setString (2, "");
     		ParameterMetaData paramMetaData = ps.getParameterMetaData();
	assertEquals("Unexpected parameter count", 2, paramMetaData.getParameterCount());

	//expected values to be stored in a 2dim. array
               String parameterMetaDataArray0 [][] = {
               //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
               {"PARAMETER_NULLABLE", "false", "128", "0", "12", "VARCHAR", "java.lang.String", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "false", "128", "0", "12", "VARCHAR", "java.lang.String", "PARAMETER_MODE_IN"}};

               testParameterMetaData(paramMetaData, parameterMetaDataArray0);

     		try {
         		ResultSet rs = ps.executeQuery();
         		rs.next();
		fail("DERBY-44 failed (didn't get SQLSTATE 22019)");
         		rs.close();
     		} catch (SQLException e) {
		assertSQLState("22019", e);
     		}
	ps.close();
}
 

@Test
public void testRowValueConstructorBindParamMetaDataWithBindArgsAtDiffPlacesOnLHSRHS() throws Exception {
    String query = "SELECT a_integer, x_integer FROM aTable WHERE (a_integer, ?) = (?, a_integer)";
    Connection conn = DriverManager.getConnection(getUrl(), TestUtil.TEST_PROPERTIES);
    PreparedStatement statement = conn.prepareStatement(query);
    ParameterMetaData pmd = statement.getParameterMetaData();
    assertEquals(2, pmd.getParameterCount());
    assertEquals(Integer.class.getName(), pmd.getParameterClassName(1));
    assertEquals(Integer.class.getName(), pmd.getParameterClassName(2));
}
 

@Test
public void testDateAdditionExpressionMetaData1() throws Exception {
    String query = "SELECT entity_id,a_string FROM atable where 1+a_date+?>a_date";
    Connection conn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TestUtil.TEST_PROPERTIES));
    PreparedStatement statement = conn.prepareStatement(query);
    ParameterMetaData pmd = statement.getParameterMetaData();
    assertEquals(1, pmd.getParameterCount());
    assertEquals(BigDecimal.class.getName(), pmd.getParameterClassName(1));
}
 

@Test
public void testDateSubstractExpressionMetaData2() throws Exception {
    String query = "SELECT entity_id,a_string FROM atable where a_date-?=a_date";
    Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, TestUtil.TEST_PROPERTIES);
    PreparedStatement statement = conn.prepareStatement(query);
    ParameterMetaData pmd = statement.getParameterMetaData();
    assertEquals(1, pmd.getParameterCount());
    // FIXME: Should really be Date, but we currently don't know if we're 
    // comparing to a date or a number where this is being calculated 
    // (which would disambiguate it).
    assertEquals(null, pmd.getParameterClassName(1));
}
 

@Test
public void testRowValueConstructorBindParamMetaDataWithMoreNumberOfBindArgs() throws Exception {
    String query = "SELECT a_integer, x_integer FROM aTable WHERE (a_integer, x_integer) = (?, ?, ?)";
    Connection conn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TestUtil.TEST_PROPERTIES));
    PreparedStatement statement = conn.prepareStatement(query);
    ParameterMetaData pmd = statement.getParameterMetaData();
    assertEquals(3, pmd.getParameterCount());
    assertEquals(Integer.class.getName(), pmd.getParameterClassName(1));
    assertEquals(Integer.class.getName(), pmd.getParameterClassName(2));
    assertEquals(null, pmd.getParameterClassName(3));
}
 

private void checkStatementExecuteQuery(Connection connection,
    boolean prepare) throws SQLException {
  final String sql = "select * from (\n"
      + "  values (1, 'a'), (null, 'b'), (3, 'c')) as t (c1, c2)";
  final Statement statement;
  final ResultSet resultSet;
  final ParameterMetaData parameterMetaData;
  if (prepare) {
    final PreparedStatement ps = connection.prepareStatement(sql);
    statement = ps;
    parameterMetaData = ps.getParameterMetaData();
    resultSet = ps.executeQuery();
  } else {
    statement = connection.createStatement();
    parameterMetaData = null;
    resultSet = statement.executeQuery(sql);
  }
  if (parameterMetaData != null) {
    assertThat(parameterMetaData.getParameterCount(), equalTo(0));
  }
  final ResultSetMetaData metaData = resultSet.getMetaData();
  assertEquals(2, metaData.getColumnCount());
  assertEquals("C1", metaData.getColumnName(1));
  assertEquals("C2", metaData.getColumnName(2));
  assertTrue(resultSet.next());
  assertTrue(resultSet.next());
  assertTrue(resultSet.next());
  assertFalse(resultSet.next());
  resultSet.close();
  statement.close();
  connection.close();
}
 

@Test
public void testKeyPrefixParameterMetaData() throws Exception {
    String query = "SELECT a_string, b_string FROM atable WHERE organization_id='000000000000000' and substr(entity_id,1,3)=? and a_string = 'foo'";
    Connection conn = DriverManager.getConnection(getUrl(), TestUtil.TEST_PROPERTIES);
    PreparedStatement statement = conn.prepareStatement(query);
    ParameterMetaData pmd = statement.getParameterMetaData();
    assertEquals(1, pmd.getParameterCount());
    assertEquals(String.class.getName(), pmd.getParameterClassName(1));
}
 

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