Welcome Guest, Not a member yet? Register   Sign In
Switch Sqlite3 DB - $db->setDatabase
#1

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) {
  }
}
[]
Reply
#2

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.
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply
#3

(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'     => '',
  ]; 
Reply
#4

(This post was last modified: 08-14-2020, 02:00 AM by captain-sensible.)

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
Reply




Theme © iAndrew 2016 - Forum software by © MyBB