db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (Commented) (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5244) DatabaseMetaData.getColumns(null, null, tableName, null) does not return the columns meta for a SYNONYM
Date Thu, 10 Nov 2011 20:45:51 GMT

    [ 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

        

Mime
View raw message