473,388 Members | 897 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,388 software developers and data experts.

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


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
Jul 20 '05 #1
4 10992
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

Jul 20 '05 #2
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
Jul 20 '05 #3
"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
Jul 20 '05 #4
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
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: James | last post by:
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...
2
by: Tim Mackey | last post by:
hi folks, i'm puzzled over this one, anyone with some solid db experience might be able to enlighten me here. i'm modelling a file system in a database as follows, and i can't figure out to...
4
by: Guinness Mann | last post by:
I'm running SQL Server 2K (sp3a) and when I run the following query (in query analyzer): SELECT id, LEN(ForeignWord) as Length, ForeignWord FROM Words WHERE Language ='Thai' and...
3
by: aaj | last post by:
SQL SERVER 2000 Hi all This is my first attempt at writing a stored procedure. I have managed to get it working but its unlikely to be the best way of handling the problem. While writing it I...
3
by: Oasis | last post by:
Hello, I'm new to c#. I have situation where I want to execute a number of insert statements that differ only in a few dynamic values. When I was a Java programmer, I would do this with a...
5
by: hishamfangs | last post by:
Hi guys! I'm facing a problem and I can't quite figure it out! I have created a table on SQL Server 2000 to store call records. We get about 250,000 calls a day, and the most frequently used...
22
by: jdokos | last post by:
Hello, I have a question about VARCHAR fields. Our application groups here are starting to use VARCHARs much more frequently. Even VARCHAR (2) to (9) length fields. They say this is because...
1
by: bonnie.tangyn | last post by:
Hello all I get Too few parameters expected 2 error and "The MS Jet Database engine cannot find the input table or query "myTempTablename". Make sure it exists and that its name is spelled...
3
by: Froefel | last post by:
Hi group I am creating a web application that uses a simple DAL as an ObjectDataSource. To retrieve data from the database, I use a DataReader object from which I then assign the various fields...
0
by: maheshmohta | last post by:
Background Often while remodeling legacy application, one of the important tasks for the architects is to have an optimum usage of storage capabilities of database. Most of the legacy applications...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.