MSSQL 7+ Connectivity

[dnglaze]

Alright, I've been messing with MSSQL compatibility for Elxis for about two weekends now.  I've determined that the biggest issue for MSSQL compatibility is based on a few small details in the queries that were written by the Elxis team.  For reference, code sections are assumed to be in the ElxisInstaller class.

MSSQL has a hard time dealing with " in the create and other queries.  Replacing all words surrounded with " with the same word surrounded by square brackets fixes this.  A possible fix is to do the following before sending a query to the MSSQL server.

Code: [Select]

if (eregi('mssql', $this->_config['DBtype']))
{
    $sql = preg_replace('/"(.+?)"/', '[$1]', $sql);
}
$conn->Execute($sql);

The other issue is that the installer does a lot of ID inserting.  This causes a problem with MSSQL again because ADOdb sets up the tables using an identity column to emulate the auto-incrementing column id.  Because of this, a query must be sent to the MSSQL server to enable the insert.  That's what the following does.

Code: [Select]

SET IDENTITY_INSERT {tableName} ON
// Inserts and such
SET IDENTITY_INSERT {tableName} OFF

The final issue is that MSSQL requires that a full value list must be used to insert into in identity column.  This means that all queries that do insert the ID must also fully list the columns.  Below as a SQL example.

Code: [Select]

INSERT INTO  elx_modules   VALUES (1, 'Polls', NULL, 1, 'right', 0, '1979-12-19 00:00:00', 1, 'mod_poll', 0, 29, 1, NULL, 0, 0, NULL)
// turns into
INSERT INTO  elx_modules  ([id],[title],[content],[ordering],[position],[checked_out],[checked_out_time],[published],[module],[numnews],[access],[showtitle],[params],[iscore],[client_id],[language]) VALUES (1, 'Polls', NULL, 1, 'right', 0, '1979-12-19 00:00:00', 1, 'mod_poll', 0, 29, 1, NULL, 0, 0, NULL)

Obviously, some of this, excuse the french, is a pain in the ass.  I went ahead and created a new XML file for the table creation that works with MSSQL.  I also made a new default data SQL file for MSSQL.  The only thing that had to be changed in the core installer were the statements creating the super admin and the double quote to square bracket conversion.  I also had to remove the ID inserts done for the soft disk entries.

After all of that, it still won't show the homepage or allow me to enter the administrator section (says the password doesn't match).  The big issue with the frontpage is that some how the access list for the guest user gets blanked out so the queries for the various components fail.

I don't know if anyone has any ideas on fixing my two current issues with this.  I'm going to keep playing with it, looking through the Elxis core looking for the issue unless some one knows why I'm having problems.  Thanks for any help in advance.

I also feel it must be said that I don't know if any of this has a place in Elxis Core, since MSSQL is such a small demographic.  At any rate, I hope this helps some one who may be having issues with Elxis on MSSQL.

[datahell]

Elxis 2008.1 updated with the latest ADOdb library.
Try with ADOdb 5.0.4a (PHP5 only version).
Use schema 0.3 with the option platform=mssql were needed.
Create separate installation XML schema / sql if needed (elxis_mssql.xml).
Many queries changed in Elxis 2008.1 for Oracle support and for being closer to native sql standards.
Elxis 2008.1 introduces "qq" as a compatibility prefix for bypassing reserved column names (sample column: qqaccess instead of just access). Database class adds and removes this prefix from the queries were needed automatically.

Elxis 2008.1 is fully usable with Oracle using the oci8 driver, why not with mssql? MsSQL is fully supported by ADOdb. I don't have mssql, if I had it I would make Elxis compatible with mssql. Is there any lite and free version of mssql?

Try to install under mssql from a script outside the Elxis going step-to-step with debugging and stop on errors enabled. I attach you my sample test script. Comment/uncomment, modify where needed.

If you wish I can send you the Elxis 2008.1 installer. Elxis 2008.1 is not finished yet, it is not even a beta version but works almost fine under Oracle. If you wish we can co-operate and make Elxis compatible with mssql before Elxis 2008.1 release.

Further details on Elxis 2008.1 can not be announced in public yet.

[old attachment deleted by admin]

[dnglaze]

I'd be more than happy to help get Elxis 2008.1 working with MSSQL, and you can feel free to send me the installer.  I think the biggest thing with ADOdb is that it can talk to MSSQL servers, but it is up to the person using ADOdb to create queries that will work with the selected database.  As far as the table creation script is concerned, the double-quoted SQL statements work from the SQL server manager, but not when run from mssql_query  .  That's the part that I'm surprised about.  I actually looked at generated SQL statements from a different ORM called Propel to see how it generated it and it used the square bracket implementation.  I don't know, MSSQL is just weird.  I wouldn't use it if I wasn't being forced to  .

By the way, I think SQL Express edition is a free, lite weight version that you can use if you like.  Check it out here http://www.microsoft.com/sql/editions/express/default.mspx.