Hello, I'm missing something... these are my thoughs:
On 1/15/07, Bojan Smojver <[EMAIL PROTECTED]> 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
