MSSQL for Joomla! 1.5

For Joomla! 1.5 Coding related discussions, please use: http://groups.google.com/group/joomla-dev-general
igevans
Joomla! Intern
Joomla! Intern
Posts: 66
Joined: Thu Feb 09, 2006 10:36 am
Location: Wales

MSSQL for Joomla! 1.5

Post by igevans » Fri Feb 16, 2007 6:41 pm

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

igevans

User avatar
louis.landry
Joomla! Ace
Joomla! Ace
Posts: 1380
Joined: Wed Aug 17, 2005 11:03 pm
Location: San Jose, California
Contact:

Re: MSSQL for Joomla! 1.5

Post by louis.landry » Sat Feb 17, 2007 4:57 pm

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
Joomla Platform Maintainer
A hacker does for love what others would not do for money.

User avatar
ianmac
Joomla! Virtuoso
Joomla! Virtuoso
Posts: 4784
Joined: Sat Sep 24, 2005 11:01 pm
Location: Toronto, Canada

Re: MSSQL for Joomla! 1.5

Post by ianmac » Sun Feb 18, 2007 9:11 am

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

User avatar
facedancer
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 172
Joined: Thu Aug 18, 2005 6:13 am
Location: Antibes, France
Contact:

Re: MSSQL for Joomla! 1.5

Post by facedancer » Sun Feb 18, 2007 1:29 pm

ianmac wrote: 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...

igevans
Joomla! Intern
Joomla! Intern
Posts: 66
Joined: Thu Feb 09, 2006 10:36 am
Location: Wales

Re: MSSQL for Joomla! 1.5

Post by igevans » Sun Feb 18, 2007 4:24 pm

facedancer wrote: 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
You do not have the required permissions to view the files attached to this post.

jummy
Joomla! Fledgling
Joomla! Fledgling
Posts: 1
Joined: Tue Feb 20, 2007 6:41 am

Re: MSSQL for Joomla! 1.5

Post by jummy » Tue Feb 20, 2007 6:55 am

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.

User avatar
Jinx
Joomla! Champion
Joomla! Champion
Posts: 6508
Joined: Fri Aug 12, 2005 12:47 am
Contact:

Re: MSSQL for Joomla! 1.5

Post by Jinx » Wed Feb 21, 2007 12:55 am

If the database driver works with a plain Joomla! 1.5 install we could consider adding it to the core in a future release.
Johan Janssens - Joomla Co-Founder, Lead Developer of Joomla 1.5

http://www.joomlatools.com - Joomla extensions that just work

User avatar
louis.landry
Joomla! Ace
Joomla! Ace
Posts: 1380
Joined: Wed Aug 17, 2005 11:03 pm
Location: San Jose, California
Contact:

Re: MSSQL for Joomla! 1.5

Post by louis.landry » Wed Feb 21, 2007 1:13 am

*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
Joomla Platform Maintainer
A hacker does for love what others would not do for money.

igevans
Joomla! Intern
Joomla! Intern
Posts: 66
Joined: Thu Feb 09, 2006 10:36 am
Location: Wales

Re: MSSQL for Joomla! 1.5

Post by igevans » Thu Feb 22, 2007 10:53 am

louis.landry wrote: *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

User avatar
Jinx
Joomla! Champion
Joomla! Champion
Posts: 6508
Joined: Fri Aug 12, 2005 12:47 am
Contact:

Re: MSSQL for Joomla! 1.5

Post by Jinx » Fri Feb 23, 2007 9:44 pm

Interesting, are there any recommendations you would like to make as how we can adapt our DB drvers to accomodate for this problem ?
Johan Janssens - Joomla Co-Founder, Lead Developer of Joomla 1.5

http://www.joomlatools.com - Joomla extensions that just work

igevans
Joomla! Intern
Joomla! Intern
Posts: 66
Joined: Thu Feb 09, 2006 10:36 am
Location: Wales

Re: MSSQL for Joomla! 1.5

Post by igevans » Sat Feb 24, 2007 1:27 pm

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

User avatar
Jinx
Joomla! Champion
Joomla! Champion
Posts: 6508
Joined: Fri Aug 12, 2005 12:47 am
Contact:

Re: MSSQL for Joomla! 1.5

Post by Jinx » Sat Feb 24, 2007 9:27 pm

Oke, thanks for the valuable feedback looking forward to your first alpha code. Keep up the good work !
Johan Janssens - Joomla Co-Founder, Lead Developer of Joomla 1.5

http://www.joomlatools.com - Joomla extensions that just work

mknz
Joomla! Apprentice
Joomla! Apprentice
Posts: 25
Joined: Tue May 01, 2007 9:38 pm

Re: MSSQL for Joomla! 1.5

Post by mknz » Wed May 02, 2007 2:38 am

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!

orware
Joomla! Explorer
Joomla! Explorer
Posts: 255
Joined: Mon Jul 10, 2006 8:16 pm
Location: CA
Contact:

Re: MSSQL for Joomla! 1.5

Post by orware » Wed May 02, 2007 6:16 am

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

User avatar
Vince
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 143
Joined: Sun Aug 21, 2005 11:41 pm
Location: UK
Contact:

Re: MSSQL for Joomla! 1.5

Post by Vince » Wed May 02, 2007 1:24 pm

mknz wrote: 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 may be shot down in flames for this, but have you considered using the following instead of Joomla?

http://www.dotnetnuke.com/

