Empty date/time variable with Oracle.

[cornelis]

Dear reader,

I have a problem with a variable received from a Oracle SQL select statement.

The select statement is:

$query = "(SELECT ORDER_LINE.ORDER_ID,ORDER_LINE.CCONTH_ID,
ORDER_LINE.CUST_COMPANY_ID,ORDER_LINE.IS_RESOLVED,ORDER_LINE.OPEN_DATE,
to_char(ORDER_LINE.OPEN_DATE,'MM-DD-YYYY HH:MI AM') as test,
to_char(ORDER_LINE.OPEN_TIME,'DD-MM-YYYY HH24:MI:SS') as test2,
ORDER_LINE.ORDER_TYPE_ID,ORDER_LINE.PO_ID,ORDER_LINE.REQUEST_ID,ORDER_LINE.SITE_COMPANY_DESCR,
ORDER_LINE.SITE_COMPANY_ID,SERVICE_CALL.BPART_ID,SERVICE_CALL.CONTR_ETA,SERVICE_CALL.REFNO,
SERVICE_CALL.SERIAL_NO,BPART.DESCR,SERVICE_CALL_ENV.RESOLVE_DATE,SERVICE_CALL_ENV.RESOLVE_TIME,
SERVICE_CALL_ENV.Z_UDF_DATE_1,SERVICE_CALL_ENV.Z_UDF_TIME_1
……………………….

I retrieve the sql with the next php functions.

$count_cursor = ($query);
$stmt = OCIParse($conn,$count_cursor);
ociexecute($stmt);
$aantal=OCIFetchStatement($stmt,$results)
or die ("No result");
reset($results);

I want to display the test and the test2 value (date/time fields in Oracle database).

$teller=0;
While ($aantal<>$teller)
{
$callnum=($results[REQUEST_ID][$teller]);
$opendatum=($results[test][$teller]);
$opentijd=($results[test2][$teller]);
$debnummer=($results[CUST_COMPANY_ID][$teller]);
$ordersoort=($results[ORDER_TYPE_ID][$teller]);
$uwref=($results[REFNO][$teller]);
if (is_null($uwref)){
$uwref= 'geen';}
$contractnr=($results[CCONTH_ID][$teller]);
if (is_null($contractnr)){
$contractnr= 'geen';}
$garsoort=($results[WARR_TYPE_ID][$teller]);
$sitenaam=($results[SITE_COMPANY_DESCR][$teller]);
$siteid=($results[SITE_COMPANY_ID][$teller]);
$serienr=($results[SERIAL_NO][$teller]);
$proddescr=($results[DESCR][$teller]);
$afspraakdag=($results[Z_UDF_DATE_1][$teller]);
$afspraaktijd=($results[Z_UDF_TIME_1][$teller]);
$eta=($results[CONTR_ETA][$teller]);
$productnr=($results[BPART_ID][$teller]);
$resdatum=($results[RESOLVE_DATE][$teller]);
$restijd=($results[RESOLVE_TIME][$teller]);

echo $opendatum;
echo $opentijd;

}

The variable $opendatum and the variable opentijd are empty.
The rest of the variable have a value.

Wen I use the SQL select script in a query tool I see the date and time in values formatted in the select script.

The question is how can I displayed the date and time value from Oracle in WebPages ?

Tanks for a reaction.

Kees de Hoop

[cornelis]

To day I have found the solution of this problem.
The result of the ORACLE query gives a column name in a capital format.
The column name in the example script is 'test' and 'test2'
With the function var_dump() I have see that the column name 'test' and 'test2' are changed to 'TEST' and 'TEST2'
A have modify the program rules

$opendatum=($results[test][$teller]);
$opentijd=($results[test2][$teller]);

In

$opendatum=($results[TEST][$teller]);
$opentijd=($results[TEST2][$teller]);

And the PHP script works.

This issue is solved.