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

Question

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");

Answer 1

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.

Answer 2

According to this, 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