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

The following examples show how to use java.sql.DatabaseMetaData#getExportedKeys() . 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: Show.java    From jsqsh with Apache License 2.0 6 votes vote down vote up
private ResultSet doExportedKeys(Session session, Connection con, Options options)
    throws SQLException {
    
    if (options.arguments.size() != 3
        || !options.arguments.get(1).equalsIgnoreCase("keys")) {
        
        session.err.println("Use: \\show exported keys [[catalog.]schema.]table-pattern");
        return null;
    }
    
    SQLConnectionContext ctx = (SQLConnectionContext) session.getConnectionContext();
    SQLObjectName name = new SQLObjectName(ctx, options.arguments.get(2));
    
    DatabaseMetaData meta = con.getMetaData();
    return meta.getExportedKeys(
        (options.catalog != null ? options.catalog : name.getCatalog()),
        (options.schemaPattern != null ? options.schemaPattern : name.getSchema()),
        (options.tablePattern != null ? options.tablePattern : name.getName()));
}
 
Example 2
Source File: RDBMSDataHandler.java    From micro-integrator with Apache License 2.0 6 votes vote down vote up
/**
 * This method reads foreign keys of the table.
 *
 * @param tableName Name of the table
 * @throws ODataServiceFault
 */
private NavigationTable readForeignKeys(String tableName, DatabaseMetaData metaData, String catalog)
        throws ODataServiceFault {
    ResultSet resultSet = null;
    try {
        resultSet = metaData.getExportedKeys(catalog, null, tableName);
        NavigationTable navigationLinks = new NavigationTable();
        while (resultSet.next()) {
            // foreignKeyTableName means the table name of the table which used columns as foreign keys in that table.
            String primaryKeyColumnName = resultSet.getString("PKCOLUMN_NAME");
            String foreignKeyTableName = resultSet.getString("FKTABLE_NAME");
            String foreignKeyColumnName = resultSet.getString("FKCOLUMN_NAME");
            List<NavigationKeys> columnList = navigationLinks.getNavigationKeys(foreignKeyTableName);
            if (columnList == null) {
                columnList = new ArrayList<>();
                navigationLinks.addNavigationKeys(foreignKeyTableName, columnList);
            }
            columnList.add(new NavigationKeys(primaryKeyColumnName, foreignKeyColumnName));
        }
        return navigationLinks;
    } catch (SQLException e) {
        throw new ODataServiceFault(e, "Error in reading " + tableName + " table meta data. :" + e.getMessage());
    } finally {
        releaseResources(resultSet, null);
    }
}
 
Example 3
Source File: ClearDatabaseLifecycle.java    From rice with Educational Community License v2.0 6 votes vote down vote up
protected Map<String, List<String[]>> indexExportedKeys(DatabaseMetaData metaData, String schemaName) throws SQLException {
	Map<String, List<String[]>> exportedKeys = new HashMap<String, List<String[]>>();
    if (!isUsingDerby(metaData) && isUsingOracle(metaData)) {
    	ResultSet keyResultSet = metaData.getExportedKeys(null, schemaName, null);
    	while (keyResultSet.next()) {
    		String tableName = keyResultSet.getString("PKTABLE_NAME");
    		if (shouldTableBeCleared(tableName)) {
    			List<String[]> exportedKeyNames = exportedKeys.get(tableName);
    			if (exportedKeyNames == null) {
    				exportedKeyNames = new ArrayList<String[]>();
    				exportedKeys.put(tableName, exportedKeyNames);
    			}
    			final String fkName = keyResultSet.getString("FK_NAME");
    			final String fkTableName = keyResultSet.getString("FKTABLE_NAME");
    			exportedKeyNames.add(new String[] { fkName, fkTableName });
    		}
    	}
    	keyResultSet.close();
    }
    return exportedKeys;        
}
 
Example 4
Source File: MetaDataRegressionTest.java    From r-course with MIT License 6 votes vote down vote up
/**
 * Bug #43714 - useInformationSchema with DatabaseMetaData.getExportedKeys()
 * throws exception
 */
public void testBug43714() throws Exception {
    Connection c_IS = null;
    try {
        c_IS = getConnectionWithProps("useInformationSchema=true");
        DatabaseMetaData dbmd = c_IS.getMetaData();
        this.rs = dbmd.getExportedKeys("x", "y", "z");
    } finally {
        try {
            if (c_IS != null) {
                c_IS.close();
            }
        } catch (SQLException ex) {
        }
    }
}
 
Example 5
Source File: MetaDataRegressionTest.java    From Komondor with GNU General Public License v3.0 6 votes vote down vote up
/**
 * Bug #43714 - useInformationSchema with DatabaseMetaData.getExportedKeys()
 * throws exception
 */
