ODBC query on MS SQL Server returning first 255 characters only in PHP PDO (FreeTDS)

I'm currently trying to pull some data from a SQL Server database view that we have restricted access to from our Linux web server.

We don't need to edit the data just display it in a webpage.开发者_运维技巧

It all looks fine until we try to output and only get the first 255 characters of a text field.

Does anyone know if this is a problem with using FreeTDS through PHP::PDO or if it should work fine? I've seen other people out there having similar problems, but there don't seem to be many answers.

I'm using this as the connection string for the MS SQL db:

$dbConn = new PDO("odbc:Driver=FreeTDS;DSN=OURDSN;UID=WWWUser;PWD=ourpassword");

According to the FreeTDS User Guide, the issue seems to be that FreeTDS can only handle varchar up to 255 characters when talking to SQL Server "due to limitations inherent in the protocol definition". Anything bigger than that needs to be data type text.

You can resolve the issue either by modifying your schema accordingly, or converting the data type during your query, like this:

SELECT CAST(mycol as TEXT) FROM mytable

You can increase the size of text fields in the /etc/odbc.ini file used by FreeTDS.

[name_of_connection]
TextSize = 2097152

You can also try using the PHP low level odbc routines to make sure that you can get that level of data retrieval, then work back up to using PDO.

FreeTDS, by default, uses protocol version 4.2 If you up the protocol to 7.0 you can retrieve more than 255 bytes of a varchar. You can use the "CAST" hack, or you can ALTER COLUMN col varchar(max).

varchar(max) is a completely different column type than varchar (DATA_TYPE 2005 vs 12) and will be streamed over freetds 4.2 w/o truncating.

Why not upgrade to version 7? Because UTF-8 cannot be stored in varchar with newer protocols. SQL Server will transmit ALL protocol information in UCS-2 (like UTF-16) and convert your data into the table or column collation before saving. But, this requires you to prefix UTF8 data with N. INSERT into tbl (txt) values (N'hello world')

Why not CAST? CAST AS TEXT is not compatible with MySQL (need to do CAST AS CHAR).

Staying on protocol 4.2 and defining your varchars as varchar(max) let's you write the most compatible SQL.

One more factoid for this issue. As of 2015, returning a value of type XML results in the first 256 characters being returned cleanly. However, most of the rest of the XML will be returned as apparently random garbage, with an occasional clear fragment of text. In fact, if I had to guess, the query returns a random block of memory for all characters after 256.

In my specific case, I was generating XML (using multiple FOR XML nested queries) to send to a website for display. In this case, the solution I found was to use the CAST hack, casting the data to varchar(max).

So remember: If you see a block of 256 clear characters followed by random garbage in your query results, it is probably an XML value being returned as an XML type instead of a varchar(max) type.

CAVEAT: This may only apply if the XML is dynamically generated.