PHP script is truncating text field to 4096 characters when connecting to SQL Server 2005 & 2016 via ODBC / FreeTDS

Question

I'm using FreeTDS / ODBC to connect to an SQLServer2005 database from CentOS7 using PHP. It's working well, except for one field--a large "note" text field that is getting truncated at 4096 characters. How can I get my PHP odbc_exec query to retrieve the entire text field?

I'm in the process of migrating the database from SQLServer2005 to SQLServer2016, but the text field truncating issue seems to behave the same way on both.

I tried setting the textsize within the query itself:

SET TEXTSIZE 2147483647; SELECT note FROM soap WHERE organizationId=41 and patientId=2019 AND noteId=189

I have tested the above connection & query in isql with my odbc/freetds settings & it does seem to retrieve the entire text field correctly. Here is part of the output from my freetds.log:

17:30:50.367355 29543 (token.c:1542):tds7_get_data_info:
        colname = note
        type = 35 (text)
        server's type = 35 (text)
        column_varint_size = 4
        column_size = 2147483647 (2147483647 on server)

I have tried setting the text size in the odbc.ini file:

[ClaimsServiceNewTest]
Driver                  = /usr/lib64/libtdsodbc.so.0
Description             = Advanced Billing Service Database (TESTING OLD SQL)
Trace                   = Yes
TraceFile               =/tmp/sql.log
ForceTrace              = Yes
SERVERNAME              = ClaimsServiceNewTest
User                    = ********
Password                = ********
Database                = ********
Port                    = 1433
TDS_Version             = 7.1
TextSize                = 2147483647

I have also set the text size in the freetds.conf file:

 # If you get out-of-memory errors, it may mean that your client
        # is trying to allocate a huge buffer for a TEXT field.
        # Try setting 'text size' to a more reasonable limit
        text size = 4294967295

It is only when I try to run the query in my PHP script that it get's truncated.

Any suggestions? I am fairly new to all of this, so please let me know if I'm missing something obvious. I also noticed that isql is outputting a TDSDUMP to my freetds.log file, but my PHP queries are not--I don't know if this is expected behavior, but if you have suggestions on outputting TDSDUMP from my PHP queries, I imagine that it would have additional useful info.

Thanks for the consideration!

EDIT: One great suggestion by Mark Thomas was to change the mssql.textlimit and mssql.textsize in php.ini. I've changed this in the php.ini file (and also done it in-script with: ini_set('mssql.textlimit', 10000000); , but I don't see that it has made a difference (restarting apache after changes to php.ini should be sufficient, right?).

Unrelated, but you might want to consider upgrading to a supported version of sql server. — HoneyBadger
– HoneyBadger, Commented May 1, 2019 at 7:15
Thanks--you reminded me to mention that as part of this migration, I'm actually going to be moving the database to SQL Server Management Studio v17.9.1. I have a test database setup in 17.9.1 and it seems to be working, but I haven't pulled the trigger on that part of the migration yet. The text truncating issue behaves the same when connecting to the new 17.9.1 database. — dmbean
– dmbean, Commented May 1, 2019 at 7:59
Er... SQL Server Management Studio is not a database server at all :-? Whatever, you may want to try odbc_binmode(..., ODBC_BINMODE_RETURN). That fixed the problem for me when using another DBMS (Oracle). — Álvaro González
– Álvaro González, Commented May 1, 2019 at 8:07
Ha, I've been up too long--the new database is SQL Server 2016 (13.0.5081.1). I've just tried your odbc_binmode suggestion and didn't immediately get good results (it seems to be returning '1' so far), but I appreciate the suggestion and I'll explore it a bit more. — dmbean
– dmbean, Commented May 1, 2019 at 8:32
I seem to recall having to cast the field as varchar(max). Try cast( varchar(max), note ) as note in place of note in your query. — serverSentinel
– serverSentinel, Commented May 1, 2019 at 20:22

dmbean · Accepted Answer · 2019-05-01 22:01:26Z

2

Thanks to Mark Thomas' answer, I was able to (eventually) find the solution. In php.ini, the handling of LONG fields by ODBC needed to be changed from the default of odbc.defaultlrl = 4096 to a higher value--I used odbc.defaultlrl = 2000000

Problem solved!

Thanks again for all of the assistance and pointing me in the right directions.

answered May 1, 2019 at 22:01

dmbean

415 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mark Thomas · Accepted Answer · 2019-05-01 08:20:34Z

0

I'm a little rusty with PHP, but i think there is a default limit that can come into play when connecting to certain data types like in your example.

This previous posting here discusses it and has a solution that might help by checking the default value in the php.ini file for mssql text file sizes: mssql_fetch_object text character limit?

answered May 1, 2019 at 8:20

Mark Thomas

261 silver badge2 bronze badges

1 Comment

dmbean Over a year ago

Thanks for the suggestion! I've changed the mssql.textlimit and mssql.textsize values both in my php.ini file, as well as in-script with ini_set , but no dice so far.

Collectives™ on Stack Overflow

PHP script is truncating text field to 4096 characters when connecting to SQL Server 2005 & 2016 via ODBC / FreeTDS

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related