Php/SQL

[jianbo]

When I use php to get data from MSSQL database, it looks like if one field contails more than 255 characters, I only can get first 255 characters. Is there any way to solve this problem? Thanks!

 

[DRJ478]

You probably have the field defined in a restrictive manner. Check your field definition in the MySQL database. VARCHAR for example has a 255 character limit. I do not beleive this is a PHP issue.

 

[jianbo]

But I can see whole content in database, I define big enough varchar field.

 

[sleipnir214]

MSSQL is squirrely when it comes to fetching large chunks of data.

On what platform are you running PHP? And how are you connecting to the database -- ODBC or native libraries?

Exactly how is the column defined in MSSQL? Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[jianbo]

My PHP is running on the Apache Server, connection like
$con = MSSQL_CONNECT($SQLServer,$SQLUser,$SQLPass); MSSQL_SELECT_DB($DBName);
the colomn defined in MSSQL is varchar(500).
Any more suggestion? Thanks!

 

[sleipnir214]

I'm sorry. I was not specific enough.

On what version of which OS?
With what version of Apache?
With what version of MSSQL?
With what version of the freetds libraries? Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[jianbo]

Apache 1.3 under windows NT 4.0
php is 4.2.3
MSSQL has both SQL 2000 and SQL 7, looks like both have this problem
I ddon't know "freetds libraries"
Do you mean specific version of Apache, SQL or OS causes this problem? thanks.

 

[sleipnir214]

FreeTDS is a library for accessing MSSQL databases from non Win32 OSes. On Win32, you use Mi¢ro$oft's libraries instead.

I don't know what might cause the problem -- I only just now found out that you're running on Win32. And that does make a difference in how PHP behaves when accessing MSSQL because PHP will use different libraries to support the mssql_*() family of functions.

But to ask the question again....Exactly how has the column in which you are storing this text been defined? Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[jianbo]

"Exactly how has the column in which you are storing this text been defined"
I thought I answered this question, is there any more detailed info?
I defined column as varchar(500), it stored data from other application.
But you reminde me that I should try to insert more than 255 characters into column with PHP, then I can at least know the problem comes from PHP or MSSQL.

 

[jianbo]

Insertion is fine. I tried with PHP to insert 450 characters into a varchar(500) field, it worked.
But I am more confused. Which one causes the problem, PHP,SQL Server or even Apache Server?

 

[sleipnir214]

were yout able to both insert the data and fetch it again? Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[jianbo]

unfortunately, it was cut into 255 characters :-(

 

[sleipnir214]

Take a look at the comments on the online manual page for mssql_query():

http://www.php.net/manual/en/function.mssql-query.php

.

There seems to be comment on the problem, and a workaround. Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[jianbo]

Thanks,sleipnir214
I found it. Post here.

spotspaperdoll at yahoo dot com

If you have difficulty using mssql_query to retrieve *long* text values stored as VARCHAR/CHAR data on your SQL server, try the following.
[This is different than retrieving beyond the 4096 character SQL TEXT data type problem (with resolution) mentioned above.]

Specifically, retrieving text values longer than 256 characters can be a problem.

For example, if you have:

SQL table "WebComments": UserID [int], UserComments [varchar(4000)]
with the data: 100, 'blah blah blah blah...going on for 3000 characters..."

Unless you're lucky, when you execute the following code:
[...]
$SQL="select UserID, UserComments from WebComments where UserID=100";
$Result=mssql_query($SQL, $LinkID);
$Data=mssql_fetch_array($Result);
echo $Data["UserComments"];

only the first 255-ish characters of the UserComments field value will be displayed.

To get around this problem, you can change your database to use SQL TEXT data types instead, but that would be a pain.

To get around this without changing your database, change your initial query to:

$SQL="select UserID, convert(text,UserComments) as UserComments from WebComments where UserID=100";

and keep the rest of the PHP code the same. Now when you execute the code, the entire UserComments field value should be displayed on your screen.

The length of the data displayed should only be limited by your SQL connection's TEXTSIZE value (that defaults to 4096 characters on SQL 7.0) as indicated in the above message regarding surpassing the 4096 character limit for SQL TEXT fields.

On a related note, if you're testing this type of thing using the SQL Query Analyzer, by default, the Query Analyzer will only show you up to 256 characters per field at a time. You may not notice any differences in your testing unless you change the Query Analyzer's display to > 256 characters. Look under Query/Current Connection Options.../Advanced/Maximum characters per column off the Menu Bar to change this value.