Joomla! Discussion Forums

I have written a JDatabaseMSSQL - is this of interest, and anyone want to help me test it?

igevans

Would love to see people testing this.  I don't have mssql nor the time to jump into it, but it would be great to see it as an option.

Louis

Where can I get sequel server?  How do I get setup?

I have a windows machine around that I could use to test it, but it is only XP Home...  will that do it?  Or do I need XP Pro and some other stuff...

This is a great thing, but it may be hard to find people to test it because the project by its nature attracts people who are on MySQL...

Ian

http://www.microsoft.com/sql/editions/e ... fault.mspx
It's free, should work on XP Home, not sure though...

SQL Server 2005 Express works on Windows XP Pro, so I'm assuming it will work on Home also.

SQL Server 2005 is quite new, the 'Express' version of SQL Server 2000, known as MSDE, can be downloaded here.
Windows Vista does not support SQL Server 2000.

The attached zipfile contains an Alpha version of a JDatabaseMSSQL class, not yet properly tested.  As well as the core MySQL class, it is derived from an MSSQL 2000 class I had in another project.  There are specific issues with the PHP mssql functions, especially queries returning columns of type 'ntext', which can't be returned in their native format.  I haven't tried it with MSSQL 2005 yet, although I'm hoping that it will at least be backwards compatible.

I think it would be mostly useful for people who want a common handler to access existing MSSQL databases, rather than hosting the core joomla database on MSSQL.

Ifan

Ifan - thanks for starting the ball rolling on MSSQL support.  It's a big deal to me since I have at least one site that I'd like to run Joomla on that only supports SQL Server 2000.  In my case, I need to host all database tables including the core Joomla database on MSSQL.  I'll start testing your code in the next few days.

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

*hint, hint* Along with that goes the following:  If while developing these drivers you run across queries in the core that are incompatible with other database systems due to non ANSI compliant SQL ... we wouldn't mind being helped with that too

Louis

The problem there is that MSSQL isn't very compliant itself, because it uses Transact-SQL.  In particular, its handling of dates is all over the place - this, for example.  Also, the PHP driver will fail when trying to return columns of type 'ntext' natively - meaning that even the simplest query (SELECT * FROM table) can fail on some tables.  The MSSQL driver will try to work around this, but it means that it has to know the column types in a table, dates need to be 'Quoted' and that some SELECT queries need to be parsed for *.

There's a very useful comparison of SQL implementations here.

Ifan

Interesting, are there any recommendations you would like to make as how we can adapt our DB drvers to accomodate for this problem ?

It's quite a challenge, and for any complex queries maybe impossible, but it will boil down to parsing queries for non-compliance and modifying them as required.

A few ideas for good practice which could make this workable would be:

1) always use identifiers (tables fields etc) in lowercase, SQL keywords (SELECT FROM SUM MAX) in uppercase, and quote values
- then the class can parse the query easily to quote identifiers, substitute incompatible keywords, change LIMIT OFFSET syntax, etc

2) always use "table.field" identifiers and avoid using aliases for identifiers unless absolutely necessary
- then the parser can find the fields and the table to which they belong, and decide whether to convert/cast them for compliance (eg: MSSQL would search for "tbl.*" and transform it into a field list only if there are ntext columns in the table)

3) agree a standard format for dates, and don't use timezones
- something which works for as many databases as possible, and is easily recognised by a regexp parser (eg: yyyy-mm-ddThh:ii:ss.fff)

4) avoid using special types of field which only exist in certain databases
- eg the MySQL timestamp functionality

5) where possible, keep SQL queries simple by doing calculations, concatenation etc in PHP

6) Write queries that work unparsed in MySQL, unless your app is always hosted on another DB type
- quicker for most sites, which will be on a MySQL server, other DB classes only have to search for and parse MySQL functions/keywords/quoting/etc

Some of these are probably already in use, and will also have benefits for JoomFish, which I think also parses queries before running them to the DB.

Still working on the MSSQL class, so I'll write a draft MySQL->MSSQL parser function for you to look at.

Ifan

Oke, thanks for the valuable feedback looking forward to your first alpha code. Keep up the good work !

Any news on this...need someone to test?
I have an in-house mssql/.net program at work and that's not going to change. 
I can't find anything like Joomla in .net for the website.
I've decided to quit trying and build this whole thing in Joomla,
but I need to figure out how to query the mssql for user info, queries, etc.
This is the closest I've found, but I really need a complete solution.
http://extensions.joomla.org/component/ ... Itemid,35/

Anyhow, let me know if there's any way to make this work....Thanks!

To mknz:

I've been working with .NET the past couple of months for my coursework for school, but it was only in the last class that we were actually doing some web development. Since my time at UC Berkeley (and before that with my introduction to Mambo) I had been steadily becoming a small open source zealot and I really did not want to work with ASP.NET 2.0 at first because of it. The whole .NET framework has grown on me quite a bit since my first introduction and I must say that it has some really really great stuff and if there were something out for ASP similar to Joomla (and if I leased an IIS Virtual Private Server rather than a PHP one) I'd might even consider switching to it.

Anyway, you mentioned that there is nothing comparable to Joomla on the .NET platform so I thought I'd point you to the following URL which I discovered just the other week:

http://www.php-compiler.net/doku.php

Perhaps you know about it, perhaps you don't, but it allows you to use and program in PHP and use the built-in .NET classes. It might take a bit of work to get things working perfectly, but maybe you'll be able to figure something out :-).

-Omar

I may be shot down in flames for this, but have you considered using the following instead of Joomla?

