Connection with MS SQL

For Joomla! 1.5 Coding related discussions, please use: http://groups.google.com/group/joomla-dev-general
Forum rules
Please use the mailing list here: http://groups.google.com/group/joomla-dev-general rather than this forum.
gurukatre
Joomla! Intern
Joomla! Intern
Posts: 50
Joined: Tue Apr 28, 2009 7:37 am
Location: pune
Contact:

Connection with MS SQL

Postby gurukatre » Wed Jun 20, 2012 7:24 pm

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

User avatar
Per Yngve Berg
Joomla! Master
Joomla! Master
Posts: 21431
Joined: Mon Oct 27, 2008 9:27 pm
Location: Akershus, Norway

Re: Connection with MS SQL

Postby Per Yngve Berg » Wed Jun 20, 2012 8:23 pm

I use this code. Check what MSSQL library that is install in php on your server (sqlsrv_connect or mssql_connect).

Code: Select all

      $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);      

gurukatre
Joomla! Intern
Joomla! Intern
Posts: 50
Joined: Tue Apr 28, 2009 7:37 am
Location: pune
Contact:

Re: Connection with MS SQL

Postby gurukatre » Wed Jun 20, 2012 9:45 pm

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

User avatar
Per Yngve Berg
Joomla! Master
Joomla! Master
Posts: 21431
Joined: Mon Oct 27, 2008 9:27 pm
Location: Akershus, Norway

Re: Connection with MS SQL

Postby Per Yngve Berg » Thu Jun 21, 2012 5:08 am

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: Select all

$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.

gurukatre
Joomla! Intern
Joomla! Intern
Posts: 50
Joined: Tue Apr 28, 2009 7:37 am
Location: pune
Contact:

Re: Connection with MS SQL

Postby gurukatre » Fri Jun 22, 2012 6:06 am

Many many thanks.

I will try it as well and let you know
Thanks
Kind Regards

gurukatre
Joomla! Intern
Joomla! Intern
Posts: 50
Joined: Tue Apr 28, 2009 7:37 am
Location: pune
Contact:

Re: Connection with MS SQL

Postby gurukatre » Sat Jun 30, 2012 10:56 pm

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

gurukatre
Joomla! Intern
Joomla! Intern
Posts: 50
Joined: Tue Apr 28, 2009 7:37 am
Location: pune
Contact:

Re: Connection with MS SQL

Postby gurukatre » Sun Sep 02, 2012 9:11 pm

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

User avatar
Per Yngve Berg
Joomla! Master
Joomla! Master
Posts: 21431
Joined: Mon Oct 27, 2008 9:27 pm
Location: Akershus, Norway

Re: Connection with MS SQL

Postby Per Yngve Berg » Mon Sep 03, 2012 3:40 pm

Do as it says, upgrade the db client to a newer version.

gurukatre
Joomla! Intern
Joomla! Intern
Posts: 50
Joined: Tue Apr 28, 2009 7:37 am
Location: pune
Contact:

Re: Connection with MS SQL

Postby gurukatre » Mon Sep 03, 2012 4:04 pm

Thanks to reply.
i will do and let you know.
Thanks
Kind Regards

gurukatre
Joomla! Intern
Joomla! Intern
Posts: 50
Joined: Tue Apr 28, 2009 7:37 am
Location: pune
Contact:

Re: Connection with MS SQL

Postby gurukatre » Thu Nov 08, 2012 7:27 pm

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


Return to “Joomla! 1.5 Coding”

Who is online

Users browsing this forum: No registered users and 6 guests