Limitation to mssql_query return?

[irbk]

I've got an SQL DB. One field for a peticular record has about 10,000 characters in it. When I run a select query as shown here

$query = "SELECT * FROM tblSafetyContent WHERE ContentID = 2";
$subresult = @mssql_query ($query);
if ($subresult) {
 while ($row = mssql_fetch_array($subresult, MSSQL_ASSOC)) {
  echo $row['Content'];
 }
}

I'm only getting about the first 4,000 characters in the field. Anyone know of a limitation that would be preventing me from returning the entire field?

Thanks in advance.

 

[jpadie]

yes, this is a known issue.

the php manual recommends (user comments) to include this before your query

mssql_query ( 'SET TEXTSIZE 65536' , $myConn ); //where $myCon is your connection handle.
ini_set ( 'mssql.textlimit' , '65536' );
ini_set ( 'mssql.textsize' , '65536' );

 

[irbk]

Sorry for the delayed response here. I didn't look in the manual first before I posted here. My bad. I see through a phpinfo() that my host has the mssql.textlimit and textsize both set at "server default". I'm guessing this is where the issue comes in. Unfortunatly, my host will not update the PHP.ini file to something larger. So that means that I have to manually put in the informaion generiously provided by jpadie. However, I'm not really sure how I would go about doing that. Do I do something like

require_once ('mssql_connection.php');
mssql_query ('SET TEXTSIZE 65536'); //because I've already called my connection
ini_set ( 'mssql.textlimit' , '65536' );
ini_set ( 'mssql.textsize' , '65536' );
$query = "SELECT * FROM tblSafetyContent WHERE ContentID = 2";
$subresult = @mssql_query ($query);
if ($subresult) {
 while ($row = mssql_fetch_array($subresult, MSSQL_ASSOC)) {
  echo $row['Content'];
 }
}

I'll try that code but I'm not real sure how to do the inline (for lack of a better term) ini edits.

Thanks.

 

[irbk]

Well, that seemed to do something. I now see in my phpinfo() that my "Local Value" is set to 65536. However, I'm still only getting about 4,000 characters returned from my query when it should be more like 10,000. Did I do something wrong?
Any other ideas?

Thanks in advance.

 

[jpadie]

when you say field i'm taking you at your word - a column. can you tell us the data type of the affected column?

 

[irbk]

Datatype of the colum is "Text". In SQL terms, this should give me about 2^31-1 worth of storage. Or over 2 trillion characters. As I said earlier, I know all the data is getting INTO the database. (I tested this by opening up Access, created an ODBC connecton to the server, and ran a query through Access, and got everything back.) However, when I run the code shown above, I'm only getting about 4,000 characters returned.

 

[irbk]

Is it possible that even though I'm setting

mssql_query ('SET TEXTSIZE 65536');
ini_set ( 'mssql.textlimit' , '65536' );
ini_set ( 'mssql.textsize' , '65536' );

my web host is overriding the settings and only allowing me to return 4096?!?

 

[jpadie]

it's unlikely.

do you get the same result if you use an odbc connection?

my query about datatypes was in case you use nvarchar which i believe is limited to 4000 or so chars.

i'm sure that there is an easy solution to this - just can't see the wood for the trees. you might have more luck in the sql server forum.