I know there are several questions that is similar to this question, but somehow I am not able to get it to work, and I have been going around to check for solution but unfortunately I cant find any:
Situation: I have a web application built in CodeIgniter Framework, at the moment it can save records into its own MySQL Database(DB1), I am also required to create another datalink to another Database which is a MSSQL that will serve as our ledger, lets call that database DB2.
I have tried the:
$active_group = 'default';
$active_record = TRUE;
$db['MSSQL']['hostname'] = 'xxx.xxx.xxx.xxx\SQLEXPRESS';
$db['MSSQL']['port'] = 1433;
$db['MSSQL']['username'] = 'sa';
$db['MSSQL']['password'] = 'xxxxxxxxxxxxxx';
$db['MSSQL']['database'] = 'DB2';
$db['MSSQL']['dbdriver'] = 'mssql';
$db['MSSQL']['dbprefix'] = '';
$db['MSSQL']['pconnect'] = FALSE;
$db['MSSQL']['db_debug'] = TRUE;
$db['MSSQL']['cache_on'] = FALSE;
$db['MSSQL']['cachedir'] = '';
$db['MSSQL']['char_set'] = 'utf8';
$db['MSSQL']['dbcollat'] = 'utf8_general_ci';
$db['MSSQL']['swap_pre'] = '';
$db['MSSQL']['autoinit'] = TRUE;
$db['MSSQL']['stricton'] = FALSE;
$db['default']['hostname'] = '127.0.0.1';
$db['default']['username'] = 'root';
$db['default']['password'] = '';
$db['default']['database'] = 'DB1';
$db['default']['dbdriver'] = 'mysqli';
$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;
I can access and get records from the DB1 using this in Model:
class Transaction_model extends CI_Model{
function __construct(){
parent::__construct();}
public function get_transaction(){
$default_db = $this->load->database('default',TRUE);
$qry_res = $default_db->query("CALL sp_view_transaction()");
$res = $qry_res->result();
$qry_res->next_result();
$qry_res->free_result();
$return $res;
class Transaction extends My_Controller{
public function index(){
parent::__construct();}
public function get_transaction(){
$this->load->model('transaction_model');
$result = $this->transaction_model->get_transaction();
print_r($result);
but the problem starts to occur when I am trying to run stored procedure for the MSSQL: Controller: $this->load->model('transaction_model'); $result = $this->transaction_model->MSQL_Transaction(); $print_r($result);
Model:
Public function MSQL_Transaction(){
$db = $this->load->database('MSSQL',TRUE);
$qry = $db->query("EXEC sp_view_list 1");
$res = $qry->result();
$qry->next_result();
$qry->free_result();
return $res;
Anyone can give me advise on what I should do? I need to make an update on DB2 when I have successfully inserted a record in DB1. so far it is only returning empty web page which does not show any error. Thank you very much in advance. I am just showing the select query coz I find it the easiest to understand, if anyone can give me a sample on how it should be done, it will really help alot.
cheers.
