Connecting to SQL Server via CodeIgniter

Question

I'm trying to setup a Windows dev environment: Windows 8.1 with IIS 8.5 running SQL Server 2008RC2 and PHP 5.3.24

CodeIgniter 2.1.4

I can connect to the database just fine via PDO in a normal PHP script. But when I try to connect via CodeIgniter I get this error:

"Unable to connect to your database server using the provided settings.
Filename: C\inetpub\wwwroot\ci\system\database\DB_Driver.php
Line number 124"

If I open PHP Manager in the IIS interface I can see that the two required drivers are enabled:

php_sqlsrv_53_ts.dll
php_pdo_sqlsrv_53_ts.dll

Both of those show up in my ini file.

In my CodeIgniter database config file I have the driver set to: sqlsrv (if I use mssql I get a blank screen).

I have the /system/database/drivers/sqlsrv drivers in my CI install.

The only thing that is odd is in phpinfo() under Configure Command it shows --without-mssql and --without-pdo-mssql but I know it's working outside of CI.

phpinfo() also shows the ini file path at C:\WINDOWS but it also shows the loaded ini path as C:\Program Files (x86)\PHP\v5.3\php.ini

I tried moving it but no luck.

My /application/config/database config file:

$db['default']['hostname'] = 'localhost';
$db['default']['username'] = 'localsql';
$db['default']['password'] = 'password';
$db['default']['database'] = 'elements';
$db['default']['dbdriver'] = 'sqlsrv';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = '';
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_general_ci';
$db['default']['swap_pre'] = '';
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;

Am I missing anything specific?

what is your configuration of db connect in application\config\config.php — Patato, Nov 24 '13 at 11:25
just set the driver to PDO in application/config/database.php — ahmad, Nov 24 '13 at 11:44
Patato I added my config to my original post, it wouldn't let me format it in a comment. — , Nov 25 '13 at 05:17
If I change the driver to PDO I get a fatal error, see below: — , Nov 25 '13 at 05:18
Fatal error: Uncaught exception 'PDOException' with message 'invalid data source name' in C:\inetpub\wwwroot\ci\system\database\drivers\pdo\pdo_driver.php:114 Stack trace: #0 — , Nov 25 '13 at 05:18

score 4 · Answer 1 · answered Dec 26 '13 at 15:35

First, change

$db['default']['database'] = ‘elements;
$db['default']['dbdriver'] = ‘sq’lsrv;

to

$db['default']['database'] = 'elements';
$db['default']['dbdriver'] = 'sqlsrv';

in your config file.

Second, driver sqlsrv is buggy. Open /system/database/drivers/sqlsrv/sqlsrv_driver.php

To allow pconnect in your configuration, change line 89 from

$this->db_connect(TRUE);

to

return $this->db_connect(TRUE);

If you want to use affected_rows correctly, then change line 274 from

return @sqlrv_rows_affected($this->conn_id);

to

return @sqlsrv_num_rows($this->result_id);

I saw multiple suggestions of how to fix affected_rows posted elsewhere, but changing _execute to not use Scrollable will break stored sessions if you're also using sqlsrv for session validation.

score 3 · Answer 2 · edited May 23 '17 at 11:53

In case it helps, here's 3 different ways of connecting to SQL Server using CodeIgnitor 2 (I am using version 2.2.1) that work for me using Windows 8.1, SQL Server 2012, and PHP 5.3 running under IIS:

In my case I have a named SQL Server instance with name of "MSSQLSERVER2012". If you don't use a named instance you can change (local)\MSSQLSERVER2012 to (local) in all 3 examples below.

Method #1: sqlsrv: Use these settings in database.php:

$db['local_windows_sqlsrv']['username'] = 'username';
$db['local_windows_sqlsrv']['password'] = 'SQL_PASSWORD_HERE';
$db['local_windows_sqlsrv']['database'] = 'my_db_name';
$db['local_windows_sqlsrv']['hostname'] = '(local)\MSSQLSERVER2012';
$db['local_windows_sqlsrv']['dbdriver'] = 'sqlsrv';
$db['local_windows_sqlsrv']['dbprefix'] = '';
$db['local_windows_sqlsrv']['pconnect'] = FALSE;
$db['local_windows_sqlsrv']['db_debug'] = TRUE;
$db['local_windows_sqlsrv']['cache_on'] = FALSE;
$db['local_windows_sqlsrv']['cachedir'] = '';
$db['local_windows_sqlsrv']['char_set'] = 'utf8';
$db['local_windows_sqlsrv']['dbcollat'] = 'utf8_general_ci';
$db['local_windows_sqlsrv']['swap_pre'] = '';
$db['local_windows_sqlsrv']['autoinit'] = TRUE;
$db['local_windows_sqlsrv']['stricton'] = FALSE;

