apr-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bojan Smojver <bo...@rexursive.com>
Subject Re: DBD: Prepared statements, BLOBs etc.
Date Tue, 16 Jan 2007 00:54:12 GMT
On Mon, 2007-01-15 at 16:02 -0800, Chris Darroch wrote:

>    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;

Sorry, I kind of remembered you did, but I couldn't locate the thread.

>  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.

Aha, there is more to OCI than meets the eye. It sucks :-(

>    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.

OUCH! This is even worse - we'd need to know that we're actually doing
an INSERT and then act upon it. It *really* sucks! :-(

>    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.

Right. I think I'm starting to understand now.

>    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.

I agree. Parsing SQL should be kept to a minimum in APU - things can go
horribly wrong if we don't do it quite right.

I started playing with the driver already and since I already invented
the opaque apr_dbd_blob_t and accompanying functions, there is nothing
that needs doing once we decide to switch to LOB locators - the required
info will be there. And for the character mode, we can just keep
length:table:column:payload syntax (as discussed previously) and we
should be fine (which is also done).

>    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.)

I'll try to implement as much a possible (most likely with SQLT_LNG for
now, to keep code simple), but if I get stuck, the APR_ENOTIMPL may be
the only option.

> If memory serves, PostgreSQL also has some LOB weirdness to it.

It has a thingy called bytea, which is a binary string. If you whack it
in through a prepared statement, there is little that needs to be done
to it, apart from telling PostgreSQL that it's a binary and passing the
length in. If it's part of the literal SQL query, then it needs to be
escaped, but that's not something we should be worried about, as the
preferred way is to do prepared statements anyway.

> 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.

We should be able to work around at least some of that stuff. I'll just
keep the existing code for apr_dbd_blob_t and when I'm done modifying
the driver, I'll post the result, so that everyone can have a good
laugh :-).

Seriously, I just wasn't sure any more where the weirdness came from, so
I wanted to make sure. Thanks for your explanation and if I ask again,
feel free to flame me (although I'm bound to forget that quickly, given
my powers of remembering things :-).

>    As I'm not an APR committer, I have no vote in this,

That is a real shame.

Thanks for your time. Hopefully, I'll have some code for you to review
in the coming weeks.

-- 
Bojan


Mime
View raw message