Connection with MS SQL
-
- Joomla! Intern
- Posts: 50
- Joined: Tue Apr 28, 2009 7:37 am
- Location: pune
- Contact:
Connection with MS SQL
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.
<?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);
?>
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.
<?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
Kind Regards
- Per Yngve Berg
- Joomla! Master
- Posts: 30938
- Joined: Mon Oct 27, 2008 9:27 pm
- Location: Romerike, Norway
Re: Connection with MS SQL
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);
-
- Joomla! Intern
- Posts: 50
- Joined: Tue Apr 28, 2009 7:37 am
- Location: pune
- Contact:
Re: Connection with MS SQL
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);
}
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
Kind Regards
- Per Yngve Berg
- Joomla! Master
- Posts: 30938
- Joined: Mon Oct 27, 2008 9:27 pm
- Location: Romerike, Norway
Re: Connection with MS SQL
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.
$link will be true if a successfull connection.
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 {
-
- Joomla! Intern
- Posts: 50
- Joined: Tue Apr 28, 2009 7:37 am
- Location: pune
- Contact:
Re: Connection with MS SQL
Many many thanks.
I will try it as well and let you know
I will try it as well and let you know
Thanks
Kind Regards
Kind Regards
-
- Joomla! Intern
- Posts: 50
- Joined: Tue Apr 28, 2009 7:37 am
- Location: pune
- Contact:
Re: Connection with MS SQL
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
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
Kind Regards
-
- Joomla! Intern
- Posts: 50
- Joined: Tue Apr 28, 2009 7:37 am
- Location: pune
- Contact:
Re: Connection with MS SQL
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
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
Kind Regards
- Per Yngve Berg
- Joomla! Master
- Posts: 30938
- Joined: Mon Oct 27, 2008 9:27 pm
- Location: Romerike, Norway
Re: Connection with MS SQL
Do as it says, upgrade the db client to a newer version.
-
- Joomla! Intern
- Posts: 50
- Joined: Tue Apr 28, 2009 7:37 am
- Location: pune
- Contact:
-
- Joomla! Intern
- Posts: 50
- Joined: Tue Apr 28, 2009 7:37 am
- Location: pune
- Contact:
Re: Connection with MS SQL
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.
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
Kind Regards