From: duh at dowebwedo dot com Operating system: Debian GNU/Linux 3.0 PHP version: 4.3.4RC1 PHP Bug Type: MSSQL related Bug description: char and varchar fields are being rtrimmed (and also ltrimmed?) using freetds
Description: ------------ I am busy developing a new improved version of our intranet running on Apache/php/Debian and moved in this version from ODBC to MSSQL/Sybase connections (ODBC gave a lot of overhead and appeared being quite slow). In several intranet functions we aquire data from the Exact financial suite (http://www.exactsoftware.com) which is largely used in The Netherlands and abroad and which currently uses MSSQL as a database backend. In the most recent versions of exact you can still see their MS-DOS history (exact used btrieve and several other MS-DOS databases in the old days) because several columns are still padded to the maximum column width. Hence the word "hi" in a varchar(8) would be stored as "hi " (hi+6 spaces). Now when using mssql_* functions in php over freetds all selected values are right trimmed, so SELECT hi FROM table will return "hi" instead of the actual data in the table "hi ". I have currently only tried selecting, i don't know what happens when inserting (probably the same?). Obviously, this is not what I want since this would lead to data inconsistency (in your financial system!) and an unuseable financial system (which ofcourse is the worst that could happen to a company). At first I thought it was due to the fact that sybase used to right trim these values so freetds does it as well for compatibility's sake. But when I executed a query command line through the tsql (/usr/local/bin/tsql) application it appeared that then the values were NOT being right trimmed. So appearantly the interface between freetds and my application (which in my opinion can only be php) does the trimming of values with spaces. Ofcourse one could say that I need to trim or append spaces to these values myself, but since most data and software is dynamic and only some (var)char fields will get appended and some don't, there is no way of telling which columns should be appended (or prepended) and which shouldn't. For the sake of data consistency I would either like to see this bug fixed, or -if it is no bug but a feature- see a configuration option being introduced to overrule this trimming. Reproduce code: --------------- In php the following code will return trimmed values: $db = mssql_connect('server','user','pass'); $result= mssql_query("SELECT TOP 1 medewerker FROM Efw_001.[dbo].Prres1 WHERE medewerker IS NOT NULL AND medewerker LIKE '% %'"); $a = mssql_fetch_assoc($result); print_r($a); but commandline this values are not being trimmed: # tsql -S server -U user -P pass locale is "C" charset is "ANSI_X3.4-1968" Msg 5703, Level 0, State 1, Server NTS1, Line 0 Changed language setting to us_english. 1> SELECT TOP 1 medewerker FROM Efw_001.[dbo].Prres1 WHERE medewerker IS NOT NULL AND medewerker LIKE '% %' 2> go medewerker '100 ' (size=8) 1> SELECT TOP 1 rtrim(medewerker) as trimmed_medewerker FROM Efw_001.[dbo].Prres1 WHERE medewerker IS NOT NULL AND medewerker LIKE '% %' 2> go trimmed_medewerker '100' (size=3) (I added single quotes around the results and wrote the sizes behind it so you can see the difference between the two queries) Expected result: ---------------- Obviously I expect to get exactly the same data that is stored in the database instead of modified (trimmed) data. -- Edit bug report at http://bugs.php.net/?id=25777&edit=1 -- Try a CVS snapshot (php4): http://bugs.php.net/fix.php?id=25777&r=trysnapshot4 Try a CVS snapshot (php5): http://bugs.php.net/fix.php?id=25777&r=trysnapshot5 Fixed in CVS: http://bugs.php.net/fix.php?id=25777&r=fixedcvs Fixed in release: http://bugs.php.net/fix.php?id=25777&r=alreadyfixed Need backtrace: http://bugs.php.net/fix.php?id=25777&r=needtrace Try newer version: http://bugs.php.net/fix.php?id=25777&r=oldversion Not developer issue: http://bugs.php.net/fix.php?id=25777&r=support Expected behavior: http://bugs.php.net/fix.php?id=25777&r=notwrong Not enough info: http://bugs.php.net/fix.php?id=25777&r=notenoughinfo Submitted twice: http://bugs.php.net/fix.php?id=25777&r=submittedtwice register_globals: http://bugs.php.net/fix.php?id=25777&r=globals PHP 3 support discontinued: http://bugs.php.net/fix.php?id=25777&r=php3 Daylight Savings: http://bugs.php.net/fix.php?id=25777&r=dst IIS Stability: http://bugs.php.net/fix.php?id=25777&r=isapi Install GNU Sed: http://bugs.php.net/fix.php?id=25777&r=gnused Floating point limitations: http://bugs.php.net/fix.php?id=25777&r=float