public void testBug43714() throws Exception {
    Connection c_IS = null;
    try {
        c_IS = getConnectionWithProps("useInformationSchema=true");
        DatabaseMetaData dbmd = c_IS.getMetaData();
        this.rs = dbmd.getExportedKeys("x", "y", "z");
    } finally {
        try {
            if (c_IS != null) {
                c_IS.close();
            }
        } catch (SQLException ex) {
        }
    }
}
 
Example 6
Source File: MetaResultSetTest.java    From calcite-avatica with Apache License 2.0 6 votes vote down vote up
@Test public void testGetExportedKeys() throws SQLException {
  DatabaseMetaData metadata = getDatabaseMetadata();
  try (ResultSet rs = metadata.getExportedKeys(null, null, null)) {
    ResultSetMetaData rsMeta = rs.getMetaData();

    assertEquals(14, rsMeta.getColumnCount());
    assertColumn(rsMeta, 1, "PKTABLE_CAT", Types.VARCHAR, DatabaseMetaData.columnNullable);
    assertColumn(rsMeta, 2, "PKTABLE_SCHEM", Types.VARCHAR, DatabaseMetaData.columnNullable);
    assertColumn(rsMeta, 3, "PKTABLE_NAME", Types.VARCHAR, DatabaseMetaData.columnNoNulls);
    assertColumn(rsMeta, 4, "PKCOLUMN_NAME", Types.VARCHAR, DatabaseMetaData.columnNoNulls);
    assertColumn(rsMeta, 5, "FKTABLE_CAT", Types.VARCHAR, DatabaseMetaData.columnNullable);
    assertColumn(rsMeta, 6, "FKTABLE_SCHEM", Types.VARCHAR, DatabaseMetaData.columnNullable);
    assertColumn(rsMeta, 7, "FKTABLE_NAME", Types.VARCHAR, DatabaseMetaData.columnNoNulls);
    assertColumn(rsMeta, 8, "FKCOLUMN_NAME", Types.VARCHAR, DatabaseMetaData.columnNoNulls);
    assertColumn(rsMeta, 9, "KEY_SEQ", Types.SMALLINT, DatabaseMetaData.columnNoNulls);
    assertColumn(rsMeta, 10, "UPDATE_RULE", Types.SMALLINT, DatabaseMetaData.columnNoNulls);
    assertColumn(rsMeta, 11, "DELETE_RULE", Types.SMALLINT, DatabaseMetaData.columnNoNulls);
    assertColumn(rsMeta, 12, "FK_NAME", Types.VARCHAR, DatabaseMetaData.columnNullable);
    assertColumn(rsMeta, 13, "PK_NAME", Types.VARCHAR, DatabaseMetaData.columnNullable);
    assertColumn(rsMeta, 14, "DEFERABILITY", Types.SMALLINT, DatabaseMetaData.columnNoNulls);
  }
}
 
Example 7
Source File: DataMigrator.java    From scriptella-etl with Apache License 2.0 6 votes vote down vote up
private static int[][] getTablesMatrix(DbSchema schema, String[] tables) throws SQLException {
    final DatabaseMetaData metaData = schema.getMetaData();
    int n = tables.length;
    int m[][] = new int[n][n];

    for (int[] a : m) {
        Arrays.fill(a, 0);
    }

    for (int i = 0; i < n; i++) {
        final ResultSet rs = metaData.getExportedKeys(schema.getCatalog(), schema.getSchema(), tables[i]);
        while (rs.next()) {
            String t2 = rs.getString("FKTABLE_NAME");
            int i2 = indexOf(tables, t2);

            if (i2 >= 0) {
                m[i][i2] += ((rs.getInt("DELETE_RULE") != 2) ? 10 : 1);
            }
        }

        rs.close();
    }
    return m;
}
 
Example 8
Source File: DatabaseMetaDataTest.java    From gemfirexd-oss with Apache License 2.0 5 votes vote down vote up
/**
 * Helper method for testing getExportedKeys - calls dmd.getExportedKeys for
 * the JDBC call, and getExportedKeysODBC for the ODBC procedure
 * @throws SQLException
 */
private ResultSet[] getExportedKeys(
        String catalog, String schema, String table) throws SQLException
{
    ResultSet[] rss = new ResultSet[2];
    DatabaseMetaData dmd = getDMD();
    rss[0]= dmd.getExportedKeys(catalog, schema, table);
    rss[1]= getExportedKeysODBC(catalog, schema, table);

    assertGetImportedAndExportedKeysShape(rss);

    return rss;
}
 
Example 9
Source File: DatabaseMetaDataTest.java    From gemfirexd-oss with Apache License 2.0 5 votes vote down vote up
/**
 * Helper method for testing getExportedKeys - calls dmd.getExportedKeys for
 * the JDBC call, and getExportedKeysODBC for the ODBC procedure
 * @throws SQLException
 */
private ResultSet[] getExportedKeys(
        String catalog, String schema, String table) throws SQLException
{
    ResultSet[] rss = new ResultSet[2];
    DatabaseMetaData dmd = getDMD();
    rss[0]= dmd.getExportedKeys(catalog, schema, table);
    rss[1]= getExportedKeysODBC(catalog, schema, table);

    assertGetImportedAndExportedKeysShape(rss);

    return rss;
}
 
Example 10
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 11
Source File: MetadataTest.java    From r-course with MIT License 5 votes vote down vote up
/**
 * Tests the implementation of Information Schema for foreign key.
 */
public void testGetExportedKeysUsingInfoSchema() throws Exception {
    if (versionMeetsMinimum(5, 0, 7)) {
        this.stmt.executeUpdate("DROP TABLE IF EXISTS child");
        this.stmt.executeUpdate("DROP TABLE If EXISTS parent");
        this.stmt.executeUpdate("CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB");
        this.stmt.executeUpdate(
                "CREATE TABLE child(id INT, parent_id INT, " + "FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE SET NULL) ENGINE=INNODB");
        Properties props = new Properties();
        props.put("useInformationSchema", "true");
        Connection conn1 = null;
        try {
            conn1 = getConnectionWithProps(props);
            DatabaseMetaData metaData = conn1.getMetaData();
            this.rs = metaData.getExportedKeys(null, null, "parent");
            this.rs.next();
            assertEquals("parent", this.rs.getString("PKTABLE_NAME"));
            assertEquals("id", this.rs.getString("PKCOLUMN_NAME"));
            assertEquals("child", this.rs.getString("FKTABLE_NAME"));
            assertEquals("parent_id", this.rs.getString("FKCOLUMN_NAME"));
        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS child");
            this.stmt.executeUpdate("DROP TABLE If EXISTS parent");
            if (conn1 != null) {
                conn1.close();
            }
        }
    }
}
 
Example 12
Source File: ConcurrentConnTest.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
public static void getExportedKeys(DatabaseMetaData dmd, String tablePattern,PrintStream out)
throws SQLException
{
  ResultSet rs = dmd.getExportedKeys(null, null, tablePattern);
  while (rs.next())
  {
    // 1.PKTABLE_CAT String => primary key table catalog (may be null)
    String pkTableCat = rs.getString(1);

    // 2.PKTABLE_SCHEM String => primary key table schema (may be null)
    String pkTableSchem = rs.getString(2);

    // 3.PKTABLE_NAME String => primary key table name
    String pkTableName = rs.getString(3);

    // 4.PKCOLUMN_NAME String => primary key column name
    String pkColumnName = rs.getString(4);

    // 5.FKTABLE_CAT String => foreign key table catalog
    // (may be null) being exported (may be null)
    String fkTableCat = rs.getString(5);

    // 6.FKTABLE_SCHEM String => foreign key table schema
    // (may be null) being exported (may be null)
    String fkTableSchem = rs.getString(6);

    // 7.FKTABLE_NAME String => foreign key table name being exported
    String fkTableName = rs.getString(7);

    // 8.FKCOLUMN_NAME String => foreign key column name being exported
    String fkColumnName = rs.getString(8);

    // 9.KEY_SEQ short => sequence number within foreign key
    short keySeq = rs.getShort(9);

    // 10.UPDATE_RULE short => What happens to foreign key when
    // primary is updated:
    //         importedNoAction - do not allow update of primary key if
    //                        it has been imported
    //         importedKeyCascade - change imported key to agree
    //                          with primary key update
    //         importedKeySetNull - change imported key to NULL if its
    //                          primary key has been updated
    //         importedKeySetDefault - change imported key to default
    //                             values if its primary key has
    //                             been updated
    //         importedKeyRestrict - same as importedKeyNoAction
    //                           (for ODBC 2.x compatibility)
    short updateRule = rs.getShort(10);

    // 11.DELETE_RULE short => What happens to the foreign key
    // when primary is deleted.
    //         importedKeyNoAction - do not allow delete of primary key
    //                           if it has been imported
    //         importedKeyCascade - delete rows that import a deleted key
    //         importedKeySetNull - change imported key to NULL if
    //                          its primary key has been deleted
    //         importedKeyRestrict - same as importedKeyNoAction
    //                           (for ODBC 2.x compatibility)
    //         importedKeySetDefault - change imported key to default
    //                             if its primary key has
    //         been deleted
    short deleteRule = rs.getShort(11);

    // 12.FK_NAME String => foreign key name (may be null)
    String fkName = rs.getString(12);

    // 13.PK_NAME String => primary key name (may be null)
    String pkName = rs.getString(13);

    // 14.DEFERRABILITY short => can the evaluation of foreign key
    // constraints be deferred until commit
    //         importedKeyInitiallyDeferred - see SQL92 for definition
    //         importedKeyInitiallyImmediate - see SQL92 for definition
    //         importedKeyNotDeferrable - see SQL92 for definition
    short deferrability = rs.getShort(14);

  }
  rs.close();
}
 
