Clever Geek Handbook

Connecting codeigniter to mysql and oracle in one application

I am using Mysql and Oracle for my CI application. I tried to connect it but found that I cannot make a query to the Oracle database. It always gave the error that the table does not exist.

I have already set database.php to something like this

$active_group = 'oracle';
$active_record = true;

$db['oracle']['hostname'] = '10.10.10.1:1521/ocidb';
$db['oracle']['username'] = 'ociuser';
$db['oracle']['password'] = 'ocipass';
$db['oracle']['database'] = 'ocidb';
$db['oracle']['dbdriver'] = 'oci8';
$db['oracle']['dbprefix'] = '';
$db['oracle']['pconnect'] = TRUE;
$db['oracle']['db_debug'] = FALSE;
$db['oracle']['cache_on'] = FALSE;
$db['oracle']['cachedir'] = '';
$db['oracle']['char_set'] = 'utf8';
$db['oracle']['dbcollat'] = 'utf8_general_ci';
$db['oracle']['swap_pre'] = '';
$db['oracle']['autoinit'] = TRUE;
$db['oracle']['stricton'] = FALSE;

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

$db['default']['hostname'] = 'localhost';
$db['default']['username'] = 'root';
$db['default']['password'] = '';
$db['default']['database'] = 'mysqldb';
$db['default']['dbdriver'] = 'mysql';
$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;
Run codeHide result


In the controller, I want to query the table in oracle to load it there.

function citizen(){
  $this->load->database('oracle',true);

  $data['someone'] = $this->people_model->getPeople();
  
  $this->load->view('myview',$data);
}
Run codeHide result


And here people_model

function getPeople(){
  return $this->db->get('people')->result();
}
Run codeHide result


When I run it it gets the error

Error Number: 1146

Table 'mysqldb.people' doesn't exist

SELECT * FROM (`people`) WHERE `id` = '21111'
Run codeHide result


It seems like it is still making a query in mysql while the table people are in oracle. I also tried to load oracle database in model instead of controller but same result.

How can I make a query to oracle in this case. Any answer would be appreciated. Thank.

+3


source to share


2 answers


I have worked with oracle and mysql using Codeigniter.

You have used $this->load->database('oracle',true);

which should be assigned to a variable since you used the second parameter true. like this

$oracle_db=$this->load->database('oracle',true);//connected with oracle
$mysql_db=$this->load->database('default',true);//connected with mysql

Now you can use these two variables for your request. how

$oracle_db->get('people')->result();

or



$mysql_db->get('people')->result();

So your model should be like this (don't load the database to your controller)

function __construct()//model construct function
{
    parent::__construct();
    $this->oracle_db=$this->load->database('oracle',true);
    $this->mysql_db=$this->load->database('default',true);
}
function getPeople(){
   return $this->oracle_db->get('people')->result();
}

Hope you can understand. Make sure it connects to your oracle db.

My .php database for oracle was like this:

$tns = "
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = YOUR_IP)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = YOUR_SID)
    )
  )
       ";
$db['oracle']['hostname'] = $tns;
+5


source


You need to load the database and use this object to query from the database

$oracle =  $this->load->database('oracle',true);
$query = $oracle->query("SELECT * FROM people");


and change the pconnect flag to false

as CI is having problems keeping persistent connections to multiple databases.

+1


source






More articles:


All Articles