Forum Stats

  • 3,827,488 Users
  • 2,260,783 Discussions
  • 7,897,278 Comments

Discussions

oci_bind_by_name binds boolean false as NULL

dregad
dregad Member Posts: 2
edited May 13, 2014 11:27AM in PHP

Hi,

I'm trying to insert a PHP boolean variable into a column defined as number. It works fine for TRUE, but fails with ORA-01400 for FALSE.

Reason for using NUMBER(1) is for portability with other DB engines.

create table test (version varchar(10), obsolete number(1) not null);

<?php
$conn = oci_connect("user", "password", "localhost/XE");

$sql = "INSERT INTO test (version, obsolete) values (:0, :1)";
$param = array( 'test', false );

$stmt=oci_parse($conn, $sql);
oci_bind_by_name($stmt, ":0", $param[0]);
oci_bind_by_name($stmt, ":1", $param[1]);
var_dump(oci_error($conn));

var_dump(oci_execute($stmt));

$ php /tmp/test.php
bool(false)
PHP Warning:  oci_execute(): ORA-01400: cannot insert NULL into ("USER"."TEST"."OBSOLETE") in /tmp/test.php on line 12
bool(false)

If I cast to (int) prior to binding the boolean it works so I have an obvious workaround, but my expectation would be that false would be converted to 0, just like true is automatically stored as 1 (with  $param = array( 'test', true );, the execution is successful)

$ php /tmp/test.php
bool(false)
bool(true)

SQL> select * from test;

VERSION      OBSOLETE
---------- ----------
test                1

Is this a bug ? If not, can someone explain why this happens and maybe what I'm doing wrong ?

Cheers

Damien

------

PHP 5.5.9-1ubuntu4

OCI8 Version => 2.0.8
Oracle XE 11.2.0.2.0

Answers

  • Christopher Jones-Oracle
    Christopher Jones-Oracle Member Posts: 1,648 Employee

    The default type used for oci_bind_by_name is SQLT_CHR, so the boolean value you are passing is being converted internally to a string. In zend_operators.c::_convert_to_string():

     ...
      case IS_BOOL:
        if (Z_LVAL_P(op)) {
          Z_STRVAL_P(op) = estrndup_rel("1", 1);
          Z_STRLEN_P(op) = 1;
        } else {
          Z_STRVAL_P(op) = STR_EMPTY_ALLOC();
          Z_STRLEN_P(op) = 0;
        }
      ...
    

    So the 'false' value is converted to an empty string by the second branch.  This leads to the ORA-1400 because of Oracle's traditional handling of empty strings as NULL.

    When you cast to (int) a different conversion path results in the internal representation becoming the string "0".  This is converted to 0 by the underlying Oracle libraries.

    For other readers reference, but not useful in your scenario, OCI8 2.0 (when using Oracle 12c) has support for binding the PL/SQL BOOLEAN type:

      $s = oci_parse($c, "begin :b := true; end;");
      oci_bind_by_name($s, ':b', $b, -1, OCI_B_BOL);
      oci_execute($s);
      var_dump($b); // Outputs: bool(true)
    
This discussion has been closed.