SQLForeignKey does not work

Hi,

I am running a PostgreSQL database (PostgreSQL 8.3.5 on i686-pc-linux-
gnu, compiled by GCC gcc (SUSE Linux) 4.3.2 [gcc-4_3-branch revision
141291]) and
trying to retrieve foreign keys for a table belonging to a query.

I know some time back this has been working.

But today I am reusing PostgreSQL instead of another database and it
seems not working any more. I have copied the SQL statement from the
log and tried it
in pgAdmin and I'll get a result of two rows - as expected, but I
didn't get the keys.

What's me wondering, is the log file reports reading at least the
first tuple having the data I have seen in pgAdmin.

What's wrong?

The code was running on Mac OS X 10.5.2 (PPC), but I'll have also no
luck on my Windows machines.
(On Windows my ANSI driver is 08.03.04.00 and the older one is
07.03.0200 - the one I carried with my distribution due to claiming of
working SQLForeignKeys)

Thanks

Lothar

This is my function I use for each select query to determine foreign
keys to optionally show a drop down field per foreign key used in the
selection:

void LB_STDCALL lbQuery::prepareFKList() {
    #define TAB_LEN 100
    #define COL_LEN 100

    unsigned char*   szTable = NULL;     /* Table to display   */

    UCHAR   szPkTable[TAB_LEN];  /* Primary key table name */
    UCHAR   szFkTable[TAB_LEN];  /* Foreign key table name */
    UCHAR   szPkCol[COL_LEN];  /* Primary key column   */
    UCHAR   szFkCol[COL_LEN];  /* Foreign key column   */

    SQLHSTMT         hstmt;

    SQLINTEGER      cbPkTable = TAB_LEN;
    SQLINTEGER     cbPkCol = TAB_LEN;
    SQLINTEGER    cbFkTable = TAB_LEN;
    SQLINTEGER    cbFkCol = TAB_LEN;
    SQLINTEGER    cbKeySeq = TAB_LEN;
    SQLSMALLINT      iKeySeq;
    SQLRETURN         retcode;

    retcode = SQLAllocStmt(hdbc, &hstmt); /* Statement handle */

    if (retcode != SQL_SUCCESS)
    {
            _dbError_DBC("SQLAllocStmt()", hdbc);
    }

    SQLBindCol(hstmt, 3, SQL_C_CHAR, szPkTable, TAB_LEN, &cbPkTable);
    SQLBindCol(hstmt, 4, SQL_C_CHAR, szPkCol, COL_LEN, &cbPkCol);
    SQLBindCol(hstmt, 5, SQL_C_CHAR, &iKeySeq, TAB_LEN, &cbKeySeq); //
SSHORT
    SQLBindCol(hstmt, 7, SQL_C_CHAR, szFkTable, TAB_LEN, &cbFkTable);
    SQLBindCol(hstmt, 8, SQL_C_CHAR, szFkCol, COL_LEN, &cbFkCol);

    char*  T;
    char* C;

// code to get the table of the first column (query is only about one
table)

    C = getColumnName(1);
    T = getTableName(C->charrep());

    szTable = T; // Copying and the like omitted in the code here

    retcode = SQLForeignKeys(hstmt,
                 NULL, 0,      /* Primary catalog   */
                 NULL, 0,      /* Primary schema   */
                 NULL, 0,      /* Primary table   */
                 NULL, 0,      /* Foreign catalog   */
                 NULL, 0,      /* Foreign schema   */
                 szTable, SQL_NTS); /* Foreign table   */


    while ((retcode == SQL_SUCCESS) || (retcode ==
SQL_SUCCESS_WITH_INFO)) {

       retcode = SQLFetch(hstmt);

       if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
          lbErrCodes err = ERR_NONE;

          printf("%s ( %s ) <-- %s ( %s )\n", szPkTable, szPkCol,
szFkTable, szFkCol);
       }
    }

    SQLFreeStmt(hstmt, SQL_DROP);

    free(szTable);
}

Here is the log the code produces for the above code snippet:

PGAPI_ForeignKeys: entering Foreign Key Case #2PGAPI_ExecDirect:
entering...
**** PGAPI_ExecDirect: hstmt=1100688, statement='SELECT    pt.tgargs,
pt.tgnargs,         pt.tgdeferrable,         pt.tginitdeferred,         pp1.proname,
pp2.proname,         pc.oid,         pc1.oid,         pc1.relname,         pn.nspname FROM
pg_catalog.pg_class pc,         pg_catalog.pg_proc pp1,         pg_catalog.pg_proc
pp2,         pg_catalog.pg_trigger pt1,         pg_catalog.pg_trigger pt2,
pg_catalog.pg_proc pp,         pg_catalog.pg_trigger pt,
pg_catalog.pg_class pc1,         pg_catalog.pg_namespace pn,
pg_catalog.pg_namespace pn1 WHERE    pt.tgrelid = pc.oid AND pp.oid =
pt.tgfoid AND pt1.tgconstrrelid = pc.oid AND pp1.oid = pt1.tgfoid AND
pt2.tgfoid = pp2.oid AND pt2.tgconstrrelid = pc.oid AND
((pc.relname='user_anwendungen') AND (pn1.oid = pc.relnamespace) AND
(pn1.nspname = 'public') AND (pp.proname LIKE '%ins') AND (pp1.proname
LIKE '%upd') AND (pp2.proname LIKE '%del') AND
(pt1.tgrelid=pt.tgconstrrelid) AND (pt1.tgconstrname=pt.tgconstrname)
AND (pt2.tgrelid=pt.tgconstrrelid) AND
(pt2.tgconstrname=pt.tgconstrname) AND (pt.tgconstrrelid=pc1.oid) AND
(pc1.relnamespace=pn.oid))'
PGAPI_ExecDirect: calling PGAPI_Execute...
PGAPI_Execute: entering...
PGAPI_Execute: clear errors...
recycle statement: self= 1100688
Exec_with_parameters_resolved: copying statement params:
trans_status=1, len=987, stmt='SELECT    pt.tgargs,         pt.tgnargs,
pt.tgdeferrable,         pt.tginitdeferred,         pp1.proname,         pp2.proname,
pc.oid,         pc1.oid,         pc1.relname,         pn.nspname FROM
pg_catalog.pg_class pc,         pg_catalog.pg_proc pp1,         pg_catalog.pg_proc
pp2,         pg_catalog.pg_trigger pt1,         pg_catalog.pg_trigger pt2,
pg_catalog.pg_proc pp,         pg_catalog.pg_trigger pt,
pg_catalog.pg_class pc1,         pg_catalog.pg_namespace pn,
pg_catalog.pg_namespace pn1 WHERE    pt.tgrelid = pc.oid AND pp.oid =
pt.tgfoid AND pt1.tgconstrrelid = pc.oid AND pp1.oid = pt1.tgfoid AND
pt2.tgfoid = pp2.oid AND pt2.tgconstrrelid = pc.oid AND
((pc.relname='user_anwendungen') AND (pn1.oid = pc.relnamespace) AND
(pn1.nspname = 'public') AND (pp.proname LIKE '%ins') AND (pp1.proname
LIKE '%upd') AND (pp2.proname LIKE '%del') AND
(pt1.tgrelid=pt.tgconstrrelid) AND (pt1.tgconstrname=pt.tgconstrname)
AND (pt2.tgrelid=pt.tgconstrrelid) AND
(pt2.tgconstrname=pt.tgconstrname) AND (pt.tgconstrrelid=pc1.oid) AND
(pc1.relnamespace=pn.oid))'
    stmt_with_params = 'SELECT    pt.tgargs,         pt.tgnargs,
