Oracle extension oddity

Today I was working on some sql for Oracle and connecting to the database with the PHP4 OCI extension. I’ve done this many times before, but today ran in to a little oddity that I thought was worth mentioning (mainly so I don’t forget and fall in to the trap again!)…

More often than not I’m pulling back fields and using their default name or in addition to the ‘as’ keyword to rename it, such as:

[sql]select
t1.name
t2.email_address as email
from
name_table t1,
email_table t2
where
t1.id = t2.user_id(+)[/sql]

And when the dataset is pulled back with OCIFetchStatement the field names are always in uppercase. I personally prefer lowercase so often use the array_change_key_case() function, but in this instance I wasn’t and just accessing the fields in uppercase, such as $results[‘EMAIL’].

Then I changed my sql to have a case statement which looked something like:

[sql]case
when
t2.email_address like ‘%domain.com’
or t2.email_address like ‘%anotherdomain.co.uk’
or t2.email_address like ‘%similardomain.net’
then
t2.email_address
else
”
end “email”[/sql]

And suddenly the email addresses weren’t showing on the site. I ran the sql, and it was bringing back the email, but for some reason they just weren’t displaying when they should.

Turns out that using the double-quote around email_address was forcing the Oracle extension to return the field name in exactly the same way I had requested it – all lower case. I changed that to “EMAIL” and all was right with the world again.

Something for me to bear in mind for the future.