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

The following examples show how to use java.sql.PreparedStatement#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 want to check out the right sidebar which shows the related API usage.
Example 1
/** 
         * 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();
}
 
Example 2
Source Project: gemfirexd-oss   File: UDTTest.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * 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();
}
 
Example 3
/**
 * 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();
}
 
Example 4
Source Project: gemfirexd-oss   File: UDTTest.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * 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();
}
 
Example 5
Source Project: phoenix   File: QueryMetaDataTest.java    License: Apache License 2.0 5 votes vote down vote up
@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));
}
 
Example 6
@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));
}
 
Example 7
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();
}
 
Example 8
Source Project: phoenix   File: QueryMetaDataTest.java    License: Apache License 2.0 5 votes vote down vote up
@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));
}
 
Example 9
@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));
}
 
Example 10
Source Project: phoenix   File: QueryMetaDataTest.java    License: Apache License 2.0 5 votes vote down vote up
@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));
}
 
Example 11
@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));
}
 
Example 12
/** 
 * 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();
}
 
Example 13
Source Project: phoenix   File: QueryMetaDataTest.java    License: Apache License 2.0 5 votes vote down vote up
@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));
}
 
Example 14
@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));
}
 
Example 15
Source Project: phoenix   File: QueryMetaDataTest.java    License: Apache License 2.0 5 votes vote down vote up
@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));
}
 
Example 16
Source Project: phoenix   File: QueryMetaDataTest.java    License: Apache License 2.0 5 votes vote down vote up
@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));
}
 
Example 17
Source Project: phoenix   File: QueryMetaDataTest.java    License: Apache License 2.0 5 votes vote down vote up
@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));

}
 
Example 18
Source Project: phoenix   File: QueryMetaDataTest.java    License: Apache License 2.0 5 votes vote down vote up
@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));
    }
}
 
Example 19
Source Project: phoenix   File: QueryMetaDataTest.java    License: Apache License 2.0 5 votes vote down vote up
@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));
}
 
Example 20
/**
        * 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();
}