getPrimaryKeys

Hi all,

I have a problem with the data returned from:

   java.sql.DatabaseMetaData.getPrimaryKeys(String,String,String)

As stated in the API docs the ResultSet is ordered by COLUMN_NAME.
To save a list or array of key columns ordered by sequence within
the primary key I (and maybe others) use the column SEQ_NO.

In the following helper function I get an ArrayIndexOutOfBoundsException
since McKoi returns SEQ_NO zero based.

String[] getPrimaryKeyColumns(DatabaseMetaData dbMeta,
                               String schema,
                               String tabName) throws SQLException {
   String[] keyCols = new String[20];
   int keyCount = 0;
   ResultSet rset = dbMeta.getPrimaryKeys(null, schema, tabName);
   while (rset.next()) {
     String colName = rset.getString(4);
     int seq_no = rset.getInt(5);
     keyCols[seq_no - 1] = colName;
     keyCount = Math.max(keyCount, seq_no);
   }
   rset.close();
   return ArrayUtil.subArray(keyCols, 0, keyCount);
}


Most databases (Oracle, DB/2, MaxDB, Sybase, Informix, PostgeSQL
etc.) return column sequence numbers unit based.

In contrast McKoi returns column sequence numbers here zero based,
whereas the function getColumns returns them unit based.

I propose to move to unit based column sequence numbers for all
meta-data functions. This could be done with the following changes
to the McKoi 1.0.3 source code in file:

mckoi1.0.3\src\com\mckoi\database\Database.java

line 21
OLD: *
NEW: * 2004-11-03 dzoettl: helper views changed to return unit based 
column sequence numbers.

line 1281
OLD:"         \"SYS_INFO.sUSRPrimaryColumns.seq_no\" \"KEY_SEQ\",\n" +
NEW:"         \"SYS_INFO.sUSRPrimaryColumns.seq_no\" + 1 \"KEY_SEQ\",\n" +

line 1298
OLD:"         \"sUSRForeignColumns.seq_no\" \"KEY_SEQ\",\n" +
NEW:"         \"sUSRForeignColumns.seq_no\" + 1 \"KEY_SEQ\",\n" +

line 1319
OLD:"         \"sUSRForeignColumns.seq_no\" \"KEY_SEQ\",\n" +
NEW:"         \"sUSRForeignColumns.seq_no\" + 1 \"KEY_SEQ\",\n" +

line 1340
OLD:"         \"sUSRForeignColumns.seq_no\" \"KEY_SEQ\",\n" +
NEW:"         \"sUSRForeignColumns.seq_no\" + 1 \"KEY_SEQ\",\n" +


Conversion of an existing database can be done by executing the
following SQL statements:

   DROP VIEW SYS_JDBC.PrimaryKeys;
CREATE VIEW SYS_JDBC.PrimaryKeys AS
SELECT NULL "TABLE_CAT",
        "schema" "TABLE_SCHEM",
        "table" "TABLE_NAME",
        "column" "COLUMN_NAME",
        "SYS_INFO.sUSRPrimaryColumns.seq_no" + 1 "KEY_SEQ",
        "name" "PK_NAME"
   FROM SYS_INFO.sUSRPKeyInfo, SYS_INFO.sUSRPrimaryColumns
  WHERE sUSRPKeyInfo.id = sUSRPrimaryColumns.pk_id
    AND "schema" IN
           ( SELECT "name" FROM SYS_JDBC.ThisUserSchemaInfo );

   DROP VIEW SYS_JDBC.ImportedKeys;