- Vince
:-[ :-[
www.hostbaron.com - low price Joomla starter packages.

www.SpanishVilla.com - Buy, rent or sell a Spanish property.

mknz
Joomla! Apprentice
Joomla! Apprentice
Posts: 25
Joined: Tue May 01, 2007 9:38 pm

Re: MSSQL for Joomla! 1.5

Post by mknz » Wed May 02, 2007 2:04 pm

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.

mknz
Joomla! Apprentice
Joomla! Apprentice
Posts: 25
Joined: Tue May 01, 2007 9:38 pm

Re: MSSQL for Joomla! 1.5

Post by mknz » Wed May 02, 2007 3:06 pm

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!

User avatar
webamoeba
Joomla! Explorer
Joomla! Explorer
Posts: 433
Joined: Fri Sep 16, 2005 9:13 am
Contact:

Re: MSSQL for Joomla! 1.5

Post by webamoeba » Wed May 02, 2007 6:45 pm

@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?

mknz
Joomla! Apprentice
Joomla! Apprentice
Posts: 25
Joined: Tue May 01, 2007 9:38 pm

Re: MSSQL for Joomla! 1.5

Post by mknz » Wed May 02, 2007 7:00 pm

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.

User avatar
webamoeba
Joomla! Explorer
Joomla! Explorer
Posts: 433
Joined: Fri Sep 16, 2005 9:13 am
Contact:

Re: MSSQL for Joomla! 1.5

Post by webamoeba » Thu May 03, 2007 10:15 am

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.

mknz
Joomla! Apprentice
Joomla! Apprentice
Posts: 25
Joined: Tue May 01, 2007 9:38 pm

Re: MSSQL for Joomla! 1.5

Post by mknz » Thu May 03, 2007 1:24 pm

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.

User avatar
webamoeba
Joomla! Explorer
Joomla! Explorer
Posts: 433
Joined: Fri Sep 16, 2005 9:13 am
Contact:

Re: MSSQL for Joomla! 1.5

Post by webamoeba » Thu May 03, 2007 1:37 pm

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 :P

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:

Code: Select all

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE install SYSTEM "http://dev.joomla.org/xml/1.5/component-install.dtd">
<install type="component" version="1.5">
  <name>My Extension</name>
  <creationDate>MonthName Year</creationDate>
  <author>Author's Name</author>
  <authorEmail>Author's Email</authorEmail>
  <authorUrl>Author's Website</authorUrl>
  <copyright>Copyright Notice</copyright>
  <license>Component License Agreement</license>
  <version>Component Version</version>
  <description>Component Description</description>
  <administration>
    <menu>My Extension</menu>
    <files>
       <filename>admin.myextension.php</filename>
       <filename>config.xml</filename>
       <folder>adobd</folder>
    </files>
  </administration>
  <install />
  <uninstall />
</install>
Create an XML file named config.xml and put this in it:

Code: Select all

<?xml version="1.0" encoding="utf-8"?>
<config>
    <params>
        <param name="username" type="text" label="Username" description="MSSQL Username" size="30" />
        <param name="password" type="password" label="Password" description="MSSQL Password" size="30" />
        <param name="server" type="text" label="Server" description="MSSQL Server" size="30" />
    </params>
</config>
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! :D

JackBreech
Joomla! Apprentice
Joomla! Apprentice
Posts: 6
Joined: Tue May 08, 2007 3:33 pm

Re: MSSQL for Joomla! 1.5

Post by JackBreech » Tue May 08, 2007 3:43 pm

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  8)

User avatar
dayron
Joomla! Apprentice
Joomla! Apprentice
Posts: 15
Joined: Thu Feb 22, 2007 2:52 am

Re: MSSQL for Joomla! 1.5

Post by dayron » Tue May 08, 2007 6:58 pm

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 :).
JackBreech wrote: 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  8)

mknz
Joomla! Apprentice
Joomla! Apprentice
Posts: 25
Joined: Tue May 01, 2007 9:38 pm

Re: MSSQL for Joomla! 1.5

Post by mknz » Tue May 08, 2007 7:05 pm

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.

User avatar
webamoeba
Joomla! Explorer
Joomla! Explorer
Posts: 433
Joined: Fri Sep 16, 2005 9:13 am
Contact:

Re: MSSQL for Joomla! 1.5

Post by webamoeba » Tue May 08, 2007 7:11 pm

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

JackBreech
Joomla! Apprentice
Joomla! Apprentice
Posts: 6
Joined: Tue May 08, 2007 3:33 pm

Re: MSSQL for Joomla! 1.5

Post by JackBreech » Tue May 08, 2007 7:26 pm

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?

mknz
Joomla! Apprentice
Joomla! Apprentice
Posts: 25
Joined: Tue May 01, 2007 9:38 pm

Re: MSSQL for Joomla! 1.5

Post by mknz » Tue May 08, 2007 7:28 pm

webamoeba wrote:
mknz wrote:However as an update, I've had to bite the bullet and go with DNN.
:o
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.

User avatar
webamoeba
Joomla! Explorer
Joomla! Explorer
Posts: 433
Joined: Fri Sep 16, 2005 9:13 am
Contact:

Re: MSSQL for Joomla! 1.5

Post by webamoeba » Tue May 08, 2007 7:35 pm

mknz wrote:I'm just not a programmer
excuses excuses ;) lol.

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

mknz
Joomla! Apprentice
Joomla! Apprentice
Posts: 25
Joined: Tue May 01, 2007 9:38 pm

Re: MSSQL for Joomla! 1.5

Post by mknz » Tue May 08, 2007 7:40 pm

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?


Locked

Return to “Joomla! 1.5 Coding”