MSSQL for Joomla! 1.5

[mknz]

We did it. It's really no big deal if you can get a good windows person to deal with the MSsql part. We have a bunch of automated process' that check the MYsql for changes. I added a flag field to every table I needed to search and added that code in everything I wanted to track (jos_users and jos_comprofiler for sure). Then, the MSsql gets the info and all is well. Whenever we do a batch process or change on the other end, it pushes the info to MYsql.

So, we have two full-fledged data sources working in tandem. Some info is unique on each side, but the corresponding fields are somewhat self-sufficient. We have not had to fix anything in the three months it's been up and I don't foresee any problems with it.

Just thought I'd let you guys know. Sometimes, as corny as it sounds, you have to think outside the box...the box being J! in this case.

[onestop]

Hooray!!! How did you do it? Is there a component now? Any news on this would be immensely exciting, as we've been searching for a MSSQL solution for months.

[Tonie]

From the looks of it, it's a bunch of scripts that imports/exports data from the Mysql database to MSSQL.

[onestop]

From the looks of it, it's a bunch of scripts that imports/exports data from the Mysql database to MSSQL.

Where one would one go to get a look at it?

[Tonie]

Ask him, I was talking about his wording of describing the solution.

[bidsro]

I'm also interested to have this code. mknz, can you post your script here, please?

Thank you

[madoracle]

Hi All,

As a commercial, public sector and personal user I am impressed with Joomla beyond words and certainly have no issues with MySQL. However, MSSQL is the db of choice for most of the commercial side of what I do... a clean MSSQL driver would be invaluable and open unlimited possibilities particularly in light of some of the tremendous capabilities of SSIS and SSRS.

Is there any further development against this topic?

[Tonie]

Not really by the looks of it.

[oc666]

Hello
How could I use this driver? Could I use this driver to build whole joomla system?

[dattard]

Would anybody be interested in reviving this project?

[oc666]

Would anybody be interested in reviving this project?

I would, but i think it's dead.

[fireman]

Someone should do something. With Sun Microsystems (owners of MySQL) being purchased by Oracle, there is a possibility that MySQL could die, unless a fork is developed.

[ianmac]

Someone should do something. With Sun Microsystems (owners of MySQL) being purchased by Oracle, there is a possibility that MySQL could die, unless a fork is developed.

yes... someone http://en.wikipedia.org/wiki/Brian_Aker should do something http://en.wikipedia.org/wiki/Drizzle_(database_server) indeed!

Ian

[stufflebeamrc]

I'm not really sure if this information is helpful, but I have been really interested in using mssql.

I'm not really certain at this point as to whether the issue is between PHP and the database or Joomla and the database.

If it is an issue between PHP and MSSQL that is at the heart of the issue, the following links from Microsoft may be of tremendous interest in solving the problem.

http://www.microsoft.com/web/platform/phponwindows.aspx

http://www.microsoft.com/web/platform/database.aspx

If it is an issue between Joomla and MSSQL, I'm still looking for the solution.

Please let me know if this was useful information.

Randy

[ianmac]

I'm not really sure if this information is helpful, but I have been really interested in using mssql.

I'm not really certain at this point as to whether the issue is between PHP and the database or Joomla and the database.

If it is an issue between PHP and MSSQL that is at the heart of the issue, the following links from Microsoft may be of tremendous interest in solving the problem.

http://www.microsoft.com/web/platform/phponwindows.aspx

http://www.microsoft.com/web/platform/database.aspx

If it is an issue between Joomla and MSSQL, I'm still looking for the solution.

Please let me know if this was useful information.

Randy

I'm not sure I am understanding what you are asking?

Are you asking what the issue is with with Joomla! running on MSSQL?

If so, the issue is that every flavour of SQL is a little bit different and as a result the queries don't work.

Ian

[stufflebeamrc]

Randy

I'm not sure I am understanding what you are asking?

Are you asking what the issue is with with Joomla! running on MSSQL?

If so, the issue is that every flavour of SQL is a little bit different and as a result the queries don't work.

Ian

Actually, having cut my teeth on databases back in the late 80's, I do understand the issue in the differences in databases. The question that I have is not with the differences in the databases and how they handle the data that is fed to them.

