Connect to MSSQL (Microsoft) database - an example

Your code modifications and patches you want to share with others.
Locked
spambox
Joomla! Apprentice
Joomla! Apprentice
Posts: 31
Joined: Mon May 15, 2006 2:52 pm

Connect to MSSQL (Microsoft) database - an example

Post by spambox » Fri Jan 19, 2007 2:34 pm

Hi,

the past time a read various topics, people asking for howto query a mssql database. Assuming you have installed php5-sybase on your webserver, hereby a short example to show how I did. If someone else knows a better / cleaner / nicer etc. solution, shout!

Code: Select all

                
function dothetrick() {
                $db=mssql_connect("IP ADDRESS","USERNAME","PASSWORD");
                $d=mssql_select_db("DATABASE NAME", $db);
                $sql = "SELECT name,balance FROM tablename";
                $res = mssql_query($sql);
                $i = 0;
                // Convert the results to an object, so you can access your results
                // the same way like you would have queried a mysql from within Joomla
                while ($record = mssql_fetch_array($res)) {
                                $tmp = & new stdClass;
                                foreach ($record as $key => $value) {
                                $tmp->$key = & $record[$key];
                }
                                $array[$i] = $tmp;
                                $i++;
                }
                mssql_close();
                return $array;
}
If someone else has a better solution, just let me know!

paquito684
Joomla! Apprentice
Joomla! Apprentice
Posts: 6
Joined: Wed Jan 24, 2007 4:15 pm

Re: Connect to MSSQL (Microsoft) database - an example

Post by paquito684 » Thu Feb 01, 2007 9:10 am

I'm with php and joomla for 6 days, and i need connect Joomla with a MS sql server 7.0

Do you think i can use your code?

Please, i dont know what have i do with the code, help me.

I must to do a file.php or add the code to any file.php?

Thanx

lagibosen
Joomla! Fledgling
Joomla! Fledgling
Posts: 3
Joined: Sat Jun 28, 2008 11:43 pm
Contact:

Re: Connect to MSSQL (Microsoft) database - an example

Post by lagibosen » Sat Jun 28, 2008 11:55 pm

thanks for sharing your script

spambox
Joomla! Apprentice
Joomla! Apprentice
Posts: 31
Joined: Mon May 15, 2006 2:52 pm

Re: Connect to MSSQL (Microsoft) database - an example

Post by spambox » Mon Jun 30, 2008 8:28 am

Over a year ago, someone else did put some effort in writing a Microsoft SQL (MSSQL) layer for Joomla. Unfortunately, I don't remember his name anymore, but credits go to this fellow!

In the attached mssql.php I rewrote some parts of this work, so it will work with Joomla 1.5.3. Please feel free to use, or even better, to write your own enhancements! Extract & place this file in /libraries/joomla/database/database/ as mssql.php.

How to use? Very simple, just like your normal Joomla queries!
Please note: mssql support for php has to be installed on your server!

Code: Select all

$option['driver'] = 'mssql';
$option['host'] = 'hostname';
$option['user'] = 'username';
$option['password'] = 'password';
$option['database'] = 'mssqldatabasename';
$option['prefix'] = '';

$db = &JDatabase::getInstance($option);

$db->setQuery($query);

$results = $db->loadObjectList();
Even 'LIMIT 10,20' is supported, the query will be rewritten accordingly.
You do not have the required permissions to view the files attached to this post.

jackbauerr
I've been banned!
Posts: 1
Joined: Thu Jul 03, 2008 8:47 am

Re: Connect to MSSQL (Microsoft) database - an example

Post by jackbauerr » Thu Jul 03, 2008 9:51 am

errrrrr... Hi !
I am currently on a Joomla! project which should use a MSSQL Server.

I extracted your mssql.php into the correct path but where should I write the code you wrote in your post?

Thanks for your help!

jdschuitemaker
Joomla! Fledgling
Joomla! Fledgling
Posts: 2
Joined: Tue Jul 15, 2008 2:53 pm

Re: Connect to MSSQL (Microsoft) database - an example

