473,574 Members | 2,242 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 11009
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************ *************** ***********@hot mail.com>
napisal w wiadomosci news:40******** **@newspeer2.td s.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************ *************** ***********@hot mail.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************ *************** ***********@hot mail.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
2924
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 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...
2
3448
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 cleanly implement an inheritance mechanism. i have a hierarchy of folders in an sql table. every folder has a parentFolderID, if this value is 0...
4
13814
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 LEN(ForeignWord) > 300 ORDER BY Length desc I receive the following results:
3
4010
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 found some things that I don't understand so if any one could shed any light it would be much appreciated. I have posted these at the end.
3
1615
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 PreparedStatement, which will supposedly improve performance as well as make the setting of the dynamic values much more convenient than building a long...
5
1609
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 query retrieves all the calls records for the day and groups them by destination, so the query would return something like this: ...
22
11568
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 some of the application programs, specifically Java Beans cannot handle the spaces after the value in CHAR fields. Is anyone else seeing this...
1
2880
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 correctly. 3078" after I migrated "MS Access 2000" to "MS SQL Server 2000" and relinked all linked tables. The front-end interface of my application...
3
2204
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 to properties in an object, like so: in the DB, the fields are defined as follows: ProjectID int NOT NULL
0
27235
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 are constrained by the technology available at the time of their development and hence aren’t optimum as per current scenario. One of such cases is the...
0
7719
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8044
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8229
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7808
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6450
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5616
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3739
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3749
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1335
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.