I'm using FreeTDS to connect and work with a SQL Server 2008 database from PHP 5.3.15 on Linux. I have a column in this database that has a datatype of VARCHAR(MAX).
Unfortunately, I'm having trouble setting the value of the column via mssql_bind() when the value is is over 8000 characters. The value gets stored in the database, but is truncated at 8000 characters. The stored procedure that I'm calling has set the input variable as the proper datatype (VARCHAR(MAX)).
Here is what I've tried:
- in php.ini I have set both
mssql.textlimitandmssql.textsizeto their maximum allowed values (2147483647). It seems that no matter what I set these to they actually have no affect on the size of the text I can send through. That is, even setting these to a value of, say, 100, does not truncate the text at 100 characters. - setting
mssql.textlimitandmssql.textsizeusingini_set(). Again, this seems to have no affect. - setting the
maxlenattribute ofmssql_bind()to a number higher than 8000. As with the above, even setting this to a lower number seems to have no affect. - changing the datatype of the column to
TEXTand/or changing thetypeattribute ofmssql_bind()toSQLTEXT.
Additionally, to make matters more confusing (to me at least), I can get the entire string length to be stored in the database through the following means:
- in my stored procedure hard coding the length of the value of a variable declared as
VARCHAR(MAX)to more than 8000 characters. This successfully stores the entire string. - not using a stored procedure, but instead just executing a query directly in PHP as:
"UPDATE my_table SET my_large_colmn = '{$text_longer_than_8000_chars}'". This successfully stores the entire string.
So this is how I believe I've narrowed it down to the mssql_bind() method truncating the length to 8000 characters.
Is this simply a limitation of mssql_bind() and the FreeTDS driver in PHP or am I missing something?
mssql_bind()other than this truncation issue. My stored procedures all work fine (I have hundreds of them). I could post code of my stored procedure, or of the way that I'm calling the stored procedure in PHP, but I'm confident that this won't give you any more relevant information than what I've posted above. – tptcat Apr 20 at 4:56freetds.conftry to find some limitation about text size. it should be something liketext size=80000– Amir Apr 20 at 6:18TEXTfromVARCHAR(MAX), but that didn't help. From my understanding,text sizein freetds.conf is intended to limit what's returned from SQL ServerTEXTvalues because of inconsistent implementation in the default length of this type of column (the FreeTDS docs say that sometimes this get returned as 4GB so setting this to a sane value could prevent such a thing from occurring). – tptcat Apr 20 at 6:29