CREATE VIEW SYS_JDBC.ImportedKeys AS
SELECT NULL "PKTABLE_CAT",
        "sUSRFKeyInfo.ref_schema" "PKTABLE_SCHEM",
        "sUSRFKeyInfo.ref_table" "PKTABLE_NAME",
        "sUSRForeignColumns.pcolumn" "PKCOLUMN_NAME",
        NULL "FKTABLE_CAT",
        "sUSRFKeyInfo.schema" "FKTABLE_SCHEM",
        "sUSRFKeyInfo.table" "FKTABLE_NAME",
        "sUSRForeignColumns.fcolumn" "FKCOLUMN_NAME",
        "sUSRForeignColumns.seq_no" + 1 "KEY_SEQ",
        I_FRULE_CONVERT("sUSRFKeyInfo.update_rule") "UPDATE_RULE",
        I_FRULE_CONVERT("sUSRFKeyInfo.delete_rule") "DELETE_RULE",
        "sUSRFKeyInfo.name" "FK_NAME",
        NULL "PK_NAME",
        "sUSRFKeyInfo.deferred" "DEFERRABILITY"
   FROM SYS_INFO.sUSRFKeyInfo, SYS_INFO.sUSRForeignColumns
  WHERE sUSRFKeyInfo.id = sUSRForeignColumns.fk_id
    AND "sUSRFKeyInfo.schema" IN
             ( SELECT "name" FROM SYS_JDBC.ThisUserSchemaInfo );

   DROP VIEW SYS_JDBC.ExportedKeys;
CREATE VIEW SYS_JDBC.ExportedKeys AS
SELECT NULL "PKTABLE_CAT",
        "sUSRFKeyInfo.ref_schema" "PKTABLE_SCHEM",
        "sUSRFKeyInfo.ref_table" "PKTABLE_NAME",
        "sUSRForeignColumns.pcolumn" "PKCOLUMN_NAME",
        NULL "FKTABLE_CAT",
        "sUSRFKeyInfo.schema" "FKTABLE_SCHEM",
        "sUSRFKeyInfo.table" "FKTABLE_NAME",
        "sUSRForeignColumns.fcolumn" "FKCOLUMN_NAME",
        "sUSRForeignColumns.seq_no" + 1 "KEY_SEQ",
        I_FRULE_CONVERT("sUSRFKeyInfo.update_rule") "UPDATE_RULE",
        I_FRULE_CONVERT("sUSRFKeyInfo.delete_rule") "DELETE_RULE",
        "sUSRFKeyInfo.name" "FK_NAME",
        NULL "PK_NAME",
        "sUSRFKeyInfo.deferred" "DEFERRABILITY"
   FROM SYS_INFO.sUSRFKeyInfo, SYS_INFO.sUSRForeignColumns
  WHERE sUSRFKeyInfo.id = sUSRForeignColumns.fk_id
    AND "sUSRFKeyInfo.schema" IN
             ( SELECT "name" FROM SYS_JDBC.ThisUserSchemaInfo );

   DROP VIEW SYS_JDBC.CrossReference;
CREATE VIEW SYS_JDBC.CrossReference AS
SELECT NULL "PKTABLE_CAT",
        "sUSRFKeyInfo.ref_schema" "PKTABLE_SCHEM",
        "sUSRFKeyInfo.ref_table" "PKTABLE_NAME",
        "sUSRForeignColumns.pcolumn" "PKCOLUMN_NAME",
        NULL "FKTABLE_CAT",
        "sUSRFKeyInfo.schema" "FKTABLE_SCHEM",
        "sUSRFKeyInfo.table" "FKTABLE_NAME",
        "sUSRForeignColumns.fcolumn" "FKCOLUMN_NAME",
        "sUSRForeignColumns.seq_no" + 1 "KEY_SEQ",
        I_FRULE_CONVERT("sUSRFKeyInfo.update_rule") "UPDATE_RULE",
        I_FRULE_CONVERT("sUSRFKeyInfo.delete_rule") "DELETE_RULE",
        "sUSRFKeyInfo.name" "FK_NAME",
        NULL "PK_NAME",
        "sUSRFKeyInfo.deferred" "DEFERRABILITY"
   FROM SYS_INFO.sUSRFKeyInfo, SYS_INFO.sUSRForeignColumns
  WHERE sUSRFKeyInfo.id = sUSRForeignColumns.fk_id
    AND "sUSRFKeyInfo.schema" IN
             ( SELECT "name" FROM SYS_JDBC.ThisUserSchemaInfo );

COMMIT;


Sincerely,
-- 
Eckenfelder GmbH & Co.KG
-Dirk Zöttl-

Am Röderweg 1
99819 Wenigenlupnitz
http://www.eckenfelder.de
mailto:[email protected]



---------------------------------------------------------------
Mckoi SQL Database mailing list  http://www.mckoi.com/database/
To unsubscribe, send a message to [email protected]