I apologize that I did not make myself more clear.

The question is: which has the compatibility issue with MSSQL -- Joomla or PHP?

I have to assume that the compatibility is with PHP, since php.ini is the only place (that I know of) where you actually decide which database driver you will use. (ie., this is where you uncomment mysql.dll and mysqli.dll; noting that just above the mysql.dll entry there is mssql.dll.)

However, if this compatibility (or should I say incompatibility) issue actually lies with the Joomla interface, then the updated software from Microsoft (that I provided the link to above) will most likely NOT address the problem of getting Joomla to work with MSSQL.

I will be downloading and installing it on my windows server 2003, with IIS 6, and the latest releases of PHP, MSSQL, and Joomla tonight.

If the new software from Microsoft resolves the compatibility issue between PHP and MSSQL which in return, resolves the problem with Joomla, then I can open the server to others for testing. Just let me know if you would like to test.

However, if the problem exists with Joomla specifically, I won’t spin my wheels with this any further.

I hope that clarifies the issue as I understand it.

Randy

[Tonie]

It's Joomla itself, some queries in the code are specific for Mysql. Making Joomla work for MSSQL at the moment consists of looking through all queries, checking which ones are Mysql specific and change them to MSSQL or generic ANSI SQL specific.

[stufflebeamrc]

Thank you, for the information. You probably just saved me from a lot of time and frustration.

Randy

[jelutz]

I'm in a situation where I'm trying to create a component that pulls information out of a proprietary MSSQL database. They are also a MSSQL shop, so getting a MySQL database set up in the first place is questionable.

[jelutz]

So, what do I do with this .zip file?

[kflorida78]

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

I strongly suggests folks avoid DotNetNuke at all costs.
DNN is a cool infant...
However it is incredibly slow, bulky and content management isn't very intuitive. Developing applications and extensions for DNN is also far more difficult than Joomla.

I have personally built over 15 extensions for DNN because a client was hellbent on using asp.net and a free CMS.

I think down the road DNN might be good, for now you are way better off using Joomla. If you want a good pre-built CMS for asp.net your options are limited. You are really better off buying one like Ektron.

---------------

As-far-as subbing mssql for MySql for Joomla: I think this is a terrible idea. MsSql is proprietary and Microsoft has a very strict commercial license with it. Misuse of MsSql is a quick way to get sued. Furthermore many Joomla extensions are written to works specifically with MySql, so they will become inoperable. Althoughtthe SQL code in MySQL and MsSql are similar, they do differ enough to cause problems.
Besides, MySql can install on any platform, so why not use it?

----------------

I think what would be cool, is to build Joomla.net
I'm on board if someone wants to build Joomla in asp.net for when you have a client who insists on asp.net. It could be cool, as-long-as the functionality acts like Joomla. The biggest obstacle I forsee is the fact that asp.net is closed and must be compiled. So if such a thing was built, the system would have to rely heavily on XML files.

If someone starts the project, I'll join.

[bnisevic]

For those who didn't find an example how to use this MSSQL driver (because search engines find this topic) look at this board:
Connect to MSSQL (Microsoft) database - an example
http://forum.joomla.org/viewtopic.php?t=132828

[kflorida78]

For those who didn't find an example how to use this MSSQL driver (because search engines find this topic) look at this board:
Connect to MSSQL (Microsoft) database - an example
http://forum.joomla.org/viewtopic.php?t=132828

The issue isn't connecting to mssql.. that is fairly easy. The problem is that all Joomla extensions are coded with the default mysql connection or some a mysqli connection. And I have seen many which don't use the Joomla database connection function (they are hard-coded). So none of those applications will work if you convert Joomla to store on MSSql.

So by doing this you are creating yourself a can of worms and many compatibility issues.

[bnisevic]

I posted that because it was hard to find any example about this issue, and still I was unable to connect to the db.
Regarding compatibility, clearly Joomla is made on MySQL, but this SQLSrv class is good when you building an extension on client's special request.

[kflorida78]

