is it possible to connect two dbms MySQLand postgres SQL in codeigniter?

Question

I am developing like fair size project which is in developing stage using MySQL as a primary database. Now, client has some data, which is in postgres SQL, which can not be converted into MySQL. So, as I got to know that if the dbdriver is same for two databases, like if both the databases are MySQL then I am able to connect it. But here the issue is, there are two different database management systems with different DB drivers. So how to connect simultaneously both the databases? in my database.php file the code is:

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

$db['default']['hostname'] = 'localhost';
$db['default']['username'] = 'xxx';
$db['default']['password'] = 'xxxxx';
$db['default']['database'] = 'xxxx';
$db['default']['dbdriver'] = 'mysql';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = FALSE;
$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;


// ************************ anotherDatabase Connect *****************************************************

$db['database2']['hostname'] = 'xxx.xxx.xxx.xxx'; //ip address of the remote database
$db['database2']['username'] = 'xxxx';
$db['database2']['password'] = 'xxxx';
$db['database2']['database'] = 'xxxx';
$db['database2']['dbdriver'] = 'postgre';
$db['database2']['dbprefix'] = '';
$db['database2']['pconnect'] = TRUE;
$db['database2']['db_debug'] = TRUE;
$db['database2']['cache_on'] = FALSE;
$db['database2']['cachedir'] = '';
$db['database2']['char_set'] = 'utf8';
$db['database2']['dbcollat'] = 'utf8_general_ci';
$db['database2']['swap_pre'] = '';
$db['database2']['autoinit'] = TRUE;
$db['database2']['stricton'] = FALSE; 
$db['database2']['port'] = 5433;

I have been searching for this for last one week and going through lots of posts but there is no clear information about it. To test it I have written the following code.

class C_test extends CI_Controller {

  public function index()
    {
    echo "Test";
    //$this->db->close();
    //$db1 = $this->load->database('default',true);
    $this->db2 = $this->load->database('database2',true);

    echo "databasa Test ";


    // $test1 = $db1->get('table_name')->result_array();
    //print_r($test1); 

    //$this->db->close();

    echo "=====================================================";

    $test = $this->db2->get('table_name')->result_array();
    //$test = $db2->get('admin')->result_array();
    print_r($test); 

    }
}

In your question, you want to ask $this->db2 = $this->load->database('database2',true); is not working, while $db1 = $this->load->database('default',true); is working, right? — Shaunak Shukla
– Shaunak Shukla, Commented Aug 9, 2014 at 11:03
Do not test with MySQL then try to deploy live to PostgreSQL. You need to switch your test env to PostgreSQL (or preferably test on both). Otherwise pain will result. — Craig Ringer
– Craig Ringer, Commented Aug 9, 2014 at 12:06

Community · Accepted Answer · 2017-05-23 12:06:26Z

1

First enable Postgresql extension in php.ini

extension=php_pgsql.dll

You also can enable Postgresql extension for PDO as well.

extension=php_pdo_pgsql.dll

Then,

$db['database2']['hostname'] = 'localhost';
$db['database2']['username'] = 'postgres';
$db['database2']['password'] = 'postgres';
$db['database2']['database'] = 'abc_gis';
$db['database2']['dbdriver'] = 'postgre';
$db['database2']['dbprefix'] = '';
$db['database2']['pconnect'] = TRUE;
$db['database2']['db_debug'] = TRUE;
$db['database2']['cache_on'] = FALSE;
$db['database2']['cachedir'] = '';
$db['database2']['char_set'] = 'utf8';
$db['database2']['dbcollat'] = 'utf8_general_ci';
$db['database2']['swap_pre'] = '';
$db['database2']['autoinit'] = TRUE;
$db['database2']['stricton'] = FALSE;
$db['database2']['port'] = 5432; // In question 5433 is written!

To fix the problem change the simple_query function in /system/database/DB_driver.php:

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

    $this->db_select(); //<-----------------  Added this line
    return $this->_execute($sql);
}

This completely fixes the problem, so you can do stuff like this in a model

$this->db2 = $this->load->database('database2',true);

Ref1, Ref2

edited May 23, 2017 at 12:06

CommunityBot

11 silver badge

answered Aug 9, 2014 at 11:27

Shaunak Shukla

2,3472 gold badges19 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ashish Joshi Over a year ago

Thanks Shaunak but I am using linux 12.04 VPS so extension=php_pgsql.dll won't be present. And I am sure I am missing something very small thing in configuration!!.

Ashish Joshi Over a year ago

Hello Shaunak, Issue has been resolved and I have successfully connected two database with two different dbdrivers and all happy endings finally :). Thank you so much for your help. Highly appreciated.

Ashish Joshi Over a year ago

Shaunak, well i was able to connect without giving the PORT, without doing "pconnect" value FALSE.

Shaunak Shukla Over a year ago

@AshishJoshi, then you must share your answer here, for somebody who stuck with the same issue!!

Collectives™ on Stack Overflow

is it possible to connect two dbms MySQLand postgres SQL in codeigniter?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related