You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@apr.apache.org by Bojan Smojver <bo...@rexursive.com> on 2007/01/15 03:06:19 UTC
Re: DBD: Prepared statements, BLOBs etc.
Quoting Chris Darroch <ch...@pearsoncmg.com>:
> 2) Alas, for Oracle, there should probably also be the types
> APR_DBD_TYPE_CLOB
I was silly enough to download Oracle XE and start playing with the
Oracle driver...
Which brings me back to the once mentioned Oracle weirdness, where the
name of the table and the name of the column are supposedly required
in order to bind a BLOB/CLOB as INPUT parameters (i.e. to
p[b]query/select). From what I can see in the docs (Oracle Call
Interface Programmer's Guide), simple things like this are allowed in
order to bind LOBs:
----------------------------------------------------------
CREATE TABLE foo (a INTEGER );
CREATE TYPE lob_typ AS OBJECT (A1 CLOB );
CREATE TABLE lob_long_tab (C1 CLOB, C2 CLOB, CT3 lob_typ, L LONG);
void insert() /* A function in an OCI program */
{
/* The following is allowed */
ub1 buffer[8000];
text *insert_sql = (text *) "INSERT INTO lob_long_tab (C1, C2, L) \
VALUES (:1, :2, :3)";
OCIStmtPrepare(stmthp, errhp, insert_sql, strlen((char*)insert_sql),
(ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (dvoid *)buffer, 8000,
SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
OCIBindByPos(stmthp, &bindhp[1], errhp, 2, (dvoid *)buffer, 8000,
SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
OCIBindByPos(stmthp, &bindhp[2], errhp, 3, (dvoid *)buffer, 2000,
SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
OCIStmtExecute(svchp, stmthp, errhp, 1, 0, (OCISnapshot *) NULL,
(OCISnapshot *) NULL, OCI_DEFAULT);
}
----------------------------------------------------------
So, one doesn't even need the LOB descriptor or anything, let alone
the name of the table or column. Did I miss something important here?
It looks pretty straightforward to me...
If you remember, that was the reason for creating patches that would
encode (in CHARACTER (i.e. existing) mode) BLOB/CLOB values as:
length:table:column:payload
From what I can see, we could get away with just length:payload, just
like we can with all other databases we support.
In binary mode, we wouldn't have to specify table/column at all.
Again, size and payload should be just fine. This would enable us to
use some existing structures for all this, instead of having to invent
apr_dbd_blob_t.
Anyone familiar with OCI, please speak up. Chris?
--
Bojan
Re: DBD: Prepared statements, BLOBs etc.
Posted by Bojan Smojver <bo...@rexursive.com>.
On Thu, 2007-01-18 at 14:17 +1100, Geoffrey Giesemann wrote:
> Can you try:
>
> INSERT INTO Print_media(Product_id, Ad_id, Ad_sourcetext VALUES
> (2004, 1, TO_LOB(:SRCTXT))
> ^^^^^^
> instead?
Unfortunately, changing SQL wasn't an option, as this is something users
carve by hand and give to APU DBD. The goal is that it should "just
work", for most common cases.
> This suggests that the implicit data type for :SRCTXT is a LONG (for
> some reason). Is there a more specific bind type you could use? (other
> than SQLT_CHR)
The fix was actually to use types depending on the nature of LOB. For
CLOBs, either SQLT_CHR or SQLT_LNG worked fine. But for BLOBs, one needs
to use SQLT_LBI (or maybe something else I'm yet unaware of). And since
my test table had a mix of both and Oracle wouldn't tell me on which
column the problem actually was, plus the fact that I didn't find the
SQLT_LBI mentioned anywhere in the docs, it took a while to figure
things out.
Thanks for your help,
--
Bojan
Re: DBD: Prepared statements, BLOBs etc.
Posted by Geoffrey Giesemann <ge...@cs.rmit.edu.au>.
On Thu, Jan 18, 2007 at 01:37:49PM +1100, Bojan Smojver wrote:
>
> Well, I'm facing some major grief regarding this. I found another
> example, which would suggest more or less the same thing here, but using
> SQLT_CHR type:
>
> http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14249/adlob_data_interface.htm
>
> The text and code is:
>
> ----------------------------------
> Binding LONG Data for LOB Columns in Binds Greater Than 4000 Bytes
>
> The following example illustrates binding character data for a LOB
> column:
>
> void simple_insert()
> {
> word buflen;
> text buf[5000];
> text *insstmt = (text *) "INSERT INTO Print_media(Product_id, Ad_id,\
> Ad_sourcetext) VALUES (2004, 1, :SRCTXT)";
>
> OCIStmtPrepare(stmthp, errhp, insstmt, (ub4)strlen((char *)insstmt),
> (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
>
> OCIBindByName(stmthp, &bndhp[0], errhp,
> (text *) ":SRCTXT", (sb4) strlen((char *) ":SRCTXT"),
> (dvoid *) buf, (sb4) sizeof(buf), SQLT_CHR,
> (dvoid *) 0, (ub2 *) 0, (ub2 *) 0,
> (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT);
>
> memset((void *)buf, (int)'A', (size_t)5000);
> OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
> (const OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT);
> }
> ----------------------------------
>
(This is more of an Oracle query than an APR query, but here goes)
What's the actual type of the Ad_sourcetext column?
Can you try:
INSERT INTO Print_media(Product_id, Ad_id, Ad_sourcetext VALUES
(2004, 1, TO_LOB(:SRCTXT))
^^^^^^
instead?
>
> ----------------------------------
> Execute error -1: ORA-01461: can bind a LONG value only for insert into
> a LONG column
> ----------------------------------
>
>
> Values up to and including 4000 bytes work fine (minus the character
> conversion bit, which I still need to figure out). The error appears
> with 4001+ bytes.
>
This suggests that the implicit data type for :SRCTXT is a LONG (for
some reason). Is there a more specific bind type you could use? (other
than SQLT_CHR)
(I will disclaim that I've never done any C-based OCI programming
before, but I do know my way around Oracle)
--Geoff
Re: DBD: Prepared statements, BLOBs etc.
Posted by Bojan Smojver <bo...@rexursive.com>.
On Thu, 2007-01-18 at 13:37 +1100, Bojan Smojver wrote:
> Well, I'm facing some major grief regarding this.
Solved. BLOBs need to be bound as SQLT_LBI and CLOBs as SQLT_LNG or
SQLT_CHR.
--
Bojan
Re: DBD: Prepared statements, BLOBs etc.
Posted by Bojan Smojver <bo...@rexursive.com>.
Quoting Chris Darroch <ch...@pearsoncmg.com>:
> At any rate, it would be nice if OCI_NEW_LENGTH_SEMANTICS actually
> worked as promised. :-)
He, he... I see you also have a lot of confidence in Oracle docs. :-)
--
Bojan
Re: DBD: Prepared statements, BLOBs etc.
Posted by Chris Darroch <ch...@pearsoncmg.com>.
Bojan Smojver wrote:
> BTW, do you think OCI_NEW_LENGTH_SEMANTICS would be worth adding to
> OCIEnvCreate() call? Apparently (according to the documentation :-), it
> does this:
>
> OCI_NEW_LENGTH_SEMANTICS - byte-length semantics is used consistently
> for all handles, regardless of character sets.
>
> Hopefully, this would then make sure OCILobRead() returns proper number
> of bytes read.
Might do, indeed, but it appears to only be available starting with 9i.
Maybe that's sufficient for APR-util; 8i isn't supported anymore by
Oracle, IIRC.
(I say "might do" because I've tried using OCI_SHARED with OCIEnvCreate()
and discovered that it merely triggered a fascinating bug, well-documented
by other people in various bug reports to Oracle, and never fixed
acknowledged as such. There may be a fix in 10g for it; I haven't
checked. At any rate, it would be nice if OCI_NEW_LENGTH_SEMANTICS actually
worked as promised. :-)
Chris.
--
GPG Key ID: 366A375B
GPG Key Fingerprint: 485E 5041 17E1 E2BB C263 E4DE C8E3 FA36 366A 375B
Re: DBD: Prepared statements, BLOBs etc.
Posted by Bojan Smojver <bo...@rexursive.com>.
On Wed, 2007-01-17 at 20:44 -0800, Chris Darroch wrote:
> You could be hitting a character set conversion issue between
> your client and the server, or simply a poor piece of Oracle documentation.
The latter. Thanks for your help.
BTW, do you think OCI_NEW_LENGTH_SEMANTICS would be worth adding to
OCIEnvCreate() call? Apparently (according to the documentation :-), it
does this:
------------------------
OCI_NEW_LENGTH_SEMANTICS - byte-length semantics is used consistently
for all handles, regardless of character sets.
------------------------
Hopefully, this would then make sure OCILobRead() returns proper number
of bytes read.
--
Bojan
Re: DBD: Prepared statements, BLOBs etc.
Posted by Chris Darroch <ch...@pearsoncmg.com>.
Hi --
Bojan Smojver wrote:
> Looks pretty simple, doesn't it? Well, I tried with Oracle XE 10.2.0.1
> client and Instant Client 10.2.0.3, all connecting to Oracle XE Server
> (Universal and not) 10.2.0.3 on Linux - no go. I get:
>
> Execute error -1: ORA-01461: can bind a LONG value only for insert into
> a LONG column
You could be hitting a character set conversion issue between
your client and the server, or simply a poor piece of Oracle documentation.
A few possibilities are mentioned here:
http://groups.google.com/group/perl.dbi.users/browse_thread/thread/1659d16b3092699e/63df8e352aceec7a?lnk=st&q=ora+1461&rnum=7&hl=en#63df8e352aceec7a
They talk about "ML" or MetaLink in that thread; that's the central
Oracle support site. Several "notes" from MetaLink are partially quoted.
> Now, the strange thing is that the size of BLOB and CLOB in my table is
> reported as 4000 bytes. Aren't these things supposed to hold at least
> gigabytes?
Yes, they should. But, the documentation makes fairly clear that
a 4 KB limit is imposed on a lot of internal conversions and
operations. If you can get it working, more power to you! You
asked elsewhere about the murky subject of Oracle character sets and
I'm afraid I'm far from expert on it.
Personally I'm beginning to wonder if it wouldn't make more sense
to effectively return APR_ENOTIMPL and only provide LOB handling for
"sane" DB servers. Maybe a driver-specific set of calls could provide
special handling for those that really needed it.
Chris.
--
GPG Key ID: 366A375B
GPG Key Fingerprint: 485E 5041 17E1 E2BB C263 E4DE C8E3 FA36 366A 375B
Re: DBD: Prepared statements, BLOBs etc.
Posted by Bojan Smojver <bo...@rexursive.com>.
On Mon, 2007-01-15 at 13:06 +1100, Bojan Smojver wrote:
> So, one doesn't even need the LOB descriptor or anything, let alone
> the name of the table or column. Did I miss something important here?
> It looks pretty straightforward to me...
Well, I'm facing some major grief regarding this. I found another
example, which would suggest more or less the same thing here, but using
SQLT_CHR type:
http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14249/adlob_data_interface.htm
The text and code is:
----------------------------------
Binding LONG Data for LOB Columns in Binds Greater Than 4000 Bytes
The following example illustrates binding character data for a LOB
column:
void simple_insert()
{
word buflen;
text buf[5000];
text *insstmt = (text *) "INSERT INTO Print_media(Product_id, Ad_id,\
Ad_sourcetext) VALUES (2004, 1, :SRCTXT)";
OCIStmtPrepare(stmthp, errhp, insstmt, (ub4)strlen((char *)insstmt),
(ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
OCIBindByName(stmthp, &bndhp[0], errhp,
(text *) ":SRCTXT", (sb4) strlen((char *) ":SRCTXT"),
(dvoid *) buf, (sb4) sizeof(buf), SQLT_CHR,
(dvoid *) 0, (ub2 *) 0, (ub2 *) 0,
(ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT);
memset((void *)buf, (int)'A', (size_t)5000);
OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
(const OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT);
}
----------------------------------
Looks pretty simple, doesn't it? Well, I tried with Oracle XE 10.2.0.1
client and Instant Client 10.2.0.3, all connecting to Oracle XE Server
(Universal and not) 10.2.0.3 on Linux - no go. I get:
----------------------------------
Execute error -1: ORA-01461: can bind a LONG value only for insert into
a LONG column
----------------------------------
Values up to and including 4000 bytes work fine (minus the character
conversion bit, which I still need to figure out). The error appears
with 4001+ bytes.
Now, the strange thing is that the size of BLOB and CLOB in my table is
reported as 4000 bytes. Aren't these things supposed to hold at least
gigabytes?
Any suggestions welcome...
--
Bojan
Re: DBD: Prepared statements, BLOBs etc.
Posted by Bojan Smojver <bo...@rexursive.com>.
On Mon, 2007-01-15 at 21:42 -0800, Chris Darroch wrote:
> No, no flames implied!
Apologies if I created any misunderstanding. I was just giving you
permission to flame me next time, since this was the second time you
patiently explained the same problem to the list and my forgetful self.
Sorry... :-(
--
Bojan
Re: DBD: Prepared statements, BLOBs etc.
Posted by Chris Darroch <ch...@pearsoncmg.com>.
Bojan Smojver wrote:
> 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 :-).
No, no flames implied! It's fairly painful API to work with and
sufficiently non-intuitive that I always have go back and read the
docs again whenever I pick it up. I'm sorry I haven't gotten around to
a version to match up with your previous work on the DBD layer, but work
has kept me busy on other things.
Chris.
--
GPG Key ID: 366A375B
GPG Key Fingerprint: 485E 5041 17E1 E2BB C263 E4DE C8E3 FA36 366A 375B
Re: DBD: Prepared statements, BLOBs etc.
Posted by Bojan Smojver <bo...@rexursive.com>.
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
Re: DBD: Prepared statements, BLOBs etc.
Posted by Chris Darroch <ch...@pearsoncmg.com>.
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
Re: DBD: Prepared statements, BLOBs etc.
Posted by Bojan Smojver <bo...@rexursive.com>.
On Mon, 2007-01-15 at 14:37 -0800, Chris Darroch wrote:
> For binary BLOB data, that strlen() is going to be a problem.
Yep, that's right. And for the CLOBs it is going to be inefficient, if
those things are big. That's why we can go with an approach that, in
character mode, encodes both BLOB and CLOB as length:payload. In binary
mode, we can just pass this value in apr_datum_t. In both cases, no
strlen() would be needed any more.
Thanks for confirming this.
> At the very least, a way of knowing the length of binary data is
> required. Beyond that, it looks like Oracle documents a number of
> limitations on this approach:
>
> http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10779/oci05bnd.htm#428395
>
> Although it's not mentioned, my own guess is that there are performance
> reasons for using LOB locators as well, but that's just a guess.
> The issues I suspect would trip users up would be the ones that sometimes
> impose a 4 KB limit on the data, depending on the particulars of the
> SQL statement. The implicit use of temporary tables might be a surprise
> too, I suppose.
Yeah, we can sure use LOB locators if that's more efficient. Thanks for
the pointers.
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).
--
Bojan
Re: DBD: Prepared statements, BLOBs etc.
Posted by Bojan Smojver <bo...@rexursive.com>.
On Mon, 2007-01-15 at 23:58 +0100, Antonio Alvarado Hernández wrote:
> - Binding by position is same as your example bellow.
In APU DBD, we always bind by position, so we should be good.
> If you binding as at point (1). above, you are limited to 4GB of
> length (not 128 terabytes ;-)
He, he... I think we'll be OK with this. For a while ;-)
> (3). apr_dbd_lob_t can be used for real LOB piecewise manipulation. I
> don't know if this feature is needed by other drivers (e.g. MySQL,
> Postgres, and so on)
We could use it (actually, I have patches that do so :-), but it is not
mandatory. Other structures we already have in APU may do just fine
(e.g. apr_datum_t). I just wanted to stay away from introducing
something that is not entirely required. KISS and all.
> I'm not an expert in OCI nor APR-DBD but I feel this maybe my 2 cents :-)
Thanks. Any info on OCI is welcome. To me, OCI is still a bit of a
mystery :-)
--
Bojan
Re: DBD: Prepared statements, BLOBs etc.
Posted by Antonio Alvarado Hernández <aa...@gmail.com>.
Hello, I'm missing something... these are my thoughs:
On 1/15/07, Bojan Smojver <bo...@rexursive.com> wrote:
>
> Which brings me back to the once mentioned Oracle weirdness, where the
> name of the table and the name of the column are supposedly required
> in order to bind a BLOB/CLOB as INPUT parameters (i.e. to
> p[b]query/select). From what I can see in the docs (Oracle Call
> Interface Programmer's Guide), simple things like this are allowed in
> order to bind LOBs:
(1). I agreed.
- Binding by name refers to parameter names (e.g. "p1", "p2" and "p3"
in "INSERT INTO lob_long_tab (C1, C2, L) VALUES (:p1, :p2, :p3)"), not
table/columns name.
- Binding by position is same as your example bellow.
>
> ----------------------------------------------------------
> CREATE TABLE foo (a INTEGER );
> CREATE TYPE lob_typ AS OBJECT (A1 CLOB );
> CREATE TABLE lob_long_tab (C1 CLOB, C2 CLOB, CT3 lob_typ, L LONG);
>
> void insert() /* A function in an OCI program */
> {
> /* The following is allowed */
> ub1 buffer[8000];
> text *insert_sql = (text *) "INSERT INTO lob_long_tab (C1, C2, L) \
> VALUES (:1, :2, :3)";
> OCIStmtPrepare(stmthp, errhp, insert_sql, strlen((char*)insert_sql),
> (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
> OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (dvoid *)buffer, 8000,
> SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
> OCIBindByPos(stmthp, &bindhp[1], errhp, 2, (dvoid *)buffer, 8000,
> SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
> OCIBindByPos(stmthp, &bindhp[2], errhp, 3, (dvoid *)buffer, 2000,
> SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
> OCIStmtExecute(svchp, stmthp, errhp, 1, 0, (OCISnapshot *) NULL,
> (OCISnapshot *) NULL, OCI_DEFAULT);
> }
> ----------------------------------------------------------
>
> So, one doesn't even need the LOB descriptor or anything, let alone
> the name of the table or column. Did I miss something important here?
> It looks pretty straightforward to me...
(2). Yes. Lob descriptor is for streaming LOB manipulation as used
with OCILobRead2 and OCILobWriter2 (Oracle call this piecewise fetch
or update). More at
http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14250/oci07lob.htm#sthref1158
If you binding as at point (1). above, you are limited to 4GB of
length (not 128 terabytes ;-)
> If you remember, that was the reason for creating patches that would
> encode (in CHARACTER (i.e. existing) mode) BLOB/CLOB values as:
>
> length:table:column:payload
>
> From what I can see, we could get away with just length:payload, just
> like we can with all other databases we support.
>
> In binary mode, we wouldn't have to specify table/column at all.
> Again, size and payload should be just fine. This would enable us to
> use some existing structures for all this, instead of having to invent
> apr_dbd_blob_t.
(3). apr_dbd_lob_t can be used for real LOB piecewise manipulation. I
don't know if this feature is needed by other drivers (e.g. MySQL,
Postgres, and so on)
> Anyone familiar with OCI, please speak up. Chris?
>
> --
> Bojan
>
I'm not an expert in OCI nor APR-DBD but I feel this maybe my 2 cents :-)
Regards,
Antonio
Re: DBD: Prepared statements, BLOBs etc.
Posted by Bojan Smojver <bo...@rexursive.com>.
Quoting Bojan Smojver <bo...@rexursive.com>:
> I was silly enough to download Oracle XE and start playing with the
> Oracle driver...
And the result of that madness is attached :-)
You'll notice that I didn't use LOB locators to do binding in the
Oracle driver, as this would require unusual code gymnastics, as
previously discussed. Also, some things are cheap and inefficient code
reuse cases. But, it may be some kind of start...
PS. The whole thing is in the tarball due to size. You'll find there
(crude) bits that I used for testing.
--
Bojan
Re: DBD: Prepared statements, BLOBs etc.
Posted by Bojan Smojver <bo...@rexursive.com>.
Quoting Bojan Smojver <bo...@rexursive.com>:
> This would enable us to use some
> existing structures for all this, instead of having to invent
> apr_dbd_blob_t.
For instance, apr_datum_t.
--
Bojan
Re: DBD: Prepared statements, BLOBs etc.
Posted by Chris Darroch <ch...@pearsoncmg.com>.
Bojan Smojver wrote:
> OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (dvoid *)buffer, 8000,
> SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
> So, one doesn't even need the LOB descriptor or anything, let alone
> the name of the table or column. Did I miss something important here?
Well, for anything to do with OCI, I always have to go back to
basics and try to refresh my memory about all the complexities,
so my apologies if this turns out to be incorrect.
On a quick glance, I think what they're showing here is essentially
what we do now to handle LOBs, and that's treat them like LONGs
(SQLT_LNG means SQL Type LONG) when the '%L' placeholder is used.
The current apr_dbd_oracle.c then uses strlen() to determine the length
of the input argument (in the example above that's the 8000 value).
Here's a relevant bit of apr_dbd_oracle.c:
case APR_DBD_ORACLE_LOB:
/* requires strlen() over large data, which may fail for binary */
statement->args[i].value.raw = va_arg(args, char*);
statement->args[i].len =
strlen(statement->args[i].value.stringval);
sql->status = OCIBindByPos(statement->stmt,
&statement->args[i].bind,
sql->err, i+1,
(void*)statement->args[i].value.raw,
statement->args[i].len, SQLT_LNG,
&statement->args[i].ind,
NULL,
(ub2) 0, (ub4) 0,
(ub4 *) 0, OCI_DEFAULT);
For binary BLOB data, that strlen() is going to be a problem.
At the very least, a way of knowing the length of binary data is
required. Beyond that, it looks like Oracle documents a number of
limitations on this approach:
http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10779/oci05bnd.htm#428395
Although it's not mentioned, my own guess is that there are performance
reasons for using LOB locators as well, but that's just a guess.
The issues I suspect would trip users up would be the ones that sometimes
impose a 4 KB limit on the data, depending on the particulars of the
SQL statement. The implicit use of temporary tables might be a surprise
too, I suppose.
Like I said, that's a quick response based on a skim of the docs;
apologies in advance for any errors or misinformation.
Chris.
--
GPG Key ID: 366A375B
GPG Key Fingerprint: 485E 5041 17E1 E2BB C263 E4DE C8E3 FA36 366A 375B