mssql select on varchar(1024) returns only 255 chars

Hermann_Auer

Hi,

in an mssql database a field varchar(1024) is defined, and there are rows with field contents more than 300 bytes.

When I read it under php with an mssql select statement, I get only the leftmost 255 Bytes.

Example:
$sql1 = "SELECT RequisitionDesc FROM tSL_RequisitionDesc WHERE RequisitionCode = '02/220 - RMA' AND TextModuleTypeCodeID = 1541;";
$s_query = mssql_query($sql1);
if ( $s_result = mssql_fetch_row($s_query) )...results in
.... strlen($s_result) == 255 even though the 1st row's RequisitionDesc contains "#Unterstützung der Anwendungsberatung bei ... " (356 Bytes).

Does anybody know this effect? What must I do to get the full field contents? Is there any configuration to be done?

Regards and thanks: Hermann

piersk

Thats because the most you can store in varchar is 255 characters. Try text instead of varchar...

Hermann_Auer

Originally posted by piersk
Thats because the most you can store in varchar is 255 characters. Try text instead of varchar...

Hi piersk,

is this a special limitation of MSSQL only?

In the SQL Server Enterprice Manager, I can see the full 356 bytes.

I don't understand on which level the truncation happens. 😕

Thanks: Hermann

piersk

I think it is when using PHP, but I will check for you (dont have any books with me now cos im at work) and post here tonight.

mattr

You can store more than 255 characters in MS SQL, Sybase, Oracle, etc.

The 255 character limit was a self-imposed limit on character fields that most RDBMS' adopted but have since dropped in favor of wider varchars.

I don't see how your statement will execute though. MS SQL does not terminate commands with a semicolon yet you are sending it one. Do not send a semicolon at the end of your SQL string.

Try CAST-ing it to a TEXT column. Perhaps the PHP MS SQL libs are hard-coded to limiting character fields to 255 characters but not limit TEXT columns.

tsoft

Use "SELECT convert(text, RequisitionDesc) as req ..." instead. This will get you up to 4096 characters which seems to be another bizarre PHP/SQL Server limit.

Tony Rogers

netbros

Are there any plans to FIX the supposed limit imposed by either SQL or PHP? I need to store a fair amount of data in MS-SQL and can't seem to get more than 3000 characters at a time from a text datatype... which is pretty pointless and almost renders the entire PHP/MS-SQL libraries useless...

...anybody? 😕

tsoft

You can get any data you want from a Text field but you have to change some values in your php.ini first. Try

mssql.textlimit = 65535
mssql.textsize = 65535

Make sure you remove the ';' comment character at the beginning of each line.
You may need to restart your web server to see these changes.

If you can't get at your php.ini file, use ini_set() instead.

Hope this helps.

Tony Rogers

netbros

Worked! Thanks so much! 🙂

You wouldn't happen to know how to get MS-SQL to return all datetime fields in a specific format without calling CONVERT() each time would you? 🙂

I'm looking to format all returned dates as YYYY-MM-DD HH:MI:SS

Thanks again! 🙂

-- J

tsoft

Sorry, I always store my dates/times as int for epoch seconds to avoid the problem.

Tony Rogers