Home » Database » MDB2 » Bug #10153

[2007-02-22 05:36 UTC] dowdybrown at yahoo dot com

Description:
------------
In oci8.php, I had to change the constraint query to read as follows in order to get tableInfo() to work correctly with Oracle XE

version info for this file:

// $Id: oci8.php,v 1.49 2006/11/03 12:52:20 lsmith Exp $

corrected query:

$query = 'SELECT a.constraint_type, c.column_name';
$query.= ' FROM all_constraints a, all_cons_columns c';
$query.= ' WHERE (a.table_name='.$db->quote($table, 'text').' OR a.table_name='.$db->quote(strtoupper($table), 'text').')';
$query.= ' AND (a.index_name='.$db->quote($index_name, 'text').' OR a.index_name='.$db->quote(strtoupper($index_name), 'text').')';
$query.= ' AND a.constraint_name = c.constraint_name';
$query.= ' AND a.owner = '.$db->quote(strtoupper($db->dsn['username']), 'text');
$result = $db->query($query);

Test script:
---------------
$db =& MDB2::connect($dsn);
echo "\n<p>connected";
if (PEAR::isError($db)) {
die('Could not create database connection. "'.$db->getMessage().'"');
}
$db->loadModule('Reverse');

echo "<pre>";
$info=$db->reverse->tableInfo('orders');
echo "\norders info:\n";
print_r($info);
echo "</pre>";

Expected result:
----------------
With the altered constraint query, I was able to get this result:

orders info:
Array
(
[0] => Array
(
[notnull] => 1
[nativetype] => NUMBER
[length] => 22
[default] =>
[type] => NUMBER
[mdb2type] => integer
[name] => orderid
[table] => orders
[flags] => not_null
)

...

Actual result:
--------------
orders info:
mdb2_error Object
(
[error_message_prefix] =>
[mode] => 1
[level] => 1024
[code] =>
[message] => MDB2 Error: Array
[userinfo] => _doquery: [Error message: Could not execute statement]
[Last executed query: SELECT all.constraint_type, cols.column_name FROM all_constraints AS all, all_cons_columns AS cols WHERE (all.table_name='orders' OR all.table_name='ORDERS') AND (all.index_name='orders_pk' OR all.index_name='ORDERS_PK') AND all.constraint_name = cols.constraint_name AND all.owner = 'NORTHWIND']
[Native code: 936]
[Native message: ORA-00936: missing expression
]