Java Code Examples for java.sql.DatabaseMetaData#getProcedures()

The following examples show how to use java.sql.DatabaseMetaData#getProcedures() . 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: MetadataTest.java    From FoxTelem with GNU General Public License v3.0 6 votes vote down vote up
/**
 * Tests the implementation of Information Schema for description
 * of stored procedures available in a catalog.
 */
public void testGetProceduresUsingInfoSchema() throws Exception {
    createProcedure("sp1", "()\n BEGIN\nSELECT 1;end\n");
    Properties props = new Properties();
    props.setProperty(PropertyKey.useInformationSchema.getKeyName(), "true");
    Connection conn1 = null;
    try {
        conn1 = getConnectionWithProps(props);
        DatabaseMetaData metaData = conn1.getMetaData();
        this.rs = metaData.getProcedures(null, null, "sp1");
        this.rs.next();
        assertEquals("sp1", this.rs.getString("PROCEDURE_NAME"));
        assertEquals("1", this.rs.getString("PROCEDURE_TYPE"));
    } finally {
        if (conn1 != null) {
            conn1.close();
        }
    }
}
 
Example 2
Source File: MetadataTest.java    From r-course with MIT License 6 votes vote down vote up
/**
 * Tests the implementation of Information Schema for description
 * of stored procedures available in a catalog.
 */
public void testGetProceduresUsingInfoSchema() throws Exception {
    if (versionMeetsMinimum(5, 0, 7)) {
        createProcedure("sp1", "()\n BEGIN\nSELECT 1;end\n");
        Properties props = new Properties();
        props.put("useInformationSchema", "true");
        Connection conn1 = null;
        try {
            conn1 = getConnectionWithProps(props);
            DatabaseMetaData metaData = conn1.getMetaData();
            this.rs = metaData.getProcedures(null, null, "sp1");
            this.rs.next();
            assertEquals("sp1", this.rs.getString("PROCEDURE_NAME"));
            assertEquals("1", this.rs.getString("PROCEDURE_TYPE"));
        } finally {
            if (conn1 != null) {
                conn1.close();
            }
        }
    }
}
 
Example 3
Source File: MetaDataRegressionTest.java    From r-course with MIT License 6 votes vote down vote up
private void checkGetProceduresForBug69298(String stepDescription, Connection testConn) throws Exception {
    DatabaseMetaData testDbMetaData = testConn.getMetaData();
    ResultSet proceduresMD = testDbMetaData.getProcedures(null, null, "testBug69298_%");
    String sd = stepDescription + " getProcedures() ";

    assertTrue(sd + "1st of 2 rows expected.", proceduresMD.next());

    // function: testBug69298_func
    assertEquals(sd + "-> PROCEDURE_CAT", testConn.getCatalog(), proceduresMD.getString("PROCEDURE_CAT"));
    assertEquals(sd + "-> PROCEDURE_SCHEM", null, proceduresMD.getString("PROCEDURE_SCHEM"));
    assertEquals(sd + "-> PROCEDURE_NAME", "testBug69298_func", proceduresMD.getString("PROCEDURE_NAME"));
    assertEquals(sd + "-> REMARKS", "testBug69298_func comment", proceduresMD.getString("REMARKS"));
    assertEquals(sd + "-> PROCEDURE_TYPE", DatabaseMetaData.procedureReturnsResult, proceduresMD.getShort("PROCEDURE_TYPE"));

    assertTrue(sd + "2nd of 2 rows expected.", proceduresMD.next());

    // procedure: testBug69298_proc
    assertEquals(sd + "-> PROCEDURE_CAT", testConn.getCatalog(), proceduresMD.getString("PROCEDURE_CAT"));
    assertEquals(sd + "-> PROCEDURE_SCHEM", null, proceduresMD.getString("PROCEDURE_SCHEM"));
    assertEquals(sd + "-> PROCEDURE_NAME", "testBug69298_proc", proceduresMD.getString("PROCEDURE_NAME"));
    assertEquals(sd + "-> REMARKS", "testBug69298_proc comment", proceduresMD.getString("REMARKS"));
    assertEquals(sd + "-> PROCEDURE_TYPE", DatabaseMetaData.procedureNoResult, proceduresMD.getShort("PROCEDURE_TYPE"));

    assertFalse(stepDescription + "no more rows expected.", proceduresMD.next());
}
 
Example 4
Source File: Show.java    From jsqsh with Apache License 2.0 6 votes vote down vote up
private ResultSet doProcedures(Session session, Connection con, Options options)
    throws SQLException {
    
    if (options.arguments.size() > 2) {
        
        session.err.println("Use: \\show procedures [[[catalog.]schema-pattern.]proc-pattern]");
        return null;
    }
    
    SQLConnectionContext ctx = (SQLConnectionContext) session.getConnectionContext();
    SQLObjectName name =
        (options.arguments.size() == 2) ? new SQLObjectName(ctx, options.arguments.get(1))
            : new SQLObjectName(ctx, "%");
    
    DatabaseMetaData meta = con.getMetaData();
    return meta.getProcedures(
        (options.catalog != null ? options.catalog : name.getCatalog()),
        (options.schemaPattern != null ? options.schemaPattern : name.getSchema()),
        (options.tablePattern != null ? options.tablePattern : name.getName()));
}
 
Example 5
Source File: MetadataTest.java    From Komondor with GNU General Public License v3.0 6 votes vote down vote up
/**
 * Tests the implementation of Information Schema for description
 * of stored procedures available in a catalog.
 */
public void testGetProceduresUsingInfoSchema() throws Exception {
    if (versionMeetsMinimum(5, 0, 7)) {
        createProcedure("sp1", "()\n BEGIN\nSELECT 1;end\n");
        Properties props = new Properties();
        props.put("useInformationSchema", "true");
        Connection conn1 = null;
        try {
            conn1 = getConnectionWithProps(props);
            DatabaseMetaData metaData = conn1.getMetaData();
            this.rs = metaData.getProcedures(null, null, "sp1");
            this.rs.next();
            assertEquals("sp1", this.rs.getString("PROCEDURE_NAME"));
            assertEquals("1", this.rs.getString("PROCEDURE_TYPE"));
        } finally {
            if (conn1 != null) {
                conn1.close();
            }
        }
    }
}
 
Example 6
Source File: MetaDataRegressionTest.java    From Komondor with GNU General Public License v3.0 6 votes vote down vote up
private void checkGetProceduresForBug69298(String stepDescription, Connection testConn) throws Exception {
    DatabaseMetaData testDbMetaData = testConn.getMetaData();
    ResultSet proceduresMD = testDbMetaData.getProcedures(null, null, "testBug69298_%");
    String sd = stepDescription + " getProcedures() ";

    assertTrue(sd + "1st of 2 rows expected.", proceduresMD.next());

    // function: testBug69298_func
    assertEquals(sd + "-> PROCEDURE_CAT", testConn.getCatalog(), proceduresMD.getString("PROCEDURE_CAT"));
    assertEquals(sd + "-> PROCEDURE_SCHEM", null, proceduresMD.getString("PROCEDURE_SCHEM"));
    assertEquals(sd + "-> PROCEDURE_NAME", "testBug69298_func", proceduresMD.getString("PROCEDURE_NAME"));
    assertEquals(sd + "-> REMARKS", "testBug69298_func comment", proceduresMD.getString("REMARKS"));
    assertEquals(sd + "-> PROCEDURE_TYPE", DatabaseMetaData.procedureReturnsResult, proceduresMD.getShort("PROCEDURE_TYPE"));

    assertTrue(sd + "2nd of 2 rows expected.", proceduresMD.next());

    // procedure: testBug69298_proc
    assertEquals(sd + "-> PROCEDURE_CAT", testConn.getCatalog(), proceduresMD.getString("PROCEDURE_CAT"));
    assertEquals(sd + "-> PROCEDURE_SCHEM", null, proceduresMD.getString("PROCEDURE_SCHEM"));
    assertEquals(sd + "-> PROCEDURE_NAME", "testBug69298_proc", proceduresMD.getString("PROCEDURE_NAME"));
    assertEquals(sd + "-> REMARKS", "testBug69298_proc comment", proceduresMD.getString("REMARKS"));
    assertEquals(sd + "-> PROCEDURE_TYPE", DatabaseMetaData.procedureNoResult, proceduresMD.getShort("PROCEDURE_TYPE"));

    assertFalse(stepDescription + "no more rows expected.", proceduresMD.next());
}
 
Example 7
Source File: MetaDataRegressionTest.java    From Komondor with GNU General Public License v3.0 5 votes vote down vote up
private void checkGetProceduresForBug69298(String stepDescription, Connection testConn) throws Exception {
    DatabaseMetaData testDbMetaData = testConn.getMetaData();
    ResultSet proceduresMD = testDbMetaData.getProcedures(null, null, "testBug69298_%");
    String sd = stepDescription + " getProcedures() ";
    boolean isGetProceduresReturnsFunctions = ((ConnectionProperties) testConn).getGetProceduresReturnsFunctions();

    if (isGetProceduresReturnsFunctions) {
        assertTrue(sd + "1st of 2 rows expected.", proceduresMD.next());

        // function: testBug69298_func
        assertEquals(sd + "-> PROCEDURE_CAT", testConn.getCatalog(), proceduresMD.getString("PROCEDURE_CAT"));
        assertEquals(sd + "-> PROCEDURE_SCHEM", null, proceduresMD.getString("PROCEDURE_SCHEM"));
        assertEquals(sd + "-> PROCEDURE_NAME", "testBug69298_func", proceduresMD.getString("PROCEDURE_NAME"));
        assertEquals(sd + "-> REMARKS", "testBug69298_func comment", proceduresMD.getString("REMARKS"));
        assertEquals(sd + "-> PROCEDURE_TYPE", DatabaseMetaData.procedureReturnsResult, proceduresMD.getShort("PROCEDURE_TYPE"));
        assertEquals(sd + "-> SPECIFIC_NAME", "testBug69298_func", proceduresMD.getString("SPECIFIC_NAME"));

        assertTrue(sd + "2nd of 2 rows expected.", proceduresMD.next());
    } else {
        assertTrue(sd + "one row expected.", proceduresMD.next());
    }

    // procedure: testBug69298_proc
    assertEquals(sd + "-> PROCEDURE_CAT", testConn.getCatalog(), proceduresMD.getString("PROCEDURE_CAT"));
    assertEquals(sd + "-> PROCEDURE_SCHEM", null, proceduresMD.getString("PROCEDURE_SCHEM"));
    assertEquals(sd + "-> PROCEDURE_NAME", "testBug69298_proc", proceduresMD.getString("PROCEDURE_NAME"));
    assertEquals(sd + "-> REMARKS", "testBug69298_proc comment", proceduresMD.getString("REMARKS"));
    assertEquals(sd + "-> PROCEDURE_TYPE", DatabaseMetaData.procedureNoResult, proceduresMD.getShort("PROCEDURE_TYPE"));
    assertEquals(sd + "-> SPECIFIC_NAME", "testBug69298_proc", proceduresMD.getString("SPECIFIC_NAME"));

    assertFalse(stepDescription + "no more rows expected.", proceduresMD.next());
}
 
Example 8
Source File: ClientServerDUnit.java    From gemfirexd-oss with Apache License 2.0 5 votes vote down vote up
private void checkDBMetadata(Connection conn, String url) throws Exception {
  DatabaseMetaData dbmd = conn.getMetaData();
  String actualUrl = dbmd.getURL();
  // remove any trailing slash
  getLogWriter().info("Got DB " + dbmd.getDatabaseProductName() + ' '
      + dbmd.getDatabaseProductVersion() + " using URL " + actualUrl);
  assertEquals("Expected the provided URL to match", url.replaceFirst("/$",
      ""), actualUrl.replaceFirst("/$", ""));
  ResultSet rs = dbmd.getCatalogs();
  while (rs.next()) {
    getLogWriter().info("Got DB catalog: " + rs.getString(1));
  }
  rs.close();
  rs = dbmd.getSchemas();
  while (rs.next()) {
    getLogWriter().info("Got DB schema: " + rs.getString(1)
        + " in catalog=" + rs.getString(2));
  }
  rs.close();
  rs = dbmd.getProcedures(null, null, null);
  while (rs.next()) {
    getLogWriter().info("Got Procedure " + rs.getString(3) + " in catalog="
        + rs.getString(1) + ", schema=" + rs.getString(2));
  }
  rs.close();
  // also check for a few flags that are failing over network connection
  assertTrue(dbmd.othersInsertsAreVisible(ResultSet.TYPE_FORWARD_ONLY));
  assertTrue(dbmd.othersDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY));
}
 
Example 9
Source File: dbInfo.java    From openbd-core with GNU General Public License v3.0 5 votes vote down vote up
private cfData typeProcedures(cfSession _session, cfDataSource datasource, String dbname, String pattern ) throws cfmRunTimeException{
	Connection c = null;
	try {
		c = datasource.getPooledConnection();
		
		DatabaseMetaData metaData = c.getMetaData();
		
		cfQueryResultData queryResult = new cfQueryResultData(new String[] { "procedure_name", "procedure_type", "remarks" }, "DBINFO");
		
		ResultSet rset = metaData.getProcedures( dbname, null, pattern );
		int row=1;
		while ( rset.next() ){
			queryResult.addRow(1);

			queryResult.setCell(row, 1, new cfStringData(rset.getString(3)) );
			queryResult.setCell(row, 2, new cfStringData(rset.getString(8)) );
			queryResult.setCell(row, 3, new cfStringData(rset.getString(7)) );
			
			row++;
		}
		rset.close();

		return queryResult;
		
	} catch (SQLException e) {
		throwException(_session,  e.getMessage() );
	} finally {
		datasource.close(c);
	}
	
	return null;
}
 
Example 10
Source File: CallStatementOperationIT.java    From spliceengine with GNU Affero General Public License v3.0 5 votes vote down vote up
@Test
public void testCallSqlProcedures() throws Exception {
       	DatabaseMetaData dmd = methodWatcher.getOrCreateConnection().getMetaData();
           ResultSet rs = dmd.getProcedures(null, null, null);
           while(rs.next()){ // TODO No Test
           }
           DbUtils.closeQuietly(rs);
   }
 
