Learn how easy it is to sync an existing GitHub or Google Code repo to a SourceForge project! See Demo

Close

Problem with foreign keys metadata

Help
2009-11-19
2012-08-15
  • Mike Curwen
    Mike Curwen
    2009-11-19

    Hi,

    I'm using jTDS 1.2.4, Sybase 12.5.4

    In case it matters, sp_version reports:

    jConnect (TM) for JDBC(TM)/6.05(Build 26564)/P/EBF16903/JDK14/Sun May 31
    1:05:35 2009

    I believe that I'm running into bug 1471425 (). Are there any plans to address
    this bug?

    I have software (Atlassian Confluence) that checks its own database at
    startup. So to upgrade, I just start the new version of the app against the
    old database, and it will notice what's missing and issue DDL to bring the
    schema up to date.

    When I attempt an upgrade from version 2.9 to 2.9.3 I get several of these
    results:

    2009-11-19 13:51:20,092 ERROR execute Unsuccessful: alter table LINKS add
    constraint FK45157998DD41734 foreign key (CONTE

    NTID) references CONTENT

    2009-11-19 13:51:20,096 ERROR execute There is already an object named
    'FK45157998DD41734' in the database.

    Now as it happens, confluence up to 2.9.x will ignore these errors in schema
    updates. The upgrade "works" and you can use the upgraded confluence install.
    However, since Confluence 2.10.x, it Confluence no longer ignores these schema
    upgrade errors, so when attempting to upgrade from 2.9.3 to 2.10.4, the
    upgrade fails and it makes the instance un-usable.

    To see if I could get past this, I switched to the Jconnect driver, and those
    error messages went away, but curses, it has a different problem which the
    jTDS driver doesn't exhibit. And this different problem also causes the
    upgrade to fail. So I might as well switch back to jTDS and see if the foreign
    key problem can be fixed.

    During my own testing of this problem, I ran sp_fkeys null, null, null,
    'LINKS' and I did see the foreign key. But as indicated in the bug report, the
    returned data doesn't include the foreign key name.

    I also wrote the following code that uses the JDBC MetaData facility, and have
    enclosed the results of running it.

    import java.sql.*;

    public class TestJDBCDriver {

    public static void main(String args) {

    String drivers = {

    {"jTDS", "net.sourceforge.jtds.jdbc.Driver","jdbc:jtds:sybase://nlgdevdb8:5000
    /confluence"},

    {"JConn", "com.sybase.jdbc3.jdbc.SybDriver",
    "jdbc:sybase:Tds:nlgdevdb8:5000/confluence"}

    };

    try {

    for (int i = 0; i < drivers.length; i++) {

    String driverinfo = drivers_;

    System.out.println("========================");

    System.out.println(driverinfo);

    System.out.println("========================");

    Class.forName(driverinfo);

    Connection conn = DriverManager.getConnection(driverinfo, "mcurwen",
    "nlg123");

    conn.setAutoCommit(true);

    DatabaseMetaData meta = conn.getMetaData();

    // System.out.println("Sybase calls a catalog: " + meta.getCatalogTerm());

    ResultSet res = meta.getTables("confluence", null, "LINKS", null);

    while (res.next()) {

    String catalogName = res.getString("TABLE_CAT");

    String tableName = res.getString("TABLE_NAME");

    String tableType = res.getString("TABLE_TYPE");

    System.out.println(catalogName+"\t"+tableName+"\t"+tableType);

    }

    res = meta.getImportedKeys("confluence", null, "LINKS");

    while (res.next()) {

    System.out.println("PKTABLE_NAME: " + res.getString("PKTABLE_NAME"));

    System.out.println("PKCOLUMN_NAME: " + res.getString("PKCOLUMN_NAME"));

    System.out.println("FKTABLE_NAME: " + res.getString("FKTABLE_NAME"));

    System.out.println("FKCOLUMN_NAME: " + res.getString("FKCOLUMN_NAME"));

    System.out.println("FK_NAME: " + res.getString("FK_NAME"));

    System.out.println("PK_NAME: " + res.getString("PK_NAME"));

    }

    System.out.println("\n");

    conn.close();

    }

    } catch(Exception e) {

    e.printStackTrace();

    }

    }

    }

    Result:

    ========================

    jTDS

    ========================

    confluence LINKS TABLE

    PKTABLE_NAME: CONTENT

    PKCOLUMN_NAME: CONTENTID

    FKTABLE_NAME: LINKS

    FKCOLUMN_NAME: CONTENTID

    FK_NAME: null

    PK_NAME: null

    ========================

    JConn

    ========================

    confluence LINKS TABLE

    PKTABLE_NAME: CONTENT

    PKCOLUMN_NAME: CONTENTID

    FKTABLE_NAME: LINKS

    FKCOLUMN_NAME: CONTENTID

    FK_NAME: FK45157998DD41734

    PK_NAME: LINKS_17280061561

    As you can see, the JConn driver returns all data, but the jTDS one misses the
    object names. I think this must be what Hibernate/Confluence is using, and why
    it doesn't work. It would explain why jTDS/Confluence attempts to add foreign
    keys that are already there, and why JConn/Confluence doesn't.

    Somewhat strangely, when I use my Aqua Data Studio GUI (which uses jTDS to
    connect), it can show me the foreign keys in its GUI ,listed by name. I think
    it probably must abandon the JDBC-standard way (that is: it doesn't use the
    metadata) and issue queries directly against the sysobjects ?

    Again, is there any plan to address this bug? I am willing to test any code
    written for this bug.

    : https://sourceforge.net/tracker/index.php?func=detail&aid=1471425&group_id=
    33291&atid=407762

    _

     
  • momo
    momo
    2009-11-20

    Mike,

    I haven't looked into that issue, yet. But I'd agree bug seems to be the cause
    of the problem. I'll try to reproduce the problem and give you some feedback
    after a few tests. Maybe I'll be able to fix this over the weekend.

    Cheers,

    momo

     
  • Mike Curwen
    Mike Curwen
    2009-11-20

    Thanks momo,

    So I was looking at the jTDS code, just to see if I could figure out a way to
    fix the problem. I started thinking "oh, I can post-process this returned
    resultset, and iterate over it and lookup the missing data and add it".

    But, when I looked at the data that is returned by the proc, I now understand
    what Haris (the author of the 1471425 bug) was talking about.

    it is complete unusable, because more

    imported keys is perfect legal for

    same tables at all

    Right, it's completely legal (but stupid?) to do this:

    use confluence

    go

    create table foo (attachfoo numeric(19,0),ATTACHMENTID numeric(19,0))

    alter table foo add constraint cone foreign key (ATTACHMENTID) references
    ATTACHMENTS

    alter table foo add constraint ctwo foreign key (ATTACHMENTID) references
    ATTACHMENTS

    And then the sp_fkeys proc returns:

    sp_fkeys null, null, null,'foo'

    pktable_qualifier pktable_owner pktable_name pkcolumn_name fktable_qualifier
    fktable_owner fktable_name fkcolumn_name key_seq update_rule delete_rule


    confluence dbo ATTACHMENTS ATTACHMENTID confluence dbo foo ATTACHMENTID 1 1 1

    confluence dbo ATTACHMENTS ATTACHMENTID confluence dbo foo ATTACHMENTID 1 1 1

    So... first, there's not very good data to "backwards figure out" to which
    constraints they refer. And second, I can have two rows with exactly the same
    data. I suppose, since they're equal in all ways, it doesn't matter which of
    the foreign key names that I eventually find, are assigned to each row.

    But really, the SQL to find the constraint, from the data given by sp___fkeys
    ... you know what? I just stopped trying to figure it out. It's ridiculous
    that sp_fkeys just doesn't return the name of the foreign key. period.

    So, I broke open sp_fkeys, modified it to return the name, and the unmodified
    jTDS driver (as reported by my TestJDBCDriver class) now returns the expected
    foreignkey name.

    And best, Confluence doesn't try to add already-present foreign keys.

    However, I guess jTDS can't rely on everyone modifying their Sybase install,
    so I'd encourage you to still take a look, and my offer to test your code
    remains open.

    Thanks.

     
  • momo
    momo
    2009-11-22

    Thanks for your feedback! And great to hear you worked around the problem
    meanwhile. Unfortunately, I didn't have any time this weekend so maybe you can
    post your solution here for the time being so anyone running into this problem
    can benefit until I implemented a workaround in the driver itself.

    Cheers,

    momo

     
  • Mike Curwen
    Mike Curwen
    2009-11-24

    I suspect anyone here is capable of using a diff tool of some variety, so I'll
    just post my modified proc and trust you can figure out what I changed (it was
    actually pretty simple, once i figured out what the proc was doing).

    If anyone spots an error, please let me know!

    use confluence

    go

    if exists (select * from sysobjects where id = object_id('sp_fkeys') and type
    = 'P') drop procedure sp_fkeys

    go

    / sccsid = "%Z% generic/sproc/src/%M% %I% %G%" /

    /*

    note: there is one raiserror message: 18040

    messages for "sp_fkeys" 18039, 18040

    17461, "Object does not exist in this database."
    18040, "Catalog procedure %1! can not be run in a transaction.", sp_fkeys
    18043 " Primary key table name or foreign key table name or both must be
    given"
    18044, "%1! table qualifier must be name of current database."

    2009-11-20 MCURWEN Modified proc to return the foreign key name. This is
    required for jTDS drivers up to (at least) 1.2.4
    With this fix, Hibernate (and thus Confluence) can
    correctly identify existing foreign keys. Check
    https://sourceforge.net/projects/jtds/forums/forum/104389/topic/3464175
    for updates
    */
    CREATE PROCEDURE dbo.sp_fkeys

    @pktable_name varchar(32) = null,

    @pktable_owner varchar(32) = null,

    @pktable_qualifier varchar(32) = null,

    @fktable_name varchar(32) = null,

    @fktable_owner varchar(32) = null,

    @fktable_qualifier varchar(32) = null

    as

    declare @ftabid int, @ptabid int, @keycnt int, @i int

    declare @fokey1 int, @fokey2 int, @fokey3 int, @fokey4 int, @fokey5 int

    declare @fokey6 int, @fokey7 int, @fokey8 int, @fokey9 int, @fokey10 int

    declare @fokey11 int, @fokey12 int, @fokey13 int, @fokey14 int, @fokey15 int

    declare @refkey1 int, @refkey2 int, @refkey3 int, @refkey4 int, @refkey5 int

    declare @refkey6 int, @refkey7 int, @refkey8 int, @refkey9 int, @refkey10 int

    declare @refkey11 int, @refkey12 int, @refkey13 int, @refkey14 int

    declare @refkey15 int, @refkey16 int, @fokey16 int, @status int

    declare @msg varchar(1024)

    declare @msg2 varchar(1024)

    declare @ordpkey int

    declare @fkname varchar(32)

    set nocount on

    if (@@trancount = 0)

    begin

    set chained off

    end

    else

    begin

    / if inside a transaction /

    / catalog procedure sp_fkeys can not be run in a transaction./

    raiserror 18040, "sp_fkeys"

    return (1)

    end

    set transaction isolation level 1

    if (@pktable_name is null) and (@fktable_name is null)

    begin

    / If neither primary key nor foreign key table names given /

    /*

    18043 "Primary key table name or foreign key table name
    or both must be given"
    */
    raiserror 18043

    return (1)

    end

    if @fktable_qualifier is not null

    begin

    if db_name() != @fktable_qualifier

    begin

    / If qualifier doesn't match current database /

    /* 18044 "%1! Table qualifier must be name of current database"

    * 18050 "Foreign key"
    /
    exec sp_getmessage 18050, @msg2 output

    raiserror 18044, @msg2

    return (1)

    end

    end

    else

    begin

    /*

    Now make sure that foreign table qualifier is pointing to the
    current database in case it is not specified.
    */
    select @fktable_qualifier = db_name()

    end

    if @pktable_qualifier is not null

    begin

    if db_name() != @pktable_qualifier

    begin

    / If qualifier doesn't match current database /

    /* 18044 "%1! Table qualifier must be name of current database"

    * 18051 "Primary Key"
    /
    exec sp_getmessage 18051, @msg2 output

    raiserror 18044, @msg2

    return (1)

    end

    end

    else

    begin

    /*

    Now make sure that primary table qualifier is pointing to the
    current database in case it is not specified.
    */
    select @pktable_qualifier = db_name()

    end

    create table #pid (pid int, uid int, name varchar(30))

    create table #fid (fid int, uid int, name varchar(30))

    / we will sort by fkey /

    / unless pktable is null /

    select @ordpkey = 0

    if @pktable_name is not null

    begin

    if (@pktable_owner is null)

    begin

    /*

    owner is NULL, so default to the current user
    who owns this table, otherwise default to dbo
    * who owns this table.
    /
    insert into #pid

    select id, uid, name

    from sysobjects

    where name = @pktable_name and uid = user_id()

    and type in ('S', 'U')

    /*

    If the current user does not own the table, see
    if the DBO of the current database owns the table.
    */

    if ((select count(*) from #pid ) = 0)

    begin

    insert into #pid

    select id, uid, name

    from sysobjects

    where name = @pktable_name and uid = 1

    and type in ('S', 'U')

    end

    end

    else

    begin

    insert into #pid

    select id, uid, name

    from sysobjects

    where name = @pktable_name

    and uid = user_id(@pktable_owner)

    and type in ('S', 'U')

    end

    end

    else

    begin

    if (@pktable_owner is null)

    begin

    /*

    If neither pktable_name nor pktable_owner is specified,
    then we are interested in every user or system table.
    */
    insert into #pid

    select id, uid, name

    from sysobjects

    where type in ('S', 'U')

    end

    else

    begin

    insert into #pid

    select id, uid, name

    from sysobjects

    where uid = user_id(@pktable_owner)

    and type in ('S', 'U')

    end

    end

    if @fktable_name is not null

    begin

    / sort by pkey /

    select @ordpkey = 1

    if (@fktable_owner is null)

    begin

    /*

    owner is NULL, so default to the current user
    who owns this table, otherwise default to dbo
    * who owns this table.
    /
    insert into #fid

    select id, uid, name

    from sysobjects

    where name = @fktable_name and uid = user_id()

    and type in ('S', 'U')

    /*

    If the current user does not own the table, see
    if the DBO of the current database owns the table.
    */

    if ((select count(*) from #pid ) = 0)

    begin

    insert into #fid

    select id, uid, name

    from sysobjects

    where name = @fktable_name and uid = 1

    and type in ('S', 'U')

    end

    end

    else

    begin

    insert into #fid

    select id, uid, name

    from sysobjects

    where name = @fktable_name

    and uid = user_id(@fktable_owner)

    and type in ('S', 'U')

    end

    end

    else

    begin

    if (@fktable_owner is null)

    begin

    /*

    If neither fktable_name nor fktable_owner is specified,
    then we are interested in every user table or systme
    * table.
    /
    insert into #fid

    select id, uid, name

    from sysobjects

    where type in ('S', 'U')

    end

    else

    begin

    insert into #fid

    select id, uid, name

    from sysobjects

    where uid = user_id(@fktable_owner)

    and type in ('S', 'U')

    end

    end

    if (((select count(*) from #fid ) = 0) or

    ((select count(*) from #pid) = 0))

    begin

    / 17461, "Object does not exist in this database." /

    raiserror 17461

    return (1)

    end

    create table #fkey_res( pktable_qualifier varchar(32),

    pktable_owner varchar(32),

    pktable_name varchar(32),

    pkcolumn_name varchar(32),

    fktable_qualifier varchar(32),

    fktable_owner varchar(32),

    fktable_name varchar(32),

    fkcolumn_name varchar(32),

    key_seq smallint,

    update_rule smallint,

    delete_rule smallint,

    fk_name varchar(32)

    )

    create table #pkeys(seq int, keys varchar(30) null)

    create table #fkeys(seq int, keys varchar(30) null)

    /*

    Since there are possibly multiple rows in sysreferences
    that describe foreign and primary key relationships among
    two tables, so we declare a cursor on the selection from
    sysreferences and process the output at row by row basis.
    */

    declare curs_sysreferences cursor

    for

    select tableid, reftabid, keycnt,

    fokey1, fokey2, fokey3, fokey4, fokey5, fokey6, fokey7, fokey8,

    fokey9, fokey10, fokey11, fokey12, fokey13, fokey14, fokey15,

    fokey16, refkey1, refkey2, refkey3, refkey4, refkey5,

    refkey6, refkey7, refkey8, refkey9, refkey10, refkey11,

    refkey12, refkey13, refkey14, refkey15, refkey16, o.name

    from sysreferences, sysobjects o

    where constrid=o.id

    and tableid in (

    select fid from #fid)

    and reftabid in (

    select pid from #pid)

    and frgndbname is NULL and pmrydbname is NULL

    for read only

    open curs_sysreferences

    fetch curs_sysreferences into @ftabid, @ptabid, @keycnt,@fokey1,

    @fokey2, @fokey3, @fokey4, @fokey5, @fokey6, @fokey7, @fokey8,

    @fokey9, @fokey10, @fokey11, @fokey12, @fokey13, @fokey14, @fokey15,

    @fokey16, @refkey1, @refkey2, @refkey3, @refkey4, @refkey5, @refkey6,

    @refkey7, @refkey8, @refkey9, @refkey10, @refkey11, @refkey12,

    @refkey13, @refkey14, @refkey15, @refkey16, @fkname

    while (@@sqlstatus = 0)

    begin

    /*

    For each row of sysreferences which describes a foreign-
    primary key relationship, do the following.
    */

    /*

    First store the column names that belong to primary keys
    in table #pkeys for later retrieval.
    */

    delete #pkeys

    insert #pkeys values(1, col_name(@ptabid,@refkey1))

    insert #pkeys values(2, col_name(@ptabid,@refkey2))

    insert #pkeys values(3, col_name(@ptabid,@refkey3))

    insert #pkeys values(4, col_name(@ptabid,@refkey4))

    insert #pkeys values(5, col_name(@ptabid,@refkey5))

    insert #pkeys values(6, col_name(@ptabid,@refkey6))

    insert #pkeys values(7, col_name(@ptabid,@refkey7))

    insert #pkeys values(8, col_name(@ptabid,@refkey8))

    insert #pkeys values(9, col_name(@ptabid,@refkey9))

    insert #pkeys values(10, col_name(@ptabid,@refkey10))

    insert #pkeys values(11, col_name(@ptabid,@refkey11))

    insert #pkeys values(12, col_name(@ptabid,@refkey12))

    insert #pkeys values(13, col_name(@ptabid,@refkey13))

    insert #pkeys values(14, col_name(@ptabid,@refkey14))

    insert #pkeys values(15, col_name(@ptabid,@refkey15))

    insert #pkeys values(16, col_name(@ptabid,@refkey16))

    /*

    Second store the column names that belong to foreign keys
    in table #fkeys for later retrieval.
    */

    delete #fkeys

    insert #fkeys values(1, col_name(@ftabid,@fokey1))

    insert #fkeys values(2, col_name(@ftabid,@fokey2))

    insert #fkeys values(3, col_name(@ftabid,@fokey3))

    insert #fkeys values(4, col_name(@ftabid,@fokey4))

    insert #fkeys values(5, col_name(@ftabid,@fokey5))

    insert #fkeys values(6, col_name(@ftabid,@fokey6))

    insert #fkeys values(7, col_name(@ftabid,@fokey7))

    insert #fkeys values(8, col_name(@ftabid,@fokey8))

    insert #fkeys values(9, col_name(@ftabid,@fokey9))

    insert #fkeys values(10, col_name(@ftabid,@fokey10))

    insert #fkeys values(11, col_name(@ftabid,@fokey11))

    insert #fkeys values(12, col_name(@ftabid,@fokey12))

    insert #fkeys values(13, col_name(@ftabid,@fokey13))

    insert #fkeys values(14, col_name(@ftabid,@fokey14))

    insert #fkeys values(15, col_name(@ftabid,@fokey15))

    insert #fkeys values(16, col_name(@ftabid,@fokey16))

    /*

    For each column of the current foreign-primary key relation,
    create a row into result table: #fkey_res.
    */

    select @i = 1

    while (@i <= @keycnt)

    begin

    insert into #fkey_res

    select @pktable_qualifier,

    (select user_name(uid) from #pid where pid = @ptabid),

    object_name(@ptabid),

    (select keys from #pkeys where seq = @i),

    @fktable_qualifier,

    (select user_name(uid) from #fid where fid = @ftabid),

    object_name(@ftabid),

    (select keys from #fkeys where seq = @i),@i,

    1, 1, @fkname

    select @i = @i + 1

    end

    /*

    * Go to the next foreign-primary key relationship if any.
    /

    fetch curs_sysreferences into @ftabid, @ptabid, @keycnt,@fokey1,

    @fokey2, @fokey3, @fokey4, @fokey5, @fokey6, @fokey7, @fokey8,

    @fokey9, @fokey10, @fokey11, @fokey12, @fokey13, @fokey14, @fokey15,

    @fokey16, @refkey1, @refkey2, @refkey3, @refkey4, @refkey5, @refkey6,

    @refkey7, @refkey8, @refkey9, @refkey10, @refkey11, @refkey12,

    @refkey13, @refkey14, @refkey15, @refkey16, @fkname

    end

    close curs_sysreferences

    deallocate cursor curs_sysreferences

    /*

    Everything is now in the result table #fkey_res, so go ahead
    and select from the table now.
    */

    / if @ordpkey = 0 sort by fkey /

    / else sort by pkey /

    if @ordpkey = 0

    begin

    select pktable_qualifier, pktable_owner, pktable_name,

    pkcolumn_name, fktable_qualifier, fktable_owner,

    fktable_name, fkcolumn_name, key_seq, update_rule, delete_rule, fk_name

    from #fkey_res

    order by fktable_name,fktable_owner,key_seq, fktable_qualifier

    end

    else

    begin

    select pktable_qualifier, pktable_owner, pktable_name,

    pkcolumn_name, fktable_qualifier, fktable_owner,

    fktable_name, fkcolumn_name, key_seq, update_rule, delete_rule, fk_name

    from #fkey_res

    order by pktable_name,pktable_owner,key_seq, pktable_qualifier

    end

    GO

    sp_procxmode 'dbo.sp_fkeys', 'Anymode'

    GO