#25972 [Ana]: ODBC truncates multi-byte text (w/ MSSQL)

ID: 25972 Updated by: kalowsky@php.net Reported By: phpbug at chipple dot net Status: Analyzed Bug Type: Feature/Change Request Operating System: Win2K 5.00.2195 SP4 -PHP Version: 4.3.4RC2 +PHP Version: 4.3, 5 New Comment: moriyoshi, It's not as simple as you show it to be. First you must realize that PHP's ODBC layer is written as an ODBC v2 compliant system, to just randomly add in support for NVARCHAR (and friends) will break support for other database systems. The point of my post wasn't to say this isn't a bug (hence why I marked it as verified), but rather to say it's a known bug and the issue is the extension is in need of updating. Previous Comments: ------------------------------------------------------------------------ [2003-11-03 17:13:35] moriyoshi@php.net I might be wrong, but I think this is a valid bug. According to the documentation on msdn [1] [2], the fifth parameter of SQLBindCol() expects the size of the buffer in byte, while the code mentioned below appears to give it the number of characters allocated for the column (display size) instead. This kind of confusion will most likely cause unexpected behaviour like described in this report. php_odbc.c 669: ------------------------------------------------------ default: rc = SQLColAttributes(result->stmt, (UWORD)(i+1), SQL_COLUMN_DISPLAY_SIZE, NULL, 0, NULL, &displaysize); displaysize = displaysize <= result->longreadlen ? displaysize : result->longreadlen; result->values[i].value = (char *)emalloc(displaysize + 1); rc = SQLBindCol(result->stmt, (UWORD)(i+1), SQL_C_CHAR, result->values[i].value, displaysize + 1, &result->values[i].vallen); break; ------------------------------------------------------ If the driver supports ODBCv3, we should use SQL_DESC_OCTET_LENGTH, which can be used to determine the actual number of bytes. If not, we may be able to safely calculate the maximum column size by simply multiplying the display size by 2, as NVARCHAR columns are known to not contain multi-byte strings, but double-byte strings. [1] http://msdn.microsoft.com/library/en-us/odbc/htm/odch04pr_13.asp?frame=true [2] http://msdn.microsoft.com/library/en-us/odbc/htm/odappdpr_24.asp?frame=true ------------------------------------------------------------------------ [2003-10-31 09:26:28] kalowsky@php.net Is this really a bug? The debate rages like so: One the one hand ODBC does not support multi-byte characters at all. On the other, the idea of multi- byte characters didn't exist (or if it did, not very prevailent) at the time of the original authoring of the ODBC system. Next question is, is there an easy fix. Nope. I've had a fix in the works, I've just lost all time to work upon it. Marking as verified, but not really sure it's a bug. Maybe a feature request really... Following the ODBCv2 specs, NVARCHAR is a type that doesn't exist, nor does TEXT, NTEXT, and many of the other wonderful types found today. ------------------------------------------------------------------------ [2003-10-24 00:10:01] phpbug at chipple dot net Sorry here's the MSSQL extension code that should have been in the 2nd part of "Reproduce code". // MSSQL EXTENSION, data retrieved correctly $oConn = mssql_connect("localhost",C_Gen_sDbUser,C_Gen_sDbPassword); mssql_select_db("icds",$oConn); $oRs = mssql_query("SELECT * FROM T_Course WHERE aCourseID=1"); $aRow = mssql_fetch_array($oRs); // GOOD: Complete title retrieved (60 chars=120 bytes) echo $aRow["tTitle"]; mssql_close($oConn); ------------------------------------------------------------------------ [2003-10-23 23:26:31] phpbug at chipple dot net Description: ------------ This bug has been observed with PHP 4.3.3 and 4.3.4RC2. Database: MSSQL 2000 (8.00.760) SP3 I have a MSSQL database with a table containing a column tTitle of type nvarchar(80) (which stands for 80 multi-byte characters). When a string of 60 Japanese double-byte characters (120 bytes) stored in column tTitle is retrieved using PHP's ODBC extension, the value is truncated to 80 bytes. The PHP MSSQL extension retrieves the data correctly. Microsoft's ODBC driver (used from ASP) retrieves the data correctly. MSSQL table structure: CREATE TABLE [dbo].[T_Course] ( [aCourseID] [int] IDENTITY (1, 1) NOT NULL , [tTitle] [nvarchar] (80) COLLATE Japanese_CI_AS NOT NULL ) ON [PRIMARY] GO Test data (CSV): aCourseID,tTitle 1,[string of 60 Japanese double-byte characters] SQL query: SELECT * FROM T_Course WHERE aCourseID=1 Reproduce code: --------------- // ODBC EXTENSION, data truncated $oConn = odbc_connect(C_Gen_sDbDSN,C_Gen_sDbUser,C_Gen_sDbPassword); $oRs = odbc_exec($oConn,"SELECT * FROM T_Course WHERE aCourseID=1"); $aRow = odbc_fetch_array($oRs); // BAD: Title truncated to 80 _bytes_ echo $aRow["tTitle"]; odbc_close($oConn); // MSSQL EXTENSION, data retrieved correctly $oConn = odbc_connect(C_Gen_sDbDSN,C_Gen_sDbUser,C_Gen_sDbPassword); $oRs = odbc_exec($oConn,"SELECT * FROM T_Course WHERE aCourseID=1"); $aRow = odbc_fetch_array($oRs); // GOOD: Complete title retrieved (60 chars=120 bytes) echo $aRow["tTitle"]; odbc_close($oConn); Expected result: ---------------- The ODBC extension should truncate the retrieved data to 80 characters (instead of 80 bytes). Actual result: -------------- The ODBC extension truncates the retrieved data to 80 bytes. ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=25972&edit=1