Example 11
Source File: ClientServerDUnit.java    From gemfirexd-oss with Apache License 2.0 5 votes vote down vote up
private void checkDBMetadata(Connection conn, String url) throws Exception {
  DatabaseMetaData dbmd = conn.getMetaData();
  String actualUrl = dbmd.getURL();
  // remove any trailing slash
  getLogWriter().info("Got DB " + dbmd.getDatabaseProductName() + ' '
      + dbmd.getDatabaseProductVersion() + " using URL " + actualUrl);
  assertEquals("Expected the provided URL to match", url.replaceFirst("/$",
      ""), actualUrl.replaceFirst("/$", ""));
  ResultSet rs = dbmd.getCatalogs();
  while (rs.next()) {
    getLogWriter().info("Got DB catalog: " + rs.getString(1));
  }
  rs.close();
  rs = dbmd.getSchemas();
  while (rs.next()) {
    getLogWriter().info("Got DB schema: " + rs.getString(1)
        + " in catalog=" + rs.getString(2));
  }
  rs.close();
  rs = dbmd.getProcedures(null, null, null);
  while (rs.next()) {
    getLogWriter().info("Got Procedure " + rs.getString(3) + " in catalog="
        + rs.getString(1) + ", schema=" + rs.getString(2));
  }
  rs.close();
  // also check for a few flags that are failing over network connection
  assertTrue(dbmd.othersInsertsAreVisible(ResultSet.TYPE_FORWARD_ONLY));
  assertTrue(dbmd.othersDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY));
}
 
Example 12
Source File: MetadataUtilities.java    From netbeans with Apache License 2.0 5 votes vote down vote up
/**
 * Call {@link DatabaseMetaData#getProcedures(String, String, String)},
 * wrapping any internal runtime exception into an {@link SQLException}.
 */
public static ResultSet getProcedures(DatabaseMetaData dmd,
        String catalog, String schemaPattern, String procedureNamePattern)
        throws SQLException {
    try {
        return dmd.getProcedures(catalog, schemaPattern,
                procedureNamePattern);
    } catch (SQLException e) {
        throw e;
    } catch (Throwable t) {
        throw new SQLException(t);
    }
}
 
Example 13
Source File: TestDatabaseMetaData.java    From evosql with Apache License 2.0 5 votes vote down vote up
/**
 * Basic test of DatabaseMetaData functions that access system tables
 */
public void testTwo() throws Exception {

    Connection conn = newConnection();
    int        updateCount;

    try {
        TestUtil.testScript(conn, "testrun/hsqldb/TestSelf.txt");

        DatabaseMetaData dbmeta = conn.getMetaData();

        dbmeta.allProceduresAreCallable();
        dbmeta.getBestRowIdentifier(null, null, "T_1",
                                    DatabaseMetaData.bestRowTransaction,
                                    true);
        dbmeta.getCatalogs();
        dbmeta.getColumnPrivileges(null, "PUBLIC", "T_1", "%");
        dbmeta.getColumns("PUBLIC", "PUBLIC", "T_1", "%");
        dbmeta.getCrossReference(null, null, "T_1", null, null, "T_1");
        dbmeta.getExportedKeys(null, null, "T_1");
        dbmeta.getFunctionColumns(null, "%", "%", "%");
        dbmeta.getFunctions(null, "%", "%");
        dbmeta.getImportedKeys("PUBLIC", "PUBLIC", "T_1");
        dbmeta.getIndexInfo("PUBLIC", "PUBLIC", "T1", true, true);
        dbmeta.getPrimaryKeys("PUBLIC", "PUBLIC", "T_1");
        dbmeta.getProcedureColumns(null, null, "%", "%");
        dbmeta.getProcedures("PUBLIC", "%", "%");
        dbmeta.getSchemas(null, "#");
        dbmeta.getTablePrivileges(null, "%", "%");
        dbmeta.getUDTs(null, "%", "%", new int[]{ Types.DISTINCT });
    } catch (Exception e) {
        assertTrue("unable to prepare or execute DDL", false);
    } finally {
        conn.close();
    }
}
 
Example 14
Source File: MetaDataRegressionTest.java    From r-course with MIT License 5 votes vote down vote up
private void checkMetaDataInfoForBug17248345(Connection testConn) throws Exception {
    DatabaseMetaData testDbMetaData = testConn.getMetaData();
    ResultSet rsMD;
    boolean useInfoSchema = ((ConnectionProperties) testConn).getUseInformationSchema();
    boolean getProcRetFunc = ((ConnectionProperties) testConn).getGetProceduresReturnsFunctions();
    String stepDescription = "Prop. useInfoSchema(" + (useInfoSchema ? 1 : 0) + ") + getProcRetFunc(" + (getProcRetFunc ? 1 : 0) + "):";
    String sd;

    // getProcedures() must return 2 records, even if getProceduresReturnsFunctions is false once this flag only
    // applies to JDBC4. When exists a procedure and a function with same name, function is returned first.
    sd = stepDescription + " getProcedures() ";
    rsMD = testDbMetaData.getProcedures(null, null, "testBug17248345");
    assertTrue(sd + "1st of 2 rows expected.", rsMD.next());
    assertEquals(sd + " -> PROCEDURE_NAME", "testBug17248345", rsMD.getString("PROCEDURE_NAME"));
    assertTrue(sd + "2nd of 2 rows expected.", rsMD.next());
    assertEquals(sd + " -> PROCEDURE_NAME", "testBug17248345", rsMD.getString("PROCEDURE_NAME"));
    assertFalse(sd + "no more rows expected.", rsMD.next());

    // getProcedureColumns() must return 3 records, even if getProceduresReturnsFunctions is false once this flag
    // only applies to JDBC4. When exists a procedure and a function with same name, function is returned first.
    sd = stepDescription + " getProcedureColumns() ";
    rsMD = testDbMetaData.getProcedureColumns(null, null, "testBug17248345", "%");
    assertTrue(sd + "1st of 3 rows expected.", rsMD.next());
    assertEquals(sd + " -> PROCEDURE_NAME", "testBug17248345", rsMD.getString("PROCEDURE_NAME"));
    assertEquals(sd + " -> COLUMN_NAME", "", rsMD.getString("COLUMN_NAME"));
    assertTrue(sd + "2nd of 3 rows expected.", rsMD.next());
    assertEquals(sd + " -> PROCEDURE_NAME", "testBug17248345", rsMD.getString("PROCEDURE_NAME"));
    assertEquals(sd + " -> COLUMN_NAME", "funccol", rsMD.getString("COLUMN_NAME"));
    assertTrue(sd + "3rd of 3 rows expected.", rsMD.next());
    assertEquals(sd + " -> PROCEDURE_NAME", "testBug17248345", rsMD.getString("PROCEDURE_NAME"));
    assertEquals(sd + " -> COLUMN_NAME", "proccol", rsMD.getString("COLUMN_NAME"));
    assertFalse(sd + "no more rows expected.", rsMD.next());
}
 
