Java Code Examples for java.sql.DatabaseMetaData#getImportedKeys()
The following examples show how to use
java.sql.DatabaseMetaData#getImportedKeys() .
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: TestForeignKeysAreOptional.java From sqlg with MIT License | 6 votes |
|
@Test
public void testForeignKeysOnPostgres() throws Exception {
Assume.assumeTrue(this.sqlgGraph.getSqlDialect().getClass().getSimpleName().contains("Postgres"));
Configuration conf=getConfigurationClone();
conf.setProperty("implement.foreign.keys", "true");
try (SqlgGraph g = SqlgGraph.open(conf)) {
Vertex v1 = g.addVertex(T.label, "Person");
Vertex v2 = g.addVertex(T.label, "Person");
v1.addEdge("Edge1", v2);
g.tx().commit();
Connection conn = g.tx().getConnection();
DatabaseMetaData dm = conn.getMetaData();
ResultSet rs = dm.getImportedKeys("sqlggraphdb", "public", "E_Edge1");
Assert.assertTrue(rs.next());
}
}
Example 2
| Source File: SectDBSynchronizer.java From gemfirexd-oss with Apache License 2.0 | 6 votes |
|
protected void addParentTables(DatabaseMetaData metaData, String schema,
String tableName, String fullTableName) throws SQLException {
HashSet<String> parents = this.parentTables.get(fullTableName);
if (parents == null) {
parents = new HashSet<String>(5);
this.parentTables.put(fullTableName, parents);
}
ResultSet parentRS = metaData.getImportedKeys(null, schema, tableName);
while (parentRS.next()) {
String parentSchema = parentRS.getString("PKTABLE_SCHEM");
String parentTableName = parentRS.getString("PKTABLE_NAME");
parents = this.parentTables.get(fullTableName);
if (parentSchema == null) {
parentSchema = schema;
}
parents.add(parentSchema != null ? parentSchema + '.' + parentTableName
: parentTableName);
}
parentRS.close();
}
Example 3
| Source File: SectDBSynchronizer.java From gemfirexd-oss with Apache License 2.0 | 6 votes |
|
protected void addParentTables(DatabaseMetaData metaData, String schema,
String tableName, String fullTableName) throws SQLException {
HashSet<String> parents = this.parentTables.get(fullTableName);
if (parents == null) {
parents = new HashSet<String>(5);
this.parentTables.put(fullTableName, parents);
}
ResultSet parentRS = metaData.getImportedKeys(null, schema, tableName);
while (parentRS.next()) {
String parentSchema = parentRS.getString("PKTABLE_SCHEM");
String parentTableName = parentRS.getString("PKTABLE_NAME");
parents = this.parentTables.get(fullTableName);
if (parentSchema == null) {
parentSchema = schema;
}
parents.add(parentSchema != null ? parentSchema + '.' + parentTableName
: parentTableName);
}
parentRS.close();
}
Example 4
| Source File: MetaResultSetTest.java From calcite-avatica with Apache License 2.0 | 6 votes |
|
@Test public void testGetImportedKeys() throws SQLException {
DatabaseMetaData metadata = getDatabaseMetadata();
try (ResultSet rs = metadata.getImportedKeys(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 5
| Source File: TestForeignKeysAreOptional.java From sqlg with MIT License | 6 votes |
|
@Test
public void testForeignKeysOffPostgres() throws Exception {
Assume.assumeTrue(this.sqlgGraph.getSqlDialect().getClass().getSimpleName().contains("Postgres"));
Configuration conf=getConfigurationClone();
conf.setProperty("implement.foreign.keys", "false");
try (SqlgGraph g = SqlgGraph.open(conf)) {
Vertex v1 = g.addVertex(T.label, "Person");
Vertex v2 = g.addVertex(T.label, "Person");
v1.addEdge("Edge1", v2);
g.tx().commit();
Connection conn = g.tx().getConnection();
DatabaseMetaData dm = conn.getMetaData();
ResultSet rs = dm.getImportedKeys("sqlgraphdb", "public", "E_Edge1");
Assert.assertFalse(rs.next());
}
}
Example 6
| Source File: TableMetadata.java From lams with GNU General Public License v2.0 | 5 votes |
|
private void initForeignKeys(DatabaseMetaData meta) throws SQLException {
ResultSet rs = null;
try {
rs = meta.getImportedKeys( catalog, schema, name );
while ( rs.next() ) {
addForeignKey( rs );
}
}
finally {
if ( rs != null ) {
rs.close();
}
}
}
Example 7
| Source File: MetadataTest.java From r-course with MIT License | 5 votes |
|
/**
* Tests the implementation of Information Schema for foreign key.
*/
public void testGetImportedKeysUsingInfoSchema() 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.getImportedKeys(null, null, "child");
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 8
| Source File: AceQLMetaData.java From aceql-http with GNU Lesser General Public License v2.1 | 5 votes |
|
/**
* Returns the foreign imported keys for the passed table
*
* @param tableName the table name for the passed table
* @return the foreign exported keys
* @throws SQLException it any SQL Exception occurs
*/
public List<ImportedKey> getImportedKeys(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);
}
DatabaseMetaData databaseMetaData = connection.getMetaData();
ResultSet rs = databaseMetaData.getImportedKeys(catalog, schema, tableName);
List<ImportedKey> importedKeys = new ArrayList<>();
while (rs.next()) {
ImportedKey importedKey = new ImportedKey();
int i = 1;
importedKey.setCatalog(rs.getString(i++));
importedKey.setSchema(rs.getString(i++));
importedKey.setPrimaryKeyTable(rs.getString(i++));
importedKey.setPrimaryKeyColumn(rs.getString(i++));
importedKey.setForeignKeyCatalog(rs.getString(i++));
importedKey.setForeignKeySchema(rs.getString(i++));
importedKey.setForeignKeyTable(rs.getString(i++));
importedKey.setForeignKeyColumn(rs.getString(i++));
importedKey.setKeySequence(rs.getInt(i++));
importedKey.setUpdateRule(MetaDataJavaUtil.decodeRule(rs.getInt(i++)));
importedKey.setDeleteRule(MetaDataJavaUtil.decodeRule(rs.getInt(i++)));
importedKey.setForeignKeyName(rs.getString(i++));
importedKey.setPrimaryKeyName(rs.getString(i++));
importedKey.setDeferrability(rs.getInt(i++));
importedKeys.add(importedKey);
}
return importedKeys;
}
Example 9
| Source File: MetadataUtilities.java From netbeans with Apache License 2.0 | 5 votes |
|
/**
* Call {@link DatabaseMetaData#getImportedKeys(String, String, String)},
* wrapping any internal runtime exception into an {@link SQLException}.
*/
public static ResultSet getImportedKeys(DatabaseMetaData dmd,
String catalog, String schema, String table) throws SQLException {
try {
return dmd.getImportedKeys(catalog, schema, table);
} catch (SQLException e) {
throw e;
} catch (Throwable t) {
throw new SQLException(t);
}
}
Example 10
| Source File: SchemaInferrer.java From EchoQuery with GNU General Public License v2.0 | 5 votes |
|
private void populateTableToForeignKeys(String table, DatabaseMetaData md)
throws SQLException {
ResultSet foreignKeys = md.getImportedKeys(null, null, table);
while (foreignKeys.next()) {
String fkTableName = foreignKeys.getString("FKTABLE_NAME").toLowerCase();
String fkColumnName = foreignKeys.getString("FKCOLUMN_NAME").toLowerCase();
String pkTableName = foreignKeys.getString("PKTABLE_NAME").toLowerCase();
String pkColumnName = foreignKeys.getString("PKCOLUMN_NAME").toLowerCase();
List<ForeignKey> previousList = tableToForeignKeys.getOrDefault(
fkTableName, new ArrayList<ForeignKey>());
previousList.add(
new ForeignKey(fkColumnName, fkTableName, pkColumnName, pkTableName));
tableToForeignKeys.put(fkTableName, previousList);
}
}
Example 11
| Source File: DatabaseMetaDataTest.java From gemfirexd-oss with Apache License 2.0 | 5 votes |
|
/**
* Helper method for testing getImportedKeys - calls dmd.getImportedKeys for
* the JDBC call, and getImportedKeysODBC for the ODBC procedure
* @throws SQLException
*/
private ResultSet[] getImportedKeys(
String catalog, String schema, String table) throws SQLException
{
ResultSet[] rss = new ResultSet[2];
DatabaseMetaData dmd = getDMD();
rss[0]= dmd.getImportedKeys(catalog, schema, table);
rss[1]= getImportedKeysODBC(catalog, schema, table);
assertGetImportedAndExportedKeysShape(rss);
return rss;
}
Example 12
| Source File: DatabaseMetaDataTest.java From gemfirexd-oss with Apache License 2.0 | 5 votes |
|
public void testBugFixes() throws SQLException {
Statement s = createStatement();
getConnection().setAutoCommit(false);
DatabaseMetaData dmd = getDMD();
// test DERBY-655, DERBY-1343
// If a table has duplicate backing index, then it will share the
// physical conglomerate with the existing index, but the duplicate
// indexes should have their own unique logical congomerates
// associated with them. That way, it will be possible to
// distinguish the 2 indexes in SYSCONGLOMERATES from each other.
s.execute("CREATE TABLE Derby655t1(c11_ID BIGINT NOT NULL)");
s.execute("CREATE TABLE Derby655t2 (c21_ID BIGINT NOT NULL primary key)");
s.execute("ALTER TABLE Derby655t1 ADD CONSTRAINT F_12 Foreign Key (c11_ID) REFERENCES Derby655t2 (c21_ID) ON DELETE CASCADE ON UPDATE NO ACTION");
s.execute("CREATE TABLE Derby655t3(c31_ID BIGINT NOT NULL primary key)");
s.execute("ALTER TABLE Derby655t2 ADD CONSTRAINT F_443 Foreign Key (c21_ID) REFERENCES Derby655t3(c31_ID) ON DELETE CASCADE ON UPDATE NO ACTION");
ResultSet rs = dmd.getImportedKeys("", "APP", "DERBY655T1");
JDBC.assertDrainResults(rs, 1);
s.execute("drop table Derby655t1");
s.execute("drop table Derby655t2");
s.execute("drop table Derby655t3");
// This checks for a bug where you get incorrect behavior on a nested connection.
// if you do not get an error, the bug does not occur.
if(JDBC.vmSupportsJDBC3()){
s.execute("create procedure isReadO() language java external name " +
"'org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.isro'" +
" parameter style java");
s.execute("call isReadO()");
}
}
Example 13
| Source File: JdbcUtil.java From datacollector with Apache License 2.0 | 5 votes |
|
/**
* Wrapper for {@link java.sql.DatabaseMetaData#getImportedKeys(String, String, String)}
*
* @param connection An open JDBC connection
* @param tableName table name that is optionally fully qualified with a schema in the form schema.tableName
* @return List of Table Names whose primary key are referred as foreign key by the table tableName
*
* @throws SQLException
*/
public Set<String> getReferredTables(Connection connection, String schema, String tableName) throws SQLException {
DatabaseMetaData metadata = connection.getMetaData();
ResultSet result = metadata.getImportedKeys(getCatalog(connection, schema), schema, tableName);
Set<String> referredTables = new HashSet<>();
while (result.next()) {
referredTables.add(result.getString(PK_TABLE_NAME));
}
return referredTables;
}
Example 14
| Source File: MetadataTest.java From FoxTelem with GNU General Public License v3.0 | 5 votes |
|
/**
* Tests the implementation of Information Schema for foreign key.
*/
public void testGetImportedKeysUsingInfoSchema() throws Exception {
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.setProperty(PropertyKey.useInformationSchema.getKeyName(), "true");
Connection conn1 = null;
try {
conn1 = getConnectionWithProps(props);
DatabaseMetaData metaData = conn1.getMetaData();
this.rs = metaData.getImportedKeys(null, null, "child");
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 15
| Source File: DatabaseLoader.java From sqlhelper with GNU Lesser General Public License v3.0 | 5 votes |
|
private void findTableFKs(DatabaseMetaData dbMetaData, Table table) throws SQLException {
ResultSet fkRs = null;
try {
fkRs = dbMetaData.getImportedKeys(table.getCatalog(), table.getSchema(), table.getName());
List<ImportedColumn> fkColumns = new RowMapperResultSetExtractor<ImportedColumn>(new BeanRowMapper<ImportedColumn>(ImportedColumn.class)).extract(fkRs);
for (ImportedColumn fk : fkColumns) {
table.addFKColumn(fk);
}
} finally {
IOs.close(fkRs);
}
}
Example 16
| Source File: JDBCConnection.java From dbtransfer with GNU Lesser General Public License v2.1 | 4 votes |
|
public List<ForeignKeyDefinition> getForeignKeyList(Name table) throws Exception
{
DatabaseMetaData rsmd = connection.getMetaData();
System.out.println( "getting FKs: " + connection.getCatalog() + "/" + dbSchema + "/" + table.originalName + "/" );
ResultSet rs = rsmd.getImportedKeys( catalog, dbSchema, helper.outputName( table.originalName ) );
List<ForeignKeyDefinition> list = new LinkedList<ForeignKeyDefinition>();
Map<String,ForeignKeyDefinition> fks = new HashMap<String, ForeignKeyDefinition>();
while( rs.next() )
{
String fkName = rs.getString( 12 );
//System.out.println( "FK : " + fkName );
ForeignKeyDefinition fk = fks.get( fkName );
if( fk == null )
{
fk = new ForeignKeyDefinition( fkName, table );
fks.put( fkName, fk );
}
ColumnDefinition col = new ColumnDefinition();
col.referencedTable = new Name( rs.getString( 3 ) );
col.referencedColumn = new Name( rs.getString( 4 ) );
col.name = new Name( rs.getString( 8 ) );
ResultSet rsC = rsmd.getColumns( catalog, dbSchema, helper.outputName( table.originalName ), col.name.originalName );
if( rsC.next() )
{
col.sqlTypeName = rsC.getString( 6 );
if( rsC.getInt( 5 ) == Types.CHAR
|| rsC.getInt( 5 ) == Types.LONGVARCHAR
|| rsC.getInt( 5 ) == Types.VARCHAR
|| rsC.getInt( 5 ) == Types.NCHAR
|| rsC.getInt( 5 ) == Types.LONGNVARCHAR
|| rsC.getInt( 5 ) == Types.NVARCHAR )
{
col.sqlSize = rsC.getInt( 7 );
}
col.defaultValue = rsC.getString( 13 );
col.isNotNull = "NO".equals( rsC.getString( 18 ).trim() );
rsC.close();
}
else
{
new Exception( "Can't find column for fk - " + fk.name + "/ col " + col.name ).printStackTrace();
}
fk.columns.add( col );
list.add( fk );
}
rs.close();
return list;
}
Example 17
| Source File: BartDBMSUtility.java From BART with MIT License | 4 votes |
|
public static List<ForeignKey> loadForeignKeys(AccessConfiguration accessConfiguration) {
Map<String, ForeignKey> foreignKeyMap = new HashMap<String, ForeignKey>();
String schemaName = accessConfiguration.getSchemaName();
Connection connection = null;
ResultSet tableResultSet = null;
try {
if (logger.isDebugEnabled()) logger.debug("Loading foreign keys: " + accessConfiguration);
connection = QueryManager.getConnection(accessConfiguration);
String catalog = connection.getCatalog();
if (catalog == null) {
catalog = accessConfiguration.getUri();
if (logger.isDebugEnabled()) logger.debug("Catalog is null. Catalog name will be: " + catalog);
}
DatabaseMetaData databaseMetaData = connection.getMetaData();
tableResultSet = databaseMetaData.getTables(catalog, schemaName, null, new String[]{"TABLE"});
while (tableResultSet.next()) {
String tableName = tableResultSet.getString("TABLE_NAME");
if (logger.isDebugEnabled()) logger.debug("Searching foreign keys. ANALYZING TABLE = " + tableName);
ResultSet resultSet = databaseMetaData.getImportedKeys(catalog, null, tableName);
while (resultSet.next()) {
String fkeyName = resultSet.getString("FK_NAME");
String pkTableName = resultSet.getString("PKTABLE_NAME");
String pkColumnName = resultSet.getString("PKCOLUMN_NAME");
String keyPrimaryKey = pkTableName + "." + pkColumnName;
String fkTableName = resultSet.getString("FKTABLE_NAME");
String fkColumnName = resultSet.getString("FKCOLUMN_NAME");
String keyForeignKey = fkTableName + "." + fkColumnName;
if (logger.isDebugEnabled()) logger.debug("Analyzing Primary Key: " + keyPrimaryKey + " Found a Foreign Key: " + fkColumnName + " in table " + fkTableName);
if (logger.isDebugEnabled()) logger.debug("Analyzing foreign key: " + keyForeignKey + " references " + keyPrimaryKey);
addFKToMap(foreignKeyMap, fkeyName, new AttributeRef(pkTableName, pkColumnName), new AttributeRef(fkTableName, fkColumnName));
}
}
} catch (DAOException daoe) {
throw new DBMSException("Error connecting to database.\n" + accessConfiguration + "\n" + daoe.getLocalizedMessage());
} catch (SQLException sqle) {
throw new DBMSException("Error connecting to database.\n" + accessConfiguration + "\n" + sqle.getLocalizedMessage());
} finally {
QueryManager.closeResultSet(tableResultSet);
QueryManager.closeConnection(connection);
}
return new ArrayList<ForeignKey>(foreignKeyMap.values());
}
Example 18
| Source File: XMLContentExporter.java From syncope with Apache License 2.0 | 4 votes |
|
private static List<String> sortByForeignKeys(final String dbSchema, final Connection conn,
final Set<String> tableNames)
throws SQLException {
Set<MultiParentNode<String>> roots = new HashSet<>();
DatabaseMetaData meta = conn.getMetaData();
Map<String, MultiParentNode<String>> exploited = new TreeMap<>(String.CASE_INSENSITIVE_ORDER);
Set<String> pkTableNames = new HashSet<>();
for (String tableName : tableNames) {
MultiParentNode<String> node = exploited.get(tableName);
if (node == null) {
node = new MultiParentNode<>(tableName);
roots.add(node);
exploited.put(tableName, node);
}
pkTableNames.clear();
ResultSet rs = null;
try {
rs = meta.getImportedKeys(conn.getCatalog(), dbSchema, tableName);
// this is to avoid repetition
while (rs.next()) {
pkTableNames.add(rs.getString("PKTABLE_NAME"));
}
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
LOG.error("While closing tables result set", e);
}
}
}
for (String pkTableName : pkTableNames) {
if (!tableName.equalsIgnoreCase(pkTableName)) {
MultiParentNode<String> pkNode = exploited.get(pkTableName);
if (pkNode == null) {
pkNode = new MultiParentNode<>(pkTableName);
roots.add(pkNode);
exploited.put(pkTableName, pkNode);
}
pkNode.addChild(node);
if (roots.contains(node)) {
roots.remove(node);
}
}
}
}
List<String> sortedTableNames = new ArrayList<>(tableNames.size());
MultiParentNodeOp.traverseTree(roots, sortedTableNames);
// remove from sortedTableNames any table possibly added during lookup
// but matching some item in this.tablePrefixesToBeExcluded
sortedTableNames.retainAll(tableNames);
LOG.debug("Tables after retainAll {}", sortedTableNames);
Collections.reverse(sortedTableNames);
return sortedTableNames;
}
Example 19
| Source File: DatabaseMetaDataInternalIT.java From snowflake-jdbc with Apache License 2.0 | 4 votes |
|
@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 20
| Source File: DatabaseMetaDataIT.java From snowflake-jdbc with Apache License 2.0 | 4 votes |
|
@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));
}
}