Example 13
Source File: metadataMultiConn.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
public static void getExportedKeys(DatabaseMetaData dmd, String tablePattern,PrintStream out)
	throws SQLException
{
	ResultSet rs = dmd.getExportedKeys(null, null, tablePattern);
	while (rs.next())
	{
		// 1.PKTABLE_CAT String => primary key table catalog (may be null)
		String pkTableCat = rs.getString(1);

		// 2.PKTABLE_SCHEM String => primary key table schema (may be null)
		String pkTableSchem = rs.getString(2);

		// 3.PKTABLE_NAME String => primary key table name
		String pkTableName = rs.getString(3);

		// 4.PKCOLUMN_NAME String => primary key column name
		String pkColumnName = rs.getString(4);

		// 5.FKTABLE_CAT String => foreign key table catalog
		// (may be null) being exported (may be null)
		String fkTableCat = rs.getString(5);

		// 6.FKTABLE_SCHEM String => foreign key table schema
		// (may be null) being exported (may be null)
		String fkTableSchem = rs.getString(6);

		// 7.FKTABLE_NAME String => foreign key table name being exported
		String fkTableName = rs.getString(7);

		// 8.FKCOLUMN_NAME String => foreign key column name being exported
		String fkColumnName = rs.getString(8);

		// 9.KEY_SEQ short => sequence number within foreign key
		short keySeq = rs.getShort(9);

		// 10.UPDATE_RULE short => What happens to foreign key when
		// primary is updated:
		//	   importedNoAction - do not allow update of primary key if
		//                        it has been imported
		//	   importedKeyCascade - change imported key to agree
		//                          with primary key update
		//	   importedKeySetNull - change imported key to NULL if its
		//                          primary key has been updated
		//	   importedKeySetDefault - change imported key to default
		//                             values if its primary key has
		//                             been updated
		//	   importedKeyRestrict - same as importedKeyNoAction
		//                           (for ODBC 2.x compatibility)
		short updateRule = rs.getShort(10);

		// 11.DELETE_RULE short => What happens to the foreign key
		// when primary is deleted.
		//	   importedKeyNoAction - do not allow delete of primary key
		//                           if it has been imported
		//	   importedKeyCascade - delete rows that import a deleted key
		//	   importedKeySetNull - change imported key to NULL if
		//                          its primary key has been deleted
		//	   importedKeyRestrict - same as importedKeyNoAction
		//                           (for ODBC 2.x compatibility)
		//	   importedKeySetDefault - change imported key to default
		//                             if its primary key has
		//	   been deleted
		short deleteRule = rs.getShort(11);

		// 12.FK_NAME String => foreign key name (may be null)
		String fkName = rs.getString(12);

		// 13.PK_NAME String => primary key name (may be null)
		String pkName = rs.getString(13);

		// 14.DEFERRABILITY short => can the evaluation of foreign key
		// constraints be deferred until commit
		//	   importedKeyInitiallyDeferred - see SQL92 for definition
		//	   importedKeyInitiallyImmediate - see SQL92 for definition
		//	   importedKeyNotDeferrable - see SQL92 for definition
		short deferrability = rs.getShort(14);

	}
	rs.close();
}
 