Method #2: odbc with SQL Server: First make the change shown here: https://stackoverflow.com/a/9617808/908677

Then use these settings in database.php:

$db['local_windows_odbc']['database'] = 'my_db_name';
$db['local_windows_odbc']['hostname'] = 'Driver={SQL Server};Server=(local)\MSSQLSERVER2012;Database=' . $db['local_windows_odbc']['database'];
$db['local_windows_odbc']['username'] = 'username';
$db['local_windows_odbc']['password'] = 'SQL_PASSWORD_HERE';
$db['local_windows_odbc']['dbdriver'] = 'odbc';
$db['local_windows_odbc']['dbprefix'] = '';
$db['local_windows_odbc']['pconnect'] = FALSE;
$db['local_windows_odbc']['db_debug'] = TRUE;
$db['local_windows_odbc']['cache_on'] = FALSE;
$db['local_windows_odbc']['cachedir'] = '';
$db['local_windows_odbc']['char_set'] = 'utf8';
$db['local_windows_odbc']['dbcollat'] = 'utf8_general_ci';
$db['local_windows_odbc']['swap_pre'] = '';
$db['local_windows_odbc']['autoinit'] = TRUE;
$db['local_windows_odbc']['stricton'] = FALSE;

Method #3: PDO with SQL Server.

UPDATE: This method does work for establishing the connection and inserting rows, but to get querying working you need to upgrade to CodeIgnitor 3.0 as it adds true SQL Server support to PDO.

First make the following change in the __construct() in system/database/drivers/pdo/pdo_driver.php

Replace:

empty($this->database) OR $this->hostname .= ';dbname='.$this->database;

With:

if (strpos($this->hostname, 'sqlsrv') === FALSE)
{
   empty($this->database) OR $this->hostname .= ';dbname='.$this->database;
}

Then use these settings in database.php:

// PDO with SQL Server
// IMPORTANT NOTE: requires mod to __construct() in pdo_driver.php to prevent appending ';dbname='.$this->database to hostname
$db['local_windows_pdo']['username'] = 'username';
$db['local_windows_pdo']['password'] = 'SQL_PASSWORD_HERE';
$db['local_windows_pdo']['database'] = 'my_db_name';
$db['local_windows_pdo']['hostname'] = 'sqlsrv:server=(local)\MSSQLSERVER2012;Database=' . $db['local_windows_pdo']['database'];
$db['local_windows_pdo']['dbdriver'] = 'pdo';
$db['local_windows_pdo']['dbprefix'] = '';
$db['local_windows_pdo']['pconnect'] = FALSE;
$db['local_windows_pdo']['db_debug'] = TRUE;
$db['local_windows_pdo']['cache_on'] = FALSE;
$db['local_windows_pdo']['cachedir'] = '';
$db['local_windows_pdo']['char_set'] = 'utf8';
$db['local_windows_pdo']['dbcollat'] = 'utf8_general_ci';
$db['local_windows_pdo']['swap_pre'] = '';
$db['local_windows_pdo']['autoinit'] = TRUE;
$db['local_windows_pdo']['stricton'] = FALSE;

for me it worked only by changing ```['dbdriver'] = 'pdo';``` — GoharSahi, Jun 16 '20 at 21:02

score 1 · Answer 3 · edited Jan 04 '14 at 18:42

1

You must have the following in your database configuration

$db['default']['driver']='sqlsrv'; //your mistake
$db['default']['database']='your_database'; // your mistake
$db['default']['pconnect']=false;

edited Jan 04 '14 at 18:42

Charity Leschinski

2,886
2
23
40

answered Nov 28 '13 at 13:58

user2899260

41
2

Connecting to SQL Server via CodeIgniter

3 Answers3

Linked