Jonathan Leffler wrote:
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.
What sort of "peppering" ? DBI provides SQL_BLOB, and SQL_CLOB
type descriptors (as well as SQL_BLOB_LOCATOR and SQL_CLOB_LOCATOR), so
presumably DBD::Pg (or any other DBD supporting LOBs) provides the
logic to map from
$sth->bind_param(3, $somelob, SQL_CLOB);
to whatever it needs to send on the wire. No different than, e.g.,
binding an integer or decimal(15,4). If some drivers don't support that,
thats a driver conformance issue, not a requirement for a new interface
mechanism.
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(?)'.
Er, why ? I haven't used DBD::Oracle lately, but assuming you
$sth->bind_param(1, '2005-07-13', SQL_DATE),
I'd assume DBD::Oracle would be smart enough to communicate that
to Oracle (either by munging the query text, or providing type codes
in the client request structure). I certainly handle that sort of
thing in DBD::Teradata, and I suspect DBD::ODBC would as well.
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.
Have you considered updating DBD::Informix to use the SQL_BLOB/CLOB type codes ?
Regards,
Dean Arnold
Presicient Corp.
