MSSQL for Joomla! 1.5

[AmyStephen]

Hiring someone to create a GPL'ed driver for MySQL is an excellent idea. If you want to do this and are looking for recommendations for excellent developers, there are a couple I would recommend. Feel free to PM me.

[JackBreech]

Amy,

Excuse my ignorance, but are you saying that you know someone that can write a 'driver' to make Joomla! work on MSSQL?

Jack 

[AmyStephen]

Jack -

I shared a name of a developer with mknz who I *know* can develop a driver for pay that will make Joomla! work on SQL Server. If you are interested, you might PM mknz to see if you can split the costs. It would be FABULOUS to see this type of arrangement used to GPL extensions, advance Joomla!, and help pay developers for their expertise and time.

All the best,
Amy

[igevans]

Hi All,

I started this thread a few months ago.  I have done some MSSQL stuff in PHP, including synchronizing MSSQL-MySQL, so I thought I'd have a go at writing an MSSQL class for Joomla.

In theory, this is easy.  Just take the JDatabaseMySQL class definition and write a JDatabaseMSSQL class which takes the same parameters instead, then allow users or system to choose their database and away you go.  Unfortunately there are quite a few issues, most of which are to do with the different SQL implementations and not with any PHP deficiencies.

The first draft code, as well as an explanation of the main difficulties are all posted earlier in this thread.  One of the worst of them is dates, and first I got side-tracked into writing a "real" date class for Joomla, then I moved job and house and lost internet access for 4 weeks.  In the meantime, this thread seems to have come awake again.

I'm not sure that running the core Joomla! database on MSSQL is going to happen anytime soon - unless there are lots of changes to Beta2 since the start of April, there probably needs to be a great deal of re-writing SQL queries and date handling.  Since some 3PDs are parsing and changing the SQL code quite intimately (especially Joom!Fish, and probably others I don't know about) there are lots of things to break, and realistically none of this is going to get into the next release.

That said, a working JDatabaseMSSQL would at least standardise the MSSQL interface for 3PD extensions, which would avoid duplication and make them Joomla! compatible at the most basic level.  Making core Joomla! MSSQL compatible might then happen in future.

Obviously, if anyone else wants to take this up, especially paid/professional developers, that would be great.  Otherwise I will try to get something usable up over the next few weeks, which at least replicates all the JDatabaseMySQL methods, and we can see how it goes from there.

[JackBreech]

Hello Joomla! fans that are constrained to MSSQL....

IGEvans, what is the latest on the class development?

MKNZ, have you contacted the developer that Amy has recommended?  I haven't be authorized to contribute to the payment of this developer, but if I had more info I may be able to offer a proposal to my supervisors.

I think this integration would be an excellent leap forward for the entire community.  Maybe if the code works well, they could add it to 1.5 as an installation option.

As a side-note, would the work done in this effort work on both 1.0.x and 1.5?  Meaning this will help both versions, with tweaking necessary to each one or are they entirely different efforts?

Thanks again for everyone's participation and help. 

Jack 

[spambox]

Different people working on the same project? While searching for a LIMIT solution in MSSQL, I found the following thread: http://forum.joomla.org/index.php/topic,161017.0.html. Seems submission.zip has got some enhancements over mssql.zip.

Currently testing mssql within our own environment (No, Joomla is running on MySQL ; But we've got a bunch of mssql database to withdrawn information from...)

Trying the following solution:
1) Just prepare al queries for your mssql database (including pagination etc.)
2) Import mssql.php
3) Connect to different database resource (tru mssql)
4) Retrieve data from mssql

[mknz]

MKNZ, have you contacted the developer that Amy has recommended? 

I did contact him...got no response as of yet...but did you see this new post?
That's what I need...not the whole architecture on MSSQL...just interact with it and keep Joomla MySQL...

i'll see if it'll work for the users/members only stuff

[JackBreech]

Unfortunately spambox's wisdom is beyond my scope.  I hope it works your testing is fruitful.

