|
|
php.net | support | documentation | report a bug | advanced search | search howto | statistics | random bug | login |
[2004-02-05 05:31 UTC] sanjok at pict dot lviv dot ua
Description: ------------ OCIFetchInto returns false as column value if table column contains more than one umlaut character while combination of OCIFetch and OCIResult functions returns correct result. Environment: OS: Linux SuSE 7.3/8.1; DB: Oracle 8.1.7/9.0.1i; PHP: 4.3.4 release Oracle NLS_LANG is set to GERMAN_AUSTRIA.UTF8 PatchesPull RequestsHistoryAllCommentsChangesGit/SVN commits
|
|||||||||||||||||||||||||||||||||||||
Copyright © 2001-2024 The PHP GroupAll rights reserved. |
Last updated: Sat Dec 21 12:01:31 2024 UTC |
<?php /* Oracle 9.0.1i database internal encoding WE8DEC database client encoding specified by NLS_LANG parameter is GERMAN_AUSTRIA.UTF8, so Oracle automatically converts characters to client encoding. CREATE TABLE TEST ( NAME CHAR(10) ); insert into test values ('?'); insert into test values ('??'); NOTE: this error doesn't occur when we use column type VARCHAR2 insted of CHAR */ ociinternaldebug(true); $connection = OCILogon(DB_USER, DB_PASS, DB_NAME); $query = "SELECT * FROM TEST"; $statement = OCIParse ($connection, $query); OCIExecute($statement); while (OCIFetchInto ($statement, $row, OCI_ASSOC+OCI_RETURN_NULLS)) { var_dump($row); } ?> result of ociinternaldebug. OCIDebug: oci_do_connect: id=3 OCIDebug: oci_parse "SELECT * FROM TEST" id=4 conn=3 array(1) { ["NAME"]=> string(11) "u " } OCIDebug: _oci_make_zval: NAME,retlen = 11,retlen4 = 0,storage_size4 = 11,indicator 10, retcode = 1406 array(1) { ["NAME"]=> bool(false) } OCIDebug: START php_rshutdown_oci OCIDebug: END php_rshutdown_oci OCIDebug: START _oci_stmt_list_dtor: id=4 last_query="SELECT * FROM TEST" OCIDebug: START _oci_column_hash_dtor: NAME OCIDebug: END _oci_column_hash_dtor: NAME OCIDebug: START _oci_conn_list_dtor: id=3 OCIDebug: nothing to do.. OCIDebug: END _oci_conn_list_dtor: id=3 OCIDebug: END _oci_stmt_list_dtor: id=4One more investigation: If I simply convert column from char to varchar2 using ALTER TABLE statement, it doesn't help. OCIFetchInto behaves correctly only if table was created with varchar2 type. But even with varchar2 column, if I fill entire column with umlauts, OCIFetchInto and OCIFetch fail. Sample: SQL> desc test; Name Null? Typ ----------------------------------------- -------- ---------------------------- NAME VARCHAR2(10) SQL> select name, length(name) from test order by name; NAME LENGTH(NAME) ------------------------------ ------------ ? 1 ?? 2 ????? 5 ?????? 6 ?????????? 10 --So you see that Oracle shows length correctly. Now I use the following php script: ociinternaldebug(true); $connection = OCILogon("????", "????", "????"); $query = "SELECT * FROM TEST order by name"; $statement = OCIParse ($connection, $query); OCIExecute($statement); while (OCIFetchInto ($statement, $row, OCI_ASSOC+OCI_RETURN_NULLS)) { var_dump($row); } OCIExecute($statement); while(OCIFetch($statement)) { var_dump (OCIResult($statement, "NAME")); } And here's result of ociinternaldebug: OCIDebug: _oci_open_server new conn=0 dname=????<br /> OCIDebug: _oci_open_session new sess=5 user=????<br /> OCIDebug: oci_do_connect: id=6<br /> OCIDebug: oci_parse "SELECT * FROM TEST order by name" id=7 conn=6<br /> array(1) { ["NAME"]=> string(2) "?" } array(1) { ["NAME"]=> string(4) "??" } array(1) { ["NAME"]=> string(10) "?????" } OCIDebug: _oci_make_zval: NAME,retlen = 10,retlen4 = 0,storage_size4 = 11,indicator 6, retcode = 1406<br /> array(1) { ["NAME"]=> bool(false) } OCIDebug: _oci_make_zval: NAME,retlen = 10,retlen4 = 0,storage_size4 = 11,indicator 10, retcode = 1406<br /> array(1) { ["NAME"]=> bool(false) } string(2) "?" string(4) "??" string(10) "?????" OCIDebug: _oci_make_zval: NAME,retlen = 10,retlen4 = 0,storage_size4 = 11,indicator 6, retcode = 1406<br /> bool(false) OCIDebug: _oci_make_zval: NAME,retlen = 10,retlen4 = 0,storage_size4 = 11,indicator 10, retcode = 1406<br /> bool(false) OCIDebug: START php_rshutdown_oci<br /> OCIDebug: END php_rshutdown_oci<br /> OCIDebug: START _oci_stmt_list_dtor: id=7 last_query="SELECT * FROM TEST order by name"<br /> OCIDebug: START _oci_column_hash_dtor: NAME<br /> OCIDebug: END _oci_column_hash_dtor: NAME<br /> OCIDebug: START _oci_conn_list_dtor: id=6<br /> OCIDebug: nothing to do..<br /> OCIDebug: END _oci_conn_list_dtor: id=6<br /> OCIDebug: END _oci_stmt_list_dtor: id=7<br /> OCIDebug: START _oci_close_session: logging-off sess=5<br /> OCIDebug: START _oci_close_server: detaching conn=4 dbname=????<br /> OCIDebug: START php_mshutdown_oci<br /> OCIDebug: END php_mshutdown_oci<br /> ------------- Some thoughts on this error: Combination of OCIFetch+OCIResult has the same problem. The error occurs when length of column value in bytes exceeds length of column defined in oracle + 1 byte. Length of column value differs from length of field for multi-byte encodings like UTF8, so probably there's an error with memory allocation (it's just a guess). And P.S.: By the way, when I change environment variable NLS_LANG to GERMAN_AUSTRIA.WE8DEC, everything's selected fine!I have digged deeper into this problem and it seems that I have found a solution, or at least a workaround for this problem. The reason for the problem is that Oracle driver raises error 1406 ("fetched column value was truncated") while the value is not really truncated, and this situation is handled incorrectly by oci8 module (what I don't understand is why SQLPlus, which uses same libs, doesn't raise this error...). However, this solution requires changes in PHP source code (oci8.c module), so this must be done by one of PHP developers. So the solution looks this way: In "ext/oci8/oci8.c" module there's a function "_oci_make_zval". In this function there's following piece of code: ------------------------ switch (column->retcode) { case 0: /* intact value */ if (column->piecewise) { size = column->retlen4; } else { size = column->retlen; } break; default: /* XXX we SHOULD maybe have a different behaviour for unknown results! */ ZVAL_FALSE(value); return 0; } ------------------------ As far as I understand, here we set size of return string in case execution completed successfully or return FALSE in other cases (like in this case of this bug). However, in case of error 1406 we have normal return, but it's set to false because retcode is not 0. If we modify this piece of code this way: ------------------------ switch (column->retcode) { case 1406: /*Protection from invalid behaviour*/ size = column->retlen; case 0: /* intact value */ if (column->piecewise) { size = column->retlen4; } else { size = column->retlen; } break; default: /* XXX we SHOULD maybe have a different behaviour for unknown results! */ ZVAL_FALSE(value); return 0; } ------------------------ everying start to work fine. It's a DIRTY solution, although it closes the bug, but it would be nice to investigate the source of this error -- why error 1406 is raised here. If this is a bug in Oracle, than this approach can be a workaround for this bug.