PHP :: Bug #77764 :: Binding of SQLT_NUM variable resolves as NULL in SQL

[2019-03-18 14:11 UTC] 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)

[2019-03-26 19:55 UTC] 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.

[2021-09-04 12:19 UTC] 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.

[2021-10-18 14:36 UTC] cmb@php.net

-Assigned To: +Assigned To: sixd

[2021-10-18 14:36 UTC] cmb@php.net

> 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.

Patches

Pull Requests

History

	php.net \| support \| documentation \| report a bug \| advanced search \| search howto \| statistics \| random bug \| login
go to bug id or search bugs for


Copyright © 2001-2023 The PHP Group All rights reserved.	Last updated: Tue Sep 26 04:01:25 2023 UTC