Page 1 of 1

Connection with MS SQL

Posted: Wed Jun 20, 2012 7:24 pm
by gurukatre
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);
?>

Re: Connection with MS SQL

Posted: Wed Jun 20, 2012 8:23 pm
by Per Yngve Berg
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);		

Re: Connection with MS SQL

Posted: Wed Jun 20, 2012 9:45 pm
by gurukatre
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);
}

Re: Connection with MS SQL

Posted: Thu Jun 21, 2012 5:08 am
by Per Yngve Berg
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.

Re: Connection with MS SQL

Posted: Fri Jun 22, 2012 6:06 am
by gurukatre
Many many thanks.

I will try it as well and let you know

Re: Connection with MS SQL

Posted: Sat Jun 30, 2012 10:56 pm
by gurukatre
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

Re: Connection with MS SQL

Posted: Sun Sep 02, 2012 9:11 pm
by gurukatre
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

Re: Connection with MS SQL

Posted: Mon Sep 03, 2012 3:40 pm
by Per Yngve Berg
Do as it says, upgrade the db client to a newer version.

Re: Connection with MS SQL

Posted: Mon Sep 03, 2012 4:04 pm
by gurukatre
Thanks to reply.
i will do and let you know.

Re: Connection with MS SQL

Posted: Thu Nov 08, 2012 7:27 pm
by gurukatre
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.