Codeigniter - Postgresql database query (multi database)

Asked

Viewed 1,242 times

0

Well, I am developing an application that makes a listing. The listing is produced by consulting a database. The x-list is generated only by the query in the x-bank, the y-list, generated only by the query in the y-bank, etc. Neither a result should cross, for example, return the listing of X and Y on the same page.

So far, I have been able to return the query in the Mysql database, using the CI classes for the database. However, one of the banks that I must query is Postgresql.

In the Postgresql database, there are some schema, which would be divisions to form other "banks".

My doubt lies there, how do I put the SET search_path TO blablabla in the IC.

OBS¹: queries with Mysql banks are returning the proper results, the problem is only to define the search_path.

NOTE²: I know that it is possible to perform the procedure through the $query = $this->db->query('YOUR QUERY'), however, I would like to know if it is possible to use Active.

OBS³: I did not try to accomplish query() still to carry out the query, but I believe that would not find problems in returning the result through it, just wanted to take advantage of the other methods I used for Mysql.

Here is my model currently

class Crud_model extends CI_Model {

    public $banco_mysql;
    public $banco_postgre1;
    public $banco_postgre2;
    public $banco_postgre3;

    public function __construct() {

        parent::__construct();
        $this->banco_mysql = $this->load->database('bd_01', TRUE);
        $this->banco_postgre1 = $this->load->database('bd_02', TRUE);
        $this->banco_postgre2 = $this->load->database('bd_03', TRUE);
        $this->banco_postgre3 = $this->load->database('bd_04', TRUE);
    }

    public function get_name($row) {

        $this->$row['bd']->from($row['path'] . '.' . $row['tabela']);
        $this->$row['bd']->like($row['coluna'], strtoupper($row['name']));

        $result = $this->$row['bd']->get();
        return $result;
    }
}

The data contained in $row['bd'] comes from an array, it contains the name of the database I want to load. If I leave it that way in the constructor, it will work, at least in Mysql.

    public function __construct() {

        parent::__construct();
        $this->banco_mysql = $this->load->database('bd_01', TRUE);
    }

codeigniter mvc framework

  • Only one thing, not everything is possible with Active Record, some things will have to use the $this->db->query('sua query') or even $this->db->simple_query('sua query')

  • Of that I have notion, but what interests me, is to "standardize" the connections, or as many things as possible.

  • But you can already make a simple query in the two banks in a single page, mysql and postgres? or the one that is your doubt?

  • So, in fact, I noticed that I can’t do the query in both banks. It seems that, when instantiating a bank, the other does not instance. If I instantiate the two banks, not one of them works, just a blank page appears. Then I have to research a way to instantiate one of them and at the end of the query, finalize that instance so that the other is done. But so far, I have not gone after a solution, if you have a way, and can help me, I will leave the current form that my model editing the initial post.

  • So, your problem must be that you are not being able to instantiate more than one database at the same time, and not that other point that you put, if you can use both banks at the same time should already solve it. Take a look at this link that should help you, http://stackoverflow.com/questions/634291/codeigniter-using-multiple-databases

1 answer

1

Well, looking for some things related to CI and Postgresql, I found a guy with a similar doubt and decided to try something similar to what he did.

For those who are interested, and perhaps the same problem, I solved the problem as follows:

class Crud extends CI_Model {

    public function consulta($var) {

        $this->db->select('title, content, date');
        $this->db->from('search_path.mytable');
        $this->db->like('nome_coluna', $var['nome']);

        $result = $this->db->get();
     }
}

Just put the name of the schema, or bank, in the row that specifies which table to look for (in case: $this->db->from('search_path.mytable');).

In this way, it is possible to insert the search_path Postgresql and other databases that use the same system.

Browser other questions tagged codeigniter mvc framework

You are not signed in. Login or sign up in order to post.