I've got a dbi application which is currently running happily on Oracle.
However, we have to port it to MySQL for some customers who don't have
Oracle and can't afford Oracle licenses.
I'm trying to do this is 2 stages. The first stage is to replace any
Oracle-specific SQL with generic SQL, so that it will still work OK with
Oracle, but have a fighting chance with MySQL too. In particular, inline
tables and subqueries come into this category. The second stage is
to provide alternative SQL statements to accommodate changes in
syntax (outer joins, date formatting functions, etc).
That's the background, here's the question:
I'm trying to avoid the use of Oracle's NVL function where possible, by
letting perl check for nulls after the fetch has been performed.
However, I've noticed that where the final column of the select clause
contains a null, the row is not returned, and in fact, no subsequent
rows are returned. The same query in sqlplus gives me all the rows I
need. So what am I doing wrong?
Here's an example snippet, which fails as soon as it encounters a row
with a null value for fax:
-------------
$sql = qq^select office_id, name, address, tel, fax from office
order by name^;
my ($office_id, $name, $address, $tel, $fax);
$sth = $dbh->prepare($sql);
$sth->execute();
$sth->bind_col( 1, \$office_id);
$sth->bind_col( 2, \$name);
$sth->bind_col( 3, \$address);
$sth->bind_col( 4, \$tel);
$sth->bind_col( 5, \$fax);
while ($sth->fetchrow()) {
# etc
}
--------------------
Thanks in advance for any pointers.
Maria
