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 Mon, 15 Jan 2007 02:06:19 GMT
Quoting Chris Darroch <chrisd@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

Mime
View raw message