#20143 [Opn]: dbh->limitQuery dies in oci8.php because of sql error

ID: 20143 User updated by: aprasad@nhgri.nih.gov Reported By: aprasad@nhgri.nih.gov Status: Open Bug Type: PEAR related Operating System: Solaris PHP Version: 4.2.3 New Comment: Again, I'm a php newbie. Looking at the PEAR documentation I could not figure out how to get PEAR::raiseError to print out an error message. I changed my dumb little error routine redmsg("Error: returned duplicate columns ($col_list). " ."Modify query to give each returned column a unique name"); to PEAR::raiseError( Previous Comments: ------------------------------------------------------------------------ [2002-11-01 08:49:21] mj@php.net Where is the function redmesg() defined? I guess you should use PEAR::raiseError() instead. ------------------------------------------------------------------------ [2002-10-28 20:04:10] aprasad@nhgri.nih.gov When running a limitQuery like: $query = $dbh->limitQuery('select t1.id, t2.id from t1,t2', 1, 2); echo $query->getDebugInfo(); You get the Warning: OCIStmtExecute: ORA-00918: column ambiguously defined in /usr/local/lib/php/DB/oci8.php on line 532 returns Object [nativecode=ORA-00900: invalid SQL statement] This appears to me to be because the sql query generated on line 528: $q_fields = "SELECT * FROM ($query) WHERE NULL = NULL"; (SELECT * FROM (select t1.id, t2.id from t1, t2) where NULL=NULL" ) Does not compile for Oracle. I'm not sure why you have to use a sub-query here and not just use the query passed in to get the column names. ie. $q_fields = $query; If you make it past this problem you later have the problem of trying to execute: $query = "SELECT $fields FROM". " (SELECT rownum as linenum, $fields FROM". " ($query)". " WHERE rownum <= ". ($from + $count) . ") WHERE linenum >= $from"; which will fail also because $fields will contain id, id I haven't done a lot of PHP or Oracle development before, and I couldn't think up a solution to this problem that doesn't involve lots of query parsing which probably should be avoided if possible. For now I've modified the code to return an error if there are column names returned from OCIColumName() that are the same. At the bottom of this message is the context diff for my very poorly written added warning which is not production ready. I needed something fast. It uses a redmsg() function that I've defined elsewhere because I have no idea how to do error handling. I've only been writing PHP for about two weeks so it is extremely ugly. (Whew, that was a lot of disclaimers) If someone comes up with a good fix I would be interested in seeing it. Thanks, Arjun Prasad *** oci8.php.orig Mon Oct 28 20:47:14 2002 --- oci8.php Mon Oct 28 20:56:27 2002 *************** *** 526,533 **** // Let Oracle return the name of the columns instead of // coding a "home" SQL parser ! $q_fields = "SELECT * FROM ($query) WHERE NULL = NULL"; ! if (!$result = OCIParse($this->connection, $q_fields)) { return $this->oci8RaiseError(); } if (!OCIExecute($result, OCI_DEFAULT)) { --- 526,536 ---- // Let Oracle return the name of the columns instead of // coding a "home" SQL parser ! // BUG FIX: modification of the query unnecessary here ! // -- Arjun Prasad <aprasad@nhgri.nih.gov> ! // $q_fields = "SELECT * FROM ($query) WHERE NULL = NULL"; ! // if (!$result = OCIParse($this->connection, $q_fields)) { ! if (!$result = OCIParse($this->connection, $query)) { return $this->oci8RaiseError(); } if (!OCIExecute($result, OCI_DEFAULT)) { *************** *** 538,543 **** --- 541,566 ---- for ( $i = 1; $i <= $ncols; $i++ ) { $cols[] = OCIColumnName($result, $i); } + // BUG alert: check to make sure no column names are the + // same if they are then report error. + $dup_cols = array(); + for ($i = 0; $i < $ncols; $i++) { + for ($n = $i + 1; $n < $ncols; $n++) { + if ($cols[$i] == $cols[$n]) { + $dup_cols[] = $cols[$i]; + } + } + } + if (sizeof($dup_cols) > 0) { + $col_list = implode(', ', $dup_cols); + redmsg("Error: returned duplicate columns ($col_list). " + ."Modify query to give each returned column a unique name."); + } + // now just let it fail. (This should be only a temporary solution) + // END BUG "alert" -- Arjun Prasad <aprasad@nhgri.nih.gov> + + + $fields = implode(', ', $cols); // XXX Test that (tip by John Lim) //if(preg_match('/^\s*SELECT\s+/is', $query, $match)) { ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=20143&edit=1