Mailing-List: contact dev-help@apr.apache.org; run by ezmlm
Precedence: bulk
Received-SPF: neutral (herse.apache.org: local policy)
Date: Mon, 15 Jan 2007 14:37:55 -0800
From: Chris Darroch <chrisd@pearsoncmg.com>
Subject: Re: DBD: Prepared statements, BLOBs etc.
In-reply-to: <20070115130619.1mmlk8lc00gk808c@www.rexursive.com>
To: dev@apr.apache.org
Message-id: <45AC0243.5070706@pearsoncmg.com>
Organization: Pearson CTG/CMG
MIME-version: 1.0
Content-type: text/plain; charset=us-ascii
Content-transfer-encoding: 7bit
References: <1155589108.3289.26.camel@shrek.rexursive.com>
 <44EF3BEF.7060901@pearsoncmg.com>
 <1156537930.2596.1.camel@shrek.rexursive.com>
 <20060831092218.nh0wn8ls00cg0c88@www.rexursive.com>
 <20060904084356.7prsyaa30g4kok08@www.rexursive.com>
 <44FE6D0B.90004@pearsoncmg.com>
 <20070115130619.1mmlk8lc00gk808c@www.rexursive.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.13) Gecko/20060423

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