You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Prashanth G (JIRA)" <ji...@apache.org> on 2011/05/24 18:19:47 UTC
[jira] [Created] (DERBY-5244) DatabaseMetaData.getColumns(null,
null, tableName, null) does not return the columns meta for a SYNONYM
DatabaseMetaData.getColumns(null, null, tableName, null) does not return the columns meta for a SYNONYM
-------------------------------------------------------------------------------------------------------
Key: DERBY-5244
URL: https://issues.apache.org/jira/browse/DERBY-5244
Project: Derby
Issue Type: Bug
Components: JDBC
Affects Versions: 10.8.1.2
Environment: Windows
Reporter: Prashanth G
Priority: Minor
DatabaseMetaData.getColumns(null, null, tableName, null) does not return the columns meta for a SYNONYM.
Sample java code use for testing.
/**
* ListColumns.java
*/
import java.sql.*;
import java.util.Properties;
public class ListColumns {
public static void main(String[] args) {
Connection con = null;
try {
Class.forName("org.apache.derby.jdbc.ClientDriver");
Properties connectionProps = new Properties();
connectionProps.put("user", "app");
connectionProps.put("password", "app");
con = DriverManager.getConnection(
"jdbc:derby://localhost:1527/northwind", connectionProps);
String tableName = "V1";
DatabaseMetaData meta = con.getMetaData();
System.out.println("Database Product Version"
+ meta.getDatabaseProductVersion());
ResultSet res = meta.getColumns(null, null, tableName, null);
System.out.println("List of columns for " + tableName);
while (res.next()) {
System.out.println(" " + res.getString("TABLE_SCHEM") + ", "
+ res.getString("TABLE_NAME") + ", "
+ res.getString("COLUMN_NAME") + ", "
+ res.getString("TYPE_NAME") + ", "
+ res.getInt("COLUMN_SIZE") + ", "
+ res.getString("NULLABLE"));
}
res.close();
con.close();
} catch (java.lang.ClassNotFoundException e) {
System.err.println("ClassNotFoundException: " + e.getMessage());
} catch (SQLException e) {
System.err.println("SQLException: " + e.getMessage());
}
}
}
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5244) DatabaseMetaData.getColumns(null,
null, tableName, null) does not return the columns meta for a SYNONYM
Posted by "Mamta A. Satoor (Commented) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5244?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13146494#comment-13146494 ]
Mamta A. Satoor commented on DERBY-5244:
----------------------------------------
Added(revision 1199096) a test case which confirms the behavior noticed by this jira
> DatabaseMetaData.getColumns(null, null, tableName, null) does not return the columns meta for a SYNONYM
> -------------------------------------------------------------------------------------------------------
>
> Key: DERBY-5244
> URL: https://issues.apache.org/jira/browse/DERBY-5244
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.8.1.2
> Environment: Windows
> Reporter: Prashanth Godey
> Priority: Minor
> Labels: SYNONYM, getColumns
>
> DatabaseMetaData.getColumns(null, null, tableName, null) does not return the columns meta for a SYNONYM.
> Sample java code use for testing.
> /**
> * ListColumns.java
> */
> import java.sql.*;
> import java.util.Properties;
> public class ListColumns {
> public static void main(String[] args) {
> Connection con = null;
> try {
> Class.forName("org.apache.derby.jdbc.ClientDriver");
>
> Properties connectionProps = new Properties();
> connectionProps.put("user", "app");
> connectionProps.put("password", "app");
>
> con = DriverManager.getConnection(
> "jdbc:derby://localhost:1527/northwind", connectionProps);
> String tableName = "V1";
> DatabaseMetaData meta = con.getMetaData();
> System.out.println("Database Product Version"
> + meta.getDatabaseProductVersion());
> ResultSet res = meta.getColumns(null, null, tableName, null);
> System.out.println("List of columns for " + tableName);
> while (res.next()) {
> System.out.println(" " + res.getString("TABLE_SCHEM") + ", "
> + res.getString("TABLE_NAME") + ", "
> + res.getString("COLUMN_NAME") + ", "
> + res.getString("TYPE_NAME") + ", "
> + res.getInt("COLUMN_SIZE") + ", "
> + res.getString("NULLABLE"));
> }
> res.close();
> con.close();
> } catch (java.lang.ClassNotFoundException e) {
> System.err.println("ClassNotFoundException: " + e.getMessage());
> } catch (SQLException e) {
> System.err.println("SQLException: " + e.getMessage());
> }
> }
> }
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5244) DatabaseMetaData.getColumns(null,
null, tableName, null) does not return the columns meta for a SYNONYM
Posted by "Mike Matrigali (Commented) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5244?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13148111#comment-13148111 ]
Mike Matrigali commented on DERBY-5244:
---------------------------------------
The current implementation seems in keeping with the standard. If derby has a place in the documentation it would be reasonable to note the current behavior.
I would guess that option 2 might lead to problems unless other consequences are considered like drop table and drop synonym.
> DatabaseMetaData.getColumns(null, null, tableName, null) does not return the columns meta for a SYNONYM
> -------------------------------------------------------------------------------------------------------
>
> Key: DERBY-5244
> URL: https://issues.apache.org/jira/browse/DERBY-5244
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.8.1.2
> Environment: Windows
> Reporter: Prashanth Godey
> Priority: Minor
> Labels: SYNONYM, getColumns
>
> DatabaseMetaData.getColumns(null, null, tableName, null) does not return the columns meta for a SYNONYM.
> Sample java code use for testing.
> /**
> * ListColumns.java
> */
> import java.sql.*;
> import java.util.Properties;
> public class ListColumns {
> public static void main(String[] args) {
> Connection con = null;
> try {
> Class.forName("org.apache.derby.jdbc.ClientDriver");
>
> Properties connectionProps = new Properties();
> connectionProps.put("user", "app");
> connectionProps.put("password", "app");
>
> con = DriverManager.getConnection(
> "jdbc:derby://localhost:1527/northwind", connectionProps);
> String tableName = "V1";
> DatabaseMetaData meta = con.getMetaData();
> System.out.println("Database Product Version"
> + meta.getDatabaseProductVersion());
> ResultSet res = meta.getColumns(null, null, tableName, null);
> System.out.println("List of columns for " + tableName);
> while (res.next()) {
> System.out.println(" " + res.getString("TABLE_SCHEM") + ", "
> + res.getString("TABLE_NAME") + ", "
> + res.getString("COLUMN_NAME") + ", "
> + res.getString("TYPE_NAME") + ", "
> + res.getInt("COLUMN_SIZE") + ", "
> + res.getString("NULLABLE"));
> }
> res.close();
> con.close();
> } catch (java.lang.ClassNotFoundException e) {
> System.err.println("ClassNotFoundException: " + e.getMessage());
> } catch (SQLException e) {
> System.err.println("SQLException: " + e.getMessage());
> }
> }
> }
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (DERBY-5244) DatabaseMetaData.getColumns(null,
null, tableName, null) does not return the columns meta for a SYNONYM
Posted by "Kim Haase (Updated) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5244?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-5244:
-----------------------------
Component/s: (was: JDBC)
Documentation
Issue Type: Improvement (was: Bug)
Thanks, Mike -- I am reclassifying this issue as a documentation improvement rather than a JDBC bug. We can add another subtopic in the JDBC section on the java.sql.DatabaseMetaData interface.
> DatabaseMetaData.getColumns(null, null, tableName, null) does not return the columns meta for a SYNONYM
> -------------------------------------------------------------------------------------------------------
>
> Key: DERBY-5244
> URL: https://issues.apache.org/jira/browse/DERBY-5244
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Affects Versions: 10.8.1.2
> Environment: Windows
> Reporter: Prashanth Godey
> Priority: Minor
> Labels: SYNONYM, getColumns
>
> DatabaseMetaData.getColumns(null, null, tableName, null) does not return the columns meta for a SYNONYM.
> Sample java code use for testing.
> /**
> * ListColumns.java
> */
> import java.sql.*;
> import java.util.Properties;
> public class ListColumns {
> public static void main(String[] args) {
> Connection con = null;
> try {
> Class.forName("org.apache.derby.jdbc.ClientDriver");
>
> Properties connectionProps = new Properties();
> connectionProps.put("user", "app");
> connectionProps.put("password", "app");
>
> con = DriverManager.getConnection(
> "jdbc:derby://localhost:1527/northwind", connectionProps);
> String tableName = "V1";
> DatabaseMetaData meta = con.getMetaData();
> System.out.println("Database Product Version"
> + meta.getDatabaseProductVersion());
> ResultSet res = meta.getColumns(null, null, tableName, null);
> System.out.println("List of columns for " + tableName);
> while (res.next()) {
> System.out.println(" " + res.getString("TABLE_SCHEM") + ", "
> + res.getString("TABLE_NAME") + ", "
> + res.getString("COLUMN_NAME") + ", "
> + res.getString("TYPE_NAME") + ", "
> + res.getInt("COLUMN_SIZE") + ", "
> + res.getString("NULLABLE"));
> }
> res.close();
> con.close();
> } catch (java.lang.ClassNotFoundException e) {
> System.err.println("ClassNotFoundException: " + e.getMessage());
> } catch (SQLException e) {
> System.err.println("SQLException: " + e.getMessage());
> }
> }
> }
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5244) DatabaseMetaData.getColumns(null,
null, tableName, null) does not return the columns meta for a SYNONYM
Posted by "Mamta A. Satoor (Commented) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5244?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13147997#comment-13147997 ]
Mamta A. Satoor commented on DERBY-5244:
----------------------------------------
I debugged this issue and found that the problem is that for synonyms, we do not keep any rows in SYSCOLUMNS. It may have been a conscious implementation decision to do so because columns in synonyms are going to be same as the columns in underlying table/view. But metadata query associated with DatabaseMetaData.getColumns() does a join on SYSTABLES, SYSCOLUMNS AND SYSSCHEMA to get the list of columns but since there are no rows in SYSCOLUMNS, we always get an empty resultset for synonyms. Following is part of the query associated with getColumns in impl.jdbc.metadata.properties
FROM SYS.SYSSCHEMAS S, \
SYS.SYSTABLES T, \
SYS.SYSCOLUMNS C \
WHERE C.REFERENCEID = T.TABLEID AND S.SCHEMAID = T.SCHEMAID \
AND ((1=1) OR ? IS NOT NULL) \
AND (S.SCHEMANAME LIKE ?) AND (T.TABLENAME LIKE ?) AND (C.COLUMNNAME LIKE ?) \
ORDER BY TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION
Java api for getColumns say that getColumns is called on tables(http://download.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getColumns(java.lang.String, java.lang.String, java.lang.String, java.lang.String)). Does it mean that it is ok for us to return empty resultset for synonyms? I did write a test for view and saw that we do return column information for views. I will commit the test for views soon.
Possible options are
1)modify the metadata query to include synonyms - not sure how easy that might be(or if it is doable at all)
2)add rows in syscolumns(for existing databases, we can do this at the time of upgrade) so we know that the existing metadata query will work
3)see if we can tweak EmbedDatabaseMetaData.getColumns method to do something special for synonyms. Again, since we send a pattern for tablename to getColumns, I am not sure how we will be able to handle tables, views and synonyms starting with the given table name pattern,
4)I did some google search on synonyms and getColumns and found that both DB2 and Oracle it appears require a special knob to be set to true(the knob is not same for the 2 databases) before getColumns will start returning rows for synonyms.
For DB2, it is an optional connection property CatalogIncludesSynonyms {true | false}. When set to true, synonyms are included in the result sets returned from the following DatabaseMetaData methods: getColumns, getProcedureColumns, and getIndexInfo. When set to false, synonyms are omitted from result sets. The default is true.
For Oracle, it is a connection level property too, a property named "includeSynonyms" can be set to "true" to DriverManager.getConnection.
> DatabaseMetaData.getColumns(null, null, tableName, null) does not return the columns meta for a SYNONYM
> -------------------------------------------------------------------------------------------------------
>
> Key: DERBY-5244
> URL: https://issues.apache.org/jira/browse/DERBY-5244
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.8.1.2
> Environment: Windows
> Reporter: Prashanth Godey
> Priority: Minor
> Labels: SYNONYM, getColumns
>
> DatabaseMetaData.getColumns(null, null, tableName, null) does not return the columns meta for a SYNONYM.
> Sample java code use for testing.
> /**
> * ListColumns.java
> */
> import java.sql.*;
> import java.util.Properties;
> public class ListColumns {
> public static void main(String[] args) {
> Connection con = null;
> try {
> Class.forName("org.apache.derby.jdbc.ClientDriver");
>
> Properties connectionProps = new Properties();
> connectionProps.put("user", "app");
> connectionProps.put("password", "app");
>
> con = DriverManager.getConnection(
> "jdbc:derby://localhost:1527/northwind", connectionProps);
> String tableName = "V1";
> DatabaseMetaData meta = con.getMetaData();
> System.out.println("Database Product Version"
> + meta.getDatabaseProductVersion());
> ResultSet res = meta.getColumns(null, null, tableName, null);
> System.out.println("List of columns for " + tableName);
> while (res.next()) {
> System.out.println(" " + res.getString("TABLE_SCHEM") + ", "
> + res.getString("TABLE_NAME") + ", "
> + res.getString("COLUMN_NAME") + ", "
> + res.getString("TYPE_NAME") + ", "
> + res.getInt("COLUMN_SIZE") + ", "
> + res.getString("NULLABLE"));
> }
> res.close();
> con.close();
> } catch (java.lang.ClassNotFoundException e) {
> System.err.println("ClassNotFoundException: " + e.getMessage());
> } catch (SQLException e) {
> System.err.println("SQLException: " + e.getMessage());
> }
> }
> }
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5244) DatabaseMetaData.getColumns(null,
null, tableName, null) does not return the columns meta for a SYNONYM
Posted by "Mamta A. Satoor (Commented) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5244?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13148574#comment-13148574 ]
Mamta A. Satoor commented on DERBY-5244:
----------------------------------------
Yes, Mike, I should have elaborated more on option 2). It is just not adding the rows in SYSCOLUMNS but also maintaining them as needed based on say ALTER TABLE.. DROP COLUMN, ALTER TABLE .. ADD COLUMN, DROP TABLE, DROP SYNONYM etc. There might be more than these few commands that might impact what happens to rows in SYSCOLUMNS for synonyms.
> DatabaseMetaData.getColumns(null, null, tableName, null) does not return the columns meta for a SYNONYM
> -------------------------------------------------------------------------------------------------------
>
> Key: DERBY-5244
> URL: https://issues.apache.org/jira/browse/DERBY-5244
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Affects Versions: 10.8.1.2
> Environment: Windows
> Reporter: Prashanth Godey
> Priority: Minor
> Labels: SYNONYM, getColumns
>
> DatabaseMetaData.getColumns(null, null, tableName, null) does not return the columns meta for a SYNONYM.
> Sample java code use for testing.
> /**
> * ListColumns.java
> */
> import java.sql.*;
> import java.util.Properties;
> public class ListColumns {
> public static void main(String[] args) {
> Connection con = null;
> try {
> Class.forName("org.apache.derby.jdbc.ClientDriver");
>
> Properties connectionProps = new Properties();
> connectionProps.put("user", "app");
> connectionProps.put("password", "app");
>
> con = DriverManager.getConnection(
> "jdbc:derby://localhost:1527/northwind", connectionProps);
> String tableName = "V1";
> DatabaseMetaData meta = con.getMetaData();
> System.out.println("Database Product Version"
> + meta.getDatabaseProductVersion());
> ResultSet res = meta.getColumns(null, null, tableName, null);
> System.out.println("List of columns for " + tableName);
> while (res.next()) {
> System.out.println(" " + res.getString("TABLE_SCHEM") + ", "
> + res.getString("TABLE_NAME") + ", "
> + res.getString("COLUMN_NAME") + ", "
> + res.getString("TYPE_NAME") + ", "
> + res.getInt("COLUMN_SIZE") + ", "
> + res.getString("NULLABLE"));
> }
> res.close();
> con.close();
> } catch (java.lang.ClassNotFoundException e) {
> System.err.println("ClassNotFoundException: " + e.getMessage());
> } catch (SQLException e) {
> System.err.println("SQLException: " + e.getMessage());
> }
> }
> }
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira