oci_bind_by_name buggy with TO_DATE SQL function

Question

Hi all,

I have a strange issue today related to the oci_bind_by_name function I use in PHP.

Let me give you a showcase.

Here is a table with simple dates:

create table test(col1 date);
insert into test values(to_date('01/01/2009','DD/MM/YYYY'));
insert into test values(to_date('01/01/2019','DD/MM/YYYY'));
insert into test values(to_date('01/01/2029','DD/MM/YYYY'));
insert into test values(to_date('01/01/2039','DD/MM/YYYY'));

Launching this query will result in 2 rows if I bind :dt_maj_deb and :dt_maj_fin to 01/01/2009 and 01/02/2019 for example:

SELECT * 
FROM TEST 
WHERE col1 BETWEEN TO_DATE (:dt_maj_deb, 'DD/MM/YYYY') 
               AND TO_DATE (:dt_maj_fin, 'DD/MM/YYYY')

Results
-------
01.01.2009          
01.01.2019

So everything is as we may expect. My concern is when I want to launch the same query from PHP. Here is my test code:

$query = "SELECT * FROM TEST 
          WHERE col1 BETWEEN TO_DATE (:dt_maj_deb, 'DD/MM/YYYY')
                         AND TO_DATE (:dt_maj_fin, 'DD/MM/YYYY')";
$stmt = oci_parse($conn,$query);
$value = '01/01/2009';
oci_bind_by_name($stmt,':dt_maj_deb',$value);
$value = '01/02/2019';
oci_bind_by_name($stmt,':dt_maj_fin',$value);
oci_execute($stmt);
oci_fetch_all($stmt, $result);
var_dump($result);
oci_free_statement($stmt);
oci_close($conn);

Results
-------
array(1) {
    ["COL1"]=>
       array(0) {}
}

What am I missing??

Answer 1

Aren't you binding both :dt_maj_deb and :dt_maj_fin to the same $value, so when you execute they'll both hold the same date? As there are no data actually on 01/02/2019 there is nothing to return. If your second $value= was to a date that does exist in the table then you'd get exactly one row back, wouldn't you? Or, to put it another way, use different variables for the two oci_bind_by_name() calls.