I'm using PHP, SQL Server 2012, and FreeTDS. With TDS version = 7.4, all of my VARCHAR fields are being truncated to 255 characters.
I've read that this happens with TDS versions less than 7.0, so that does not apply to this configuration.
This was working correctly when we were using TDS version 7.1, but our hosting provider seems to have changed something (still working on figuring that out) and now we're unable to connect with 7.1 for some reason.
Here is the output of tsql -C:
Compile-time settings (established with the "configure" script)
Version: freetds v1.00.27
freetds.conf directory: /usr/local/etc
MS db-lib source compatibility: no
Sybase binary compatibility: no
Thread safety: yes
iconv library: yes
TDS version: auto
iODBC: no
unixodbc: yes
SSPI "trusted" logins: no
Kerberos: no
OpenSSL: yes
GnuTLS: no
MARS: no
And here is my freetds.conf:
[global]
tds version = 7.4
text size = 2147483647
[my-server-name]
host = my-server-name
port = 1433
tds version = 7.4
instance = my-db-instance
I'm connecting via PHP (using the old mssql_* functions) using the same value I used for my-server-name. I've verified that it is using that configuration by changing the tds version to 7.1, with which the connection does not work.
I was previously using freetds v0.91 and I downloaded the latest stable version and followed the build instructions on their website to update, but it did not help the issue.
There is a very large number of tables, queries, stored procedures, and functions in this code base, so updating all calls to these VARCHAR fields to use something like a CAST(column AS TEXT) is not an option.
Sample code that shows the issue for me is as follows:
<?php
$conn = mssql_connect('my-server-name', 'my-username', 'my-password');
mssql_select_db('my-database', $conn);
$result = mssql_query("SELECT longFieldName FROM myTable");
while ($row = mssql_fetch_array($result, MSSQL_ASSOC)) {
var_dump($row);
}
And this is pulling from a table that was created as:
CREATE TABLE myTable (longFieldName VARCHAR(2000));
INSERT INTO myTable (longFieldName) VALUES ('Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.');
When I run the PHP code, I expect the full string to be dumped, but instead, it outputs the following string:
array(1) {
'longFieldName' =>
string(255) "Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor i"
}
What is causing these fields to be truncated at 255 characters?
It appears this may be specific to PHP. I tried connecting using tsql and I did the query and got the full string back. I'm using PHP 5.5.15 (cli), I have xdebug set up with the following configuration:
[xdebug]
zend_extension="/usr/local/lib/xdebug.so"
xdebug.var_display_max_children = -1
xdebug.var_display_max_data = -1
xdebug.var_display_max_depth = -1
xdebug.remote_port = 9000
xdebug.remote_enable = 1
xdebug.remote_connect_back = 1
And the following MSSQL configuration:
[MSSQL]
mssql.allow_persistent = On
mssql.max_persistent = -1
mssql.max_links = -1
mssql.min_error_severity = 10
mssql.min_message_severity = 10
mssql.compatability_mode = Off
mssql.connect_timeout = 5
mssql.timeout = 6000000000
mssql.textsize = 2147483647
mssql.secure_connection = Off
I do not see this issue with version 7.0. So to summarize what happens with each version:
I believe I've figured out the truncation issue.
While I attempted to update my FreeTDS version, I actually installed a second version of FreeTDS, and that is what was being used for tsql -C. But when I enabled logging by setting dump file = /tmp/freetds.log in my freetds.conf and then ran a test and looked at the log, the first line says:
Starting log file for FreeTDS 0.91
So PHP is actually using FreeTDS version 0.91. And according to this answer, 0.91 only supports up to TDS version 7.2.
So when I specified 7.3 or 7.4, I believe it was defaulting to an older version, which is confirmed by another line lower in the log file:
Connecting to 172.19.4.129 port 1433 (TDS version 4.2)
So this is the reason for the truncation issue. I'll have to update my version of FreeTDS that PHP is using.