Re: OCI8 bug in OCIDefineByName() ?

On Wed, Jun 14, 2000 at 06:03:32PM +0100, David Newcomb wrote: > > 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. the problem is that oracle names the columns! "select a.user_name from users a" gets me (all in sqlplus): USER_NAME -------------------------------- thieso to actually rename the column (if you have more that one user_name column in your select) you'd have to say: "select a.user_name as a_user_name from users a" gets me: A_USER_NAME -------------------------------- thieso so you need to use the "as" syntax to rename the columns in your result-set. regards, tc > > > 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. > > > > -- > PHP Development Mailing List <http://www.php.net/> > To unsubscribe, e-mail: php-dev-unsubscribe@lists.php.net > For additional commands, e-mail: php-dev-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net -- Thies C. Arntzen "One Big-Mac, Small Fries and a Coke!" Digital Collections Phone +49 40 235350 Fax +49 40 23535180 Hammerbrookstr. 93 20097 Hamburg / Germany