The Joomla! Forum ™



Forum rules


Please use the mailing list here: http://groups.google.com/group/joomla-dev-general rather than this forum.



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 92 posts ]  Go to page 1, 2, 3, 4  Next
Author Message
 Post subject: MSSQL for Joomla! 1.5
PostPosted: Fri Feb 16, 2007 6:41 pm 
Joomla! Intern
Joomla! Intern

Joined: Thu Feb 09, 2006 10:36 am
Posts: 66
Location: Wales
I have written a JDatabaseMSSQL - is this of interest, and anyone want to help me test it?

igevans

_________________
Joomla! 1.5 Translations Manager -
http://joomlacode.org/gf/project/joomla_1_5_tr1/
Welsh Translations -
http://joomlacode.org/gf/project/cymraeg/


Top
 Profile  
 
PostPosted: Sat Feb 17, 2007 4:57 pm 
User avatar
Joomla! Ace
Joomla! Ace

Joined: Wed Aug 17, 2005 11:03 pm
Posts: 1388
Location: San Jose, California
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.


Top
 Profile  
 
PostPosted: Sun Feb 18, 2007 9:11 am 
User avatar
Joomla! Virtuoso
Joomla! Virtuoso

Joined: Sat Sep 24, 2005 11:01 pm
Posts: 4785
Location: Toronto, Canada
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


Top
 Profile  
 
PostPosted: Sun Feb 18, 2007 1:29 pm 
User avatar
Joomla! Enthusiast
Joomla! Enthusiast

Joined: Thu Aug 18, 2005 6:13 am
Posts: 172
Location: Antibes, France
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...


Top
 Profile  
 
PostPosted: Sun Feb 18, 2007 4:24 pm 
Joomla! Intern
Joomla! Intern

Joined: Thu Feb 09, 2006 10:36 am
Posts: 66
Location: Wales
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.

_________________
Joomla! 1.5 Translations Manager -
http://joomlacode.org/gf/project/joomla_1_5_tr1/
Welsh Translations -
http://joomlacode.org/gf/project/cymraeg/


Top
 Profile  
 
PostPosted: Tue Feb 20, 2007 6:55 am 
Joomla! Fledgling
Joomla! Fledgling

Joined: Tue Feb 20, 2007 6:41 am
Posts: 1
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.


Top
 Profile  
 
PostPosted: Wed Feb 21, 2007 12:55 am 
User avatar
Joomla! Champion
Joomla! Champion

Joined: Fri Aug 12, 2005 12:47 am
Posts: 6569
If the database [drive] 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


Top
 Profile  
 
PostPosted: Wed Feb 21, 2007 1:13 am 
User avatar
Joomla! Ace
Joomla! Ace

Joined: Wed Aug 17, 2005 11:03 pm
Posts: 1388
Location: San Jose, California
*hint, hint* Along with that goes the following:  If while developing these [drive] 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.


Top
 Profile  
 
PostPosted: Thu Feb 22, 2007 10:53 am 
Joomla! Intern
Joomla! Intern

Joined: Thu Feb 09, 2006 10:36 am
Posts: 66
Location: Wales
louis.landry wrote:
*hint, hint* Along with that goes the following:  If while developing these [drive] 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 [drive] 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 [drive] 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

_________________
Joomla! 1.5 Translations Manager -
http://joomlacode.org/gf/project/joomla_1_5_tr1/
Welsh Translations -
http://joomlacode.org/gf/project/cymraeg/


Top
 Profile  
 
PostPosted: Fri Feb 23, 2007 9:44 pm 
User avatar
Joomla! Champion
Joomla! Champion

Joined: Fri Aug 12, 2005 12:47 am
Posts: 6569
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


Top
 Profile  
 
PostPosted: Sat Feb 24, 2007 1:27 pm 
Joomla! Intern
Joomla! Intern

Joined: Thu Feb 09, 2006 10:36 am
Posts: 66
Location: Wales
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

_________________
Joomla! 1.5 Translations Manager -
http://joomlacode.org/gf/project/joomla_1_5_tr1/
Welsh Translations -
http://joomlacode.org/gf/project/cymraeg/


Top
 Profile  
 
PostPosted: Sat Feb 24, 2007 9:27 pm 
User avatar
Joomla! Champion
Joomla! Champion

Joined: Fri Aug 12, 2005 12:47 am
Posts: 6569
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


Top
 Profile  
 
PostPosted: Wed May 02, 2007 2:38 am 
Joomla! Apprentice
Joomla! Apprentice

Joined: Tue May 01, 2007 9:38 pm
Posts: 25
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!


Top
 Profile  
 