Example 15
Source File: MetaDataRegressionTest.java    From r-course with MIT License 5 votes vote down vote up
private void checkGetProceduresForBug69298(String stepDescription, Connection testConn) throws Exception {
    DatabaseMetaData testDbMetaData = testConn.getMetaData();
    ResultSet proceduresMD = testDbMetaData.getProcedures(null, null, "testBug69298_%");
    String sd = stepDescription + " getProcedures() ";
    boolean isGetProceduresReturnsFunctions = ((ConnectionProperties) testConn).getGetProceduresReturnsFunctions();

    if (isGetProceduresReturnsFunctions) {
        assertTrue(sd + "1st of 2 rows expected.", proceduresMD.next());

        // function: testBug69298_func
        assertEquals(sd + "-> PROCEDURE_CAT", testConn.getCatalog(), proceduresMD.getString("PROCEDURE_CAT"));
        assertEquals(sd + "-> PROCEDURE_SCHEM", null, proceduresMD.getString("PROCEDURE_SCHEM"));
        assertEquals(sd + "-> PROCEDURE_NAME", "testBug69298_func", proceduresMD.getString("PROCEDURE_NAME"));
        assertEquals(sd + "-> REMARKS", "testBug69298_func comment", proceduresMD.getString("REMARKS"));
        assertEquals(sd + "-> PROCEDURE_TYPE", DatabaseMetaData.procedureReturnsResult, proceduresMD.getShort("PROCEDURE_TYPE"));
        assertEquals(sd + "-> SPECIFIC_NAME", "testBug69298_func", proceduresMD.getString("SPECIFIC_NAME"));

        assertTrue(sd + "2nd of 2 rows expected.", proceduresMD.next());
    } else {
        assertTrue(sd + "one row expected.", proceduresMD.next());
    }

    // procedure: testBug69298_proc
    assertEquals(sd + "-> PROCEDURE_CAT", testConn.getCatalog(), proceduresMD.getString("PROCEDURE_CAT"));
    assertEquals(sd + "-> PROCEDURE_SCHEM", null, proceduresMD.getString("PROCEDURE_SCHEM"));
    assertEquals(sd + "-> PROCEDURE_NAME", "testBug69298_proc", proceduresMD.getString("PROCEDURE_NAME"));
    assertEquals(sd + "-> REMARKS", "testBug69298_proc comment", proceduresMD.getString("REMARKS"));
    assertEquals(sd + "-> PROCEDURE_TYPE", DatabaseMetaData.procedureNoResult, proceduresMD.getShort("PROCEDURE_TYPE"));
    assertEquals(sd + "-> SPECIFIC_NAME", "testBug69298_proc", proceduresMD.getString("SPECIFIC_NAME"));

    assertFalse(stepDescription + "no more rows expected.", proceduresMD.next());
}
 
