Skip site navigation (1) Skip section navigation (2)

SQLForeignKey does not work

From: Lothar Behrens <lothar(dot)behrens(at)lollisoft(dot)de>
To: pgsql-odbc(at)postgresql(dot)org
Subject: SQLForeignKey does not work
Date: 2009-07-18 12:58:22
Message-ID: 4CD14635-3EF2-40D7-98A0-010DC913B6EE@lollisoft.de (view raw or flat)
Thread:
Lists: pgsql-odbc
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









pgsql-odbc by date

Next:From: Mads LindstrømDate: 2009-07-18 16:56:38
Subject: Re: Any plans to support SQLDescribeParam (and friends) ?
Previous:From: Kulik, ScottDate: 2009-07-15 14:05:23
Subject: Re: help compiling psqldobc-08.03.0400

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group