ID: 29058
User updated by: mirage at rateaprof dot com
-Summary: odbc_foreignkeys does not return imported keys
Reported By: mirage at rateaprof dot com
Status: Open
Bug Type: ODBC related
Operating System: Redhat 8
PHP Version: 5.0.0RC3
New Comment:
Okay this is a partial fix, I don't know how to finish it:
In php-src/ext/odbc/php_odbc.c
look in PHP_FUNCTION(odbc_foreignkeys)
There is a line:
#ifdef HAVE_DBMAKER
#define EMPTY_TO_NULL
...
#endif
IBM DB2 also needs this feature to set fields of "" in php to NULL in
the C-ODBC call. I know this because I did a
if (!strlen(pcat)) pcat=NULL;
and the same for ptable, pschema, fcat, fschema, ftable
and the problem is fixed.
I'm not exactly the person who really knows how best to make the ifdef
detect DB2 as well, but if one of you could make it essentially:
#ifdef (HAVE_DBMAKER || HAVE_DB2)
..
#endif
Then that would be awesome! And that folks will fix the problem in DB2
:)
Cheers!
Jeff
Previous Comments:
------------------------------------------------------------------------
[2004-07-08 21:23:22] mirage at rateaprof dot com
Sorry coppied wrong code block, but the first code snip should have
read (with nulls in the fk fields). BTW Problem still exists, it was
just my 'pasteo' in the bux report.
/* get the list of foreign key columns */
cliRC = SQLForeignKeys(hstmt,
NULL,
0,
tbSchema,
SQL_NTS,
tbName,
SQL_NTS,
NULL,
0,
NULL,
SQL_NTS,
NULL,
SQL_NTS);
------------------------------------------------------------------------
[2004-07-08 05:36:52] mirage at rateaprof dot com
Description:
------------
I am running IBM DB2 v8.1 fixpack 5
I am running PHP-5RC2/3 (same php_odbc.c file, no diffs)
If I call:
odbc_foreignkeys ($dbconn, "", "schema", "table", "", "", "");
I get all foreign keys for this table (i.e. I get a list of all other
tables that reference fields in schema.table).
--
In theory, I should be able to:
odbc_foreignkeys ($dbconn, "", "", "", "", "schema", "table")
And in return I get all schema2.table2.column2 names of other tables
that columns in schema.table reference. In otherwords, what keys does
this table reference?
Well it always returns nothing...
--
So is it me, or is it php_odbc.c? Well I went ahead and tested
/opt/IBM/db2/V8.1/samples/cli/tbconstr.c and reversed the arguments for
SQLForeignKeys from ExportedKeys query:
/* get the list of foreign key columns */
cliRC = SQLForeignKeys(hstmt,
NULL,
0,
tbSchema,
SQL_NTS,
tbName,
SQL_NTS,
NULL,
0,
tbSchema,
SQL_NTS,
tbName,
SQL_NTS);
So it would become the following to query ImportedKeys
/* get the list of foreign key columns */
cliRC = SQLForeignKeys(hstmt,
NULL,
0,
NULL,
SQL_NTS,
NULL,
SQL_NTS,
NULL,
0,
tbSchema,
SQL_NTS,
tbName,
SQL_NTS);
Yup it works in the pure C+odbc version... it just doesn't work in php,
so somewhere between the php-odbc_foreignkeys(,...) call and the
odbc_php.c, we lose the return values... I can't tell where since I
don't know enough to debug php... I'd be more than happy to help, but I
don't know how.
Can someone see if perhaps return values are being thrown away? Or
arguments to the php call are lost?
--
I did check that the proper dynamic library was being used by running
ldd libphp5.so, and yes it does link to the current v8.1 library...
Reproduce code:
---------------
So I get all the tables with odbc_tables($dbconn, "", "schema", "%",
"");
Then to get Exported Keys which works:
$cols = odbc_foreignkeys($dbconn,
odbc_result($tables,1),odbc_result($tables,2), odbc_result($tables,3),
"", "", "") or die(odbc_errormsg());
Then to get Imported Keys which does not work:
$cols = odbc_foreignkeys($dbconn, "", "",
""odbc_result($tables,1),odbc_result($tables,2),
odbc_result($tables,3)) or die(odbc_errormsg());
Expected result:
----------------
If jeff.a (a) is a primary key, and jeff.b(d) references jeff.a(a):
jeff.a (a) is exported to jeff.b (d) - Yes this works
jeff.b (d) imports jeff.a (a) - this does not return from the
odbc_foreign keys call for imported keys
Actual result:
--------------
JEFF A TABLE
TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME DATA_TYPE
TYPE_NAME COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS NUM_PREC_RADIX
NULLABLE REMARKS COLUMN_DEF SQL_DATA_TYPE SQL_DATETIME_SUB
CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE
NULL JEFF A A 4 INTEGER 10 4 0 10 0
NULL NULL 4 NULL NULL 1 NO
NULL JEFF A B 4 INTEGER 10 4 0 10 1
NULL NULL 4 NULL NULL 2 YES
function result = 2
TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME KEY_SEQ PK_NAME
NULL JEFF A A 1 SQL040707170936160
function result = 1
Exported Keys
.JEFF.A
PKTABLE_CAT PKTABLE_SCHEM PKTABLE_NAME PKCOLUMN_NAME FKTABLE_CAT
FKTABLE_SCHEM FKTABLE_NAME FKCOLUMN_NAME KEY_SEQ UPDATE_RULE DELETE_RULE
FK_NAME PK_NAME DEFERRABILITY
NULL JEFF A A NULL JEFF B D 1 3 3
SQL040707170938220 SQL040707170936160 7
NULL JEFF A A NULL JEFF C AA 1 3 3
SQL040707171829700 SQL040707170936160 7
function result = 2
Imported Keys
.JEFF.A
No rows found
function result = 0
JEFF B TABLE
TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME DATA_TYPE
TYPE_NAME COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS NUM_PREC_RADIX
NULLABLE REMARKS COLUMN_DEF SQL_DATA_TYPE SQL_DATETIME_SUB
CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE
NULL JEFF B C 4 INTEGER 10 4 0 10 1
NULL NULL 4 NULL NULL 1 YES
NULL JEFF B D 4 INTEGER 10 4 0 10 1
NULL NULL 4 NULL NULL 2 YES
NULL JEFF B E 4 INTEGER 10 4 0 10 1
NULL NULL 4 NULL NULL 3 YES
function result = 3
No rows found
function result = 0
Exported Keys
.JEFF.B
No rows found
function result = 0
Imported Keys
.JEFF.B
No rows found
function result = 0
------------------------------------------------------------------------
--
Edit this bug report at http://bugs.php.net/?id=29058&edit=1
