Search code examples
jdbcforeign-keyssap-ase

JDBC retrieve foreign keys columns name (sybase ase)

I'm trying to retrieve FK of a given table with JDBC metadata. For that, I'm using the "getImportedKeys" function.

For my table 'cash_mgt_strategy', it give in resultset:

PKTABLE_CAT : 'HAWK'
PKTABLE_SCHEM : 'dbo'
PKTABLE_NAME : 'fx_execution_strategy_policy'
PKCOLUMN_NAME : 'fx_execution_strategy_policy_id'
FKTABLE_CAT  : 'HAWK'
FKTABLE_SCHEM  : 'dbo'
FKTABLE_NAME  : 'cash_mgt_strategy'
FKCOLUMN_NAME  : 'fx_est_execution_strategy_policy'
KEY_SEQ  : '1'
UPDATE_RULE  : '1'
DELETE_RULE  : '1'
FK_NAME  : 'fk_fx_est_execution_strategy_policy'
PK_NAME  : 'cash_mgt_s_1283127861'
DEFERRABILITY  : '7'

The problem is that the "FKCOLUMN_NAME : 'fx_est_execution_strategy_policy'" is not a real column of my table, but it seems to be truncated? (missing "_id" at the end)

When using an official Sybase sql client (Sybase Workspace), displaying the DDL of the table give for this constraint / foreign key:

ALTER TABLE dbo.cash_mgt_strategy ADD CONSTRAINT fk_fx_est_execution_strategy_policy FOREIGN KEY (fx_est_execution_strategy_policy_id)
REFERENCES HAWK.dbo.fx_execution_strategy_policy (fx_execution_strategy_policy_id)

So I'm wondering how to retrieve the full FKCOLUMN_NAME ?

Note that I'm using jconnect 6.0. I've tested with jconnect 7.0, same problem.

Thanks

Solution

  • You haven't provided your ASE version so I'm going to assume the following:

    • dataserver was running ASE 12.x at some point (descriptor names limited to 30 characters)
    • dataserver was upgraded to ASE 15.x/16.x (descriptor names extended to 255 characters)
    • DBA failed to upgrade/update the sp_jdbc* procs after the upgrade to ASE 15.x/16.x (hence the old ASE 12.x version of the procs - descriptors limited to 30 characters - are still in use in the dataserver)

    If the above is true then sp_version should show the older versions of the jdbc procs running in the dataserver.

    The (obvious) solution would be to have the DBA load the latest version of the jdbc stored procs (typically found under ${SYBASE}/jConnect*/sp).

    NOTE: Probably wouldn't hurt to have the DBA review the output from sp_version to see if there are any other upgrade scripts that need to be loaded (eg, installmodel, installsecurity, installcommit, etc).