PHP and MSSQL - LARGE text field retrieval

Question

Here's my deal: I've got a site running PHP 5 (5.2.5 specifically) on a windows server, that's talking to a MSSQL 2005 database for it's data. I'm accessing the database using the latest version of the ADODB framework. I have a table with a unique id and a text field. This all works well and good until the text field reaches a certain size (haven't been able to determine the exact cutoff point yet)

The issue comes when I'm trying to retrieve a string from the database that's roughly 5 million character long. This retrieve works fine if I do it through the SQL Management studio and returns all the data.

The SQL server isn't returning any error message when the query is run through PHP, but it's not returning any of the actual data.

I've played around with the usual suspects, like upping the mssql.textlimit and textsize config options like so:

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

I've also tried sending a query of "set textsize 2147483647" with no change.

If I take a substring of the data I could theoretically piece it all together, but substring returns a varchar which is limted to 8000 characters and as such would require something like 875 queries to get the full amount which isn't really a great idea.

Anybody have any ideas? +

Answer 1

PHP ini has a couple variables that you'll want to look at: max_execution_time and memory_limit are two.

expansion:

I do not use ADODB, but I do deal with exceeding large records and record sets. PHP can either time out during the execution (if the query is difficult) or it can choke on the size of the result. Sometimes you get an error message, other times not. It's possible that ADODB masks these errors from the user- I don't know. The easiest way to find out if this is the case is to run the query through PHP using mssql_query() instead of the framework. If it doesn't cack, the ADODB is the problem. If it does, fiddle with php.ini.

Answer 2

Have you tried setting the field in the Database to a BLOB? or Binary? It might be better to treat it almost like a file if it's that long.

You may also want to look into Key-Value Storage systems like Voldemort or Amazon's Dynamo at this point if you will have a lot of files like this. Relational-databases don't handle such large chunks of data that well. Yes you did mention that the admin tool handles it well, but will it scale?

Answer 3

Hy this could be a option for you max_allowed_packet
EDIT:

'
mysql> SET max_allowed_packet = 2147483647;
mysql> SELECT id, comment FROM table
    -> ORDER BY comment;
'
That should work