Re: [phplib] PHPLIB and oracle binding for >2000 characters From: Mike Green (Mike.Green <email protected>)
Date: 10/12/00

Well, with the suggestions here and those posted on the PHP manual pages for
OCI8 (especially those of jcd <email protected> on the page
http://www.php.net/manual/function.ocinewdescriptor.php), I've come up with
something that seems to be working. I thought it would be good to post it
here in case someone else can use it ...and to get your feedback.

I've extended the OCI8 DB_Sql class (db_oci8.inc,v 1.4 2000/07/12 18:22:34)
by revising its function query to read:

    function query($Query_String, $bind_var = '', $bind_val = '') {

    /* No empty queries, please, since PHP4 chokes on them. */
        if ($Query_String == "")
        /* The empty query string is passed on from the constructor,
         * when calling the class without a query, e.g. in situations
         * like these: '$db = new DB_Sql_Subclass;'
         */
        return 0;

        $this->connect();

        if ($bind_var){
            $clob = OCINewDescriptor($this->Link_ID, OCI_D_LOB);
            $Query_String .= " returning $bind_var into :the_blob";
        }

        $this->Parse=OCIParse($this->Link_ID,$Query_String);
        if(!$this->Parse) {
            $this->Error=OCIError($this->Parse);
        } else {
            if ($bind_var) {
                OCIBindByName($this->Parse, ':the_blob', &$clob, -1,
OCI_B_CLOB);
                OCIExecute($this->Parse, OCI_DEFAULT);
                if($clob->save($bind_val)){
                    OCICommit($this->Link_ID);
                }else{
                    $this->Error = "Couldn't insert CLOB into database.";
                }
                OCIFreeDesc($clob);
            } else {
                OCIExecute($this->Parse);
            }
            $this->Error=OCIError($this->Parse);
        }

        $this->Row=0;

        if($this->Debug) {
            printf("Debug: query = %s<br>\n", $Query_String);
        }

        if ($this->Error["code"]!=1403 && $this->Error["code"]!=0 &&
$this->sqoe)
            echo "<BR><FONT
color=red><B>".$this->Error["message"]."<BR>Query
:\"$Query_String\"</B></FONT>";
        return $this->Parse;
    }

This seems to be working.

It has three shortcomings that I am aware of:

1) It only allows for one type of "bind" variable: CLOB.

2) It only allows for one column to be a "bind" variable

3) One must be sure that the $Query_String is has the correct form for this
usage: "$bind_var" in $Query_String must be paired with "EMPTY_CLOB()," not
its value: $bind_val.

I can think of ways to deal with each of these, but not in the time I have
available to get the present project going. And this seems to be doing the
job for Inserts and Updates.

I find it a bit puzzling that Selects seem to work all right without using
this approach. I.e. I'm just using a straight SQL select, without any
"binds." And I seem to be having no trouble retrieving the data. Any
thoughts?

All feedback is very welcome.

Cheers!

Mike Green

Chris Johnson wrote:

> Without actually looking at the code, here's my wild-ass guess.
>
> The interface to Oracle in PHP is most likely written using Oracle's
> Pro*C pre-compiler. It takes embedded database statements and generates
> the lengthy and obtuse C statements required to call Oracle. Pro*C uses
> a concept it calls "bind variables" which the programmer declares in
> advance of actually making the SQL statement execution call to Oracle.
> Oracle's C API then uses those bind variables as a one-row cursor, and
> one can programmatically "next" through the result set, if a multiple
> row select. Actually there are 4 distinct cases, but I can no longer
> remember what they are and do not own the Oracle documentation.
>
> My guess is that there is way to send data to an Oracle server for
> insert or update style SQL statements that does not involve the use of
> bind variables, and that the non-bind variable method was used by the
> PHPLIB Oracle interface. However, as you have apparently discovered,
> this alternate method is limited to 4000 characters. In order to send
> more data, a real bind variable must be used.
>
> As you mentioned, it sounds like a bind variable interface exists in PHP
> itself (again, I have not researched this). If so, indeed it seems that
> you will need to modify the Oracle DB class in PHPLIB to make it use
> bind variables instead of whatever method it is using now.
>
> This is my guess based on maintaining two high-speed search engines
> written in C and Pro*C. All of that work used bind variables for data
> transfer.
>
> Good luck.
>
> ..chris
> --
> Chris Johnson / Chaska Internet Consulting LLP
>
> > -----Original Message-----
> > From: Mike Green [mailto:Mike.Green <email protected>]
> > Sent: Thursday, October 05, 2000 11:56 AM
> > Cc: phplib <email protected>; fabrizio.ermini <email protected>
> > Subject: Re: [phplib] PHPLIB and oracle binding for >2000 characters
> >
> >
> > Thanks!
> >
> > My problem is at the application level. And, if I understand
> > CT_split_SQL from a brief
> > look at it, I don't want to split data that belongs in one
> > row up into multiple rows. The
> > Oracle column field is a CLOB and able to hold 4 GB, not just
> > 4000 characters. It's just
> > that Oracle apparently requires one to input stings of more
> > than 4000 characters in what
> > seems to me to be a rather obtuse manner, since I have never
> > used "bind" variables.
> >
> > The solution is quite possibly very simple. But from past
> > experience with such things, I
> > know that I can easily take a wrong turn at some early point
> > and end up making things very
> > complex long before I discover the simple solution ;-) Thus
> > pointers to the right paths
> > to take will be much appreciated.

---------------------------------------------------------------------
To unsubscribe, e-mail: phplib-unsubscribe <email protected>
For additional commands, e-mail: phplib-help <email protected>