Also, unfortunately I can't use MySQL at all on my servers.  I'm still looking forward to a full MSSQL solution.  I appreciate everyone's efforts.  If there is some more definates on how feasible getting a transition, I would be able to take that to my 'funding' source and see what I could do to pay a developer.

Yeah.  Long weekend!


Jack 

[pdizzyd]

We are very interested in helping with this. Have you considered creating a project on joomlacode.org?

Please let us know we are in the throws of getting Joomla! to work on MS SQL as well.

[spinnie]

Where can I find this extension. I would love to help testing.

[lightinthedark]

I made that file that spambox refered to in his earlier post. There have been some small further developments to it since then, so I'll post up the current version here for anyone that's working on it. If an actual project gets made on joomlacode I'm quite happy for my file to be used in whole or in part to create a JDatabaseMSSQL class, which seems to me like the sensible way to implement this much-needed functionality. The abstract class / inheritance model is there for exactly this kind of thing.
Imho the most obvious / effecient / logical way of proceeding with this would be to make a JDatabaseMSSQL that will transparently transform standard sql queries into a format that MS SQL server can understand and give useful results from.

Regarding dates: I've found that for pulling stuff out the mssql db the following syntax is very useful:
CONVERT(char(8), some_date,  112) AS nice_date
or
CONVERT(char(19), some_date, 126) AS nice_date
(see the page http://msdn2.microsoft.com/en-us/librar ... L.80).aspx for docs on that)
but I don't know how you'd deal with dates going in other than scanning the query string first and converting as necessary there.

Hello
:-Lightinthedark

[tjay]

If anyone decides to turn this into a full blown component, and would like project hosting give me a shout.
I am watching the thread with great interest, alas I am no coder so all I can offer is some webspace for project use.

[catasoft]

Hey guys
While looking for a solution to this same Joomla/MSSQL issue, i ran into this page:

http://joomlacode.org/gf/project/msconnect/

The MS Connect project was created to share the code we develop to connect Joomla! 1.0.x to Microsoft SQL Server.

Hope it helps

Cata

[MaximShiryaev]

Hi.

I'm about the same for Firebird:  http://forum.joomla.org/index.php/topic ... #msg922400

Hey, core team! It looks like the only thing needed to make Joomla cross-db is a team leader. The task force is already here!

Maxim Shiryaev.

[louis.landry]

Sounds great to me .... let me know when you guys have some working code we can look at.  Also, if you run across problematic queries in the core let us know about those as well

Good luck!

Louis

[user deleted]

Sounds great to me .... let me know when you guys have some working code we can look at.  Also, if you run across problematic queries in the core let us know about those as well

Good luck!

Louis

** looks at the Q&T desk, hm, no RFC forms yet... Louis will probably fill those in soon enough **

[AmyStephen]

** looks at the Q&T desk, hm, no RFC forms yet... Louis will probably fill those in soon enough **

Joomla! got you a desk? Will the perks ever end?    hehe Keep us on track, Robin, for v 1.5! Doing good!

When this MS SQL Server driver is available, though, I will be happy to help test.

Amy

[louis.landry]

*cough*

Certainly its too late to find its way into the 1.5 core .... but I'd be happy to add a working adapter to 1.6 and solve query problems as necessary

Louis

[lightinthedark]

I know it's not MSSQL, but it is related:
http://forum.joomla.org/index.php/topic,193981.0.html

There's a discussion there about what is the best way to impliment database abstraction (for Postgres in that exact case, but the principles extend). There seem to be 2 suggestions:
A parser / translator
A JQuery query-building class / package

Currently the msconnect project is only aimed at 1.0, and I believe is using the parse-translate approach. The folks on that other thread seem to think that parsing and translating is inherantly flawed, and that a JQuery package would work better. I think they may be right... if it can be made flexible enough; I'd really like some input from the core team regarding which design to go with as they'll be more likely to know which way fits with their coding approach better. If JQuery is the way to go, I heard there was a version of this many svn revisions ago, but that it had long since faded into non-existence, does anyone know where this code could be found, and if it's even worth using this as a starting point?

Hello
:-Lightinthedark

[plamendp]

You can find it here:

http://forum.joomla.org/index.php/topic,195747.0.html

Plamen

[Jinx]

JQuery is the way to go, an parser/interpreter is slow and creates unneeded overhead.

[MaximShiryaev]

Hi.

Here are my Firebird thoughts.

Overally SQL looks OK. Following is a short list of possible problems under Firebird discovered after verifying of all appearances of "SELECT" keyword:

1. CONCAT_WS: can be either extracted to JDatabase wrapper or substituted by Firebird UDF.

2. Numeric 1 does not evaluate to TRUE and 0 to FALSE in conditions.
  Operators like "WHEN CHAR_LENGTH(a.alias) THEN..." should be replaced with "WHEN CHAR_LENGTH(a.alias) > 0 THEN..."

3. Directly double-quoted string constants. In Firebird strings are delimited by single-quotes only. In most places JDatabase quote() wrapper is used but not everywhere. There are some like  field = "com_weblinks".

4. Date and time literals. Should be extraced to JDatabase wrappers.

5. Functions MONTH and YEAR in Firebird are expressions EXTRACT(MONTH from SOME_FIELD). Should be extracted to JDatabase wrappers.

6. Function DATE_FORMAT does not exist in Firebird. Better to replace with php equivalents.

7. Not syntax consideration. Expressions like SELECT A.*, B.D, B.E are dangerous in terms of variable field count in A in future versions.


That's all for now. Tomorrow I will try to analyze all WHERE statements.

Currently my list contains only about 25-30 files with potential problems.

It looks like all that needed is something like JDatabaseSyntaxHelper class with all the syntactical tricks beyond SQL92 as methods.

Maxim.

[lightinthedark]

Hi,
Without wishing to be accused of spamming the boards, I thought I'd just put up a link to the 1-hour old project to create a JQuery class for 1.5's architecture which will hopefully resolve these db connectivity issues in time for 1.6

The project can be found at http://joomlacode.org/gf/project/jquery_1_5/

All help is welcome

[Jinx]

Nice work, for once I will act like I didn't notice your spamming attempt 

[mknz]

ok...quick update.  The witch that made us go 100% MSSQL quit!

so, now I can do whatever I want.  Problem is we're still 100% MSSQL on the backend.

I've had it with this rediculous DotNetNuke...it's so childish in comparisson to my Joomla.

Any suggestions on what I should do?

To refresh...I need to be able to pull info from the MSSQL db and have the user admin work in the .net framework (ie: able/disable Joomla users).  That's really it.

Let me know...again...not a programmer...but I could probably get us a touch of funding.

adios mis amigos-mknz

[mknz]

AmyStephen sent me a developer that might be able to do the project, but alas, he is busy. 
I think the option to use MSSQL instead of MYSQL would make Joomla a real option for business.
It has a much higher level of security and I believe it will handle even more connections at once.

I understand there may be ANSI compliance issues, but MSSQL seems to deal well with ANSI compliance...may have an issue with ANSI stored proceedures, but that's another story.

If anyone is interested in helping me develop a core MSSQL driver...please let me know soon.  I may be able to put together a small budget or lead a donation drive for such development.

It could take care of a lot of problems and I believe it could be a major catalyst for Joomla.

[AmyStephen]

You guys might want to pay attention to this thread.

[SmartSource-IT]

Another possible solution.

Install DNN and use nukelet to run Joomla inside DNN.  http://nukelet.com/

that's what I'm looking for...that...

Why not just slap a copy of ADOdb into a component and add the necessary configuration options (username password) to the config.xml file?

how would I go about doing that?

I could live with a site users/CMS content in MySQL and a component level querying the MSSQL.  That's brilliant.

[fedemil]

News for joomla on MS SQL Server?!

I'm very interesting for this solution...i must work with MS SQL...unfortunately 

The fiel mssql.php in the previous post is for Joomla! 1.5?
I must create a db with MySQL and then i porting the db to MSSQL and i can use mssql.php like driver?
Can you help me? 

[tatek]

If the database driver works with a plain Joomla! 1.5 install we could consider adding it to the core in a future release.

emmmmmmmmmmmmmmm glad to hear this.

Tate