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.  [ 10 posts ] 
Author Message
 Post subject: Connection with MS SQL
PostPosted: Wed Jun 20, 2012 7:24 pm 
Joomla! Intern
Joomla! Intern

Joined: Tue Apr 28, 2009 7:37 am
Posts: 50
Location: pune
Hi All,

Does any buddy know how to connect with ms sql server.

I tried below code but it doesn't work.

This is in core php . but i want this for joomla. i will convert it once i got success to connect with mssql. :geek:

<?php
$myServer = "localhost";
$myUser = "your_name";
$myPass = "your_password";
$myDB = "examples";

//connection to the database
$dbhandle = mssql_connect($myServer, $myUser, $myPass)
or die("Couldn't connect to SQL Server on $myServer");

//select a database to work with
$selected = mssql_select_db($myDB, $dbhandle)
or die("Couldn't open database $myDB");

//declare the SQL statement that will query the database
$query = "SELECT id, name, year ";
$query .= "FROM cars ";
$query .= "WHERE name='BMW'";

//execute the SQL query and return records
$result = mssql_query($query);

$numRows = mssql_num_rows($result);
echo "<h1>" . $numRows . " Row" . ($numRows == 1 ? "" : "s") . " Returned </h1>";

//display the results
while($row = mssql_fetch_array($result))
{
echo "<li>" . $row["id"] . $row["name"] . $row["year"] . "</li>";
}
//close the connection
mssql_close($dbhandle);
?>

_________________
Thanks
Kind Regards


Top
 Profile  
 
PostPosted: Wed Jun 20, 2012 8:23 pm 
Joomla! Master
Joomla! Master

Joined: Mon Oct 27, 2008 9:27 pm
Posts: 17208
Location: Akershus, Norway
I use this code. Check what MSSQL library that is install in php on your server (sqlsrv_connect or mssql_connect).

Code:
      $server   = $this->params->get('host');
      $username = $this->params->get('username');
      $password = $this->params->get('password');
      $database = $this->params->get('database');
      $connectionOptions = array("Database"=>$database,"PWD"=>$password,"UID"=>$username,"CharacterSet" => "UTF-8");
      

      // Connect to MSSQL
      $link = sqlsrv_connect( $server, $connectionOptions);      


Top
 Profile  
 
PostPosted: Wed Jun 20, 2012 9:45 pm 
Joomla! Intern
Joomla! Intern

Joined: Tue Apr 28, 2009 7:37 am
Posts: 50
Location: pune
Many Thanks for your valuable reply.

is there any way to detect the library so that we can use code like below

if( lib1)
{
$link = sqlsrv_connect( $server, $connectionOptions);
}
else
{
$link = mssql_connect( $server, $connectionOptions);
}

_________________
Thanks
Kind Regards


Top
 Profile  
 
PostPosted: Thu Jun 21, 2012 5:08 am 
Joomla! Master
Joomla! Master

Joined: Mon Oct 27, 2008 9:27 pm
Posts: 17208
Location: Akershus, Norway
If you do not get a connection, try the other. Are you trying this on a Windows server?

J1.5 does not have a MSSQL library. J2.5 can use a MS SQL server as it's database.

Code:
$link = sqlsrv_connect( $server, $connectionOptions);      
      if (!$link) {
         $response->status = JAUTHENTICATE_STATUS_FAILURE;
         $response->error_message = JText::_('NO_CONNECTION_TO_DATABASE');
         return false;
      }
      else {


$link will be true if a successfull connection.


Top
 Profile  
 
PostPosted: Fri Jun 22, 2012 6:06 am 
Joomla! Intern
Joomla! Intern

Joined: Tue Apr 28, 2009 7:37 am
Posts: 50
Location: pune
Many many thanks.

I will try it as well and let you know

_________________
Thanks
Kind Regards


Top
 Profile  
 
PostPosted: Sat Jun 30, 2012 10:56 pm 
Joomla! Intern
Joomla! Intern

Joined: Tue Apr 28, 2009 7:37 am
Posts: 50
Location: pune
Hi

I just got success to connect with mssql server

i am using linux and apache as web server
and using following code to connect

$db=mssql_connect($server.':1433', $username, $password) or die("Connect failed");

//select a database to work with
$selected = mssql_select_db($database, $db) or die("Couldn't open database $db");


now i need to find the way to use inside joomla so that i can use joomla's library
if you please let me know

_________________
Thanks
Kind Regards


Top
 Profile  
 
PostPosted: Sun Sep 02, 2012 9:11 pm 
Joomla! Intern
Joomla! Intern

Joined: Tue Apr 28, 2009 7:37 am
Posts: 50
Location: pune
Hi

Do you know for some columns i am getting this error

Warning: mssql_query(): message: Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier. (severity 16)

please can you help to resolve this problem.

i am using mssql_connect
and mssql_query

_________________
Thanks
Kind Regards


Top
 Profile  
 
PostPosted: Mon Sep 03, 2012 3:40 pm 
Joomla! Master
Joomla! Master

Joined: Mon Oct 27, 2008 9:27 pm
Posts: 17208
Location: Akershus, Norway
Do as it says, upgrade the db client to a newer version.


Top
 Profile  
 
PostPosted: Mon Sep 03, 2012 4:04 pm 
Joomla! Intern
Joomla! Intern

Joined: Tue Apr 28, 2009 7:37 am
Posts: 50
Location: pune
Thanks to reply.
i will do and let you know.

_________________
Thanks
Kind Regards


Top
 Profile  
 
PostPosted: Thu Nov 08, 2012 7:27 pm 
Joomla! Intern
Joomla! Intern

Joined: Tue Apr 28, 2009 7:37 am
Posts: 50
Location: pune
Yes you where right.

i have updated the db and problem get solved.

please help me in another problem

i am getting values from the ms sql server but some value are showing special character
see the following example


first row

ݩA�i=�L������ 0 0 ��_[�Y=C���r�8�� Dec 1 2010 04:22:44:773PM 0 17Ř43,44PK Ze zdanitelných plnění vykázaných na řádcích 3 až 13 (pořízení majetku - pozdější aktivace) - krácený odpočet 0 0 1 wSUߓ�B��D��[�


second row
B���L��_P�" 0 0 ��_[�Y=C���r�8�� Dec 1 2010 04:24:28:807PM 1 17Ř51B Hodnota plnění nezapočítávaných do výpočtu koeficientů (


i stuck with this character �


Please help as early as possible.

_________________
Thanks
Kind Regards


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 10 posts ] 



Who is online

Users browsing this forum: No registered users and 20 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