SQL Server, PHP and Truncating Text

Posted in Blog, December 22, 2004 | 8 comments

PHP can work with a large number of database systems, most common of which is MySQL. However, SQL Server is a very poweful alternative to MySQL, and well worth considering for larger projects.

However, settings for the mssql extension in PHP can easily be overlooked, resulting in strange behaviour. One of the most unusual problems I encountered on a recent project was that every time text was pulled from a column of type "text", it was truncated at around 4000 characters.

If you are experiencing a similar problem, there are a few ways to work around it.

You need to increase the maximum size of a text column to be returned from SQL Server by PHP. You can do this with a simple SQL query:

SET TEXTSIZE 2147483647
Which you can run with the following PHP (best run just after you make a connection).

mssql_query("SET TEXTSIZE 2147483647");
A better way to work around the issue is to change the "textlimit" and "textsize" settings within php.ini, like so:

mssql.textlimit = 2147483647
mssql.textsize = 2147483647

In the above examples, I have used "2147483647" (number of bytes) for the value. You don't need to set it this high necessarily, however there is no real harm in doing so.

And why does this not happen with ASP? When you use Microsoft's SQL Server ODBC driver and OLE DB Provider, textsize is automatically set to 2147483647 when a connection is made.

Syndication

If you like this post, subscribe to my full feed or partial feed.

8 comments (Add Yours)

You can keep up to date with this discussion by subscribing to the RSS or Atom feed.

manicka prakash

India #1: September 28, 2005

after that also i was not increasing the limit . then what can i do to increase the limit from 4 kb

JORGE OROZCO

Mexico #2: July 19, 2006

manicka if u dont have a text field instead u have a varchar the limit that u still have is 255, attemp to do a cast.
myvarchar varchar(4000)

$sql="select cast (myvarcharfield as text) from mytable";
mssql_queyr($sql);

United States #3: September 5, 2006

You must edit C:\WINDOWS\php.ini and uncomment the following two lines:
; Valid range 0 - 2147483647. Default = 4096.
;mssql.textlimit = 2147483647
; Valid range 0 - 2147483647. Default = 4096.
;mssql.textsize = 2147483647

and change them to:

; Valid range 0 - 2147483647. Default = 4096.
mssql.textlimit = 2147483647

; Valid range 0 - 2147483647. Default = 4096.
mssql.textsize = 2147483647

The R

United Kingdom #4: January 8, 2007

I found this didn't make a difference with my ms sql database. Neither did changing to datatypes to text as this gave issues with other pages that were trying to update the same tables.

I managed to eliminate the 255 limit by setting up an odbc connection to the database.
First you have to set up an odbc connection to the database on the server that the db exists on, I did this using IIS. In this example the odbc when set up was given the name: odbcname.
Once you have created this on the server you need the following code(tweak to suit) in the php page/s. (All other code, for example layout and display, obviously remain the same.)

#connect to db
$conn = odbc_connect('odbcname','username','password');
if(!$conn) {exit("Err:Conn"); }

#Query
$sql = "select * from table";

#execute
$result = odbc_exec($conn, $sql);
if( !$result )
{
exit ("Could not execute Query");
}

#data retrieve
$rowodbc = odbc_fetch_array($result);

#create variables from data
$variable =. $rowodbc["column_name"];

#close connection
odbc_close( $conn );

P.S I only had to change the connection type from mssql to odbc on pages that were trying to retrieve more than 255 characters, both types will work along side each other.

Hope this helps

Eli Burmin

United States #5: January 30, 2008

had an issue where mssql.text* settings were ignored by php. explicitly setting the values with ini_set() worked fine.

Zaps

United States #6: February 4, 2008

What about truncating the displayed value but keeping the full value intact in the DB?

Thanks & I love JD too!

Anonymous

United States #7: April 15, 2008

The problem I'm experiencing is that the odbc_fetch_array (using sql server) is truncating the COLUMN name to 31 characters. Has anyone ever experienced this?

Ruben Zevallos Jr.

Brazil #8: April 30, 2008

I´m getting another problem... the VarChar fields with more than 255 chars cannot be used... so... this very usefull feature are not avaliable...