http://www.dotnetnuke.com/

- Vince

It totally sucks and I love my Joomla.
It's hard to get setup in a shared environment...no easy Joomla install.
Once it's up, it's not what I've come to know from my favorite CMS (Joomla).
I had to buy a really crappy news program just to find out it's no Joomla.
No backend admin (and the front end is lame). 
It's an uphill battle and I'm sure getting joomla to work with mssql has to be a better idea.

I just need to figure out a way to get this to work.

Am I looking for a library, data access layer, or will I have to run a cron job to update my mssql and mysql to each other's updates?

Any ideas appreciated. 

And DNN was a good suggestion Vince...sorry to shoot you down in flames.

Omar-
I didn't see your post at first...so sorry.

That's fine and all...but...
I really don't want to get into all that...I'm not a programmer...well, not really.

I just need a plug and play system like Joomla...that can work with mssql.

I was looking into the ADOdb Database Abstraction Library for PHP...I think it works with Mambo...maybe that'll do it:
http://adodb.sourceforge.net/

Of course, I'm also considering just limiting my mssql relationship with the site to certain pages with something like this:
http://extensions.joomla.org/component/ ... Itemid,35/

or perhaps there is something out there:
http://forum.joomla.org/index.php/topic,132828.0.html

Thanks!

@mknz

ADOdb doesn;t exactly work with Joomla!...

As I understand it, there are methods in place to allow Joomla!'s database abstraction layer to handle ADOdb type requests. In other words it's round the wrong way for what your looking to do.

Also the ADOdb stuff in Joomla! isn't complete yet.

Plus there's the issue of data types and function between MySQL and MSSQL. It's unlikey that, even if you had a MSSQL driver for Joomla!, that it would simply `plug and play`. I'll admit I'm not all that familiar with MSSQL, but I remember about 6 months ago virtually pulling my hair out because of the handling of dates in MSSQL. Somthing which `juct works` in MySQL.

Just as a matter of asking, why do you need to run it with MSSQL?

I have an in-house membership management system that's all MSSQL.  The backend is .net and the report writer, check programs, etc. is all Microsoft junk.

I've tried to build the site in .net to work with all of that...I've even tried DNN and the 'starter kits' but it all comes VERY SHORT of anything like Joomla.

as far as the ado...I was meaning that it works with Mambo and that might throw Mambo back in the running for this particular site...not sure how I feel about that option yet...it's still better than DNN.

The DBQ thing looks interesting, but the download is gone...until then, I'm going to see what I can do in an MSSQL cron job to update the MySQL server...that would be pretty darn cool.

hey mknz

I was wondering off on a totally different line of thought in post. Now it's all clear

Sunycing is one of those `good ideas` which some how always seems to end up more difficult than it should (at least in my experience). Why not just slap a copy of ADOdb into a component and add the necessary configuration options (username password) to the config.xml file?

Then it should be plain sailing

and there'd be no neeed for duplication of data.

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



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.

Er... hmmm. Only just occured to me that you may not be using 1.5 oh well here are some 1.5esque instructions to get you going

Create a new folder somewhere where you can prepare your new component.

Download yourself a copy of ADOdb and put it to it's own folder. Then create a compoennt install XMl file. should look summit like this:



Create an XML file named config.xml and put this in it:



Then create a file called admin.myextension.php and add this to it:
[php]// no direct access
defined( '_JEXEC' ) or die( 'Restricted access' );

JToolBarHelper::preferences();[/php]

That should get you started. Once you;ve done that bung it all in an archive and try and install it as a component!

Note that I just wrote this on the fly so there are probably some mistakes lol.

@Moderators

Can we split this thread into two? Seems to have gone way off topic... fanks!

Since this seems to be the definiative topic for Joomla and MSSQL, I wanted to inquire on the status of compatibility.  I've just started with an organization that NEEDS Joomla! and only will allow us to use MSSQL.  I believe DNN is the backup, but I'm not excited about that alternative at all.  I'm not a programmer, but how and what can be done to assist in this process?  Thanks so much for your work so far!

Jack 

Joomla for MSSQL would be amazing. Microsoft offers crazy discounts to non-profits for ther products. Right now I use Joomla fr my side biz, but forthe company I work fr we're an MSSQL only shop. I'd love to see Joomla work with MSSQL. Sadly I'm not a programmer, so all I can do is tell the programmers in the trenches how cool it would be .

I agree!

However as an update, I've had to bite the bullet and go with DNN.

Any ideas on how to accomplish the integration on a site wide level would be much appreciated.

Or perhaps it might make more sense to make it an option in a later release.

More than cool, it could reach a large, previously untapped community, limited to MS products in their organization.  I'm sure it would certainly sweep the federal sector.  Anyone that knows programming have any thoughts on how to push this forward?  Is there anything a non-programmer like myself could do to assist?

just for the one project.  I can't do the amount of work it'll take to completely integrate everything.
I'm just not a programmer.  I love Joomla, but I love it's ease of use and the fact that I can do a heavy project with little or no coding.

This one's just WAY over my head and I'm not ashamed to admit it.

I'm with JackBreech...let me know how I can help!!!  I'll setup a guinea pig site to test...just on the off chance it'll rock the DNN site.

excuses excuses lol.

Maybe you could convince your employer (unless your self employed) to pay someone to write the MSSQL driver - hehe.

maybe...but I'd have to get a figure going.
Without a figure, I'm shooting in the dark.

ANYBODY?

and if there's anyone else out there in a similar situation...want to split it?