apr-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Chris Darroch <chr...@pearsoncmg.com>
Subject LONGs and LOBs [Was: improved error reporting for apr_dbd_mysql.c]
Date Wed, 31 May 2006 17:00:21 GMT
Nick Kew wrote:

> The Oracle driver uses uppercase; %C for CLOB and %B for BLOB,
> %L for LOB (hey, I can't even remember which of these are working
> and which are placeholders!).  Is there any reason not to follow that 
> convention for other drivers implementing LOBs?

   The Oracle driver is in an interim state at the moment with respect
to CLOBs and BLOBs.  I use it in production with CLOBs, but it's not
as efficient as it could be.  I will get back to it someday!


   As output parameters, CLOBs work OK.  There's undoubtedly some
excess memory allocation, though.  LONGs should work too.

   BLOBs I haven't tested in a while; the principal problem is that
apr_dbd_get_entry() has no way to return a buffer length; since BLOBs
may contain byte values of zero, they shouldn't really be returned
as a null-terminated string.  That suggests to me the need for an API
change down the road.  Similar issues exist for passing BLOBs as
input parameters to p[v]query() too, of course.

   While we're on the subject, I believe there's also no easy way to
distinguish between an error return from apr_dbd_get_entry() and
the return of a valid NULL column value.  If we're changing the
get_entry() function at some point in the future, could we add
something like an int *null_flag argument that's set to 0 or 1?


   As for LONGs and LOBs as input parameters, things are a little
more sticky.  At the moment, %L is the thing that works.  When
p[v]query() runs, it uses OCIBindByPos() to bind the input string
to the prepared statement.  This call requires a length argument,
so strlen() is used, which isn't ideal for LONG/LOB values, which
one might reasonably expect to be large.

   IIRC, the "right" way to handle LOBs here is really painful.
You can't pass a CLOB value to an INSERT statement directly with
OCIBindByPos(), for instance, the way you can most other simple
input value types.

   You have to do the insert with an empty CLOB "placeholder", then
retrieve the ID for the newly inserted row, perform a "SELECT ...
FOR UPDATE" statement on that row ID, retrieve the OCILobLocator* for
the CLOB from the output parameters, and finally perform OCILobWrite() 
on that locator.  Oh, and of course you need to wrap all this in
a transaction in the case where the user hasn't already started one,
so that you don't wind up with just the newly inserted row but
no CLOB data in there if any error occurs along the way.

   Worse, there's no way to query a prepared OCI statement handle to
find out, say, what the table name in question is.  Perl's DBD::Oracle
performs a post_execute_lobs() function that runs after the main
OCIStmtExecute() call in dbd_st_execute() if the initial pass
over the input parameters determined that any were LOBs.  The
post_execute_lobs() function then calls init_lob_refetch() to
do a lot of magic -- basically, it does its own parsing of your
SQL statement to find the table name, then uses OCIDescribeAny()
to get the schema for that table and tries to match up your
original input parameters against the fields in the table.
Of course, there are situations where it can't do this; the
DBD::Oracle manual says:

> One further wrinkle: for inserts and updates of LOBs, DBD::Oracle has
> to be able to tell which parameters relate to which table fields.  In
> all cases where it can possibly work it out for itself, it does, how-
> ever, if there are multiple LOB fields of the same type in the table
> then you need to tell it which field each LOB param relates to:
> 
> $sth->bind_param($idx, $value, { ora_type=>ORA_CLOB, ora_field=>'foo' }

   Our %C and %B options correspond to Perl's ora_type in that they let
us know in advance if there are any LOBs among the input parameters.
However, even if we borrowed Perl's init_lob_refetch() magic parsing,
we'd still need some further kind of hint in the query string; something
like %C:colname to correspond to the ora_field hint.

   I should add: IANAL, so I don't know what's required in terms of
licenses and legal stuff if logic is re-implemented from DBD::Oracle
in APR.

   Once the %C and %B options are functional, they could be supported
in p[v]select() as well, but the utility of that is somewhat uncertain
to me; you can't usually use a LONG/LOB value in a WHERE clause.
It's possible some DBMS_LOB-type PL/SQL functions might allow it,
though; I'm not expert on those.


   So, a quick summary:

- get_entry() would need a buffer length return value to properly
  support BLOBs
- get_entry() could also use an argument to flag NULLs vs. errors
- get_entry() could do better with memory allocation and charset issues
  for LONGs/LOBs

- p[v]query() supports %L now for use with LONGs and LOBs
- p[v]query() could implement a lot of logic for "better" LOB support,
  in which case they'll use %C[:colname] and %B[:colname] options
- p[v]query() will also need a buffer length argument to support BLOBs fully
- p[v]select() could support %L, %C, and %B too, but I'm unsure if
  there's real utility in that

Chris.

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


Mime
View raw message