pt.tgdeferrable,         pt.tginitdeferred,         pp1.proname,         pp2.proname,
pc.oid,         pc1.oid,         pc1.relname,         pn.nspname FROM
pg_catalog.pg_class pc,         pg_catalog.pg_proc pp1,         pg_catalog.pg_proc
pp2,         pg_catalog.pg_trigger pt1,         pg_catalog.pg_trigger pt2,
pg_catalog.pg_proc pp,         pg_catalog.pg_trigger pt,
pg_catalog.pg_class pc1,         pg_catalog.pg_namespace pn,
pg_catalog.pg_namespace pn1 WHERE    pt.tgrelid = pc.oid AND pp.oid =
pt.tgfoid AND pt1.tgconstrrelid = pc.oid AND pp1.oid = pt1.tgfoid AND
pt2.tgfoid = pp2.oid AND pt2.tgconstrrelid = pc.oid AND
((pc.relname='user_anwendungen') AND (pn1.oid = pc.relnamespace) AND
(pn1.nspname = 'public') AND (pp.proname LIKE '%ins') AND (pp1.proname
LIKE '%upd') AND (pp2.proname LIKE '%del') AND
(pt1.tgrelid=pt.tgconstrrelid) AND (pt1.tgconstrname=pt.tgconstrname)
AND (pt2.tgrelid=pt.tgconstrrelid) AND
(pt2.tgconstrname=pt.tgconstrname) AND (pt.tgconstrrelid=pc1.oid) AND
(pc1.relnamespace=pn.oid))'
        Sending SELECT statement on stmt=1100688,
cursor_name='SQL_CUR0x10cb90'
send_query(): conn=8447488, query='SELECT    pt.tgargs,         pt.tgnargs,
pt.tgdeferrable,         pt.tginitdeferred,         pp1.proname,         pp2.proname,
pc.oid,         pc1.oid,         pc1.relname,         pn.nspname FROM
pg_catalog.pg_class pc,         pg_catalog.pg_proc pp1,         pg_catalog.pg_proc
pp2,         pg_catalog.pg_trigger pt1,         pg_catalog.pg_trigger pt2,
pg_catalog.pg_proc pp,         pg_catalog.pg_trigger pt,
pg_catalog.pg_class pc1,         pg_catalog.pg_namespace pn,
pg_catalog.pg_namespace pn1 WHERE    pt.tgrelid = pc.oid AND pp.oid =
pt.tgfoid AND pt1.tgconstrrelid = pc.oid AND pp1.oid = pt1.tgfoid AND
pt2.tgfoid = pp2.oid AND pt2.tgconstrrelid = pc.oid AND
((pc.relname='user_anwendungen') AND (pn1.oid = pc.relnamespace) AND
(pn1.nspname = 'public') AND (pp.proname LIKE '%ins') AND (pp1.proname
LIKE '%upd') AND (pp2.proname LIKE '%del') AND
(pt1.tgrelid=pt.tgconstrrelid) AND (pt1.tgconstrname=pt.tgconstrname)
AND (pt2.tgrelid=pt.tgconstrrelid) AND
(pt2.tgconstrname=pt.tgconstrname) AND (pt.tgconstrrelid=pc1.oid) AND
(pc1.relnamespace=pn.oid))'
send_query: done sending query
in QR_Constructor
exit QR_Constructor
read 426, global_socket_buffersize=4096
send_query: got id = 'P'
send_query: got id = 'T'
QR_fetch_tuples: cursor = '', self->cursor=0
num_fields = 10
READING ATTTYPMOD
CI_read_fields: fieldname='tgargs', adtid=17, adtsize=-1, atttypmod=-1
READING ATTTYPMOD
CI_read_fields: fieldname='tgnargs', adtid=21, adtsize=2, atttypmod=-1
READING ATTTYPMOD
CI_read_fields: fieldname='tgdeferrable', adtid=16, adtsize=1,
atttypmod=-1
READING ATTTYPMOD
CI_read_fields: fieldname='tginitdeferred', adtid=16, adtsize=1,
atttypmod=-1
READING ATTTYPMOD
CI_read_fields: fieldname='proname', adtid=19, adtsize=64, atttypmod=-1
READING ATTTYPMOD
CI_read_fields: fieldname='proname', adtid=19, adtsize=64, atttypmod=-1
READING ATTTYPMOD
CI_read_fields: fieldname='oid', adtid=26, adtsize=4, atttypmod=-1
READING ATTTYPMOD
CI_read_fields: fieldname='oid', adtid=26, adtsize=4, atttypmod=-1
READING ATTTYPMOD
CI_read_fields: fieldname='relname', adtid=19, adtsize=64, atttypmod=-1
READING ATTTYPMOD
CI_read_fields: fieldname='nspname', adtid=19, adtsize=64, atttypmod=-1
QR_fetch_tuples: past CI_read_fields: num_fields = 10
MALLOC: tuple_size = 100, size = 8000
next_tuple: inTuples = true, falling through: fcount = 101,
fetch_count = 101
qresult: len=0, buffer=''
qresult: len=1, buffer='0'
qresult: len=1, buffer='f'
qresult: len=1, buffer='f'
qresult: len=20, buffer='RI_FKey_noaction_upd'
qresult: len=20, buffer='RI_FKey_noaction_del'
qresult: len=6, buffer='172708'
qresult: len=6, buffer='172714'
qresult: len=5, buffer='users'
qresult: len=6, buffer='public'
qresult: len=0, buffer=''
qresult: len=1, buffer='0'
qresult: len=1, buffer='f'
qresult: len=1, buffer='f'
qresult: len=20, buffer='RI_FKey_noaction_upd'
qresult: len=20, buffer='RI_FKey_noaction_del'
qresult: len=6, buffer='172708'
qresult: len=6, buffer='172594'
qresult: len=11, buffer='anwendungen'
qresult: len=6, buffer='public'
end of tuple list -- setting inUse to false: this = 1101040
_next_tuple: 'C' fetch_total = 2 & this_fetch = 2
send_query: got id = 'Z'
      done sending the query:
extend_column_bindings: entering ... self=1100768,
bindings_allocated=0, num_columns=10
exit extend_column_bindings
PGAPI_ExecDirect: returned 0 from PGAPI_Execute
PGAPI_BindCol: entering...
**** PGAPI_BindCol: stmt = 1100688, icol = 1
**** : fCType=-2 rgb=bfffbb7e valusMax=1024 pcb=0
        bound buffer[0] = 3221207934
PGAPI_BindCol: entering...
**** PGAPI_BindCol: stmt = 1100688, icol = 2
**** : fCType=5 rgb=bfffb8dc valusMax=0 pcb=0
        bound buffer[1] = 3221207260
PGAPI_BindCol: entering...
**** PGAPI_BindCol: stmt = 1100688, icol = 3
**** : fCType=1 rgb=bfffb8d8 valusMax=2 pcb=0
        bound buffer[2] = 3221207256
PGAPI_BindCol: entering...
**** PGAPI_BindCol: stmt = 1100688, icol = 4
**** : fCType=1 rgb=bfffb8da valusMax=2 pcb=0
        bound buffer[3] = 3221207258
PGAPI_BindCol: entering...
**** PGAPI_BindCol: stmt = 1100688, icol = 5
**** : fCType=1 rgb=bfffb938 valusMax=64 pcb=0
        bound buffer[4] = 3221207352
PGAPI_BindCol: entering...
**** PGAPI_BindCol: stmt = 1100688, icol = 6
**** : fCType=1 rgb=bfffb8f8 valusMax=64 pcb=0
        bound buffer[5] = 3221207288
PGAPI_BindCol: entering...
**** PGAPI_BindCol: stmt = 1100688, icol = 7
**** : fCType=-18 rgb=bfffb8f0 valusMax=4 pcb=0
        bound buffer[6] = 3221207280
PGAPI_BindCol: entering...
**** PGAPI_BindCol: stmt = 1100688, icol = 8
**** : fCType=-18 rgb=bfffb8f4 valusMax=4 pcb=0
        bound buffer[7] = 3221207284
PGAPI_BindCol: entering...
**** PGAPI_BindCol: stmt = 1100688, icol = 9
**** : fCType=1 rgb=bfffb978 valusMax=64 pcb=0
        bound buffer[8] = 3221207416
PGAPI_BindCol: entering...
**** PGAPI_BindCol: stmt = 1100688, icol = 10
**** : fCType=1 rgb=bfffb9fa valusMax=64 pcb=0
        bound buffer[9] = 3221207546
