57918

PHP String Limitation On odbc_fetch_array

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.

Answer1:

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.

Answer2:

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.

<strong>ODBC Long Read Length</strong>

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:

    <li>

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

    </li> <li>

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

    </li> <li>

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

    </li> </ul>

    <strong>FreeTDS</strong>

    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.

    <strong>php_mssql</strong>

    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");

    <strong>SQL Server</strong>

    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.

Recommend

  • resource(3) of type (odbc result) while i am inserting data to ms access using php script
  • ODBC/MYSQL Insert a Query Result from ODBC to a databse in MYSQL
  • segfault with unixODBC (DB2) + PHP + CentOS
  • LAMP (PHP) accessing Access Database with ODBC with MDBTools Driver
  • zf2 tablegateway select columns by column name
  • In Jaspersoft Studio, Sql server procedure returning fewer fields than expected
  • SQL Error : integrity constraint violation : foreign key no parent
  • PE file - what's missing?
  • @Autowired for @ModelAttribute
  • BeautifulSoup difference between findAll and findChildren
  • Memory error in python- how to use more memory
  • Django rest serializer Breaks when data exists
  • Change multiple background-images with jQuery
  • Ajax Loaded meta Tags
  • How to rebase a series of branches?
  • Xamarin Forms - UWP Fonts
  • Using $this when not in object context
  • Android screen density dpi vs ppi
  • C# - Is there a limit to the size of an httpWebRequest stream?
  • Azure Cloud Service Web Role web pages do not load
  • Optimizing database types to compact database (SQLite)
  • TFS: Get latest causes slow project reloading
  • DirectX11 ClearRenderTargetViewback with transparent buffer?
  • Running a C# exe file
  • Arrow is showed instead of the material design version hamburger icon. Why doesn't syncState in
  • Change an a tag attribute in JavaScript based on screen width
  • what is the difference between the asp.net mvc application and asp.net web application
  • How to limit post in wp_query
  • Upload files with Ajax and Jquery
  • Do I've to free mysql result after storing it?
  • Delete MySQLi record without showing the id in the URL
  • Arrays break string types in Julia
  • A cron job substitute?
  • InvalidAuthenticityToken between subdomains when logging in with Rails app
  • json Serialization in asp
  • SQL merge duplicate rows and join values that are different
  • Hits per day in Google Big Query
  • LevelDB C iterator
  • Can't mass-assign protected attributes when import data from csv file
  • Reading document lines to the user (python)