Advice Needed : Nasty Problem PHP/MS SQL Server and Varchar fields > 255 in Length

[James]

I am currently working on a PHP based website that needs to be able to draw
from Oracle, MS SQL Server, MySQL and given time and demand other RDBMS. I
took a lot of time and care creating a flexible and solid wrapper and am
deep into coding. The only problem is a noticed VARCHAR fields being drawn
from SQL Server 2000 are being truncated to 255 characters.

I searched around php.net and found the following :

Note to Win32 Users: Due to a limitation in the underlying API used by PHP
(MS DbLib C API), the length of VARCHAR fields is limited to 255. If you
need to store more data, use a TEXT field instead.
(http://www.php.net/manual/en/functio...eld-length.php)

The only problem with this advice is Text fields seem to be limited to 16
characters in length, and I am having similar results in terms of truncation
with other character based fields that can store more than 255 characters.

I am using PHP 4.3.3 running on IIS using the php_mssql.dll extensions and
the functions referenced here http://www.php.net/manual/en/ref.mssql.php.
What are my options here? Has anybody worked around this or am I missing
something obvious?

James

[Chung Leong]

No, text fields can handle text string up to 2-Gig. The 16 bytes refers to
the space used up by the string pointer inside the record. The problem with
text/ntext is that they're slow, and you can't use them in ORDER BY or GROUP
BY.

The superlame way of retrieving more than 255 chars from a MSSQL varchar is
to retrieve the field in multiple sections, using the SUBSTR() function,
then concatenate them in PHP:

SELECT SUBSTR(msg, 1, 255) AS msg_1, SUBSTR(msg, 256, 255) AS msg_2, ....

$msg = $row['msg_1'] . $row['msg_2'] . $row['msg_3'] ...

Uzytkownik "James" <ja**************************************@hotmail. com>
napisal w wiadomosci news:40**********@newspeer2.tds.net...

I am currently working on a PHP based website that needs to be able to draw from Oracle, MS SQL Server, MySQL and given time and demand other RDBMS. I took a lot of time and care creating a flexible and solid wrapper and am
deep into coding. The only problem is a noticed VARCHAR fields being drawn from SQL Server 2000 are being truncated to 255 characters.

I searched around php.net and found the following :

Note to Win32 Users: Due to a limitation in the underlying API used by PHP
(MS DbLib C API), the length of VARCHAR fields is limited to 255. If you
need to store more data, use a TEXT field instead.
(http://www.php.net/manual/en/functio...eld-length.php)

The only problem with this advice is Text fields seem to be limited to 16
characters in length, and I am having similar results in terms of truncation with other character based fields that can store more than 255 characters.

I am using PHP 4.3.3 running on IIS using the php_mssql.dll extensions and
the functions referenced here http://www.php.net/manual/en/ref.mssql.php.
What are my options here? Has anybody worked around this or am I missing
something obvious?

James

[Erland Sommarskog]

James (ja**************************************@hotmail. com) writes:

The only problem is a noticed VARCHAR fields being drawn
from SQL Server 2000 are being truncated to 255 characters.

I searched around php.net and found the following :

Note to Win32 Users: Due to a limitation in the underlying API used by PHP
(MS DbLib C API), the length of VARCHAR fields is limited to 255. If you
need to store more data, use a TEXT field instead.
(http://www.php.net/manual/en/functio...eld-length.php)

The only problem with this advice is Text fields seem to be limited to
16 characters in length, and I am having similar results in terms of
truncation with other character based fields that can store more than
255 characters.

As pointed out by Chung Leong, there is room for 2GB of data in text.
The 16 bytes you see is just a pointer.

However, text is fairly cumbersome and not really easy to use. I don't
know anything about PHP, but it's apparent that PHP uses DB-Library to
access SQL Server. And while I think this is a very nice API, Microsoft
does not think so, and has not developed DB-Library since the release of
SQL 6.5, which was seven years ago. The next version of SQL Server, slated
for release this year, will accept connections from DB-Library, but will
not come with files needed for development. You may not even get the DB-
Library run-time DLL:s, but have to find them elsewhere.

Thus, there are all reasons to look into alternative means of connections
for PHP to MS SQL Server.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[James]

"Erland Sommarskog" <so****@algonet.se> wrote in message

Note to Win32 Users: Due to a limitation in the underlying API used by PHP (MS DbLib C API), the length of VARCHAR fields is limited to 255. If you
need to store more data, use a TEXT field instead.
(http://www.php.net/manual/en/functio...eld-length.php)

The only problem with this advice is Text fields seem to be limited to
16 characters in length, and I am having similar results in terms of
truncation with other character based fields that can store more than
255 characters.

As pointed out by Chung Leong, there is room for 2GB of data in text.
The 16 bytes you see is just a pointer.

Thus, there are all reasons to look into alternative means of connections
for PHP to MS SQL Server.

I believe the Linux PHP builds use that alternate means and the Warning
(given only on one page and not the main PHP MS SQL Server driver page as it
should have been) -- I'll be testing that later on today.

Retrieving a field in chunks may not be such a big issue, it is rare that
this will happen frequently in the app however the possibility is there on
some 60% of the fields and I do need to be able to Group and Order By on the
majority of these fields. I may just have to not support the MS SQL Server
at this time -- the goal was an app that could run on Linux and Windows
webservers and connect to just about any DB alive - its a shame that one of
the staple db's is so poorly supported by the PHP project given that I am
tied to it. My work is with non-profits and we have to be able to utilize
the licensing they already have so demanding the use of particular software
will only drive the pricing up.

Strangley I had similar issues when working with ASP and SQL Server in the
past - fields would simply not show up sometimes if they where over 255
characters in length and it is a known and documented issue that affects
'certain databases' according to MS but I have only ever seen with the SQL
Server and MSDE - never Oracle, MySQL, Postgre or even Access -- its a shame
I like the SQL Server but it seems that everytime I get contracted to use it
with a web back end I run into problems that make it a royal pain in the ass
to work with.

Thanks for the feedback,

J

[Jochen Daum]

Hi James!
On Mon, 9 Feb 2004 22:39:20 -0600, "James"
<ja**************************************@hotmail. com> wrote:

I am currently working on a PHP based website that needs to be able to draw
from Oracle, MS SQL Server, MySQL and given time and demand other RDBMS. I
took a lot of time and care creating a flexible and solid wrapper and am
deep into coding. The only problem is a noticed VARCHAR fields being drawn
from SQL Server 2000 are being truncated to 255 characters.

I searched around php.net and found the following :

Note to Win32 Users: Due to a limitation in the underlying API used by PHP
(MS DbLib C API), the length of VARCHAR fields is limited to 255. If you
need to store more data, use a TEXT field instead.
(http://www.php.net/manual/en/functio...eld-length.php)

The only problem with this advice is Text fields seem to be limited to 16
characters in length, and I am having similar results in terms of truncation
with other character based fields that can store more than 255 characters.

I am using PHP 4.3.3 running on IIS using the php_mssql.dll extensions and
the functions referenced here http://www.php.net/manual/en/ref.mssql.php.
What are my options here? Has anybody worked around this or am I missing
something obvious?

If you are working with FreeTDS, the limitations is there. Use the
TDSVER environment variable and set it to 7.0 or 2000 (check the last
on www.freetds.org). SQL Server 6.5 had this limitation, but others
don't have it. I use happily 8k varchars on SQL 7.0

HTH, Jochen
--
Jochen Daum - Cabletalk Group Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.
http://sourceforge.net/projects/phpdbedittk/

[Jochen Daum]

Hi JAmes!
On Tue, 10 Feb 2004 07:42:49 -0600, "James"
<ja**************************************@hotmail. com> wrote:

"Erland Sommarskog" <so****@algonet.se> wrote in message

>
> Note to Win32 Users: Due to a limitation in the underlying API used byPHP > (MS DbLib C API), the length of VARCHAR fields is limited to 255. If you
> need to store more data, use a TEXT field instead.
> (http://www.php.net/manual/en/functio...eld-length.php)
>
> The only problem with this advice is Text fields seem to be limited to
> 16 characters in length, and I am having similar results in terms of
> truncation with other character based fields that can store more than
> 255 characters.

As pointed out by Chung Leong, there is room for 2GB of data in text.
The 16 bytes you see is just a pointer.

Thus, there are all reasons to look into alternative means of connections
for PHP to MS SQL Server.

I believe the Linux PHP builds use that alternate means and the Warning
(given only on one page and not the main PHP MS SQL Server driver page as it
should have been) -- I'll be testing that later on today.

The other thing you can do is:

select cast(field as text) as field from table

to get the complete content.
HTH, Jochen
--
Jochen Daum - Cabletalk Group Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.
http://sourceforge.net/projects/phpdbedittk/

[Erland Sommarskog]

James (ja**************************************@hotmail. com) writes:

Strangley I had similar issues when working with ASP and SQL Server in
the past - fields would simply not show up sometimes if they where over
255 characters in length and it is a known and documented issue that
affects 'certain databases' according to MS but I have only ever seen
with the SQL Server and MSDE - never Oracle, MySQL, Postgre or even
Access -- its a shame I like the SQL Server but it seems that everytime
I get contracted to use it with a web back end I run into problems that
make it a royal pain in the ass to work with.

I don't know more about ASP than I know about ASP, but I would expect
ASP today have any problems with longer varchar values.

Of course there was a time when SQL Server did not have anything better
than varchar(255) (and text). If you were accessing ASP from a machine
with a version of ODBC that did not support the new and improved types
in SQL7, then you would be in that boat. But that's long ago.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Chung Leong]

Holy crap. Why didn't I think of that...

Uzytkownik "Jochen Daum" <jo*********@cans.co.nz> napisal w wiadomosci
news:57********************************@4ax.com...

Hi JAmes!
On Tue, 10 Feb 2004 07:42:49 -0600, "James"
<ja**************************************@hotmail. com> wrote:

"Erland Sommarskog" <so****@algonet.se> wrote in message

>
> Note to Win32 Users: Due to a limitation in the underlying API used by

PHP

> (MS DbLib C API), the length of VARCHAR fields is limited to 255. If

you > need to store more data, use a TEXT field instead.
> (http://www.php.net/manual/en/functio...eld-length.php)
>
> The only problem with this advice is Text fields seem to be limited to > 16 characters in length, and I am having similar results in terms of
> truncation with other character based fields that can store more than
> 255 characters.

As pointed out by Chung Leong, there is room for 2GB of data in text.
The 16 bytes you see is just a pointer.

Thus, there are all reasons to look into alternative means of connections for PHP to MS SQL Server.

I believe the Linux PHP builds use that alternate means and the Warning
(given only on one page and not the main PHP MS SQL Server driver page as

itshould have been) -- I'll be testing that later on today.

The other thing you can do is:

select cast(field as text) as field from table

to get the complete content.
HTH, Jochen
--
Jochen Daum - Cabletalk Group Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.
http://sourceforge.net/projects/phpdbedittk/