CodeIgniter and MS SQL server

[eluser]tajinder.singh88[/eluser]
Hello Guys!

Please help me to compose database.php of Codeigniter if I am using MS SQL Server 2005 express edition, PHP and IIS 7.

As I searched, mssql driver of codeigniter has some issue. So one has suggested to use sqlsrv driver. This driver is available at:-

http://www.kaweb.co.uk/wp-content/2008/0...sqlsrv.zip

I have downloaded ci_php_sqlsrv.zip and unzipped and copied under system/database/driver.

Even then CodeIgniter is not able to connect with mssql server.

Please please help me to figure it out.

[eluser]pickupman[/eluser]
I have connected to MS SQL database using ODBC. Setup a system DSN ODBC connection. Here's my application/config/database.php

Code:

$db['default']['hostname'] = "UPS_WS_12"; //System DSN name
$db['default']['username'] = "user"; //DB user name
$db['default']['password'] = "pass"; //DB pass
$db['default']['database'] = "";
$db['default']['dbdriver'] = "odbc";
$db['default']['dbprefix'] = "";
$db['default']['pconnect'] = FALSE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;

I tried using the MSSQL driver to know avail. I guess I wasn't getting the dsn://string right or something. ODBC is working without a hitch.

[eluser]tajinder.singh88[/eluser]
Wow It really Works.... thanks pickupman....

I am able to connect with SQL Server DB but a simple model_function gives me some syntactical error. the model_function os:-i
function validateLogin($username, $password)
{
$this->db->select('i_user_id');
$this->db->from('user_master UM');
$this->db->where('UM.vc_username',$username);
$this->db->where('UM.vc_password',$password);
$this->db->where('UM.i_status', 1);
$this->db->where('UM.isdeleted', 0);
if($this->db->count_all_results())
{
$this->db->select('i_user_id');
$this->db->from('user_master UM');
$this->db->where('UM.vc_username',$username);
$this->db->where('UM.vc_password',$password);
$this->db->where('UM.i_status', 1);
$this->db->where('UM.isdeleted', 0);
return $this->db->get()->row()->i_user_id;
}
else
{
return false;
}
}

and error on browser:-
---------------
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ')'.

SELECT COUNT(*) AS numrows FROM (user_master UM) WHERE UM.vc_username = 'admin' AND UM.vc_password = 'admin' AND UM.i_status = 1 AND UM.isdeleted = 0
---------------

If I modify this query to fire directly in sql server then run good:-
----------------
SELECT COUNT(*) AS numrows FROM user_master UM WHERE UM.vc_username = 'admin' AND UM.vc_password = 'admin' AND UM.i_status = 1 AND UM.isdeleted = 0
----------------
(just eliminated the parenthesis in from clause)

how do I fix this problem via CodeIgniter.

[eluser]tajinder.singh88[/eluser]
Well it is bit awkward, But I am answering my own question. hehe

I have updated function _from_tables($tables) under system/database/odbc/odbc_driver.php.

remove parenthesis from return statement:-

Instead of:-
return '('.implode(', ', $tables).')';
It should be:-
return implode(', ', $tables);

That it...

Thankyou so much pickupman...thankyou so much.

[eluser]pickupman[/eluser]
Glad you got working. I am not sure why there issues using the mssql driver, but odbc works and that's fine for me.