#18758 [Opn->Fbk]: OCI8 ext not handling Oracle 9i TIMESTAMP datatypes

ID: 18758 Updated by: kalowsky@php.net Reported By: robert@ud.com -Status: Open +Status: Feedback Bug Type: OCI8 related Operating System: RH Linux 7.2 PHP Version: 4.2.1 New Comment: I think some of this has been delt with in the non stable CVS versions. can you try one please and confirm or deny this? Previous Comments: ------------------------------------------------------------------------ [2002-08-06 12:49:20] robert@ud.com PHP 4.2.1 compiled using --with-oci8 option (9i client libs), connecting/querying a remote Oracle 9i database server. Test query simply returns the current timestamp from the database server using the native Oracle 9i TIMESTAMP and/or TIMESTAMP WITH TIME ZONE datatypes. (reference: http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/server.920/a96540/sql_elements2a.htm#47732) However, the PHP/OCI8 extension does not know what to do with that datatype and appears to treat it as unknown, thus the query result shows up blank in the output. Details: ------------------ This PHP code: ############## $objDBCN = OCILogon("uid","pwd","sid"); $objSTH = OCIParse($objDBCN, "SELECT CURRENT_TIMESTAMP FROM DUAL"); OCIExecute($objSTH); %> <pre style="font-family:verdana;font-size:8.5pt"> <% OCIFetchStatement($objSTH, $objRS); echo "Column Data Type: " . OCIColumnType($objSTH, 1). "\r\n"; print_r($objRS); %> </pre> <% OCIFreeStatement($objSTH); OCILogOff($objDBCN); ########### returns this blank/unknown output: *********************** Column Data Type: 188 Array ( [CURRENT_TIMESTAMP] => Array ( [0] => ) ) *********************** ...but this PHP code (casting the timestamp datatype as a CHAR): ############## $objDBCN = OCILogon("uid","pwd","sid"); $objSTH = OCIParse($objDBCN, "SELECT TO_CHAR(CURRENT_TIMESTAMP) FROM DUAL"); OCIExecute($objSTH); %> <pre style="font-family:verdana;font-size:8.5pt"> <% OCIFetchStatement($objSTH, $objRS); echo "Column Data Type: " . OCIColumnType($objSTH, 1). "\r\n"; print_r($objRS); %> </pre> <% OCIFreeStatement($objSTH); OCILogOff($objDBCN); ########### returns good/valid output: **************** Column Data Type: VARCHAR Array ( [TO_CHAR(CURRENT_TIMESTAMP)] => Array ( [0] => 06-AUG-02 04.43.41.230000 PM +00:00 ) ) **************** Note: The datatype in the second query is correctly found as "VARCHAR", but the first reports just an integer (which means that the PHP/OCI8 code was not able to translate the "188" into something it knows about. (187 = TIMESTAMP, 188 = TIMESTAMP WITH TIME ZONE). Obviously, I can work around this problem by casting all TIMESTAMP datatypes to CHAR in my Oracle SQL statements, but the reality is that our applications use functions/"stored procs" that are shared by C code _and_ PHP, and hacking away from the native supported datatypes is not very efficient. It would be nice if the PHP/OCI8 extension could be updated to handle the TIMESTAMP datatype just as it is able to handle the DATE datatype today. ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=18758&edit=1