Example 14
Source File: DatabaseMetaDataInternalIT.java    From snowflake-jdbc with Apache License 2.0 4 votes vote down vote up
@Test
@ConditionalIgnoreRule.ConditionalIgnore(condition = RunningOnGithubAction.class)
public void testGetMetaDataUseConnectionCtx() throws SQLException
{
  Connection connection = getConnection();
  Statement statement = connection.createStatement();

  // setup: reset session db and schema, enable the parameter
  statement.execute("use database JDBC_DB1");
  statement.execute("use schema JDBC_SCHEMA11");
  statement.execute("alter SESSION set CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX=true");

  DatabaseMetaData databaseMetaData = connection.getMetaData();

  // this should only return JDBC_SCHEMA11
  ResultSet resultSet = databaseMetaData.getSchemas(null, null);
  assertEquals(1, getSizeOfResultSet(resultSet));

  // only returns tables in JDBC_DB1.JDBC_SCHEMA11
  resultSet = databaseMetaData.getTables(null, null, null, null);
  assertEquals(1, getSizeOfResultSet(resultSet));

  statement.execute("use schema JDBC_SCHEMA12");
  resultSet = databaseMetaData.getTables(null, null, null, null);
  assertEquals(2, getSizeOfResultSet(resultSet));

  resultSet = databaseMetaData.getColumns(null, null, null, null);
  assertEquals(4, getSizeOfResultSet(resultSet));

  statement.execute("use schema TEST_CTX");
  resultSet = databaseMetaData.getPrimaryKeys(null, null, null);
  assertEquals(1, getSizeOfResultSet(resultSet));

  resultSet = databaseMetaData.getImportedKeys(null, null, null);
  assertEquals(1, getSizeOfResultSet(resultSet));

  resultSet = databaseMetaData.getExportedKeys(null, null, null);
  assertEquals(1, getSizeOfResultSet(resultSet));

  resultSet = databaseMetaData.getCrossReference(null, null, null, null,
                                                 null, null);
  assertEquals(1, getSizeOfResultSet(resultSet));
}
 
Example 15
Source File: DatabaseMetaDataIT.java    From snowflake-jdbc with Apache License 2.0 4 votes vote down vote up
@Test
public void testUseConnectionCtx() throws SQLException
{
  try (Connection connection = getConnection())
  {
    connection.createStatement().execute("alter SESSION set CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX=true");
    String schema = connection.getSchema();
    DatabaseMetaData databaseMetaData = connection.getMetaData();

    // create tables within current schema.
    connection.createStatement().execute("create or replace schema TEST_CTX");
    connection.createStatement().execute("create or replace table CTX_TBL_A (colA string, colB decimal, " +
                                         "colC number PRIMARY KEY);");
    connection.createStatement().execute("create or replace table CTX_TBL_B (colA string, colB decimal, " +
                                         "colC number FOREIGN KEY REFERENCES CTX_TBL_A (colC));");
    connection.createStatement().execute("create or replace table CTX_TBL_C (colA string, colB decimal, " +
                                         "colC number, colD int, colE timestamp, colF string, colG number);");
    // now create more tables under current schema
    connection.createStatement().execute("use schema " + schema);
    connection.createStatement().execute("create or replace table CTX_TBL_D (colA string, colB decimal, " +
                                         "colC number PRIMARY KEY);");
    connection.createStatement().execute("create or replace table CTX_TBL_E (colA string, colB decimal, " +
                                         "colC number FOREIGN KEY REFERENCES CTX_TBL_D (colC));");
    connection.createStatement().execute("create or replace table CTX_TBL_F (colA string, colB decimal, " +
                                         "colC number, colD int, colE timestamp, colF string, colG number);");

    // this should only return TEST_CTX schema and tables
    connection.createStatement().execute("use schema TEST_CTX");

    ResultSet resultSet = databaseMetaData.getSchemas(null, null);
    assertEquals(1, getSizeOfResultSet(resultSet));

    resultSet = databaseMetaData.getTables(null, null, null, null);
    assertEquals(3, getSizeOfResultSet(resultSet));

    resultSet = databaseMetaData.getColumns(null, null, null, null);
    assertEquals(13, getSizeOfResultSet(resultSet));

    resultSet = databaseMetaData.getPrimaryKeys(null, null, null);
    assertEquals(1, getSizeOfResultSet(resultSet));

    resultSet = databaseMetaData.getImportedKeys(null, null, null);
    assertEquals(1, getSizeOfResultSet(resultSet));

    resultSet = databaseMetaData.getExportedKeys(null, null, null);
    assertEquals(1, getSizeOfResultSet(resultSet));

    resultSet = databaseMetaData.getCrossReference(null, null, null, null,
                                                   null, null);
    assertEquals(1, getSizeOfResultSet(resultSet));

    // Now compare results to setting client metadata to false.
    connection.createStatement().execute("alter SESSION set CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX=false");
    databaseMetaData = connection.getMetaData();

    resultSet = databaseMetaData.getSchemas(null, null);
    assertThat(getSizeOfResultSet(resultSet), greaterThanOrEqualTo(2));

    resultSet = databaseMetaData.getTables(null, null, null, null);
    assertThat(getSizeOfResultSet(resultSet), greaterThanOrEqualTo(6));

    resultSet = databaseMetaData.getColumns(null, null, null, null);
    assertThat(getSizeOfResultSet(resultSet), greaterThanOrEqualTo(26));

    resultSet = databaseMetaData.getPrimaryKeys(null, null, null);
    assertThat(getSizeOfResultSet(resultSet), greaterThanOrEqualTo(2));

    resultSet = databaseMetaData.getImportedKeys(null, null, null);
    assertThat(getSizeOfResultSet(resultSet), greaterThanOrEqualTo(2));

    resultSet = databaseMetaData.getExportedKeys(null, null, null);
    assertThat(getSizeOfResultSet(resultSet), greaterThanOrEqualTo(2));

    resultSet = databaseMetaData.getCrossReference(null, null, null, null,
                                                   null, null);
    assertThat(getSizeOfResultSet(resultSet), greaterThanOrEqualTo(2));

  }
}
 
