Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
oci_bind_by_name binds boolean false as NULL
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
-
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)