[
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
