Bug #77764 [Opn]: Binding of SQLT_NUM variable resolves as NULL in SQL
Edit report at https://bugs.php.net/bug.php?id=77764&edit=1
ID: 77764
Updated by: cmb@php.net
Reported by: m dot a dot ogarkov at gmail dot com
Summary: Binding of SQLT_NUM variable resolves as NULL in SQL
Status: Open
Type: Bug
Package: OCI8 related
Operating System: Linux
PHP Version: 7.3.3
-Assigned To:
+Assigned To: sixd
Block user comment: N
Private report: N
New Comment:
> OCI8 will write 21 bytes into a zend_long-value.
This would be *very* bad. Chris, could you please have a look at
this?
> I think the SQLT_NUM-implementation is missing and SQLT_NUM
> shoud be rejected until someone has written a conversion-routine
> between PHP-double and Oracle-NUMBER format.
SQLT_NUM is apparently not properly implemented, but instead of
having the proper conversion, it might be okay to work with the
binary data provided as string. One may still do the conversion
in PHP.
Previous Comments:
------------------------------------------------------------------------
[2021-09-04 12:19:39] php-oci8 dot pkoch at dfgh dot net
oci_bind_by_name() should reject $type==SQLT_NUM.
Bind-type SQLT_NUM will ask oci8 to store/fetch an integer value into/from a 21 byte array where the
integer is stored in the oracle-internal NUMBER-format.
But the pointer that is used with SQLT_NUM-binds points to an 8-byte integer value (zend_long).
It's unpredictable what happens in this case. OCI8 will write 21 bytes into a zend_long-value.
This overwrites 13 bytes immediately after the zend_long value and the zend_long-value will have a
wrong value.
On my machine
$conn=oci_new_connect('scott','tiger','ORCL');
$stat=oci_parse($conn, "begin :out:=123; end;");
oci_bind_by_name($stat, ":out", $result, -1, SQLT_NUM);
oci_execute($stat);
var_dump($result);
results in:
int(1573570)
I think the SQLT_NUM-implementation is missing and SQLT_NUM shoud be rejected until someone has
written a conversion-routine between PHP-double and Oracle-NUMBER format.
------------------------------------------------------------------------
[2019-03-26 19:55:45] camporter1 at gmail dot com
The basic issue is that SQLT_NUM is treated just like SLQT_INT, which allows long values only.
For proper handling of doubles (which involves behind the scenes changing them into a string)
I'd recommend using SQLT_LNG, or not providing the type at all which does the same thing.
------------------------------------------------------------------------
[2019-03-18 14:11:22] m dot a dot ogarkov at gmail dot com
Description:
------------
When binding with SQLT_NUM type, we get NULL in sql/plsql.
Variables is converted to INT, why int?
Test script:
---------------
<?php
declare(strict_types=1);
$handle = oci_connect(getenv("DB_USER"), getenv("DB_PASSWORD"),
getenv("DB_HOST"));
$var = 15.123;
$st = oci_parse($handle, "SELECT :0 FROM DUAL");
oci_bind_by_name($st, ":0", $var, -1, SQLT_NUM);
oci_execute($st);
var_dump(oci_fetch_array($st));
var_dump($var);
Expected result:
----------------
we should get NUMBER sql type:
"15.123" => (float) 15.123
"15" => (float) 15
Actual result:
--------------
array(2) {
[0]=>
NULL
[":0"]=>
NULL
}
int(15)
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=77764&edit=1
Thread (4 messages)