Codeigniter - Fatal error to connect MySQL and SQL Server

Question

I need to connect to MySQL and SQL Server in same application. My php version 5.6.11 and codeigniter 3.0.1. The steps I followed to establish the connection below:

1. I download this file and paste into xampp\php\ext

   php_sqlsrv_56_ts
   php_pdo_sqlsrv_56_ts

2. Edit php.ini file like this:

   extension=php_sqlsrv_56_ts.dll
   extension=php_pdo_sqlsrv_56_ts.dll

database.php

$active_group = 'default';
$query_builder = TRUE;

$db['default'] = array(
    'dsn'   => '',
    'hostname' => 'localhost',
    'username' => 'root',
    'password' => '',
    'database' => 'vtp',
    'dbdriver' => 'mysqli',
    '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
);

$db['biodb'] = array(
    'hostname' => '192.168.20.231',
    'username' => 'abzalali',
    'password' => '',
    'database' => 'pdata',
    'dbdriver' => 'sqlsrv',
    'dbprefix' => '',
    'pconnect' => FALSE,
    'db_debug' => TRUE,
    'cache_on' => FALSE,
    'autoinit' => TRUE,
    'stricton' => FALSE,
);

Controller:

<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class biodb extends CI_Controller {

    public function __construct() {

        parent::__construct();
        $this->sqlsrvr = $this->load->database('biodb', true);
    }

    public function index() {
        $query = $biodb->get('arb_protest_pp');
        foreach ($query->result() as $row)
            echo $row->id;
    }

}

Then I call the controller 'biodb' in url, and getting error:

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 65488 bytes) in C:\xampp\htdocs\vtp\system\database\DB_driver.php on line 771

After that: I added a new line "$this->db_select();" in "DB_Driver.php" in this function

public function simple_query($sql)
{
    if ( ! $this->conn_id)
    {
        $this->initialize();
    }
    $this->db_select();
    return $this->_execute($sql);
}

And then I also getting another error:

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 65488 bytes) in C:\xampp\htdocs\vtp\system\database\DB_driver.php on line 642

I really didn't find out what I missed actually. And I need to work with codeigniter active record this sqlserver db.

Answer 1

I've run into many problems in the past using the the straight up sqlsrv driver with CodeIgniter.

I would convert your sqlsrv to pdo_sql_srv by doing the following in your databbase.php configuration file:

$db['biodb'] = array(
    'hostname' => '192.168.20.231',
    'username' => 'abzalali',
    'password' => '',
    'database' => 'pdata',
    'dbdriver' => 'pdo',
    'subdriver' => 'sqlsrv',
    'dbprefix' => '',
    'pconnect' => FALSE,
    'db_debug' => TRUE,
    'cache_on' => FALSE,
    'autoinit' => TRUE,
    'stricton' => FALSE,
);

I'm also surprised you aren't getting an undefined var error on your index method in your controller. Try changing it to this:

public function index() {
    //you need to call the database property you 
    //created in your construct which appears to be sqlsrvr
    $query = $this->sqlsrvr->get('arb_protest_pp');
    foreach ($query->result() as $row) {
        echo $row->id;
    }
}