CODEWITHSUNDEEP
phpsql-server-2008codeigniterconfigurationconnection
Bhavin Rana
Bhavin Rana

Reputation: 1582

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

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');

Upvotes: 3

Views: 53991

Answers (7)

qwertzman
qwertzman

Reputation: 782

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

Upvotes: 0

Martin Pfeffer
Martin Pfeffer

Reputation: 12627

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

enter image description here

Upvotes: 0

PC.
PC.

Reputation: 481

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 :)

Upvotes: 0

Charity Leschinski
Charity Leschinski

Reputation: 2906

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;

Upvotes: 3

FAISAL
FAISAL

Reputation: 350

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

Upvotes: 0

heriawan
heriawan

Reputation: 146

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

CodeIgniter MSSQL connection

Upvotes: 3

Starx
Starx

Reputation: 78971

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');
}
?>

Upvotes: 0

Related Questions