I've dropped perl6-language off the addressee list - this is pretty much
internals of DBI or DBD::WhatNot and not Perl language per se.
On 7/12/05, Sam Vilain <[EMAIL PROTECTED]> wrote:
>
> Dean Arnold wrote:
> > RE: LOBs and "SQL Parse Trees": having recently implemented
> > LOB support for a JDBC driver (and soon for a DBD), I can assure
> > you that SQL parse trees are unneeded to support them. For databases
>
> Great!
>
> Perhaps you can shed some light on how to do it for this, then.
>
> SQL command;
>
> INSERT INTO FOO (?, ?, ?, ?);
>
> Column 3 is a BYTEA column in Pg and needs special peppering to work.
>
> or this;
>
> SELECT
> *
> FROM
> FOO
> WHERE
> SOME_DATE_COLUMN > ?
>
> SOME_DATE_COLUMN is the database native date type. On Oracle you'll
> need to convert the ? to a 'TO_DATE(?)'.
DBD::Informix deals with both of these correctly in a variety of ways. The
DATE column is the easier - Informix Dynamic Server (IDS) is very good about
converting strings to DATE values - and to most other types. Also, since
Informix describes the types of the columns of the INSERT statement - and
can describe the input parameters of the SELECT statement (using DESCRIBE
INPUT) in the more recent versions of IDS - it can arrange the necessary
conversion.
The BYTEA example - corresponding to BYTE in IDS - is trickier. The string
you supply is converted into the relevant C structure - it happens to be a
loc_t in Informix ESQL/C - and then passed to the database. For INSERT, this
is easy because the types are described and the code in DBD::Informix can
tell that it needs to treat that properly. In other places, you have to use
the Informix type codes to convey the information to DBD::Informix. From
'perldoc DBD::Informix':
$upd = 'UPDATE SomeTable SET TextCol = ? WHERE Pkey = ?';
$sth = $dbh->prepare($upd);
$sth->bind_param(1, $blob_val, { ix_type => IX_TEXT });
$sth->bind_param(2, $pkey);
$sth->execute;
Internally, DBD::Informix knows that it must do the Perl string to Informix
loc_t mapping when this is specified.
Yes, it is a bit of work for the driver - but, for at least some drivers, it
is doable.
--
Jonathan Leffler <[EMAIL PROTECTED]> #include <disclaimer.h>
Guardian of DBD::Informix - v2005.01 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."
