apr-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Antonio Alvarado Hernández" <aalvara...@gmail.com>
Subject Re: DBD: Prepared statements, BLOBs etc.
Date Mon, 15 Jan 2007 22:58:19 GMT
Hello, I'm missing something... these are my thoughs:

On 1/15/07, Bojan Smojver <bojan@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

Mime
View raw message