There are some major issues, in my opinion, that are preventing MSSQL from being fully supported in PHP and thus Drupal. Most, if not all, of these revolve around the terribly outdated DBLIB library - forcing some to use alternative libraries (FreeTDS or the recently released SQL Server Driver for PHP). I want to get this out in the open so that we may address them appropriately. I have run into these myself and have had to do some creative work-arounds to get Drupal to behave the way I need it to while running on SQL Server. As far as I can tell, this applies to all versions of SQL Server and the latest PHP release.
Once we can reach agreement that these issues exist, we can then work to address them. I looked at the new SQL Server Driver for PHP, but it has an entirely new set of functions that would require an entire re-write of database.mssql.inc, however, should we start developing for this driver since Microsoft has discontinued (to my knowledge) ntwdblib.dll??
At the very least, we should develop a to-do list for all of this, perhaps a wiki page so that we can coordinate our efforts effectively. I also plan on creating a separate list for incompatibilities between MySQL / SQL Server 2000 / SQL Server 2005 / SQL Server 2008 so that module developers have a place to go to help guide them in supporting 'mssql' in addition to 'pgsql', 'mysql', and 'mysqli'.
-
VARCHAR Data Truncated at 255 Characters
There is an issue where you can not pull more than 255 characters from a VARCHAR field. The fix is either to change this field to type TEXT (as I usually do) or, there is a suggestion to try: "SELECT CAST(F AS TEXT) AS F FROM ...", which I have not tried yet.
For Reference:
http://bugs.php.net/bug.php?id=25544&edit=1 -
Fetch Column Name Truncated to 30 Characters
This issue is apparent, but not limited to, CCK fields whose names in the database exceed 30 characters. This results in CCK field data not appearing in, for example, a view. Currently I have a really ugly patch that truncates the name of the field CCK is expecting to 30 chars so that it matches the truncated field name fetched from the DB. From php.net:
"Note: In Windows, the DBLIB from Microsoft is used. Functions that
return a column name are based on the dbcolname() function in DBLIB.
DBLIB was developed for SQL Server 6.x where the max identifier length
is 30. For this reason, the maximum column length is 30 characters. On
platforms where FreeTDS is used (Linux), this is not a problem."For Reference:
http://bugs.php.net/bug.php?id=33060&edit=1 -
Extra Space Added When Pulling Empty Data
When pulling "" (empty) data into a form field, for example, the field has an empty space entered into it. The suggestion is to use a trim() function to strip these. This only happens when the data stored in the DB is empty (no spaces). I am currently experiencing this with nodes that use CCK fields, for example, and the ones that are not required and that I don't fill out have a single space in them when I go to edit the node.
For Reference:
http://bugs.php.net/bug.php?id=36968&edit=1
Comments
some thoughts
1) This may or may not be a problem in D6. See Schema API docs at http://drupal.org/node/159605. I guess mssql could do a hook_schema_alter() to change varchar fields as needed.
2) I'm pretty sure that cck field names have a max of 32 characters, at least on D6. we can probably coerce them to reduce to 30 - seems like a minor concession.
3) i'm pretty sure this is a very old bug and it caused me enough aggravation that i stopped maintaining the mssql port of drupal because of it. it was a bit of a "last straw" bug. avoiding this is a big reason to move onto the new driver, as you suggest.
2) Well, don't forget CCK
2) Well, don't forget CCK prepends 'content_field_' to every field, so I believe you'd have to enforce a max size of 16. I typically use the naming convention 'nodetype_fieldname', so none of mine would fit. (Granted I can just adopt a new pattern in the future, just dropping in my two cents.)
Or ... patch CCK
Would it not be possible to patch CCK to either remove the prefix or at least shorten it to 'CCK_'? Why does CCK need to prepend anything?
Well... it has to prepend
Well... it has to prepend something so I don't add a field to a node called "users" and cause all kinds of trouble. Arguably, it doesn't have to be as long as it is.
But then again, I'm not sure core and important modules should be adjusted to accommodate such a poor implementation... surely something better can be done on the other end to address the issue.
Agreed
This 30 character limitation is pathetic at best and as Moshe said, we should really consider which driver we want to go with. There is a FreeTDS driver, however, I not been able to get it to work. The new driver released by MS is promising, but it's not an official release yet and I'm not sure exactly when it will be done. I'll investigate into it and see what I can find. Does everyone else agree that the existing driver is junk and to not bother trying to deal with it?
Aren't we looking at PDO for D7
Maybe http://www.php.net/manual/en/ref.pdo-dblib.php is appropriate.
Be aware of the following bug
I like the idea of using PDO, but you should be aware of the following bug ...
http://bugs.php.net/bug.php?id=38805
It will be interesting to see how the relationship between Microsoft and Zend evolves to propel or inhibit the use of standard libraries.
It could just as easily
It could just as easily give an error for a field blacklist or if the column exists in the node_revisions table already.
CCK is supposed to be becoming a part of core. Adjusting the poor out of the implementation is a must. Of course use of the implementation must be just as smart.
Actually, field names are
Actually, field names are only prefixed with 'field_', so regular node_load queries are probably not so bad. The problem is more likely the Views queries, because the Views fields have very long aliased names. It's the Views queries that use the 'content_field_' alias. That could probably be shortened, but if we do it will break every View that anyone has ever created, forcing everyone to pull up and save each View to fix them, and that becomes a maintenance nightmare. But it could be done if necessary.
And if we do shorten the field names to say 24 characters, how do we make sure we don't break sites that have field names that will become duplicates if we truncate them at 24 characters? And even if you do shorten the names, how short do you have to go?
The starting point has to be figuring out exactly where this is really an issue and what our maximum length really must be, then we'd have to work backwards from that through the changes that need to be made in CCK while looking for ways to fix or avoid the problems this change will introduce. I wouldn't want to head down this path, with all the maintenance problems it will introduce, without being really sure ahead of time that it is actually the right fix.
And while I'm thinking about
And while I'm thinking about it, those long Views queries are made up of a long table name and a long field name and a long alias, so another question is whether the 30 character limit is on the alias name or the combination of the table and field name (content_field_table.field_my_field) or only on the actual physical field name itself.
Where exactly
Where exactly is this limitation being discussed? I find http://swik.net/ms-sql-server doesn't give such limitations on table, index or column names.
Just a reference from Oracle and DB2 implementation
According to above 3 restriction:
chr(1), before every query, and after fetching. Again, it is proved as function within my personal research project, where I am planning to reuse for MSSQL.IMHO ODBC should be a better choice for MSSQL supporting, as it will also useful for EnterpriceDB, too (ODBC is the official PHP driver implementation for EnterpriseDB). On the other hand, I have already post a cross database aware patch for D6/D7 (http://drupal.org/node/172541), which is currently support for both mysql, mysqli, pgsql, oci8, pdo_mysql and also pdo_pgsql. According to my research and schedule, the support with MSSQL/ODBC should be simpler than existing DB API implementation.
Edison Wong
CEO, Co-founder
PantaRei Design Limited
PDO and Oracle/DB2/MSSQL
Earnie,
Regarding PDO. The main thing about PDO and the other databases that PDO supports is that it still uses the same native client libraries for the particular database flavor that give us trouble.
Example ( Ref: http://php.net/pdo ):
PDO MSSQL = DBLIB = The DBLIB client library will still have the 30 char limit problem.
PDO DB2 = DB2 Client lib = Currently using only the v 9.1 client libs
PDO DB2 (via ODBC) = Depends on what you have installed for your ODBC, usually the Clouldspace client.
However, it does seem that the ODBC drivers univerisally solve this limitations, and PDO supports ODBC. I'm going to do some research, but from what I can gather, ODBC no longer presents the performance hit most complained about years ago when using ODBC. We'll see though.