SQLForeignKey does not work
am 18.07.2009 14:58:22 von Lothar BehrensHi,
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
--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc