Home › Blogs › martinjevans's blog

Rush on DBD::ODBC issues today

2 June, 2009 - 18:46 — martinjevans

I frequent perl monks a bit and late yesterday an issue came in with DBD::ODBC and Microsoft Access. The perl monks node and rt 46597 detail it all quite well. It appears DBD::ODBC was not following the DBI specification (as it was originally written) to the letter with respect to sticky bound parameter types but since then Tim Bunce has modified the DBI pod slightly to allow DBDs to enable a bound parameter to be rebound with a different type. The change to DBD::ODBC was fairly small but by the time I'd added a test case, bumped the version to 1.21_1 and tested a dozen or so ODBC drivers this consumed a fair amount of time.

Just when I thought I was safe from DBD::ODBC issues for the day, another thread Prepare, SQL query with to_date call came up on dbi-users. Dates, times and timestamps in Oracle via ODBC have always been a bit of an issue and in fact there is a DBD::ODBC FAQ item titled Why do I get "Datetime field overflow" when attempting to insert a date into Oracle? about it. However, in this case the FAQ didn't seem to apply. At Easysoft, we wrote 2 Oracle ODBC drivers (one using OCI - Oracle Call Interface) and one a "wire-protocol" driver not requiring the OCI interface installation. When I tried the posters code with the wire-protocol driver it failed in a similar way. It turns out our driver (one of the few which handles SQLDescribeParam for Oracle) returns a type of SQL_TYPE_DATE for parameters bound to Oracle date columns. When you bind plain dates (without the usual ODBC syntax) DBD::ODBC says the C type is SQL_CHAR but the SQL type is SQL_TYPE_DATE (from the driver) which means the ODBC driver needs to parse the date and convert it. If you override the parameter type and set it to SQL_VARCHAR the driver says the c-type and sql-type are the same and passes it to Oracle untouched. The proper solution is to bind Oracle dates using the ODBC syntax for dates as all ODBC drivers need to support this. See the posts for more information but an example of some working and not working binds are illustrated here:

use DBI;
use strict;

# table is "create table martin (a date, b int)"

my $h = DBI->connect;

$h->do(q{alter session set nls_date_format='DD-MON-YY'});

my $s = $h->prepare(q{select * from v$nls_parameters where parameter ='NLS_DATE_FORMAT'});
$s->execute;
print DBI::dump_results($s);

my $date = '30-DEC-99';
my $dateodbc = qq/{ d '1999-12-30'}/;

# the following works ok - resulting in 2099-12-30 being inserted
$h->do(qq{insert into martin values ('$date', 1)});

# the following works resulting in 1999-12-30 being inserted
$h->do(qq{insert into martin values ($dateodbc, 2)});

# fails
eval {
    my $s = $h->prepare(q{insert into martin values(?,3)});
    $s->bind_param(1, $date);
    # fails
    # Numeric value out of range: invalid character in date/time string (SQL-22003)
    $s->execute;
};

# works resulting in 2099-12-30 being inserted
eval {
    my $s = $h->prepare(q{insert into martin values(?,4)});
    $s->bind_param(1, $date, DBI::SQL_VARCHAR);
    $s->execute;
};

# works resulting in 1999-12-30 being inserted
eval {
    my $s = $h->prepare(q{insert into martin values(?,5)});
    $s->bind_param(1, $dateodbc);
    $s->execute;
};

Trackback URL for this post:

http://www.martin-evans.me.uk/trackback/21