Connect to more than one database in a single connection

When you open a connection to a mssql database, you can jump to other databases if you have permissions.

Let's imagine that there are DATABASE_NAME_1 and DATABASE_NAME_2 databases.
For example, when you connect to DATABASE_NAME_1, the following query will jump to the other database.

PHP Code:

SELECT * from DATABASE_NAME_2.dbo.tableName where id=1 


I have 3 databases on the same server. I am currently opening 3 different connections to these databases with codeigniter. This is very costly in terms of speed. What is the way to connect to one database with codeigniter and jump to other databases?

PHP Code:

$this->db->table('DATABASE_NAME_2.dbo.tableName')->select('*')->where('id',1)->get()->getResult(); 


Codeigniter outputs the following for this result.

PHP Code:

Select * from DATABASE_NAME_1.dbo.DATABASE_NAME_2.dbo.tableName where id=1 


How can I provide usage with builder?

In short, such usage is not expected, so we can't.
Therefore, we need to extend the database classes.

ci-phpunit-test | CodeIgniter Testing Guide | CodeIgniter 3 to 4 Upgrade Helper

Or is this a bug in SQLSRV\Builder\getFullName() ?
I'm not sure.

Why don't you create a bug report?
https://github.com/codeigniter4/CodeIgni...new/choose

ci-phpunit-test | CodeIgniter Testing Guide | CodeIgniter 3 to 4 Upgrade Helper

PHP Code:

$this->db = \Config\Database::connect('default');
 //Default Database = ACCOUNT_DB;
 $querydb1 = $this->db->query('select * from users')->getRow();
 $querydb2 = $this->db->query('select * from LOG_DB.dbo.adminLogs')->getRow();
 
 print_r($querydb1);
 print_r($querydb2);
 die(); 


The Code above works Properly, but you cannot switch to another database with Builder because builder always adds the database of the active connection itself at the beginning of the table name.


Update Post  2:
This is the result when you try to do it with buider.

PHP Code:

$builderForDb2 = $this->db->table('LOG_DB.dbo.adminLogs',false,false,false);
 $builderForDb2->where('id',1);
 $builderResult = $builderForDb2->get()->getRow();
 print_r($builderResult);

//Tsql Result : SELECT * FROM "ACCOUNT_DB"."dbo"."LOG_DB.dbo.adminLogs
FROM "ACCOUNT_DB"."dbo"."VALLET_LOG.dbo.adminLogs 


When setting the table name $this->db->table('LOG_DB.dbo.adminLogs'); with builder, if it takes one more parameter, and according to this parameter, if it does not add the database in the active connection to the beginning of the table name, my problem is solved. I can do this by interfering with the kernel, but there must be a way to do it without interfering with the kernel. Can't we change the database name of the active database connection later?




Update Post : 3
The code below works, but if you use left join etc. features to tables in different databases in the builder, you will encounter problems again.also every time you change the database name you have to change it back, or you have to set the database name before each query.

PHP Code:

$this->db->setDatabase('LOG_DB');
 $builderForDb2 = $this->db->table('adminLogs');
 $builderForDb2->where('id',32);
 $builderResult = $builderForDb2->get()->getRow();
 echo $this->db->getLastQuery();
 print_r($builderResult);
//Tsql SELECT * from LOG_DB.dbo.adminLogs where id=32 


To summarize the result,
I will not use builder when I will use left join innerjoin etc. features between two databases. I will continue to use $db->query('TSQL'). I will not need too many such specific queries anyway.

$db->setDatabase('DB_NAME'); is enough for me to use builder for single table queries.

This way I can jump to different databases on the same server with a single database connection.

Changing the kernel and checking and reorganizing every update is time-costly for developers. That's why I didn't interfere with the kernel.

However, while developing the kernel, it could have been done that for each property that takes a table name, an additional parameter DBNAME and a check whether the scheme should be added to the table name or not would have made everything very easy.


The getFullName function in \Database\SQLSRV\Builder.php file could be like this.

PHP Code:

private function getFullName(string $table,$prefix=true): string
    {
        $alias = '';

        if (strpos($table, ' ') !== false) {
            $alias = explode(' ', $table);
            $table = array_shift($alias);
            $alias = ' ' . implode(' ', $alias);
        }
        //PrefixControl
        if($prefix == false)
        {
            return str_replace('"', '', $table);
        }
       //PrefixControl
        if ($this->db->escapeChar === '"') {
            return '"' . $this->db->getDatabase() . '"."' . $this->db->schema . '"."' . str_replace('"', '', $table) . '"' . $alias;
        }

        return '[' . $this->db->getDatabase() . '].[' . $this->db->schema . '].[' . str_replace('"', '', $table) . ']' . str_replace('"', '', $alias);
    } 


I thought this was the most practical and radical solution. With the minimal and ineffective fix in the kernel, I implemented the following

\Database\SQLSRV\Builder.php
I included the following code in the getFullName method.

\Database\SQLSRV\Builder.php
getFullName() metoduna şu kodu dahil ettim.

PHP Code:

private function getFullName(string $table): string
    {
        $alias = '';
        
        if (strpos($table, ' ') !== false) {
            $alias = explode(' ', $table);
            $table = array_shift($alias);
            $alias = ' ' . implode(' ', $alias);
        }
         //Multiple Db Table Connect
        if(str_contains($table,'"dbo"'))
        {
            return $table;
        }
         //Multiple Db Table Connect
        if ($this->db->escapeChar === '"')
        {
            return '"' . $this->db->getDatabase() . '"."' . $this->db->schema . '"."' . str_replace('"', '', $table) . '"' . $alias;
        }

        return '[' . $this->db->getDatabase() . '].[' . $this->db->schema . '].[' . str_replace('"', '', $table) . ']' . str_replace('"', '', $alias);
    } 


PHP Code:

$this->db = \Config\Database::connect('default');
 $this->db->table('LOG_DB.dbo.adminLogs')->select('*')->get()->getRow();
 echo $this->db->getLastQuery();
 die();
//TSQL : SELECT * FROM "LOG_DB"."dbo"."adminLogs" 


I have reported a bug because at least the behavior is not consistent.
https://github.com/codeigniter4/CodeIgni...ssues/8697

ci-phpunit-test | CodeIgniter Testing Guide | CodeIgniter 3 to 4 Upgrade Helper