Data auto truncated by MSSQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
phpwalker
Forum Commoner
Posts: 81
Joined: Sun Apr 23, 2006 12:18 pm

Data auto truncated by MSSQL

Post by phpwalker »

Hi all, when i retrieved data from MSSQL database, it was truncated into 4096 character instead of it's full length of data which is >80k char. How can i fix this? I use mssql_fetch_array() to retrieve the data. I've no problems inserting the data, but problems occurred during retrieving it.


Thanks in advance.
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Re: Data auto truncated by MSSQL

Post by Zoxive »

The size of the field is set to small, try increasing it.

I'm guessing its set manually, or tiny text/tiny blob.

Normal Text is roughly 60k, so Long Text/Blob should suffice.
phpwalker
Forum Commoner
Posts: 81
Joined: Sun Apr 23, 2006 12:18 pm

Re: Data auto truncated by MSSQL

Post by phpwalker »

No, it was stored 80k characters in IMAGE data type, but when I retrieve it, it was truncated into 4093 chars only.

I've do the following setting:

in php.ini, i've set these two to
mssql.textsize = 2147483647
mssql.textlimit = 2147483647
and in my code, i've run this as well

Code: Select all

$sql = "SET TEXTSIZE 2147483647";
@mssql_query($sql, $conn);  


No problem when i insert the data(80k characters), just when i retrieve it using SELECT statement, the data was auto truncated to 4k characters. Anyone???
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Re: Data auto truncated by MSSQL

Post by Zoxive »

You should be able to look at the Warnings, or errors log. It should be spitting out those when inserting the data. (But the data will still go in, just to the longest length allowed)

Side note, databases are the wrong place to store images. Images belong on the file system.


(I also just noticed this is MSSQL, not Mysql which is am familiar with, so this all could be iirrelevant.)
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Data auto truncated by MSSQL

Post by Benjamin »

You mention MSSQL which I have no experience with.

This should come in handy regardless: http://help.scibit.com/Mascon/masconMyS ... Types.html

They are more than likely somewhat similar to MSSQL.
phpwalker
Forum Commoner
Posts: 81
Joined: Sun Apr 23, 2006 12:18 pm

Re: Data auto truncated by MSSQL

Post by phpwalker »

Thanks everyone, I've finally solved that.

Actually the answer is where I mention in previous post
mssql.textsize = 2147483647
mssql.textlimit = 2147483647
Somehow, don't know why it can't work if modify it in php.ini. After changing to use ini_set(_above_mention_) in the script, it only works like a charm. Thanks all.

MSSQL information really lesser and more difficult to find compare to MYSQL, hope more people can share the knowledge.

Ciaos~
Post Reply