Example 16
Source File: MetaDataRegressionTest.java    From Komondor with GNU General Public License v3.0 4 votes vote down vote up
private void checkMetaDataInfoForBug17248345(Connection testConn) throws Exception {
    DatabaseMetaData testDbMetaData = testConn.getMetaData();
    ResultSet rsMD;
    boolean useInfoSchema = ((ConnectionProperties) testConn).getUseInformationSchema();
    boolean getProcRetFunc = ((ConnectionProperties) testConn).getGetProceduresReturnsFunctions();
    String stepDescription = "Prop. useInfoSchema(" + (useInfoSchema ? 1 : 0) + ") + getProcRetFunc(" + (getProcRetFunc ? 1 : 0) + "):";
    String sd;

    // getFunctions() must return 1 record.
    sd = stepDescription + " getFunctions() ";
    rsMD = testDbMetaData.getFunctions(null, null, "testBug17248345");
    assertTrue(sd + "one row expected.", rsMD.next());
    assertEquals(sd + " -> FUNCTION_NAME", "testBug17248345", rsMD.getString("FUNCTION_NAME"));
    assertFalse(sd + "no more rows expected.", rsMD.next());

    // getFunctionColumns() must return 2 records (func return + func param).
    sd = stepDescription + " getFunctionColumns() ";
    rsMD = testDbMetaData.getFunctionColumns(null, null, "testBug17248345", "%");
    assertTrue(sd + "1st of 2 rows expected.", rsMD.next());
    assertEquals(sd + " -> FUNCTION_NAME", "testBug17248345", rsMD.getString("FUNCTION_NAME"));
    assertEquals(sd + " -> COLUMN_NAME", "", rsMD.getString("COLUMN_NAME"));
    assertTrue(sd + "2nd of 2 rows expected.", rsMD.next());
    assertEquals(sd + " -> FUNCTION_NAME", "testBug17248345", rsMD.getString("FUNCTION_NAME"));
    assertEquals(sd + " -> COLUMN_NAME", "funccol", rsMD.getString("COLUMN_NAME"));
    assertFalse(sd + "no more rows expected.", rsMD.next());

    // getProcedures() must return 1 or 2 records, depending on if getProceduresReturnsFunctions is false or true
    // respectively. When exists a procedure and a function with same name, function is returned first.
    sd = stepDescription + " getProcedures() ";
    rsMD = testDbMetaData.getProcedures(null, null, "testBug17248345");
    if (getProcRetFunc) {
        assertTrue(sd + "1st of 2 rows expected.", rsMD.next());
        assertEquals(sd + " -> PROCEDURE_NAME", "testBug17248345", rsMD.getString("PROCEDURE_NAME"));
        assertTrue(sd + "2nd of 2 rows expected.", rsMD.next());
    } else {
        assertTrue(sd + "one row expected.", rsMD.next());
    }
    assertEquals(sd + " -> PROCEDURE_NAME", "testBug17248345", rsMD.getString("PROCEDURE_NAME"));
    assertFalse(sd + "no more rows expected.", rsMD.next());

    // getProcedureColumns() must return 1 or 3 records, depending on if getProceduresReturnsFunctions is false or
    // true respectively. When exists a procedure and a function with same name, function is returned first.
    sd = stepDescription + " getProcedureColumns() ";
    rsMD = testDbMetaData.getProcedureColumns(null, null, "testBug17248345", "%");
    if (getProcRetFunc) {
        assertTrue(sd + "1st of 3 rows expected.", rsMD.next());
        assertEquals(sd + " -> PROCEDURE_NAME", "testBug17248345", rsMD.getString("PROCEDURE_NAME"));
        assertEquals(sd + " -> COLUMN_NAME", "", rsMD.getString("COLUMN_NAME"));
        assertTrue(sd + "2nd of 3 rows expected.", rsMD.next());
        assertEquals(sd + " -> PROCEDURE_NAME", "testBug17248345", rsMD.getString("PROCEDURE_NAME"));
        assertEquals(sd + " -> COLUMN_NAME", "funccol", rsMD.getString("COLUMN_NAME"));
        assertTrue(sd + "3rd of 3 rows expected.", rsMD.next());
    } else {
        assertTrue(sd + "one row expected.", rsMD.next());
    }
    assertEquals(sd + " -> PROCEDURE_NAME", "testBug17248345", rsMD.getString("PROCEDURE_NAME"));
    assertEquals(sd + " -> COLUMN_NAME", "proccol", rsMD.getString("COLUMN_NAME"));
    assertFalse(sd + "no more rows expected.", rsMD.next());
}
 
Example 17
Source File: DatabaseMetaDataTest.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
/**
 * Helper method for testing getProcedures - calls dmd.getProcedures for
 * the JDBC call, and getProceduresODBC for the ODBC procedure
 * @throws SQLException
 */
private ResultSet[] getProcedures(
        String catalog, String schemaPattern, String procedureNamePattern)
    throws SQLException
{
    ResultSet[] rs = new ResultSet[2];
    DatabaseMetaData dmd = getDMD();
    rs[0]= dmd.getProcedures(catalog, schemaPattern, procedureNamePattern);
    rs[1]= getProceduresODBC(catalog, schemaPattern, procedureNamePattern);

    String[] columnNames = new String[] {
            "PROCEDURE_CAT","PROCEDURE_SCHEM","PROCEDURE_NAME",
            "RESERVED1","RESERVED2","RESERVED3",
            "REMARKS","PROCEDURE_TYPE","SPECIFIC_NAME"};

    int[] columnTypes = new int[] {
            Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.INTEGER,
            Types.INTEGER, Types.INTEGER, Types.VARCHAR, Types.SMALLINT,
            Types.VARCHAR};

    boolean[] nullability = new boolean[] {
            false, false, false, true, true, true, true, true, false};

    // JDBC result set
    assertMetaDataResultSet(rs[0], columnNames, columnTypes, nullability);

    // change the shape for ODBC - one less column, no SPECIFIC_NAME (9)

    String[] odbcColumnNames = new String[columnNames.length - 1];
    System.arraycopy(columnNames, 0, odbcColumnNames, 0, odbcColumnNames.length);
    int[] odbcColumnTypes = new int[columnTypes.length - 1];
    System.arraycopy(columnTypes, 0, odbcColumnTypes, 0, odbcColumnTypes.length);
    boolean[] odbcNullability = new boolean[nullability.length - 1];
    System.arraycopy(nullability, 0, odbcNullability, 0, odbcNullability.length);

    // Change column names
    odbcColumnNames[4 - 1] = "NUM_INPUT_PARAMS";
    odbcColumnNames[5 - 1] = "NUM_OUTPUT_PARAMS";
    odbcColumnNames[6 - 1] = "NUM_RESULT_SETS";

    // ODBC result set.
    assertMetaDataResultSet(
            rs[1], odbcColumnNames, odbcColumnTypes, odbcNullability);

    return rs;
}
 
