How do I connect to a SQL Server database in CodeIgniter?

Question

How do I connect to a SQL Server database in CodeIgniter?

I'm currently starting an application in CodeIgniter and I would like to use SQL Server.

$active_group = 'default';
$active_record = TRUE;

$db['default']['hostname'] = '#.#.#.27';
$db['default']['username'] = '@@@@@@';
$db['default']['password'] = '@@@@@@@@@';
$db['default']['database'] = '$$$$$$$$$$$$$';
$db['default']['dbdriver'] = 'mssql';
$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;

After auto-loading the database, it shows just a blank white page with no errors. Can you please tell me what other changes I have to make to work with the SQL Server database?

#autoload.php#
$autoload['libraries'] = array('database');

Answer 1

use use $db['default']['dbdriver'] = 'sqlsrv';
and install Microsoft Drivers for PHP for SQL Server on windows

CodeIgniter MSSQL connection

Answer 2

I couldn't get it to work with the supported driver of mssql so I used sqlsrv

I normally connect with ip,port as the hostname, so I changed that.

pconnect was also giving me issues. I set it to FALSE and it started working.

Here is the configuration I got to work:

$db['default']['hostname'] = '127.0.0.1,1433';
$db['default']['username'] = 'username1';
$db['default']['password'] = 'secretpassword';
$db['default']['database'] = 'databasename';
$db['default']['dbdriver'] = 'sqlsrv';
$db['default']['pconnect'] = FALSE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;

Answer 3

it is the same process as mysql. use the same configuration given below:

$active_group = 'default'; $active_record = TRUE;

$db['default']['hostname'] = 'xxx.xx.xx.xx\SQLEXPRESS';

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

$db['default']['password'] =

'xxxxxxx'; $db['default']['database'] = 'xxxxx';

$db['default']['dbdriver'] = 'mssql';

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

In your Model use same as

function selectagent($table,$agent) {

$field = $this->db->get_where($table, array('var1' =>$agent,'day(date)'=>date('d')));

  $fields=$field->result();

  return $fields;
}

Answer 4

I just got this problem solved. And I was connecting to MSSQL hosted with microsoft Azure

Steps I followed after several research work on internet are as follows :

database.cfg :

$db['default']['hostname'] = 'XXXXXXX.database.windows.net';
$db['default']['username'] = 'username';
$db['default']['password'] = 'password';
$db['default']['database'] = 'databasename';
$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;

Mainly dbdriver,pcconnect and the hostname you should configure properly. Rest are common. Get hostname from your azure database details.

And I also modified couple of system files as I heard there were issues with DB driver.

system/database/drivers/sqlsrv/sqlsrv_driver.php

function db_pconnect()
    {
        //$this->db_connect(TRUE);
        return $this->db_connect(TRUE);
    }

and

function affected_rows()
{
    //return @sqlrv_rows_affected($this->conn_id);
    return @sqlsrv_num_rows($this->result_id);
}

I was able to connect to database and create database application.

Hope it will help someone in need :)

Answer 5

Follow these simple steps:

1. Install the instance with "mixed mode"-enabled. If your unsure watch this - video starts at the right sequence. Maybe it's a good idea to watch it completely.

2. change the db-config in '...ci/application/config/database.php'

   $active_group = 'default';
   $query_builder = TRUE;
   
   $db['default'] = array(
       'dsn' => '',
       'hostname' => 'localhost',
       'username' => 'sa', // <- use 'sa'
       'password' => 'THE_PASSWORD_YOU_SET_ON_INSTALL', // "mixed-mode"
       'database' => 'ci',
       'dbdriver' => 'sqlsrv',
   //  'dbdriver' => 'mysqli', // <- my old "non-server" config (approx. 70% slower)
       'dbprefix' => '',
       'pconnect' => FALSE,
       'db_debug' => (ENVIRONMENT !== 'production'),
       'cache_on' => FALSE,
       'cachedir' => '',
       'char_set' => 'utf8',
       'dbcollat' => 'utf8_general_ci',
       'swap_pre' => '',
       'encrypt' => FALSE,
       'compress' => FALSE,
       'stricton' => FALSE,
       'failover' => array(),
       'save_queries' => TRUE
   );
   

Answer 6

How about this? I've seen it work on some servers. Is this a Windows server?

$db['default']['dbdriver'] = "odbc";
$db['default']['dbprefix'] = "";
$db['default']['pconnect'] = FALSE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE; 

further reading

hope this helps

Answer 7

Your Configuration code is fine. Further reading here

IF you believe the errors are not showing up, then go to the index.php and on the top place the following snippet to show the errors.

error_reporting(E_ALL);

Other, then this check if the MSSQL service is running and is accessible. May be create a simple .php and try a connection with plain codes.

A non CI file Something like,

<?php
$server = 'YOURPC\SQLEXPRESS';
$link = mssql_connect($server, 'user', 'pass');

if (!$link) {
    die('Something went wrong while connecting to MSSQL');
}
?>