4

I have the following sql 

SELECT
bw_imp_step as imp_action,   
FROM cm3rm1 m1 INNER JOIN cm3rm2 m2 ON m1.number=m2.number
WHERE m1.number='$id'

when this same query is returned in DbVisualizer it returns the entire string however running this same query in my php limits the string and cuts it off towards the end.

The string that is returned is roughly 5500 characters.

Below is the php script that runs the above query:

$connection = odbc_connect("Driver={SQL  Server};Provider=SQLNCLI;Server=sname;Database=cbname;","username","password")

$sql = "SELECT
bw_imp_step as imp_action, 
FROM cm3rm1 m1 INNER JOIN cm3rm2 m2 ON m1.number=m2.number
WHERE m1.number='$id'";
$result = odbc_exec ($connection,$sql);

if ($row = odbc_fetch_array($result){
   $imp_action = $row["imp_action"];
}

When I put put $imp_action to the page or even to a file it cuts off the string at around 5000charactes.

So my question is: Does PHP limit the size of a string being returned by a resultset? I have read that php can handle a string size of 2GB and this string is not even close to that size.

Mike Causer
  • 8,196
  • 2
  • 43
  • 63
Andrew Burns
  • 346
  • 4
  • 15

2 Answers2

6

I know this is an old post, but I just ran into this too and the situation was sufficiently complex to warrant a complete answer.

There are several places where the your data could be getting truncated.

ODBC Long Read Length

ODBC has a default long read length (lrl) for large columns. Make sure the lrl for your fetch is sufficiently large, by doing one of the following:

  • Set the default in php.ini to something bigger, like odbc.defaultlrl = 10000.

  • Or use ini_set("odbc.defaultlrl", "10000"); to set it in your code before making your connection.

  • Or use odbc_longreadlen($result, "10000"); to set it in your code before fetching your result.

FreeTDS

If you're using FreeTDS to connect to an MS SQL DB from a Linux environment, there's a text size limit set in your freetds.conf file. Double check that your configuration is large enough here.

php_mssql

As in @Anujan's answer, some people (not the OP, I think) may be using php_mssql to make their connection, in which case you want to make sure your mssql.textlimit and mssql.textsize are sufficiently large, either by settings them in your php.ini or with the following in your code (before making your connection):

ini_set('mssql.textlimit', "10000");
ini_set('mssql.textsize', "10000");

SQL Server

The SQL Server itself sets a default text size and you may need to explicitly bump it up (especially if using ODBC without php_mssql and the configuration given for it above). In this case, add the following to the beginning of your query string:

SET TEXTSIZE 10000
SELECT big_ol_column FROM wherever
...

Note that some changes (like to php.ini) may require you to reload/restart PHP/Nginx/Apache to take effect.

You should be able to figure out which of these is capping your result by looking at your truncated result size. For me, everything kept coming back 4096 characters long, so I new whatever was capping me was set to that.

Also note that "10000" in the example above is not a magic number—adjust it to suit your needs.

joe_flash
  • 259
  • 1
  • 4
  • 8
1

You can set the max text size through php.ini like this

mssql.textlimit = 2147483647
mssql.textsize = 2147483647

If you don't have access to php.ini, you can just run this query.

SET TEXTSIZE 2147483647

Another option is just to use ini_set with the values like this

ini_set('mssql.textlimit', '2147483647');
ini_set('mssql.textsize', '2147483647');

If you're going to do it the ini_set way, make sure it's done before a database connection is established.

Anujan
  • 928
  • 1
  • 9
  • 20