byterock

In part 15 we had a look at a look at some extra XML goodies that DBD::Oracle give you. Now we are going to see how the statement handle can save you arse.

LOGID, LogID, LOgid

Egad those really are three table names I once encountered in a Oracle DB and I am not even going to show you all the fields in some of these three that where upper, lower and even mixed case.

Well how can DBI statement handle help?? Well you can at least get everything out of a table in the same case. So lets take this SQL for example

Select "First_Name", "last_NAME" from USER_NAMES

Don't laugh I actually had that above table. You could of course just deal with it as you go along but the DBI $sth has the handy 'NAME_lc_hash' attribute which can does the dirty work for you.

So on the select from this table you could do something like this

$sql = 'Select "First_NAME", "last_NAME" from USER_NAMES
$sth = $dbh->prepare($sql);
$sth->execute();
my ($row) = $sth->fetchrow();

print "First Name: $row->[ $sth->{NAME_lc_hash}{first_name} ]\n";
print "Last Name: $row->[ $sth->{NAME_lc_hash}{last_name} ]\n";

and there you have it. No need for you to use goofy mixed case in you key names;
You could of course thy this as well

print "First Name: $row->[ $sth->{NAME_uc_hash}{FIRST_NAME} ]\n";
print "Last Name: $row->[ $sth->{NAME_uc_hash}{LAST_NAME} ]\n";

and if you are so inclined

print "First Name: $row->[ $sth->{NAME_hash}{First_NAME} ]\n"; print "Last Name: $row->[ $sth->{NAME_hash}{last_NAME} ]\n";

The first two little beauties come in very handy when you are trying to write portable perl code.

Yet Another Path

If you really don't like this more low level approach you can use the 'FetchHashKeyName' on the Database Handle to set everything one way before you get it.

You have of course the option of either 'NAME_lc', lower case or 'NAME_uc' upper case, your could also set it to 'NAME' but that is just the default value.

So our above little bit of code can be changed like this

$dbh->FetchHashKeyName = 'NAME_lc'; $sth = $dbh->prepare($sql); $sth->execute(); my ($row) = $sth->fetchrow_hashref();

print "First Name: $row->{first_name} \n";
print "Last Name: $row->{last_name} \n";

Note that the 'FetchHashKeyName ' value is sticky it will hang around with the $dbh until you change it. As well it is a good idea to set this value before you prepare the statement.

This one of the really useful attributes that a make writing portable code a little less of a hassle.