I posted that because it was hard to find any example about this issue, and still I was unable to connect to the db.
Regarding compatibility, clearly Joomla is made on MySQL, but this SQLSrv class is good when you building an extension on client's special request.

Here is an example how to write to MSSQL, I hope this helps:

Code: Select all

 //-ENTER HOST ADDRESS HERE BETWEEN SINGLE QUOTES->
  $server = '111.111.111.11';
 //--DATABASE USERNAME HERE-->
  $dbuser = 'uSeRnAmE';
 //--PASSWORD HERE-->
  $dbpass = 'pAsSwOrD';
  
  // Connect to MSSQL
  $mssql_link = mssql_connect($server, $dbuser, $dbpass);
  
  if (!$mssql_link) {
      //--this provides an error if your application can't connect to the database->
	  die('Error connecting to MSSQL');
  }
  
  //--QUERY ----------->
	$query = mssql_query(" SELECT * FROM DatabaseName.dbo.TableName WHERE this_column='name' ");
	$rows = mssql_fetch_array($query, MSSQL_ASSOC);
	
	//get values
	$name = $rows['this_column'];
//--END QUERY----------|

//display result->
echo $name;

Note:
Do a google search like so:
php: mssql query

So you can find all the ways to query a mssql database via php. The above method works good for pulling one result.. But in most situations you are better using a foreach loop to pull arrays.

And you may optionally close your mssql connection to nominally speed up your page by adding a close function:

Code: Select all

mssql_close($mssql_link);

Hope that is useful.

[bnisevic]

Yes, I tried this php function before, with the following code:

Code: Select all

$host_inst = 'PROBOOK\SQLEXPRESS';
$username  = 'Bojann';
$password  = 'bojan41241';			
$dbconnect = mssql_connect($host_inst, $username, $password);         
if ( $dbconnect ) {
    $connected = '<b><font color="green">OK!</font></b>';
} else {
    $connected = '<b><font color="red">FAILED</font></b>';
}

But, I'm getting this error

Fatal error: Call to undefined function mssql_connect() in D:\.......

So, I understand that mssq_connect() isn't built-in function. I'm using PHP Version 5.3.1.

[bnisevic]

I found this:

For the mssql extension to work, all of the following must be true -

The php.ini that you are changing must be the one that php is using.

The php_mssql.dll must be in the ext folder.

The extension_dir setting in php.ini must be set to the ext folder, something like extension_dir = c:\php\ext

To get changes made to php.ini to take effect, you must stop and start the IIS web service. Do this in the services control panel or reboot your computer. Just stopping and starting the web site in the IIS management console is not enough.

mssql requires: ntwdblib.dll to be available on the Windows PATH. It is better to add the php folder to the Windows PATH instead of moving any files around. Re-logon or reboot your computer to get any changes made to the Windows PATH statement to take effect.

The php folder must have proper permissions set to allow the IIS user account to read the files in the php folder and in the ext folder.

I suppose the JDatabaseMSSQL driver (and JDatabaseSQLSrvin J 1.6) don't need this settings???

[bnisevic]

Oooh I see now! In mssql.php driver, in it's constructor we have this in first if statement:

Code: Select all

if (!function_exists( 'mssql_connect' )) {
			$this->_errorNum = 1;
			$this->_errorMsg = 'The MSSQL adapter "mssql" is not available.';
			return;
}

So, your PHP must pass that check. I'll try to use tips from previous post.

[bnisevic]

OK, guys, my conclusion.

Making MSSQL (SQLSrv) driver and JDatabaseMSSQL (JDatabaseSQLSrv) to work needs additional settings (editing php.ini, installing some DLLs, setting up IIS, restarting Apache, etc...). Even when MSSQL works it needs additional settings to work with unicode or using CAST for
ntext to text fields.
But using ODBC is way more easier for settings and for usage! It only needs to add new connection for SQL Server in ODBC like someone wrote before:

1. Administrative Tools -> Data Sources (ODBC)
2. System DNS
3. Add... "SQL Server"

Then we can simply use functions like odbc_connect(), odbc_exec()!

[Dupont77]

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

igevans

have you been successful? I like to try it, if successful share your idea then ill be gladly to apply it..