Example 16
Source File: DatabaseMetaDataIT.java    From snowflake-jdbc with Apache License 2.0 4 votes vote down vote up
@Test
public void testGetExportedKeys() throws Throwable
{
  try (Connection connection = getConnection())
  {
    String database = connection.getCatalog();
    String schema = connection.getSchema();
    final String targetTable1 = "T0";
    final String targetTable2 = "T1";

    connection.createStatement().execute(
        "create or replace table " +
        targetTable1 + "(C1 int primary key, C2 string)");
    connection.createStatement().execute(
        "create or replace table " +
        targetTable2 + "(C1 int primary key, C2 string, C3 int references " + targetTable1 + ")");


    DatabaseMetaData metaData = connection.getMetaData();

    ResultSet resultSet = metaData.getExportedKeys(database, schema, targetTable1);
    verifyResultSetMetaDataColumns(resultSet, DBMetadataResultSetMetadata.GET_FOREIGN_KEYS);

    assertTrue(resultSet.next());
    assertEquals(database, resultSet.getString("PKTABLE_CAT"));
    assertEquals(schema, resultSet.getString("PKTABLE_SCHEM"));
    assertEquals(targetTable1, resultSet.getString("PKTABLE_NAME"));
    assertEquals("C1", resultSet.getString("PKCOLUMN_NAME"));
    assertEquals(database, resultSet.getString("FKTABLE_CAT"));
    assertEquals(schema, resultSet.getString("FKTABLE_SCHEM"));
    assertEquals(targetTable2, resultSet.getString("FKTABLE_NAME"));
    assertEquals("C3", resultSet.getString("FKCOLUMN_NAME"));
    assertEquals(1, resultSet.getInt("KEY_SEQ"));
    assertNotEquals("", resultSet.getString("PK_NAME"));
    assertNotEquals("", resultSet.getString("FK_NAME"));
    assertEquals(DatabaseMetaData.importedKeyNoAction,
                 resultSet.getShort("UPDATE_RULE"));
    assertEquals(DatabaseMetaData.importedKeyNoAction,
                 resultSet.getShort("DELETE_RULE"));
    assertEquals(DatabaseMetaData.importedKeyNotDeferrable,
                 resultSet.getShort("DEFERRABILITY"));

    connection.createStatement().execute("drop table if exists " + targetTable1);
    connection.createStatement().execute("drop table if exists " + targetTable2);
  }
}
 
Example 17
Source File: MetaDataJavaPrinter.java    From aceql-http with GNU Lesser General Public License v2.1 4 votes vote down vote up
private void printGetExportedKeys(String tableName) throws SQLException {

	if (tableName == null) {
	    throw new NullPointerException("tableName is null!");
	}
	/**
	<pre><code>
        1.PKTABLE_CAT String => primary key table catalog (may be null)
        2.PKTABLE_SCHEM String => primary key table schema (may be null)
        3.PKTABLE_NAME String => primary key table name
        4.PKCOLUMN_NAME String => primary key Column name
        5.FKTABLE_CAT String => foreign key table catalog (may be null)being exported (may be null)
        6.FKTABLE_SCHEM String => foreign key table schema (may be null)being exported (may be null)
        7.FKTABLE_NAME String => foreign key table namebeing exported
        8.FKCOLUMN_NAME String => foreign key Column namebeing exported
        9.KEY_SEQ short => sequence number within foreign key( a valueof 1 represents the first Column of the foreign key, a value of 2 wouldrepresent the second Column within the foreign key).
        10.UPDATE_RULE short => What happens toforeign key when primary is updated: ◦ importedNoAction - do not allow update of primarykey if it has been imported
        ◦ importedKeyCascade - change imported key to agreewith primary key update
        ◦ importedKeySetNull - change imported key to NULL ifits primary key has been updated
        ◦ importedKeySetDefault - change imported key to default valuesif its primary key has been updated
        ◦ importedKeyRestrict - same as importedKeyNoAction(for ODBC 2.x compatibility)

        11.DELETE_RULE short => What happens tothe foreign key when primary is deleted. ◦ importedKeyNoAction - do not allow delete of primarykey if it has been imported
        ◦ importedKeyCascade - delete rows that import a deleted key
        ◦ importedKeySetNull - change imported key to NULL ifits primary key has been deleted
        ◦ importedKeyRestrict - same as importedKeyNoAction(for ODBC 2.x compatibility)
        ◦ importedKeySetDefault - change imported key to default ifits primary key has been deleted

        12.FK_NAME String => foreign key name (may be null)
        13.PK_NAME String => primary key name (may be null)
        14.DEFERRABILITY short => can the evaluation of foreign keyconstraints be deferred until commit ◦ importedKeyInitiallyDeferred - see SQL92 for definition
        ◦ importedKeyInitiallyImmediate - see SQL92 for definition
        ◦ importedKeyNotDeferrable - see SQL92 for definition

	</code></pre>
	 */

	DatabaseMetaData databaseMetaData = connection.getMetaData();
	ResultSet rs = databaseMetaData.getExportedKeys(catalog, null, tableName);

	while (rs.next()) {
	    System.out.println();
	    for (int i = 1; i < 15; i++) {
		System.out.println("databaseMetaData.getExportedKeys( " + tableName + ") " + i + ": " + rs.getString(i));
	    }
	}

    }
 
Example 18
Source File: metadataMultiConnTest.java    From spliceengine with GNU Affero General Public License v3.0 4 votes vote down vote up
public void getExportedKeys(DatabaseMetaData dmd) throws SQLException {
	ResultSet rs = dmd.getExportedKeys(null, null, "%");
	JDBC.assertDrainResults(rs);
}
 
Example 19
Source File: metadataMultiConn.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
public static void getExportedKeys(DatabaseMetaData dmd, String tablePattern,PrintStream out)
	throws SQLException
{
	ResultSet rs = dmd.getExportedKeys(null, null, tablePattern);
	while (rs.next())
	{
		// 1.PKTABLE_CAT String => primary key table catalog (may be null)
		String pkTableCat = rs.getString(1);

		// 2.PKTABLE_SCHEM String => primary key table schema (may be null)
		String pkTableSchem = rs.getString(2);

		// 3.PKTABLE_NAME String => primary key table name
		String pkTableName = rs.getString(3);

		// 4.PKCOLUMN_NAME String => primary key column name
		String pkColumnName = rs.getString(4);

		// 5.FKTABLE_CAT String => foreign key table catalog
		// (may be null) being exported (may be null)
		String fkTableCat = rs.getString(5);

		// 6.FKTABLE_SCHEM String => foreign key table schema
		// (may be null) being exported (may be null)
		String fkTableSchem = rs.getString(6);

		// 7.FKTABLE_NAME String => foreign key table name being exported
		String fkTableName = rs.getString(7);

		// 8.FKCOLUMN_NAME String => foreign key column name being exported
		String fkColumnName = rs.getString(8);

		// 9.KEY_SEQ short => sequence number within foreign key
		short keySeq = rs.getShort(9);

		// 10.UPDATE_RULE short => What happens to foreign key when
		// primary is updated:
		//	   importedNoAction - do not allow update of primary key if
		//                        it has been imported
		//	   importedKeyCascade - change imported key to agree
		//                          with primary key update
		//	   importedKeySetNull - change imported key to NULL if its
		//                          primary key has been updated
		//	   importedKeySetDefault - change imported key to default
		//                             values if its primary key has
		//                             been updated
		//	   importedKeyRestrict - same as importedKeyNoAction
		//                           (for ODBC 2.x compatibility)
		short updateRule = rs.getShort(10);

		// 11.DELETE_RULE short => What happens to the foreign key
		// when primary is deleted.
		//	   importedKeyNoAction - do not allow delete of primary key
		//                           if it has been imported
		//	   importedKeyCascade - delete rows that import a deleted key
		//	   importedKeySetNull - change imported key to NULL if
		//                          its primary key has been deleted
		//	   importedKeyRestrict - same as importedKeyNoAction
		//                           (for ODBC 2.x compatibility)
		//	   importedKeySetDefault - change imported key to default
		//                             if its primary key has
		//	   been deleted
		short deleteRule = rs.getShort(11);

		// 12.FK_NAME String => foreign key name (may be null)
		String fkName = rs.getString(12);

		// 13.PK_NAME String => primary key name (may be null)
		String pkName = rs.getString(13);

		// 14.DEFERRABILITY short => can the evaluation of foreign key
		// constraints be deferred until commit
		//	   importedKeyInitiallyDeferred - see SQL92 for definition
		//	   importedKeyInitiallyImmediate - see SQL92 for definition
		//	   importedKeyNotDeferrable - see SQL92 for definition
		short deferrability = rs.getShort(14);

	}
	rs.close();
}
 
