Package home | Report new bug | New search | Development Roadmap Status: Open | Feedback | All | Closed Since Version 2.5.0b5

Bug #11975 Extended::autoExecute() does not work with Oracle when using LOB's
Submitted: 2007-09-04 15:15 UTC
From: pgm Assigned: quipo
Status: Closed Package: MDB2 (version 2.4.1)
PHP Version: 5.2.1 OS: XP PRO
Roadmaps: (Not assigned)    
Subscription  


 [2007-09-04 15:15 UTC] pgm (Pieter Meulen, van der)
Description: ------------ MDB2_Extended::autoExecute() calls MDB2_Extended::autoPrepare() which calls MDB2_Driver_xxx::prepare(). prepare() needs an array with column names that are LOB types in order to prepare a query for Oracle. Otherwise the following query is created (see test script): "INSERT INTO files (id, document, picture) VALUES (:0, EMPTY_CLOB(), EMPTY_BLOB()) RETURNING 1, 2 INTO :1, :2". Instead of: "INSERT INTO files (id, document, picture) VALUES (:0, EMPTY_CLOB(), EMPTY_BLOB()) RETURNING document, picture INTO :1, :2". The "1" and "2" after "RETURNING" are there because of the following code in MDB2_Driver_oci8::prepare() : " if (isset($types[$parameter]) && ($types[$parameter] == 'clob' || $types[$parameter] == 'blob') ) { if (!isset($lobs[$parameter])) { $lobs[$parameter] = $parameter; } ". I've fixed this by creating a lobs array in MDB2_Extended::autoPrepare() (see patch). This works with INSERT and UPDATE using autoExecute() with Oracle 10g and Postgresql 8.1. Test script: --------------- $dsn=array( 'phptype' => 'oci8', 'protocol' => 'tcp', 'hostspec'=> 'tsnname', 'database'=> 'DRIVER_TEST', 'username'=> 'DRIVER_TEST', 'password'=> 'password', 'charset'=> 'utf8' ); $options=array( 'ssl' => true, 'use_transactions'=>true, ); $db=&MDB2::connect($dsn, $options); $res=$db->loadModule('Extended'); $clob = str_repeat('0123456789', 1000); $blob = str_repeat("\00\01\02\03\04\05\06\07\08\09", 1000); $res=$db->extended->autoExecute( 'files', // Table name array('id'=>time(), 'document'=>$clob, 'picture'=>$blob), // Array (col => val) MDB2_AUTOQUERY_INSERT, // Query type to build null, // Where clause array('integer', 'clob', 'blob') // Types ); Expected result: ---------------- Success Actual result: -------------- _execute: [Error message: could not execute statement] [Last executed query: INSERT INTO files (id, document, picture) VALUES (:0, EMPTY_CLOB(), EMPTY_BLOB()) RETURNING 1, 2 INTO :1, :2] [Native code: 932] [Native message: ORA-00932: inconsistent datatypes: expected CLOB got NUMBER]

Comments

 [2007-09-19 20:54 UTC] quipo (Lorenzo Alberton)
This bug has been fixed in CVS. If this was a documentation problem, the fix will appear on pear.php.net by the end of next Sunday (CET). If this was a problem with the pear.php.net website, the change should be live shortly. Otherwise, the fix will appear in the package's next release. Thank you for the report and for helping us make PEAR better.
 [2008-03-17 01:31 UTC] louyuhang (David Lou)
I found if I use Patch version 2007-09-04 15:15 UTC for MDB2 Bug #11975, then when I use autoPrepare but set the parameter types null, will report error at foreach. It's looks like need a if condition before foreach. So I thought this patch maybe should change to: + $lobs=array(); + if ($types) + { + foreach ($types as $param => $type) { + if ( ($type == 'clob') || ($type == 'blob') ) + $lobs[$param] = $table_fields[$param]; + } + } + return $db->prepare($query, $types, $result_types, $lobs);