String table) throws SQLException {
//check if the dictionary is at 10.2 revision. If not, then that means
//stored prepared statements for metadata calls are using the old
//optimizer override syntax which is not recognized by 10.2 egnine.
//This can happen if we are in soft upgrade mode. Since in soft
//upgrade mode, we can't change the system tables in an backward
//incompatible way, I am going to try to read the metadata sql from
//metadata.properties file rather than rely on system tables.
boolean newOptimizerOverridesSyntaxSupported;
try {
newOptimizerOverridesSyntaxSupported =
getLanguageConnectionContext().getDataDictionary().checkVersion(
DataDictionary.DD_VERSION_DERBY_10_2,null);
} catch (Throwable t) {
throw handleException(t);
}
//We can safely goto system table since data dictionary is at 10.2
//and hence is using new optimizer overrides syntax.
if (newOptimizerOverridesSyntaxSupported)
return doGetPrimaryKeys(catalog, schema, table, "getPrimaryKeys");
else
{
//Can't use stored prepared statements because they don't use the new
//new optimizer override syntax. Need to read the sql from metadata.properties
synchronized (getConnectionSynchronization()) {
setupContextStack();
ResultSet rs = null;
try {
String queryText = getQueryDescriptions().getProperty("getPrimaryKeys");
PreparedStatement s = getEmbedConnection().prepareMetaDataStatement(queryText);
s.setString(1, swapNull(catalog));
s.setString(2, swapNull(schema));
s.setString(3, swapNull(table));
rs = s.executeQuery();
} catch (Throwable t) {
throw handleException(t);
} finally {
restoreContextStack();
}
return rs;
}
}
}
The sql from getPrimaryKeys in metadata.properties looks as follows
getPrimaryKeys=\
SELECT CAST ('' AS VARCHAR(128)) AS TABLE_CAT, \
S.SCHEMANAME AS TABLE_SCHEM, T.TABLENAME AS TABLE_NAME, \
COLS.COLUMNNAME AS COLUMN_NAME, \
CONGLOMS.DESCRIPTOR.getKeyColumnPosition
(COLS.COLUMNNUMBER) AS KEY_SEQ, \
CONS.CONSTRAINTNAME AS PK_NAME \
FROM --DERBY-PROPERTIES joinOrder=FIXED \n \
SYS.SYSTABLES T --DERBY-PROPERTIES index='SYSTABLES_INDEX1' \n\
, SYS.SYSSCHEMAS S --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSSCHEMAS_INDEX1' \n\
, SYS.SYSCONSTRAINTS CONS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSCONSTRAINTS_INDEX3' \n\
, SYS.SYSKEYS KEYS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSKEYS_INDEX1' \n\
, SYS.SYSCONGLOMERATES
CONGLOMS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1' \n\
, SYS.SYSCOLUMNS COLS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSCOLUMNS_INDEX1' \n\
WHERE ((1=1) OR ? IS NOT NULL) AND
S.SCHEMANAME LIKE ? AND T.TABLENAME LIKE ? AND \
T.SCHEMAID = S.SCHEMAID AND \
T.TABLEID = COLS.REFERENCEID AND T.TABLEID = CONGLOMS.TABLEID AND \
CONS.TABLEID = T.TABLEID AND CONS.TYPE = 'P' AND \
CONS.CONSTRAINTID = KEYS.CONSTRAINTID AND \
(CASE WHEN CONGLOMS.DESCRIPTOR IS NOT NULL THEN \
CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER) ELSE \
0 END) <> 0 AND \
KEYS.CONGLOMERATEID
= CONGLOMS.CONGLOMERATEID \
ORDER BY COLUMN_NAME
The line number 4 of the sql is CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER) AS KEY_SEQ which gives syntax error when I am trying to run metadata query as prepared statement rather than stored prepared statement. Any help in getting over this hump will be great. One drawback I see with this possible solution is that every call to getPrimaryKeys in 10.2 will now check if the database is in soft upgrade mode and 99% of the time, that will not be the case. So, there is this additional check which might have some small performance issue. But the plus point is that not all the metadata calls have to do this check, only the ones that use optimizer overrides in their sql. Those metadata calls are getCrossReference, getImportedKeys, getPrimaryKeys.
