CodeIgniter Forums

Full Version: Switch Sqlite3 DB - $db->setDatabase
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Hi,
I am trying to dinamically switch a database (Sqlite3) on CI4.
Queries are working nice with the default database.
From the docs I read this:

"You don’t need to create separate database configurations if you only need to use a different database on the same connection. You can switch to a different database when you need to, like this:

$db->setDatabase($database2_name);"


In my class I tried to set a different database before executing the query:

        $this->db->setDatabase('C:\data\test.DB');

        var_dump($this->db);// the default database isn't changed (see the object dump at the end of the post).
                                     // The query is executed on the default database
        $sql = "SELECT * FROM myTable";
        $query = $this->db->query($sql);
        $results = $query->getResultArray();


...

Am I missing something?

Thanks

Rob

================
object(CodeIgniter\Database\SQLite3\Connection)#61 (39) {
  ["DBDriver"]=>
  string(7) "SQLite3"
  ["_random_keyword":protected]=>
  array(1) {
    [0]=>
    string(8) "RANDOM()"
  }
  ["DSN":protected]=>
  string(0) ""
  ["port":protected]=>
  string(0) ""
  ["hostname":protected]=>
  string(0) ""
  ["username":protected]=>
  string(0) ""
  ["password":protected]=>
  string(0) ""
  ["database":protected]=>
  string(76) "C:\data\default.DB"
  ["subdriver":protected]=>
  NULL
  ["DBPrefix":protected]=>
  string(0) ""
  ["pConnect":protected]=>
  bool(false)
  ["DBDebug":protected]=>
  bool(true)
  ["cacheOn":protected]=>
  bool(false)
  ["cacheDir":protected]=>
  string(0) ""
  ["charset":protected]=>
  string(4) "utf8"
  ["DBCollat":protected]=>
  string(15) "utf8_general_ci"
  ["swapPre":protected]=>
  string(0) ""
  ["encrypt":protected]=>
  bool(false)
  ["compress":protected]=>
  bool(false)
  ["strictOn":protected]=>
  bool(false)
  ["failover":protected]=>
  array(0) {
  }
  ["lastQuery":protected]=>
  NULL
  ["connID"]=>
  bool(false)
  ["resultID"]=>
  bool(false)
  ["protectIdentifiers"]=>
  bool(true)
  ["reservedIdentifiers":protected]=>
  array(1) {
    [0]=>
    string(1) "*"
  }
  ["escapeChar"]=>
  string(1) """
  ["likeEscapeStr"]=>
  string(13) " ESCAPE '%s' "
  ["likeEscapeChar"]=>
  string(1) "!"
  ["dataCache"]=>
  array(0) {
  }
  ["connectTime":protected]=>
  NULL
  ["connectDuration":protected]=>
  NULL
  ["pretend":protected]=>
  bool(false)
  ["transEnabled"]=>
  bool(true)
  ["transStrict"]=>
  bool(true)
  ["transDepth":protected]=>
  int(0)
  ["transStatus":protected]=>
  bool(true)
  ["transFailure":protected]=>
  bool(false)
  ["aliasedTables":protected]=>
  array(0) {
  }
}
[]
You need to create a config like below:

PHP Code:
$custom = [
    'DSN'      => '',
    'hostname' => 'localhost',
    'username' => '',
    'password' => '',
    'database' => '',
    'DBDriver' => 'MySQLi',
    'DBPrefix' => '',
    'pConnect' => false,
    'DBDebug'  => (ENVIRONMENT !== 'production'),
    'cacheOn'  => false,
    'cacheDir' => '',
    'charset'  => 'utf8',
    'DBCollat' => 'utf8_general_ci',
    'swapPre'  => '',
    'encrypt'  => false,
    'compress' => false,
    'strictOn' => false,
    'failover' => [],
    'port'     => 3306,
];

$db = \Config\Database::connect($custom); 

Fill in the Custom config above with your information.

Then the line below it. If the custom config is correct it should work.
(08-13-2020, 10:40 AM)InsiteFX Wrote: [ -> ]You need to create a config like below:

PHP Code:
$custom = [
    'DSN'      => '',
    'hostname' => 'localhost',
    'username' => '',
    'password' => '',
    'database' => '',
    'DBDriver' => 'MySQLi',
    'DBPrefix' => '',
    'pConnect' => false,
    'DBDebug'  => (ENVIRONMENT !== 'production'),
    'cacheOn'  => false,
    'cacheDir' => '',
    'charset'  => 'utf8',
    'DBCollat' => 'utf8_general_ci',
    'swapPre'  => '',
    'encrypt'  => false,
    'compress' => false,
    'strictOn' => false,
    'failover' => [],
    'port'     => 3306,
];

$db = \Config\Database::connect($custom); 

Fill in the Custom config above with your information.

Then the line below it. If the custom config is correct it should work.

======================

Thanks for your help.
Not sure if I understood well.
I am able to switch the connection of the database, this works fine:

PHP Code:
private function selectDB() {
    $dbId = $this->session->get('dbId');
    $this->db = \Config\Database::connect($dbId);


however I still don't understand how to use the
PHP Code:
$db->setDatabase($database2_name); 

method suggested in the documentation.

All the other parameters in the config are the same, the only difference is the name of the DB (SQLite3).
The idea behind that is that I would like to avoid creating a new DB connection if I am able to switch just the DB file (not sure if it is possible).

Thanks

R.

PHP Code:
  public $default = [
    'DSN'      => '',
    'hostname' => '',
    'username' => '',
    'password' => '',
    'database' => APPPATH 'Data\DB_TEST1.DB',
    'DBDriver' => 'SQLite3',
    'DBPrefix' => '',
    'pConnect' => false,
    'DBDebug'  => (ENVIRONMENT !== 'production'),
    'cacheOn'  => false,
    'cacheDir' => '',
    'charset'  => 'utf8',
    'DBCollat' => 'utf8_general_ci',
    'swapPre'  => '',
    'encrypt'  => false,
    'compress' => false,
    'strictOn' => false,
    'failover' => [],
    'port'     => '',
  ];

  public $db2 = [
    'DSN'      => '',
    'hostname' => '',
    'username' => '',
    'password' => '',
    'database' => APPPATH 'Data\DB_TEST2.DB',
    'DBDriver' => 'SQLite3',
    'DBPrefix' => '',
    'pConnect' => false,
    'DBDebug'  => (ENVIRONMENT !== 'production'),
    'cacheOn'  => false,
    'cacheDir' => '',
    'charset'  => 'utf8',
    'DBCollat' => 'utf8_general_ci',
    'swapPre'  => '',
    'encrypt'  => false,
    'compress' => false,
    'strictOn' => false,
    'failover' => [],
    'port'     => '',
  ]; 
I have been exclusively using default so found this interesting in config/Database.php

i used $custom = [ etc etc ];

but then in a controller for a quick test i used:

$db = \Config\Database::connect('custom');

//$db = \Config\Database::connect($custom); gave me an error


$query = $db->query('SELECT * FROM blog');
$results = $query->getResultArray();

foreach ($results as $row)
{
echo $row['title'];
echo $row['article'];

}





then i found it connected and i retrieved db entries