4

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.textlimit and mssql.textsize to 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.textlimit and mssql.textsize using ini_set(). Again, this seems to have no affect.
  • setting the maxlen attribute of mssql_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 TEXT and/or changing the type attribute of mssql_bind() to SQLTEXT.

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?

Zhorov
  • 28,486
  • 6
  • 27
  • 52
tptcat
  • 3,894
  • 2
  • 32
  • 51
  • Please show us some code – Amir Apr 20 '13 at 04:52
  • @Amir - I've posted the relevant pieces of code that are involved in my issue. I have no other issues with `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 '13 at 04:56
  • in file `freetds.conf` try to find some limitation about text size. it should be something like `text size=80000` – Amir Apr 20 '13 at 06:18
  • @Amir - Unfortunately that doesn't work. I even tried changing the column to `TEXT` from `VARCHAR(MAX)`, but that didn't help. From my understanding, `text size` in freetds.conf is intended to limit what's returned from SQL Server `TEXT` values 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 '13 at 06:29
  • 1
    hope this helps. http://www.serverphorums.com/read.php?8,475844 – Amir Apr 20 '13 at 07:36
  • I had seen that link before I posted my issue and I was afraid that was going to be the answer. That seems really limiting. Thanks for the research, @Amir!. – tptcat Apr 20 '13 at 13:42
  • The information in the linked article is not quite correct: the 8000 character limitation with `EXEC` has been removed if you use the `(n)varchar(max)` data type, see the [documentation](http://msdn.microsoft.com/en-us/library/ms188332(v=sql.100).aspx) for SQL 2008. You didn't mention your FreeTDS version, but apparently [7.2 and higher support it](http://stackoverflow.com/questions/7879191/freetds-and-nvarcharmax-data). – Pondlife Apr 20 '13 at 15:49
  • @Pondlife - I've changed my `freetds.conf` file to use version 7.2 (I was previously using v8), but this didn't help. As you can see from my post above, the limitation *only* exists when using `mssql_bind()`. I can directly write: `UPDATE my_table SET my_field = 'string over 8000 chars'` and it updates fine. – tptcat Apr 22 '13 at 14:51

0 Answers0