OCI8 bug in OCIDefineByName() ?
| From: | David Newcomb | Date: | Wed, 14 Jun 2000 17:03:32 +0000 |
| Subject: | OCI8 bug in OCIDefineByName() ? | ||
| Groups: | php.dev | ||
| Request: | Send a blank email to php-dev+get-21324@lists.php.net to get a copy of this message | ||
Hi all,
I've hit a possible bug in the OCIDefineByName() php_oci8 (oracle 8 module).
The "Column name" parameter dose not seem to accept values with a
dot (.) in them. This makes it very difficult to join a table on itself
using aliases.
He is an example:
Given the old classic of employees and managers!
create table djn_tmp (
WUS_USERID NUMBER(10) NOT NULL,
WUS_NAME VARCHAR2(30) NOT NULL,
WUS_BOSS NUMBER(10) NOT NULL );
insert into djn_tmp values (1, 'David', 1);
insert into djn_tmp values (2, 'Jonny', 1);
insert into djn_tmp values (3, 'Thomas', 2);
The idea being to display the userid, their name and their boss' name.
Here is some php code:
<HTML>
<BODY>
<?php
$DB_WDB = OCILogon("webdb", "webdb");
if ($DB_WDB == "")
{
echo "<BLINK>Can not connect to Web
database</BLINK><BR>\n";
die;
}
$sqlline = "SELECT A.WUS_USERID, A.WUS_NAME, B.WUS_NAME " .
"FROM DJN_TMP A, DJN_TMP B ".
"WHERE A.WUS_USERID = 2 " .
"AND A.WUS_BOSS = B.WUS_USERID";
$one = "please";
$two = "make it";
$three = "work";
echo "sqlline=$sqlline<BR>\n";
echo "<P>Before:<BR>\n";
echo "one=$one<BR>\n";
echo "two=$two<BR>\n";
echo "three=$three<BR>\n";
$sql = OCIParse($DB_WDB, $sqlline);
OCIDefineByName($sql,"A.WUS_USERID",&$one);
OCIDefineByName($sql,"A.WUS_NAME",&$two);
OCIDefineByName($sql,"B.WUS_NAME",&$three);
OCIExecute($sql);
OCIFetch($sql);
OCIFreeStatement($sql);
OCILogoff($DB_WDB);
echo "<P>After:<BR>\n";
echo "one=$one<BR>\n";
echo "two=$two<BR>\n";
echo "three=$three<BR>\n";
?>
</BODY>
</HTML>
Here are the results:
<HTML>
<BODY>
sqlline=SELECT A.WUS_USERID, A.WUS_NAME, B.WUS_NAME FROM DJN_TMP A, DJN_TMP
B WHERE A.WUS_USERID = 2 AND A.WUS_BOSS = B.WUS_USERID<BR>
<P>Before:<BR>
one=please<BR>
two=make it<BR>
three=work<BR>
<P>After:<BR>
one=please<BR>
two=make it<BR>
three=work<BR>
</BODY>
</HTML>
Here is a SQL statement run inside SQL*Plus:
WUS_USERID WUS_NAME WUS_NAME
---------- ------------------------------ ------------------------------
2 Jonny David
Help..... Don't know what to do.....????
Dose anyone know any work arounds or if their is a fix planned.
Or have I over looked something obvious.
The odd thing is that if you replace the OCIDefineByName() lines with:
OCIDefineByName($sql,A.WUS_USERID,&$one);
OCIDefineByName($sql,A.WUS_NAME,&$two);
OCIDefineByName($sql,B.WUS_NAME,&$three);
ie no quotes around the column name and it gives the same results..!!!
I don't understand help....
Thanks is advance,
David.