Example 18
Source File: LangProcedureTest.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
private static void checkMatchingProcedures(Connection conn,
        String procedureName, String[] sysAliasDefinition,
        String[] DBMetaDefinition, String[] columnDefinition)
        throws SQLException {
    // Until cs defaults to hold cursor we need to turn autocommit off
    // while we do this because one metadata call will close the other's
    // cursor
    boolean saveAutoCommit = conn.getAutoCommit();
    conn.setAutoCommit(false);

    PreparedStatement ps = conn
            .prepareStatement("select schemaname, alias, CAST (((javaclassname || '.' ) || CAST (aliasinfo AS VARCHAR(1000))) AS VARCHAR(2000)) AS SIGNATURE "
                    + " from sys.sysaliases A, sys.sysschemas S where alias like ? and A.schemaid = S.schemaid ORDER BY 1,2,3");

    ps.setString(1, procedureName);

    ResultSet rs = ps.executeQuery();
    int i = 0;
    while (rs.next()) {
        assertEquals(sysAliasDefinition[i++], rs.getString(1) + "."
                + rs.getString(2) + " AS " + rs.getString(3));
    }
    rs.close();

    DatabaseMetaData dmd = conn.getMetaData();

    rs = dmd.getProcedures(null, null, procedureName);
    // with jcc 2.1 for now this will fail on the second round,
    // because the resultset gets closed when we do getProcedureColumns.
    // thus, catch that gracefully...
    i = 0;
    while (rs.next()) {
        String schema = rs.getString(2);
        String name = rs.getString(3);
        assertEquals(schema + "." + name + " AS " + rs.getString(7)
                + " type " + TYPE(rs.getShort(8)), DBMetaDefinition[i++]);
        // get the column information.
        ResultSet rsc = dmd.getProcedureColumns(null, schema, name, null);
        int j = 0;
        while (rsc.next()) {
            assertEquals(PARAMTYPE(rsc.getShort(5)) + " "
                    + rsc.getString(4) + " " + rsc.getString(7),
                    columnDefinition[j++]);
        }
        rsc.close();
    }
    rs.close();
    // restore previous autocommit mode
    conn.setAutoCommit(saveAutoCommit);
}
 
Example 19
Source File: MetaDataRegressionTest.java    From r-course with MIT License 4 votes vote down vote up
private void checkMetaDataInfoForBug17248345(Connection testConn) throws Exception {
    DatabaseMetaData testDbMetaData = testConn.getMetaData();
    ResultSet rsMD;
    boolean useInfoSchema = ((ConnectionProperties) testConn).getUseInformationSchema();
    boolean getProcRetFunc = ((ConnectionProperties) testConn).getGetProceduresReturnsFunctions();
    String stepDescription = "Prop. useInfoSchema(" + (useInfoSchema ? 1 : 0) + ") + getProcRetFunc(" + (getProcRetFunc ? 1 : 0) + "):";
    String sd;

    // getFunctions() must return 1 record.
    sd = stepDescription + " getFunctions() ";
    rsMD = testDbMetaData.getFunctions(null, null, "testBug17248345");
    assertTrue(sd + "one row expected.", rsMD.next());
    assertEquals(sd + " -> FUNCTION_NAME", "testBug17248345", rsMD.getString("FUNCTION_NAME"));
    assertFalse(sd + "no more rows expected.", rsMD.next());

    // getFunctionColumns() must return 2 records (func return + func param).
    sd = stepDescription + " getFunctionColumns() ";
    rsMD = testDbMetaData.getFunctionColumns(null, null, "testBug17248345", "%");
    assertTrue(sd + "1st of 2 rows expected.", rsMD.next());
    assertEquals(sd + " -> FUNCTION_NAME", "testBug17248345", rsMD.getString("FUNCTION_NAME"));
    assertEquals(sd + " -> COLUMN_NAME", "", rsMD.getString("COLUMN_NAME"));
    assertTrue(sd + "2nd of 2 rows expected.", rsMD.next());
    assertEquals(sd + " -> FUNCTION_NAME", "testBug17248345", rsMD.getString("FUNCTION_NAME"));
    assertEquals(sd + " -> COLUMN_NAME", "funccol", rsMD.getString("COLUMN_NAME"));
    assertFalse(sd + "no more rows expected.", rsMD.next());

    // getProcedures() must return 1 or 2 records, depending on if getProceduresReturnsFunctions is false or true
    // respectively. When exists a procedure and a function with same name, function is returned first.
    sd = stepDescription + " getProcedures() ";
    rsMD = testDbMetaData.getProcedures(null, null, "testBug17248345");
    if (getProcRetFunc) {
        assertTrue(sd + "1st of 2 rows expected.", rsMD.next());
        assertEquals(sd + " -> PROCEDURE_NAME", "testBug17248345", rsMD.getString("PROCEDURE_NAME"));
        assertTrue(sd + "2nd of 2 rows expected.", rsMD.next());
    } else {
        assertTrue(sd + "one row expected.", rsMD.next());
    }
    assertEquals(sd + " -> PROCEDURE_NAME", "testBug17248345", rsMD.getString("PROCEDURE_NAME"));
    assertFalse(sd + "no more rows expected.", rsMD.next());

    // getProcedureColumns() must return 1 or 3 records, depending on if getProceduresReturnsFunctions is false or
    // true respectively. When exists a procedure and a function with same name, function is returned first.
    sd = stepDescription + " getProcedureColumns() ";
    rsMD = testDbMetaData.getProcedureColumns(null, null, "testBug17248345", "%");
    if (getProcRetFunc) {
        assertTrue(sd + "1st of 3 rows expected.", rsMD.next());
        assertEquals(sd + " -> PROCEDURE_NAME", "testBug17248345", rsMD.getString("PROCEDURE_NAME"));
        assertEquals(sd + " -> COLUMN_NAME", "", rsMD.getString("COLUMN_NAME"));
        assertTrue(sd + "2nd of 3 rows expected.", rsMD.next());
        assertEquals(sd + " -> PROCEDURE_NAME", "testBug17248345", rsMD.getString("PROCEDURE_NAME"));
        assertEquals(sd + " -> COLUMN_NAME", "funccol", rsMD.getString("COLUMN_NAME"));
        assertTrue(sd + "3rd of 3 rows expected.", rsMD.next());
    } else {
        assertTrue(sd + "one row expected.", rsMD.next());
    }
    assertEquals(sd + " -> PROCEDURE_NAME", "testBug17248345", rsMD.getString("PROCEDURE_NAME"));
    assertEquals(sd + " -> COLUMN_NAME", "proccol", rsMD.getString("COLUMN_NAME"));
    assertFalse(sd + "no more rows expected.", rsMD.next());
}
 
Example 20
Source File: LangProcedureTest.java    From spliceengine with GNU Affero General Public License v3.0 4 votes vote down vote up
private static void checkMatchingProcedures(Connection conn,
        String procedureName, String[] sysAliasDefinition,
        String[] DBMetaDefinition, String[] columnDefinition)
        throws SQLException {
    // Until cs defaults to hold cursor we need to turn autocommit off
    // while we do this because one metadata call will close the other's
    // cursor
    boolean saveAutoCommit = conn.getAutoCommit();
    conn.setAutoCommit(false);

    PreparedStatement ps = conn
            .prepareStatement("select schemaname, alias, CAST (((javaclassname || '.' ) || CAST (aliasinfo AS VARCHAR(1000))) AS VARCHAR(2000)) AS SIGNATURE "
                    + " from sys.sysaliases A, sys.sysschemas S where alias like ? and A.schemaid = S.schemaid ORDER BY 1,2,3");

    ps.setString(1, procedureName);

    ResultSet rs = ps.executeQuery();
    int i = 0;
    while (rs.next()) {
        assertEquals(sysAliasDefinition[i++], rs.getString(1) + "."
                + rs.getString(2) + " AS " + rs.getString(3));
    }
    rs.close();

    DatabaseMetaData dmd = conn.getMetaData();

    rs = dmd.getProcedures(null, null, procedureName);
    // with jcc 2.1 for now this will fail on the second round,
    // because the resultset gets closed when we do getProcedureColumns.
    // thus, catch that gracefully...
    i = 0;
    while (rs.next()) {
        String schema = rs.getString(2);
        String name = rs.getString(3);
        assertEquals(schema + "." + name + " AS " + rs.getString(7)
                + " type " + TYPE(rs.getShort(8)), DBMetaDefinition[i++]);
        // get the column information.
        ResultSet rsc = dmd.getProcedureColumns(null, schema, name, null);
        int j = 0;
        while (rsc.next()) {
            assertEquals(PARAMTYPE(rsc.getShort(5)) + " "
                    + rsc.getString(4) + " " + rsc.getString(7),
                    columnDefinition[j++]);
        }
        rsc.close();
    }
    rs.close();
    // restore previous autocommit mode
    conn.setAutoCommit(saveAutoCommit);
}