#41538 [Bgs]: Stange behavior when binding NULL variable
| From: | leligeour at ensiie dot fr | Date: | Wed, 30 May 2007 12:15:18 +0000 |
| Subject: | #41538 [Bgs]: Stange behavior when binding NULL variable | ||
| References: | 1 | Groups: | php.bugs |
| Request: | Send a blank email to php-bugs+get-113192@lists.php.net to get a copy of this message | ||
ID: 41538
User updated by: leligeour at ensiie dot fr
Reported By: leligeour at ensiie dot fr
Status: Bogus
Bug Type: OCI8 related
Operating System: Linux - FC6 - x86_64
PHP Version: 5.2.2
New Comment:
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) ""
}
}
*/
?>
Previous Comments:
------------------------------------------------------------------------
[2007-05-30 09:07:43] tony2001@php.net
Right, this is how it should work.
------------------------------------------------------------------------
[2007-05-30 08:59:26] 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
------------------------------------------------------------------------
--
Edit this bug report at http://bugs.php.net/?id=41538&edit=1