From: mirage at rateaprof dot com
Operating system: Redhat 8
PHP version: 5.0.0RC3
PHP Bug Type: ODBC related
Bug description: odbc_foreignkeys does not return imported keys
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 bug report at http://bugs.php.net/?id=29058&edit=1
--
Try a CVS snapshot (php4): http://bugs.php.net/fix.php?id=29058&r=trysnapshot4
Try a CVS snapshot (php5): http://bugs.php.net/fix.php?id=29058&r=trysnapshot5
Fixed in CVS: http://bugs.php.net/fix.php?id=29058&r=fixedcvs
Fixed in release: http://bugs.php.net/fix.php?id=29058&r=alreadyfixed
Need backtrace: http://bugs.php.net/fix.php?id=29058&r=needtrace
Need Reproduce Script: http://bugs.php.net/fix.php?id=29058&r=needscript
Try newer version: http://bugs.php.net/fix.php?id=29058&r=oldversion
Not developer issue: http://bugs.php.net/fix.php?id=29058&r=support
Expected behavior: http://bugs.php.net/fix.php?id=29058&r=notwrong
Not enough info: http://bugs.php.net/fix.php?id=29058&r=notenoughinfo
Submitted twice: http://bugs.php.net/fix.php?id=29058&r=submittedtwice
register_globals: http://bugs.php.net/fix.php?id=29058&r=globals
PHP 3 support discontinued: http://bugs.php.net/fix.php?id=29058&r=php3
Daylight Savings: http://bugs.php.net/fix.php?id=29058&r=dst
IIS Stability: http://bugs.php.net/fix.php?id=29058&r=isapi
Install GNU Sed: http://bugs.php.net/fix.php?id=29058&r=gnused
Floating point limitations: http://bugs.php.net/fix.php?id=29058&r=float
