Re: bug in OCI8 functions (fwd)

On Fri, 13 Aug 1999, Rasmus Lerdorf wrote: > > > ---------- Forwarded message ---------- > Date: Thu, 12 Aug 1999 16:37:22 -0500 > From: patrick wagstrom <patrick@lecltd.com> > To: core@php.net > Subject: bug in OCI8 functions > > I've come accross a small bug in PHPs implementation of some of the OCI > functions. When a table is double aliased and a field it retrieved > twice from the same table, unpredictable results occur. Here is a > sample query that shows this: > > $l_strQuery = "SELECT a.contact_title,". > " a.create_date, b.first_name, b.last_name,". > " a.modify_date, c.first_name, c.last_name". > " FROM contact a, users b, users c". > " WHERE a.contact_id = 2". > " AND b.user_id = a.created_by_id". > " AND c.user_id = a.modified_by_id"; > > Which is a fairly straight forward query that would retrive a lot of > information from a table called contact and convert numeric codes for > created_by_id and modified_by_id to the first and last name of those > people. > > The problem lies with how oracle returns result and the fact that the > result must be stored in a PHP associative array. When the query is > performed at the sqlplus command line: hi, if you use ocifetchinto(...,OCI_ASSOC) you're *right* and you will only get back the value of last of the columns that share the same name. there are a number of workarounds that i can see: 1. use sql to alias the columns in the select: "select a.first_name as a_first_name, b.first_name as b_first_name" 2. don't use OCI_ASSOC in the ocifetchinto call. that way the resulting array will be index by column-position - column-name clashes won't harm that way. i'm sorry there's no better way right now. i might be able to add another flag to ocifetchinto which will create unique column-names from the query ("A.FIRST_NAME" instead of "FIRST_NAME") - but as the workarounds are quite good and handy i won't put that high on my list. regards, tc > > SQL> select a.contact_title, a.create_date, b.first_name, b.last_name, > a.modify_date, c.first_name, c.last_name > 2 from contact a, users b, users c > 3 where a.created_by_id = b.user_id > 4 and a.modified_by_id = c.user_id > 5 and a.contact_id = 2; > > CONTACT_TITLE CREATE_DA > FIRST_NAME > LAST_NAME MODIFY_DA > ------------------------------------------------ --------- > -------------------------------- > ------------------------------------------------ --------- > FIRST_NAME LAST_NAME > -------------------------------- > ------------------------------------------------ > Frederick Lowe 11-AUG-99 > SYSTEM > SYSTEM 11-AUG-99 > PATRICK WAGSTROM > > As you can see the column names FIRST_NAME and LAST_NAME appear twice. > And the result is that I can only get one of the FIRST_NAME, LAST_NAME > pairs, usually its the later one (although it seems to be somewhat > sparodic). > > I'm not entirely sure how to fix this, however I am looking into it, but > I've little experience with hacking the PHP source code and only a > moderate amount with OCI. > > I was wondering if there had been other reports of such a problem before > I devote a day to familiarizing myself with the problem and how to fix > it. > > Thanks. > 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