php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Request #27303 OCIBindByName binds numeric PHP values as characters
Submitted: 2004-02-18 02:14 UTC Modified: 2005-09-09 14:24 UTC
From: cjbj at hotmail dot com Assigned: tony2001 (profile)
Status: Closed Package: Feature/Change Request
PHP Version: 4CVS-2004-02-18 (stable) OS: *
Private report: No CVE-ID: None
 [2004-02-18 02:14 UTC] cjbj at hotmail dot com
Description:
------------
Numeric PHP variables seem to be treated as strings when bound with
OCIBindByName.  The means the desired number of significant digits
needs to be specified in the OCIBindByName call.  Othewise only the
most significant digit is returned.

PHP scripts could be simpler and safer to write if:

    (i) a pre-existing numeric PHP variable automatically caused a
        SQLT_INT bind

    (ii) OCI's SQLT_INT type could be specified in the OCIBindByName
         call, with a new OCI_B_xxx define

For example, try a RETURNING INTO clause with a sequence value:

    <?php 

    /*
      Execute these in SQL*Plus prior to running this PHP script:
	    drop sequence myseq;
	    drop table mytab;
	    create sequence myseq;
	    create table mytab (mydata varchar2(20), seqcol number);
    */

    define('MYLIMIT', 200);
    define('INITMYBV', 11);

    $conn = OCILogon("scott", "tiger", "mydb");
    $stmt = "insert into mytab (mydata, seqcol) values ('Some data', myseq.nextval) returning seqcol into :mybv";

    $stid = OCIParse($conn, $stmt);
    if (!$stid) { echo "Parse error"; die; }

    //$mybv = INITMYBV;   // Uncomment this for the 2nd test only
    $r = OCIBindByName($stid, ':MYBV', $mybv /*, 5 */);  // Uncomment this for the 3rd test only
    if (!$r) { echo "Bind error"; die; }

    for ($i = 1; $i < MYLIMIT; $i++) {
      $r = OCIExecute($stid, OCI_DEFAULT);
      if (!$r) { echo "Execute error"; die; }
      echo "Bind variable is $mybv<br>\n";
    }

    OCICommit($conn);

    ?>

The output is:

    Bind variable is 1
    Bind variable is 2
    Bind variable is 3
    Bind variable is 4
    Bind variable is 5
    Bind variable is 6
    Bind variable is 7
    Bind variable is 8
    Bind variable is 9
    Bind variable is 1
    ...

    [This continues with 10 rows of 1's, then 10 rows of 2's etc. as
     if the least significant digit(s) are being stripped.]

Querying the table in a SQL*Plus sessions shows the inserts are
occuring correctly and the sequence number is increasing past nine:

    MYDATA                   SEQCOL
    -------------------- ----------
    Some data                     1
    Some data                     2
    Some data                     3
    Some data                     4
    Some data                     5
    Some data                     6
    Some data                     7
    Some data                     8
    Some data                     9
    Some data                    10
    ...
    Some data                   199

Expected result

    Bind variable is 1
    Bind variable is 2
    Bind variable is 3
    Bind variable is 4
    Bind variable is 5
    Bind variable is 6
    Bind variable is 7
    Bind variable is 8
    Bind variable is 9
    Bind variable is 10
    ...
    Bind variable is 199

Testcase 2 is to set $mybv to a value before the OCIBindByName by
uncommenting the line marked 2nd test.  The value set is bound as a
string.  The number of digits in it will determine the maximum width
of the returned bind result.  For example if $mybv is set to 11, then
all two-digit values are displayed correctly.

Testcase 3 is to pass a width to OCIBindByName.  Uncomment the text
marked 3rd test.  This works until the number of digits in the
sequence number is greater than the passed width.

Another different testcase might be calling a PL/SQL procedure with a
IN/OUT parameter.  Although the passed value is correct, the out value
may not be.

If the current behavior is deemed desired, can this bug be made a
documentation bug?  Users may be easily tricked because there is no
mention of the default bind type being character, and the
OCIBindByName documentation "and the necessary storage space will be
allocated" could be thought to apply to PHP numeric variables.



Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2004-02-20 02:38 UTC] tony2001@php.net
Yep, currently oci_bind_by_name uses SQLT_CHR for all types, except LOBs, FILEs & ROWIDs.
You propose to add new constant - OCI_B_INT (SQLT_INT) to use it with numeric values? Am I right?

 [2004-03-09 02:06 UTC] cjbj at hotmail dot com
It would seem logical and faster to bind PHP integers as Oracle
integers.  It might make the "returning into" example from before
simpler (no need to specify a width) and hence more intuitive and
foolproof.

A new constant OCI_B_INT could map to SQLT_INT (depending on how PHP
internally stores integers).
 [2004-03-09 02:45 UTC] tony2001@php.net
Ok, it will be added after PHP5 & PHP4 nearest releases.
We're in feature freeze now and I'll wait till its end.
 [2005-09-09 14:24 UTC] tony2001@php.net
The bug has been fixed in OCI8 v.1.1, which is available in CVS HEAD and PECL (use `pear install oci8-beta` to install it).
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Apr 18 00:01:28 2024 UTC