php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #41538 Stange behavior when binding NULL variable
Submitted: 2007-05-30 08:59 UTC Modified: 2007-05-30 12:15 UTC
From: leligeour at ensiie dot fr Assigned:
Status: Not a bug Package: OCI8 related
PHP Version: 5.2.2 OS: Linux - FC6 - x86_64
Private report: No CVE-ID: None
View Developer Edit
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: leligeour at ensiie dot fr
New email:
PHP Version: OS:

 

 [2007-05-30 08:59 UTC] leligeour at ensiie dot fr 
Description:
------------
Hi,

I'm trying to insert NULL value into a Orcale field (whether VARCHAR2 or NUMBER) using oci_bind_by_name. I think that the NULL vars are not handled properly.

Expected result:
----------------
I expected something similar to :
1. Inserting "" in a VARCHAR2 should result in an empty field in Oracle (and not a NULL entry)
2. Inserting NULL in a VARCHAR2 should result in a NULL entry in Oracle
3. Inserting NULL in a NUMBER should result in a NULL entry in Oracle

I don't think if it should be the default behavior, but it's definitely something that should be possible using php NULL variable. 

Actual result:
--------------
Since this behavior is not documented in the function's help page, here is what I observe:
1. when using SQLT_CHR NULL is inserted as soon as an empty string or NULL is entered
2. when using SQLT_INT, 0 is inserting when NULL is entered

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2007-05-30 09:07 UTC] tony2001@php.net 
Right, this is how it should work.
 [2007-05-30 12:15 UTC] leligeour at ensiie dot fr 
Test case :

<?
// Init test values
$valuesInt[0] = 1;
$valuesInt[1] = 2;
$valuesInt[2] = 0;
$valuesInt[3] = NULL;
$valuesChar[0] = "test1";
$valuesChar[1] = "test2";
$valuesChar[2] = "";
$valuesChar[3] = NULL;

// Create test table
$query = "CREATE TABLE BIND_NULL_VALUES( ID NUMBER(2), STRING VARCHAR2(32) )";
$stmt = oci_parse($link,$query);
$res = oci_execute($stmt, OCI_DEFAULT);
oci_commit($link);
oci_free_statement($stmt);

// Insert values using oci_bind_by_name
$query = "INSERT INTO BIND_NULL_VALUES ( ID, STRING ) VALUES (:MYINT,:MYSTRING)";
$stmt = oci_parse($link,$query);
for( $i = 0 ; $i < 4 ; $i++ ) {
        oci_bind_by_name($stmt,":MYINT",$valuesInt[$i],-1,SQLT_INT);
        oci_bind_by_name($stmt,":MYSTRING",$valuesChar[$i],-1,SQLT_CHR);
        $res = oci_execute($stmt, OCI_DEFAULT);
}
oci_commit($link);
oci_free_statement($stmt);

// Insert NULL integer and empty string
$query = "INSERT INTO BIND_NULL_VALUES ( ID, STRING ) VALUES (NULL,'')";
$stmt = oci_parse($link,$query);
$res = oci_execute($stmt, OCI_DEFAULT);
oci_commit($link);
oci_free_statement($stmt);

// Select and print all data
$query = "SELECT * FROM BIND_NULL_VALUES";
$stmt = oci_parse($link,$query);
$res = oci_execute($stmt, OCI_DEFAULT);
oci_fetch_all($stmt, $res);
var_dump($res);
oci_free_statement($stmt);

// Drop test table
$query = "DROP TABLE BIND_NULL_VALUES";
$stmt = oci_parse($link,$query);
$res = oci_execute($stmt, OCI_DEFAULT);
oci_commit($link);
oci_free_statement($stmt);

/*
Actual result
-------------

array(2) {
  ["ID"]=>
  array(5) {
    [0]=>
    string(1) "1"
    [1]=>
    string(1) "2"
    [2]=>
    string(1) "0"
    [3]=>
    string(1) "0"
    [4]=>
    NULL
  }
  ["STRING"]=>
  array(5) {
    [0]=>
    string(5) "test1"
    [1]=>
    string(5) "test2"
    [2]=>
    NULL
    [3]=>
    NULL
    [4]=>
    NULL
  }
}

Expected result
---------------

array(2) {
  ["ID"]=>
  array(5) {
    [0]=>
    string(1) "1"
    [1]=>
    string(1) "2"
    [2]=>
    string(1) "0"
    [3]=>
    NULL
    [4]=>
    NULL
  }
  ["STRING"]=>
  array(5) {
    [0]=>
    string(5) "test1"
    [1]=>
    string(5) "test2"
    [2]=>
    string(0) ""
    [3]=>
    NULL
    [4]=>
    string(0) ""
  }
}
*/


?>
 
PHP Copyright © 2001-2025 The PHP Group
All rights reserved. 		Last updated: Mon May 12 22:01:30 2025 UTC