apr-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Chris Darroch <chr...@pearsoncmg.com>
Subject Re: DBD: Prepared statements, BLOBs etc.
Date Tue, 16 Jan 2007 00:02:05 GMT
Bojan Smojver wrote:

> I was actually wondering about the need to pass the name of the table
> and the name of column to p[b]query/select, which was the bit that
> wasn't clear to me. I just can't see the need for that, after my brief
> reading of OCI documentation. Which would then enable us to use
> apr_datum_t for passing binary BLOB/CLOB data into pbquery/select,
> instead of inventing yet another structure and a set of functions to
> deal with it (as it would be opaque).

   The best resource I've found for explicating the mysteries of OCI
is the Perl DBD::Oracle module:

http://search.cpan.org/dist/DBD-Oracle/Oracle.pm#Simple_Usage

   I quoted them once before; skim down to the paragraph that begins
"Worse, there's no way ...":

http://marc.theaimsgroup.com/?l=apr-dev&m=114909480018188&w=2

   In short, DBD::Oracle performs heroic feats to learn the table and
field names of a particular LOB, so it can retrieve the appropriate LOB
locator.  IIRC, this is because, when inserting into a table with LOB
columns, you have to insert a row with empty LOB placeholders.  Then you
have to retrieve back the locators for each LOB -- these don't exist
until you do the insert -- and write the final values into each LOB
using its locator.

   You can't retrieve back the locators without the table and field
name (or position), but of course these aren't provided in a neat package
by the user's SQL statement.  So, in order to make this whole process
appear like an atomic insert, DBD::Oracle parses your SQL to try to
determine the table name, and then looks for a LOB in that table.
If that works, it all seems like a single atomic insert to the user.

   But, if there are several LOBs, hints from the user are required,
because there's just no auto-magic way of knowing which LOB should get
which input data.

   My own sense was that APR would probably be better off without
the SQL-parsing magic of DBD::Oracle, since even at its best, it can't
handle every case.  I'd guess that it can also be fooled by especially
hairy SQL statements.  Hence, this whole notion of requiring a full
set of identifiers for LOBs, across the board.

   Or, it might be better for APR to just ignore Oracle and return,
in essence, APR_ENOTIMPL in some cases.  (Since drivers don't return
APR error codes from all functions, this might not be literally possible.)
If memory serves, PostgreSQL also has some LOB weirdness to it.
Maybe it would be better to offer LOB support only where the database
conforms to a "sane standard", whatever that is, and allow drivers to
offer support for non-standard weirdness via some other mechanism.

   As I'm not an APR committer, I have no vote in this, but I'm certainly
interested in opinions and in the outcome, as I'm a user of apr_dbd +
mod_dbd + Oracle + LOBs.  Thanks!

Chris.

-- 
GPG Key ID: 366A375B
GPG Key Fingerprint: 485E 5041 17E1 E2BB C263  E4DE C8E3 FA36 366A 375B


Mime
View raw message