Example 20
Source File: AceQLMetaData.java    From aceql-http with GNU Lesser General Public License v2.1 4 votes vote down vote up
/**
    * Returns the foreign exported keys for the passed table name
    *
    * @param tableName the table name for the passed table name
    * @return the foreign exported keys
    * @throws SQLException it any SQL Exception occurs
    */
   public List<ExportedKey> getExportedKeys(String tableName) throws SQLException {
if (tableName == null) {
    throw new NullPointerException("tableName is null!");
}

if (!tableNamesSet.contains(tableName.toLowerCase())) {
    throw new IllegalArgumentException("table does not exists: " + tableName);
}

/**
 * <pre>
<code>
databaseMetaData.getExportedKeys( customer) 1: null                                       1.PKTABLE_CAT String => primary key table catalog (may be null)
databaseMetaData.getExportedKeys( customer) 2: public                                     2.PKTABLE_SCHEM String => primary key table schema (may be null)
databaseMetaData.getExportedKeys( customer) 3: customer                                   3.PKTABLE_NAME String => primary key table name
databaseMetaData.getExportedKeys( customer) 4: customer_id                                4.PKCOLUMN_NAME String => primary key column name

databaseMetaData.getExportedKeys( customer) 5: null                                       5.FKTABLE_CAT String => foreign key table catalog (may be null)being exported (may be null)
databaseMetaData.getExportedKeys( customer) 6: public                                     6.FKTABLE_SCHEM String => foreign key table schema (may be null)being exported (may be null)
databaseMetaData.getExportedKeys( customer) 7: orderlog2                                  7.FKTABLE_NAME String => foreign key table namebeing exported
databaseMetaData.getExportedKeys( customer) 8: customer_id                                8.FKCOLUMN_NAME String => foreign key column namebeing exported

databaseMetaData.getExportedKeys( customer) 9: 1                                          9.KEY_SEQ short => sequence number within foreign key( a valueof 1 represents the first column
databaseMetaData.getExportedKeys( customer) 10: 3                                         10.UPDATE_RULE short => What happens toforeign key when primary is updated: ◦ importedNoAction
databaseMetaData.getExportedKeys( customer) 11: 3					  11.DELETE_RULE short
databaseMetaData.getExportedKeys( customer) 12: orderlog2_customer_id_fkey		  12.FK_NAME String => foreign key name (may be null)
databaseMetaData.getExportedKeys( customer) 13: customer_pkey                             13.PK_NAME String => primary key name (may be null)

databaseMetaData.getExportedKeys( customer) 14: 7                                         14.DEFERRABILITY short => can the evaluation of foreign keyconstraints be deferred until commit
</code>
 * </pre>
 */

DatabaseMetaData databaseMetaData = connection.getMetaData();
ResultSet rs = databaseMetaData.getExportedKeys(catalog, schema, tableName);

List<ExportedKey> exportedKeys = new ArrayList<>();
while (rs.next()) {
    ExportedKey exportedKey = new ExportedKey();
    int i = 1;
    exportedKey.setCatalog(rs.getString(i++));
    exportedKey.setSchema(rs.getString(i++));
    exportedKey.setPrimaryKeyTable(rs.getString(i++));
    exportedKey.setPrimaryKeyColumn(rs.getString(i++));

    exportedKey.setForeignKeyCatalog(rs.getString(i++));
    exportedKey.setForeignKeySchema(rs.getString(i++));
    exportedKey.setForeignKeyTable(rs.getString(i++));
    exportedKey.setForeignKeyColumn(rs.getString(i++));

    exportedKey.setKeySequence(rs.getInt(i++));
    exportedKey.setUpdateRule(MetaDataJavaUtil.decodeRule(rs.getInt(i++)));
    exportedKey.setDeleteRule(MetaDataJavaUtil.decodeRule(rs.getInt(i++)));
    exportedKey.setForeignKeyName(rs.getString(i++));
    exportedKey.setPrimaryKeyName(rs.getString(i++));

    exportedKey.setDeferrability(rs.getInt(i++));
    exportedKeys.add(exportedKey);
}

return exportedKeys;

   }