Cannot display entire field contents from DB.

[seahorse123]

I have text contents that is saved in the table column "message varchar 5000"
However when I tried to retrieve it, it only display the first 255 characters:

$query="select message from tb1 where id=1";
$result=mssql_query($query);
for ($i=0;$i < mssql_num_rows($result); ++$i)
{
$line=mssql_fetch_row($result);
$message=$line[0];
print "$message";
}

I don't why it only displays the part of text, anyone can help me? thanks.

 

[amrita418]

If you were running the query in query analyzer then you can update the Maximum Characters Per Column option under Tools/Options/Results Pane, but not really sure how to set that at connection level. Try that and see what you get.

Regards,
AA

 

[hmckillop]

What is $message declared as? Have you tried printing
"print $line[0];" to see what it returns?


"I'm living so far beyond my income that we may almost be said to be living apart

 

[seahorse123]

Yes, I tried "print $line[0];", it still cut the text to around 255 characters.

Any idea?

 

[SQLBill]

1. are you sure the column is set for a length of 5000?

2. what are you using as a front-end, could that be where the data is being shortened?

-SQLBill

Posting advice: FAQ481-4875

 

[seahorse123]

I use Microsoft SQL server Enterprise Manager to build the table.
1) Yes, I have set the column "message varchar 5000".
2) If I directly go to "SQL server Enterprise Manager-->Console Root-->SQL server group-->tables", and open table, I can see the column "message" has more contents(though I'm not sure it's around 5000, but at least much large than 255 characters)

The weird thing is when I use SELECT statement, it just cuts around 255 characters.

 

[SQLBill]

Ahhh...that explains it....

I've seen somewhere else that Enterprise Manager only shows 255 characters, no matter what.

-SQLBill

Posting advice: FAQ481-4875

 

[seahorse123]

So what is the solution to display text that has 255 or more characters?
As the data is alreay stored in the table, only issue is how to display that column.

 

[jbenson001]

amrita418 has the answer in a previous post.. there are settings in QA

 

[SQLBill]

I would use a front-end that is designed to do queries (Enterprise Manager, while it CAN do queries, is really to MANAGE SQL Server). As jbenson001 says, use Query Analyzer. Either get to it via Start>Programs or in Enterprise Manager, highlight your database, click on Tools in the menu bar, and select Query Analyzer.

-SQLBill

Posting advice: FAQ481-4875

 

[Wholsea]

Extract the data contents to a file, you can do this by right clicking on the table and exporting the data to a flat file. I believe you should be able to punch in your SQL query to select specific data so you do not have to weed through all the records in the table to find the one you are looking for...

ASP, VB, VBS, Cold Fusion, SQL, DTS, T-SQL, PL-SQL, IBM-MQ, Crystal Reports, Crystal Enterprise

 

[seahorse123]

I went to Query Analyzer and changed "Maximum Characters Per Column" to 8192, when I run SELECT statement from "Query Analyzer", it shows correct text(more than 255 characters), but still doesn't display correctly on the browser.
I don't think it's caused by Query Analyzer setting.

$query="select message from tb1 where id=1";
$result=mssql_query($query);
for ($i=0;$i < mssql_num_rows($result); ++$i)
{
$line=mssql_fetch_row($result);
$message=$line[0];
print "$message";
}

I doubt if "mssql_fetch_row()" cuts the text?

 

[hmckillop]

sounds like an odbc datatype problem.

if the data is fine in the database and you are positive the variables are the correct length, and the resultset is not showing the correct data, it must be the field types that driver is setting.

in php is there the equivalent parameters as there is in adodb. if so, you can create a stored procedure which receives and id and returns an output parameter containing the message in correct datatype. This should mean any problems with the driver assigning the incorrect datatype is no longer a problem?

"I'm living so far beyond my income that we may almost be said to be living apart

 

[seahorse123]

Finally I found the solution.
If I use "select message from tb1", then mssql_query() only take first 255 characters, so I have change "varchar 5000" to "text" type, or do this:

SELECT CONVERT(TEXT message) AS message FROM tb1

then it works fine.