Re: getPrimaryKeys

Dirk,

It's not clear from the JDBC specification that KEY_SEQ should not be 
zero based, but it does appear most other JDBC implementations start at 
1.  I will commit this change.

Thanks,
Toby.

Dirk Zöttl wrote:

> 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:



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