Thread: BUG #4371: Foreign Key constraints not working with ODBC
The following bug has been logged online:
Bug reference: 4371
Logged by: Zahid Khan
Email address: khanzahid235@yahoo.com
PostgreSQL version: 8.3.3
Operating system: Linux
Description: Foreign Key constraints not working with ODBC
Details:
I am unable to get the foreign key information using SQLForeignKeys() API
function of psqlODBC driver. Same driver works with PG8.2 and returns
success on SQLFetch() with SQLForeignKeys() call. But in PG8.3 i am getting
NO_DATA_FOUND on SQLFetch() call.
Please find below sample code how i am using this.
cliRC = SQLForeignKeys(hstmt, NULL, 0, tbSchema, SQL_NTS, tbName, SQL_NTS,
NULL, 0, NULL, SQL_NTS, NULL, SQL_NTS);
/* bind column 6 to variable */
cliRC = SQLBindCol(hstmt, 6, SQL_C_CHAR, (SQLPOINTER)fkTableSch.val, 129,
&fkTableSch.ind);
/* bind column 7 to variable */
cliRC = SQLBindCol(hstmt, 7, SQL_C_CHAR, (SQLPOINTER)fkTableName.val, 129,
&fkTableName.ind);
/* bind column 8 to variable */
cliRC = SQLBindCol(hstmt, 8, SQL_C_CHAR, (SQLPOINTER)fkColumnName.val,
129, &fkColumnName.ind);
/* fetch each row and display */
WriteResultFile( "\n Fetch each row and display.\n");
/* fetch next row */
cliRC = SQLFetch(hstmt);
if (cliRC == SQL_NO_DATA_FOUND)
{
WriteResultFile( "\n Data not found.\n");
}
As a reference following are the quires which odbc driver is sending to
server for getting this information.
Query is [SELECT pt.tgargs, pt.tgnargs, pt.tgdeferrable,
pt.tginitdeferred, pp1.proname, pp2.proname, pc.oid,
pc1.oid, pc1.relname, pt.tgconstrname, pn1.nspname FROM
pg_catalog.pg_class pc, pg_catalog.pg_class pc1,
pg_catalog.pg_proc pp, pg_catalog.pg_proc pp1, pg_catalog.pg_proc
pp2, pg_catalog.pg_trigger pt, pg_catalog.pg_trigger pt1,
pg_catalog.pg_trigger pt2, pg_catalog.pg_namespace pn,
pg_catalog.pg_namespace pn1 WHERE pc.relname='DEPT321'AND pn.nspname =
'public' AND pc.relnamespace = pn.oid AND pt.tgconstrrelid =
pc.oid AND pp.oid = pt.tgfoid AND pp.proname Like '%ins' AND
pt1.tgconstrname = pt.tgconstrname AND pt1.tgconstrrelid = pt.tgrelid
AND pt1.tgrelid = pc.oid AND pc1.oid = pt.tgrelid AND pp1.oid
= pt1.tgfoid AND pp1.proname like '%upd' AND (pp1.proname not
like '%check%') AND pt2.tgconstrname = pt.tgconstrname AND
pt2.tgconstrrelid = pt.tgrelid AND pt2.tgrelid = pc.oid AND
pp2.oid = pt2.tgfoid AND pp2.proname Like '%del' AND pn1.oid =
pc1.relnamespace order by pt.tgconstrname]
***********************************************
Query is [select ta.attname, ia.attnum, ic.relname, n.nspname, tc.relname
from pg_catalog.pg_attribute ta, pg_catalog.pg_attribute ia,
pg_catalog.pg_class tc, pg_catalog.pg_index i, pg_catalog.pg_namespace n,
pg_catalog.pg_class ic where tc.relname = 'dept321' AND n.nspname = 'public'
AND tc.oid = i.indrelid AND n.oid = tc.relnamespace AND i.indisprimary = 't'
AND ia.attrelid = i.indexrelid AND ta.attrelid = i.indrelid AND ta.attnum =
i.indkey[ia.attnum-1] AND (NOT ta.attisdropped) AND (NOT ia.attisdropped)
AND ic.oid = i.indexrelid order by ia.attnum]
Hi. Ahh ...Probably, It will solve by the next release. http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/psqlodbc/psqlodbc/docs/release.html.diff?r1=1.37&r2=1.38 4.) Change the implemetatin of SQLForeignKeys() for 8.3+ servers.<br> It is still under adjustment with a check now. and a little patch are due to be added. If possible. please try CVS-HEAD. Regards, Hiroshi Saito ----- Original Message ----- From: "Zahid Khan" <khanzahid235@yahoo.com> > > The following bug has been logged online: > > Bug reference: 4371 > Logged by: Zahid Khan > Email address: khanzahid235@yahoo.com > PostgreSQL version: 8.3.3 > Operating system: Linux > Description: Foreign Key constraints not working with ODBC > Details: > > I am unable to get the foreign key information using SQLForeignKeys() API > function of psqlODBC driver. Same driver works with PG8.2 and returns > success on SQLFetch() with SQLForeignKeys() call. But in PG8.3 i am getting > NO_DATA_FOUND on SQLFetch() call. > > Please find below sample code how i am using this. > > cliRC = SQLForeignKeys(hstmt, NULL, 0, tbSchema, SQL_NTS, tbName, SQL_NTS, > NULL, 0, NULL, SQL_NTS, NULL, SQL_NTS); > > /* bind column 6 to variable */ > cliRC = SQLBindCol(hstmt, 6, SQL_C_CHAR, (SQLPOINTER)fkTableSch.val, 129, > &fkTableSch.ind); > > /* bind column 7 to variable */ > cliRC = SQLBindCol(hstmt, 7, SQL_C_CHAR, (SQLPOINTER)fkTableName.val, 129, > &fkTableName.ind); > > /* bind column 8 to variable */ > cliRC = SQLBindCol(hstmt, 8, SQL_C_CHAR, (SQLPOINTER)fkColumnName.val, > 129, &fkColumnName.ind); > > /* fetch each row and display */ > WriteResultFile( "\n Fetch each row and display.\n"); > > /* fetch next row */ > cliRC = SQLFetch(hstmt); > > if (cliRC == SQL_NO_DATA_FOUND) > { > WriteResultFile( "\n Data not found.\n"); > } > > > As a reference following are the quires which odbc driver is sending to > server for getting this information. > > Query is [SELECT pt.tgargs, pt.tgnargs, pt.tgdeferrable, > pt.tginitdeferred, pp1.proname, pp2.proname, pc.oid, > pc1.oid, pc1.relname, pt.tgconstrname, pn1.nspname FROM > pg_catalog.pg_class pc, pg_catalog.pg_class pc1, > pg_catalog.pg_proc pp, pg_catalog.pg_proc pp1, pg_catalog.pg_proc > pp2, pg_catalog.pg_trigger pt, pg_catalog.pg_trigger pt1, > pg_catalog.pg_trigger pt2, pg_catalog.pg_namespace pn, > pg_catalog.pg_namespace pn1 WHERE pc.relname='DEPT321'AND pn.nspname = > 'public' AND pc.relnamespace = pn.oid AND pt.tgconstrrelid = > pc.oid AND pp.oid = pt.tgfoid AND pp.proname Like '%ins' AND > pt1.tgconstrname = pt.tgconstrname AND pt1.tgconstrrelid = pt.tgrelid > AND pt1.tgrelid = pc.oid AND pc1.oid = pt.tgrelid AND pp1.oid > = pt1.tgfoid AND pp1.proname like '%upd' AND (pp1.proname not > like '%check%') AND pt2.tgconstrname = pt.tgconstrname AND > pt2.tgconstrrelid = pt.tgrelid AND pt2.tgrelid = pc.oid AND > pp2.oid = pt2.tgfoid AND pp2.proname Like '%del' AND pn1.oid = > pc1.relnamespace order by pt.tgconstrname] > > *********************************************** > Query is [select ta.attname, ia.attnum, ic.relname, n.nspname, tc.relname > from pg_catalog.pg_attribute ta, pg_catalog.pg_attribute ia, > pg_catalog.pg_class tc, pg_catalog.pg_index i, pg_catalog.pg_namespace n, > pg_catalog.pg_class ic where tc.relname = 'dept321' AND n.nspname = 'public' > AND tc.oid = i.indrelid AND n.oid = tc.relnamespace AND i.indisprimary = 't' > AND ia.attrelid = i.indexrelid AND ta.attrelid = i.indrelid AND ta.attnum = > i.indkey[ia.attnum-1] AND (NOT ta.attisdropped) AND (NOT ia.attisdropped) > AND ic.oid = i.indexrelid order by ia.attnum] > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs