Using two databases

[eluser]gcanizal[/eluser]
Hi,

I have a question related with database management. I have the database "A" as default database in the file "database.php": $active_group = "A"
In the same file I have defined database "B".
One of my controllers uses B database:

Code:

$query = "SELECT *...."
        $this->a = $this->load->database('A',TRUE);
        $result_set = $this->a->query($query);

And then it uses database "A":

Code:

$query = "SELECT *...."
        $result_set = $this->db->query($query);

But the second piece of code returns a "DATABASE ERROR".

So, I want to know if after the first piece of code the default data base has changed and, in this case, it is now "B".

Does someone use multiple databases? Which is the best way to manage them?

Thank you in advance.

[eluser]rogierb[/eluser]
I'm confused, the second database is 'B' but in your example you load 'A' as the second database.
This would mean you have connected to 'A' twice.

On of our apps uses multi database. The key is to test all of your connection data and test if the server your code resides on, has access to that database.

Once you are satisfied all the connections work flawlessly, then build your second or third or whatever connection.
You won't have any problems, the way you connect to A

Code:

$this->a = $this->load->database('A',TRUE);
$result_set = $this->a->query($query);

Is the correct way to connect to another database

[eluser]gcanizal[/eluser]

Hi,

I have a question related with database management. I have the database “A” as default database in the file “database.php”: $active_group = “A”
In the same file I have defined database “B”.
One of my controllers uses B database:
$query = "SELECT *...."
$this->b = $this->load->database('B',TRUE);
$result_set = $this->b->query($query);

And then it uses database “A”:
$query = "SELECT *...."
$result_set = $this->db->query($query);


But the second piece of code returns a “DATABASE ERROR”.

So, I want to know if after the first piece of code the default data base has changed and, in this case, it is now “B”.

Does someone use multiple databases? Which is the best way to manage them?

Thank you in advance.

Sorry, I made a mistake where the bold code is. My question is simple, I want to know if "A" remains being the default database after loading "B" in this way.

[eluser]rogierb[/eluser]
Yes it will. At least if you have autoloaded it.

[eluser]gcanizal[/eluser]
I still have the same problem. If I activate database db2 in file "database.php":

Code:

$active_group = "db2";

On the one hand, I call the next controller and I have a database error:

Code:

class TestDB extends My_Controller
{
    private $db1;


    function TestDB()
    {
        parent::My_Controller();


    }


    function index()
    {

        $response = $this->A();
        $response = $this->B();    


        

    }


    public function A (){
        $this->db1 = $this->load->database('db1',TRUE);
        $result_set = $this->db1->query("SELECT * FROM TABLE1");
    }
    public function B (){
        $this->db->query("SELECT * FROM TABLE2");
    }

    


}

It returns:


A Database Error Occurred

Error Number:

SELECT * SELECT * FROM TABLE2


But if I call the functions in the other order (first I call B and then A), everything goes fine.

Is there any problem with "mssql driver"? We lose the default data base connection.


On the other hand, in the case I reconnect before all querys it works fine:

Code:

class TestDB extends My_Controller
{
    private $db1;


    function TestDB()
    {
        parent::My_Controller();


    }


    function index()
    {

        $response = $this->A();
        $response = $this->B();    


        

    }


    public function A (){
        $this->db1 = $this->load->database('db1',TRUE);
        $result_set = $this->db1->query("SELECT * FROM TABLE1");
    }
    public function B (){
                $db2 = $this->load->database('db2',TRUE);
        $db2->query("SELECT * FROM TABLE2");
    }

    


}

And finally my problem:
What happens with the sessions when they are stored in a table if we lose the default connection?


Thank you

[eluser]rogierb[/eluser]
You shouldn't loose connection. Weird that it does. If you loose connection, there will be no problem with the session. As long as you stay within your expiration time, you session stays alive.

[eluser]gcanizal[/eluser]
No, if I don't store the session in a table it still happens.

And one more clue: It doesn't happen with a MySql database, with "mysql driver". So that it must be something related with "mssql driver".

When I execute the first example (with mssql driver) the log says:

mssql_query() [<a href='function.mssql-query'>function.mssql-query</a>]: message: Invalid object name 'TABLE2'. (severity 16) C:\MyProyect\system\database\drivers\mssql\mssql_driver.php 122