Post by jdschuitemaker » Tue Jul 15, 2008 3:17 pm

I would be curious to know where to put the code too...

spambox
Joomla! Apprentice
Joomla! Apprentice
Posts: 31
Joined: Mon May 15, 2006 2:52 pm

Re: Connect to MSSQL (Microsoft) database - an example

Post by spambox » Sun Aug 10, 2008 10:42 am

The file I attached has not been made to let Joomla on itself run on a MSSQL backend. The file can be used if you develop your own component, plugin or what so ever wich needs to retrieve information from MSSQL.

So, if you develop your own component and you run a query on a MySQL database, you would normally do something like:
$db = & JFactory::getDBO();
$db->setQuery($query);
$results = $db->loadObjectList();

But if you want to query a MSSQL database, you can use the code as mentioned a few topics above. For more information on how to query a database in general, please see the Hello World 2 example on http://dev.joomla.org/component/option, ... omponents/

User avatar
PtrNrs
Joomla! Explorer
Joomla! Explorer
Posts: 355
Joined: Sat Jul 05, 2008 3:56 am
Location: Melbourne, Australia
Contact:

Re: Connect to MSSQL (Microsoft) database - an example

Post by PtrNrs » Fri Nov 14, 2008 9:47 pm

Thanks for this contribution, spambox. I think your mssql.php is based on the code produced by Ifan Evans (http://forum.joomla.org/viewtopic.php?t=142051) who has since moved away from Joomla.

A version of mssql.php has been put up in the Joomla extensions at http://extensions.joomla.org/component/ ... Itemid,35/. This uses ODBC - definitely an inferior approach as far as I'm concerned.

Is the version dated June 30, 2008 your latest?

Are you looking for collaborative development? if so, I'd be happy to help.

Particularly, I'd be looking to improve the support for stored procedure calls which unlocks huge power from SQL Server. For instance the following code would benefit from something similar to loadObjectList():-
$stmt = mssql_init('spz01');
$result=mssql_execute($stmt);

for ($i = 0; $i < mssql_num_rows( $result ); ++$i)
{
$item = mssql_fetch_row($result);
print_r ($item);
echo '<br>';
}
mssql_free_statement($stmt);


Pete Nurse
Melbourne Australia
Pete Nurse, Jix Software
http://www.jix.com.au
Jix: The Joomla Import Export & Update Utility

User avatar
mj_mohit
Joomla! Apprentice
Joomla! Apprentice
Posts: 5
Joined: Tue Nov 11, 2008 5:33 pm
Location: India
Contact:

Re: Connect to MSSQL (Microsoft) database - an example

Post by mj_mohit » Mon Dec 08, 2008 6:40 pm

hi i m not a pro in this, i would be happy to see a full mssql layer for joomla... as i m having problems with mysql right now. could not connect to mysql....blah....blah..... shared server... connection limits and a lot. so i would be happy to upgrade to mssql.
between can anyone tell me a free mysql remote host?

also i would be happy to help with mssql work. if there is anything i can do. (a learning student....learning php...)
--------- Knowing the best way isn't enough --------
-----Learning 9 wrong before 1 right is what kicks ----

User avatar
PtrNrs
Joomla! Explorer
Joomla! Explorer
Posts: 355
Joined: Sat Jul 05, 2008 3:56 am
Location: Melbourne, Australia
Contact:

Re: Connect to MSSQL (Microsoft) database - an example

Post by PtrNrs » Mon Dec 08, 2008 8:29 pm

I get the impression that a full implementation of MSSQL in Joomla is nowhere on the horizon (see also http://forum.joomla.org/viewtopic.php?t=142051). I was looking to implement a hybrid approach.

If you want to use Joomla, I think you'll need to perservere with mySQL.
Pete Nurse, Jix Software
http://www.jix.com.au
Jix: The Joomla Import Export & Update Utility

spambox
Joomla! Apprentice
Joomla! Apprentice
Posts: 31
Joined: Mon May 15, 2006 2:52 pm

Re: Connect to MSSQL (Microsoft) database - an example

Post by spambox » Wed Feb 25, 2009 2:44 pm

PtrNrs, sorry for the delay in my reply! The moment I received your message, I was on holiday and I totally forgot about it...

The version I currently have running is dated September 30th, 2008. When I run into an issue, I go fix it in mssql.php

Allthough a fully supported mssql layer for Joomla would be a bridge too far (I guess), I would be more then happy to join an unofficial mssql force.



How to use:
place file in /libraries/database/database/mssql.php

How to connect:

$options['driver'] = 'mssql';
$options['host'] = '10.4.3.17';
$options['user'] = 'username';
$options['password'] = 'password';
$options['database'] = 'database name';
$options['prefix'] = '';

$db =& JDatabase::getInstance( $options );
if ( JError::isError($db) ) {
jexit('Database Error: ' . $db->toString() );
}
if ($db->getErrorNum() > 0) {
JError::raiseError(500, 'JDatabase::getInstance: Could not connect to database <br />');
}

$query = "SELECT *"
. "\n FROM validation"
. "\n WHERE state = '1'"
. "\n AND (expiration > '$now'"
. "\n OR expiration = NULL)"
. "\n AND (name LIKE '$username')"
. "\n ORDER BY name";

$db->setQuery($query);
$results = $db->loadObjectList();
You do not have the required permissions to view the files attached to this post.

niklasenskog
Joomla! Fledgling
Joomla! Fledgling
Posts: 2
Joined: Tue Mar 10, 2009 10:11 pm

Re: Connect to MSSQL (Microsoft) database - an example

Post by niklasenskog » Tue Mar 10, 2009 10:32 pm

Anybody else had trouble downloading that latest version of mssql.php just above?
I need to share the user tables of a mssql based iis/asp classic site (does that suck? it's 2009, I had no idea!) with a joomla site currently running on myssql. This looks like it! Except *I can't get hold of the file*. The first version posted in the thread start downloads nicely though, weird no?

Brand new to Joomla, but I'm excited to get to know it! Any help downloading appreciated!

Cheers, n

spambox
Joomla! Apprentice
Joomla! Apprentice
Posts: 31
Joined: Mon May 15, 2006 2:52 pm

Re: Connect to MSSQL (Microsoft) database - an example

Post by spambox » Wed Mar 11, 2009 9:21 am

Strange, I also get a 'file could not be delivered' message.

Extract & place attached file in /libraries/database/database/mssql.php

Please note: it's still not supported, use at your own risk. Although, reading from mssql can be considered safe. Take precautions for writing, this has not been tested thoroughly.
You do not have the required permissions to view the files attached to this post.

niklasenskog
Joomla! Fledgling
Joomla! Fledgling
Posts: 2
Joined: Tue Mar 10, 2009 10:11 pm

Re: Connect to MSSQL (Microsoft) database - an example

Post by niklasenskog » Wed Mar 11, 2009 11:17 pm

Thanks, those are some speedy reflexes! I'll get to it in a while, I'll be sure to check back with this thread when I'm in the trenches!

User avatar
demo38
Joomla! Apprentice
Joomla! Apprentice
Posts: 32
Joined: Fri Jun 22, 2007 2:58 am
Location: oh, usa
Contact:

Re: Connect to MSSQL (Microsoft) database - an example

Post by demo38 » Fri Apr 17, 2009 9:10 pm

Oh, this sounds good... :pop

How would I go about implementing a MsSQL query using this? We have a MsSQL server that I would like to pull data from to display some of the information from one table to use as an employee list on our Joomla intranet.

I have placed the mssql.php file above into the joomla/database/database/ folder. CHECK.

I see that the additional code that SPAMBOX provides makes the database connection and the query, but not having much experience php scripting, I'm not sure exactly how to implement it?

Can I use this inside of JIncludes extension? I tried it that way, adding <?php the top of the snippet and plugging in our host / db / user / pass / and editing the SELECT query in the above and assigned it to a PHP (Input Code) snippet inside of JIncludes and then called the snippet from an article (plugin is enabled), but the page that displays shows the article title with nothing inside the page...

I've wasted DAYS trying to get LQM to make a connection to our SQL server through the ADObd library... Then found this post. You're the hope I have left!

Any insight is much appreciated,
-Jonathon

nvybvl
Joomla! Apprentice
Joomla! Apprentice
Posts: 12
Joined: Mon Sep 07, 2009 12:14 pm

Re: Connect to MSSQL (Microsoft) database - an example

Post by nvybvl » Sat Oct 24, 2009 11:41 pm

Hi to all, i need to know if joomla does support this connection to MSSQL
#SQL SERVER HOST
$muweb['db_host'] = "xxx"";

#DATABASE 1
$muweb['db_name'] = "xxx";

#DATABASE 2
$muweb['db_name2'] = "xxx";

#SQL USERNAME
$muweb['db_user']= "xxx";

#SQL PASSWORD
$muweb['db_password'] = "xxx";

#CONNECTION TYPE WITH SQL SERVER: ODBC/MSSQL
$muweb['connection_type'] = "xxx"";



#ONLY IF NECESSARY
#MUWEB DEBUG MODE: 1 ON / 0 OFF
$muweb['debug'] = 0;



?>
MuOnline ( mmorpg ) use this connection tipe from web to database if you use LOCALHOST.
I need this connection for my web which is on another Host that server is .

oc666
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 215
Joined: Wed Oct 10, 2007 9:46 am
Location: Israel, TA
Contact:

Re: Connect to MSSQL (Microsoft) database - an example

Post by oc666 » Wed Jan 06, 2010 12:01 pm

spambox wrote:Strange, I also get a 'file could not be delivered' message.

Extract & place attached file in /libraries/database/database/mssql.php

Please note: it's still not supported, use at your own risk. Although, reading from mssql can be considered safe. Take precautions for writing, this has not been tested thoroughly.
Sorry to popup, but the link is broken.
http://www.embad.com - new way to insert ads to your website

brokencreations
Joomla! Fledgling
Joomla! Fledgling
Posts: 1
Joined: Tue Oct 19, 2010 8:25 pm
Location: Cypress Tx
Contact:

Re: Connect to MSSQL (Microsoft) database - an example

Post by brokencreations » Tue Oct 19, 2010 9:30 pm

Has anyone been able to create a module or component with this?

I am trying to bring my company out of frame sets (WTF, you may be saying, i know...trust me) as a template framework and the underlying system is built on MS SQL and ASP.NET, I want to bring us over to Joomla because i do not have any experience with .NET or Visual Studio. I need to use the existing DB information and table structure but want to access it through Joomla. Any help/insight into using this code would be great.
Ben Clapp
Co-Owner/Lead Designer

Broken Creations Design
"destroy uniformity of artistic imagination, especially artistic works"

ByronL
Joomla! Fledgling
Joomla! Fledgling
Posts: 3
Joined: Tue Oct 26, 2010 2:33 am
Location: Western Australia
Contact:

Re: Connect to MSSQL (Microsoft) database - an example

Post by ByronL » Tue Oct 26, 2010 2:53 am

so glad to have found this thread, thanks for the help

glacekit
Joomla! Fledgling
Joomla! Fledgling
Posts: 1
Joined: Tue Sep 28, 2010 9:36 am

Re: Connect to MSSQL (Microsoft) database - an example

Post by glacekit » Tue Nov 02, 2010 6:04 am

Please upload the latest version of the mssql.php file, if anyone has it?

Sujains4445
Joomla! Fledgling
Joomla! Fledgling
Posts: 4
Joined: Wed Nov 10, 2010 6:47 am

Re: Connect to MSSQL (Microsoft) database - an example

Post by Sujains4445 » Thu Nov 18, 2010 4:40 am

By far the easiest way that I've found to connect to a SQL server is to route it through a ODBC connection.

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

Then just use odbc_connect("sql_test", "user", "pass") instead.


Locked

Return to “Core Hacks and Patches”