Codeigniter Not Connecting to SQL Server

2019-03-06 09:48发布

问题:

I'm trying to connect to an SQL server using CodeIgniter. If I use the sqlsrv driver - I get a fatal error message and if I use the odbc driver - I get an 'Unable to connect to your database server using the provided settings error message. Does anyone know how to fix this problem??? I don't mind how, I just want Codeigniter to connect to the SQL Server Database.

This is the database config file

$db['otherdb']['hostname'] = '195.234.10.55\SQLEXPRESS';
$db['otherdb']['username'] = 'username';
$db['otherdb']['password'] = 'password';
$db['otherdb']['database'] = 'ONEDB';
$db['otherdb']['dbdriver'] = 'odbc'; // Done this in both ODBC and SQLSRV
$db['otherdb']['dbprefix'] = '';
$db['otherdb']['pconnect'] = TRUE;
$db['otherdb']['db_debug'] = TRUE;
$db['otherdb']['cache_on'] = FALSE;
$db['otherdb']['cachedir'] = '';
$db['otherdb']['char_set'] = 'utf8';
$db['otherdb']['dbcollat'] = 'utf8_general_ci';
$db['otherdb']['swap_pre'] = '';
$db['otherdb']['autoinit'] = TRUE;
$db['otherdb']['stricton'] = FALSE;

Thanks

回答1:

(Don't mean to resurrect an old post, but just in case others are looking for the answer, and because this post came up during my search...)

A short step-by-step guide to connect to SQL_server from CodeIgniter 3 using native driver:

  1. First of all download PHP for SQL_server Microsoft native driver from http://www.microsoft.com/en-us/download/details.aspx?id=20098. Take care to choose the one that fits your setup.
  2. Install it to the PHP extension directory.

  3. Configure the new extension in PHP.ini. uncommenting or appending the line (depending on the choosen driver version). I.e.:

    extension = php_sqlsrv_55_ts.dll
    
  4. Restart Apache.

    Now your PHP setup is able to connect to SQL_server databases.

  5. Set up your CodeIgniter database connection by configuring application/config/database.php like

    <?php
    
        // native-driver connection
        $db['default'] = array(
            'dsn'    => '',
            'hostname' => 'SERVER\SQLEXPRESS',
            'username' => 'test_user',
            'password' => 'test_password',
            'database' => 'test01',
            'dbdriver' => 'sqlsrv',
            'dbprefix' => '',
            'pconnect' => FALSE,
            'db_debug' => TRUE,
            'cache_on' => FALSE,
            'cachedir' => 'application/cache',
            'char_set' => 'utf8',
            'dbcollat' => 'utf8_general_ci',
            'swap_pre' => '',
            'autoinit' => TRUE,
            'encrypt' => FALSE,
            'compress' => FALSE,
            'stricton' => FALSE,
            'failover' => array(),
        'save_queries' => TRUE
        );
    

Tested from CodeIgniter 3.0.1 on SQL_server 2008 R2 and SQL_server 2014 express.