PGAPI_Fetch: stmt = 1100688, stmt->result= 1101040
manual_result = 0, use_declarefetch = 0
**** SC_fetch: manual_result
fetch: cols=10, lf=0, opts = 1100768, opts->bindings = 1101952,
buffer[] = 3221207934
type = 17
value = ''
copy_and_convert: field_type = 17, fctype = -2, value = '',
cbValueMax=1024
convert_from_pgbinary: in=0, out = 0
SQL_C_BINARY: len = 0, copy_len = 0
copy_and_convert: retval = 0
fetch: cols=10, lf=1, opts = 1100768, opts->bindings = 1101952,
buffer[] = 3221207260
type = 21
value = '0'
copy_and_convert: field_type = 21, fctype = 5, value = '0', cbValueMax=0
copy_and_convert: retval = 0
fetch: cols=10, lf=2, opts = 1100768, opts->bindings = 1101952,
buffer[] = 3221207256
type = 16
value = 'f'
copy_and_convert: field_type = 16, fctype = 1, value = 'f', cbValueMax=2
PG_TYPE_BOOL: rgbValueBindRow = '0'
copy_and_convert: retval = 0
fetch: cols=10, lf=3, opts = 1100768, opts->bindings = 1101952,
buffer[] = 3221207258
type = 16
value = 'f'
copy_and_convert: field_type = 16, fctype = 1, value = 'f', cbValueMax=2
PG_TYPE_BOOL: rgbValueBindRow = '0'
copy_and_convert: retval = 0
fetch: cols=10, lf=4, opts = 1100768, opts->bindings = 1101952,
buffer[] = 3221207352
type = 19
value = 'RI_FKey_noaction_upd'
copy_and_convert: field_type = 19, fctype = 1, value =
'RI_FKey_noaction_upd', cbValueMax=64
DEFAULT: len = 20, ptr = 'RI_FKey_noaction_upd'
     SQL_C_CHAR, default: len = 20, cbValueMax = 64, rgbValueBindRow =
'RI_FKey_noaction_upd'
copy_and_convert: retval = 0
fetch: cols=10, lf=5, opts = 1100768, opts->bindings = 1101952,
buffer[] = 3221207288
type = 19
value = 'RI_FKey_noaction_del'
copy_and_convert: field_type = 19, fctype = 1, value =
'RI_FKey_noaction_del', cbValueMax=64
DEFAULT: len = 20, ptr = 'RI_FKey_noaction_del'
     SQL_C_CHAR, default: len = 20, cbValueMax = 64, rgbValueBindRow =
'RI_FKey_noaction_del'
copy_and_convert: retval = 0
fetch: cols=10, lf=6, opts = 1100768, opts->bindings = 1101952,
buffer[] = 3221207280
type = 26
value = '172708'
copy_and_convert: field_type = 26, fctype = -18, value = '172708',
cbValueMax=4
copy_and_convert: retval = 0
fetch: cols=10, lf=7, opts = 1100768, opts->bindings = 1101952,
buffer[] = 3221207284
type = 26
value = '172714'
copy_and_convert: field_type = 26, fctype = -18, value = '172714',
cbValueMax=4
copy_and_convert: retval = 0
fetch: cols=10, lf=8, opts = 1100768, opts->bindings = 1101952,
buffer[] = 3221207416
type = 19
value = 'users'
copy_and_convert: field_type = 19, fctype = 1, value = 'users',
cbValueMax=64
DEFAULT: len = 5, ptr = 'users'
     SQL_C_CHAR, default: len = 5, cbValueMax = 64, rgbValueBindRow =
'users'
copy_and_convert: retval = 0
fetch: cols=10, lf=9, opts = 1100768, opts->bindings = 1101952,
buffer[] = 3221207546
type = 19
value = 'public'
copy_and_convert: field_type = 19, fctype = 1, value = 'public',
cbValueMax=64
DEFAULT: len = 6, ptr = 'public'
     SQL_C_CHAR, default: len = 6, cbValueMax = 64, rgbValueBindRow =
'public'
copy_and_convert: retval = 0



-- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de
Lothar Behrens
Heinrich-Scheufelen-Platz 2
73252 Lenningen