PostPosted: Wed May 02, 2007 6:16 am 
Joomla! Enthusiast
Joomla! Enthusiast

Joined: Mon Jul 10, 2006 8:16 pm
Posts: 249
Location: CA
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


Top
 Profile  
 
PostPosted: Wed May 02, 2007 1:24 pm 
User avatar
Joomla! Enthusiast
Joomla! Enthusiast

Joined: Sun Aug 21, 2005 11:41 pm
Posts: 143
Location: UK
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.


Top
 Profile  
 
PostPosted: Wed May 02, 2007 2:04 pm 
Joomla! Apprentice
Joomla! Apprentice

Joined: Tue May 01, 2007 9:38 pm
Posts: 25
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.


Top
 Profile  
 
PostPosted: Wed May 02, 2007 3:06 pm 
Joomla! Apprentice
Joomla! Apprentice

Joined: Tue May 01, 2007 9:38 pm
Posts: 25
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!


Top
 Profile  
 
PostPosted: Wed May 02, 2007 6:45 pm 
User avatar
Joomla! Explorer
Joomla! Explorer

Joined: Fri Sep 16, 2005 9:13 am
Posts: 433
@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 [drive] 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?

_________________
Signature Rules viewtopic.php?f=8&t=65


Top
 Profile  
 
PostPosted: Wed May 02, 2007 7:00 pm 
Joomla! Apprentice
Joomla! Apprentice

Joined: Tue May 01, 2007 9:38 pm
Posts: 25
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.


Top
 Profile  
 
PostPosted: Thu May 03, 2007 10:15 am 
User avatar
Joomla! Explorer
Joomla! Explorer

Joined: Fri Sep 16, 2005 9:13 am
Posts: 433
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.

_________________
Signature Rules viewtopic.php?f=8&t=65


Top
 Profile  
 
PostPosted: Thu May 03, 2007 1:24 pm 
Joomla! Apprentice
Joomla! Apprentice

Joined: Tue May 01, 2007 9:38 pm
Posts: 25
that's what I'm looking for...that...

Quote:
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.


Top
 Profile  
 
PostPosted: Thu May 03, 2007 1:37 pm 
User avatar
Joomla! Explorer
Joomla! Explorer

Joined: Fri Sep 16, 2005 9:13 am
Posts: 433
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:
<?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:
<?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

_________________
Signature Rules viewtopic.php?f=8&t=65


Top
 Profile  
 
PostPosted: Tue May 08, 2007 3:43 pm 
Joomla! Apprentice
Joomla! Apprentice

Joined: Tue May 08, 2007 3:33 pm
Posts: 6
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)


Top
 Profile  
 
PostPosted: Tue May 08, 2007 6:58 pm 
User avatar
Joomla! Apprentice
Joomla! Apprentice

Joined: Thu Feb 22, 2007 2:52 am
Posts: 15
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)


Top
 Profile  
 
PostPosted: Tue May 08, 2007 7:05 pm 
Joomla! Apprentice
Joomla! Apprentice

Joined: Tue May 01, 2007 9:38 pm
Posts: 25
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.


Top
 Profile  
 
PostPosted: Tue May 08, 2007 7:11 pm 
User avatar
Joomla! Explorer
Joomla! Explorer

Joined: Fri Sep 16, 2005 9:13 am
Posts: 433
mknz wrote:
However as an update, I've had to bite the bullet and go with DNN.

:o

_________________
Signature Rules viewtopic.php?f=8&t=65


Top
 Profile  
 
PostPosted: Tue May 08, 2007 7:26 pm 
Joomla! Apprentice
Joomla! Apprentice

Joined: Tue May 08, 2007 3:33 pm
Posts: 6
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?


Top
 Profile  
 
PostPosted: Tue May 08, 2007 7:28 pm 
Joomla! Apprentice
Joomla! Apprentice

Joined: Tue May 01, 2007 9:38 pm
Posts: 25
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.


Top
 Profile  
 
PostPosted: Tue May 08, 2007 7:35 pm 
User avatar
Joomla! Explorer
Joomla! Explorer

Joined: Fri Sep 16, 2005 9:13 am
Posts: 433
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 [drive] - hehe.

_________________
Signature Rules viewtopic.php?f=8&t=65


Top
 Profile  
 
PostPosted: Tue May 08, 2007 7:40 pm 
Joomla! Apprentice
Joomla! Apprentice

Joined: Tue May 01, 2007 9:38 pm
Posts: 25
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?


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 92 posts ]  Go to page 1, 2, 3, 4  Next



Who is online

Users browsing this forum: No registered users and 13 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group