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? +

Is ADODB truncating the response? Is that the problem you're trying to overcome? — Neel
– Neel, Commented Oct 1, 2009 at 11:37

streetparade · Accepted Answer · 2009-10-05 19:54:31Z

1

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

edited Oct 5, 2009 at 19:54

answered Oct 5, 2009 at 19:48

streetparade

33k39 gold badges106 silver badges123 bronze badges

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

1 Comment

streetparade Over a year ago

Also have a look at this website cvs.moodle.org/moodle/lib/adodb/adodb-perf.inc.php?view=co

dnagirl · Accepted Answer · 2009-10-01 17:46:33Z

0

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.

edited Oct 1, 2009 at 17:46

answered Sep 30, 2009 at 18:23

dnagirl

20.5k13 gold badges86 silver badges127 bronze badges

1 Comment

Neel Over a year ago

I don't think that addresses the issue in the question. The problem seems to be that ADODB is truncating the response. Some clarification here would probably be useful here.

Gus · Accepted Answer · 2009-10-02 15:37:55Z

0

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?

answered Oct 2, 2009 at 15:37

Gus

1661 silver badge9 bronze badges

Collectives™ on Stack Overflow

PHP and MSSQL - LARGE text field retrieval

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related