How to connect Codeigniter to SQL Server using sqlsrv – best solution

This time I will show you the correct setup to connect between CodeIgniter 2.1 to SQL Server 2005 using sqlsrv driver.

Specification

1. Wamp Server 2.0 with PHP 5.2

2. CodeIgniter 2.1/2.2

3. SQL Server 2005/2008

Download First

1. Download sqlsrv dll driver SQLsrv20.exe (download here)

2. Download SQL Native Client 2008 (Native Client 10) which is compatible for SQL Server 2005/2008 (download here)

How to connect CodeIgniter to SQL Server 2005 using SQLsrv driver

1. Install / Upgrade your SQL Native Client to 2008 SP3 (Native Client 10), then restart your computer

2. Stop your Wamp Server to copy dll file

3. Install to extract sqlsrv dll , you don’t have to copy all of the files , choose the files that same as with your installed PHP version, in this case I will copy
php_pdo_sqlsrv_52_ts_vc6.dll
php _sqlsrv_52_ts_vc6.dll
because I’m using wamp server, then I’ll copy the file into :
…\wamp\bin\php\php5.2.5\ext

4. Then I will edit php.ini file in ..\wamp\bin\apache\apache2.2.8\bin and adding these lines in extension configuration section :
extension=php_pdo_sqlsrv_52_ts_vc6.dll

extension=php_sqlsrv_52_ts_vc6.dll

5. Start the wamp service

6. Create test.php (not codeigniter file) file to Test the connection by create common script like this :

Adjust with your own host/database/user/password setting in SQL Server

<?php

$serverName = "SISINDOTEK\PRIMAVERA"; //serverName\instanceName

$connectionInfo = array( "Database"=>"cisqldb", "UID"=>"sa", "PWD"=>"sa");

$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {

echo "Connection established.<br />";

}else{

echo "Connection could not be established.<br />";

die( print_r( sqlsrv_errors(), true));

}

?>

7. If the Connection established. You may try to setup your codeigniter database configuration file (database.php) in
…\wamp\www\yourWebfolder\application\config

8. Change the configuration such as mentioned bellow (Use your own configuration !)

9. Don’t forget to change the value of $db['default']['pconnect'] = FALSE;

$active_group = ‘default’;

$active_record = TRUE;

$db['default']['hostname'] = ‘SISINDOTEK\PRIMAVERA’;

$db['default']['username'] = ‘sa’;

$db['default']['password'] = ‘sa’;

$db['default']['database'] = ‘cisqldb’;

$db['default']['dbdriver'] = ‘sqlsrv’;

$db['default']['dbprefix'] = ”;

$db['default']['pconnect'] = FALSE;

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

Save the file and now you may try to run your codeigniter web that connected to SQL Server by using sqlsrv dll driver

I Hope it can’t help you,

By Freelance Inhouse Trainer
Hery Purnama
http://freelance-it-trainer.blogspot.com
Hery Purnama 081.223344.506 , IT trainer , Excel / access VBA Macro, MS Project, Primavera, PHP Ajax Jquery, Google Map API, Google Sketchup3D, Ubuntu, Sencha ExtJS, YII, Code Igniter,
Oracle, SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: