Yii Framework Forum: Connecting to different databases in a script - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Connecting to different databases in a script Rate Topic: -----

#1 User is offline   Termine 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 35
  • Joined: 18-October 11

Posted 13 March 2012 - 08:52 AM

Hello there,

I wondered if someone could offer me some advice.

Each of my clients connects to a different database. Sometimes I need to perform tasks using scripts that run on the CRON and connect to each database one at a time in a loop. Because many of the methods called are also used within the application through the front end I cannot always use name spacing for the queries e.g. SELECT * FROM database.tablename. I therefore need to change the Yii::app()->db component dynamically for example:
foreach($databases as $database){
//Override just the db connectionString (DOES NOT WORK)
Yii::app()->db->connectionString="mysql:host=localhost;dbname=$database";

$sql="SELECT * FROM sometable";
$command=Yii::app()->db->createCommand($sql);
$column = $command->queryColumn();
}

The above clearly does not work but it is the kind of functionality I am trying to achieve. I can't really use:
$connection=new CDbConnection($dsn,$username,$password);
$connection->active=true;

Because when the routines are run via the web front end it needs to read the db component from the main.php config file.

I am currently a bit lost as to how to achieve this properly. Should my script be some sort of console application that would allow me to do this more easily? Is there a way to change the db connectionString and connect to different DBs in a loop?

Any advice would be appreciated.
0

#2 User is offline   Joblo 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 689
  • Joined: 12-September 10
  • Location:Austria

Posted 13 March 2012 - 12:44 PM

A possible solution (not tested):

Config all your dbconnections in the config/main.php


  array(
    ......
    'components'=>array(
        ......
        'db1'=>array(
            'class'=>'CDbConnection',
            'connectionString'=>'mysql:host=localhost;dbname=testdb1',
            'username'=>'root',
            'password'=>'password',
            'emulatePrepare'=>true,  // needed by some MySQL installations
        ),
        'db2'=>array(
            'class'=>'CDbConnection',
            'connectionString'=>'mysql:host=localhost;dbname=testdb2',
             ...
        ),
      ...
    ),
  ...
)



and then iterate through all application components:



 foreach(Yii::app()->getComponents() as $component)
  if ($component instanceof CDbConnection) 
  {
    ...
     $component->createCommand($sql);
    ...
  }


0

#3 User is offline   Termine 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 35
  • Joined: 18-October 11

Posted 13 March 2012 - 01:32 PM

Thanks very much. I had given that kind of approach a thought, but my db instances will not be hard coded in main.php because they are generated from the sub domain. I will be looking up the names of all databases from the database and then iterating through them. Looks like I will somehow have to dynamically create the db array before making the db calls.
0

#4 User is offline   redguy 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 820
  • Joined: 02-July 10
  • Location:Central Poland

Posted 13 March 2012 - 05:46 PM

you may add this in CApplication onBeginRequest handler, or extend CWebApplication and overload init() method... or you can write your own application component "databaseConnectionFactory" which will have method 'getDbConnectionForCurrentSubdomain'... many ways to solve this
red
0

#5 User is offline   Termine 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 35
  • Joined: 18-October 11

Posted 14 March 2012 - 04:31 AM

Many thanks redguy. One can assume that the I have the database names in an array after collecting them from an 'admin' database table. When CRON runs it will look up the databases that I need to loop through. Assuming that I have:
$dbNames=array('db1','db2','db3');

And the host, username, password etc all remain the same. Could you suggest the best method of changing the database I connect to on each iteration of my script. I am not sure which one of the methods you suggest would possibly provide me with this functionality. I just need a light switching on. At the moment I am still in the dark :-) Many thanks.

PS. I am using DAO not AR for this.There is no getDbConnection() for CDbCommand?
0

#6 User is offline   Termine 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 35
  • Joined: 18-October 11

Posted 14 March 2012 - 10:33 AM

I suppose what I should be asking is how would I re-initialize the db component on each iteration? Mmmm seems this is impossible as db is read only.

Seems like Jobio's way is the only way of actually iterating through the db components. It would seem that I need to workout some way of generating the db components dynamically. My first thought is to use Yii as a 3rd party library to get the array of database names from the admin table and then use that array to build the config file that I then pass to Yii::createWebApplication($config)->run(); in the index.php file. This way I can loop through instances of CDbConnection in my script. Of course I will need some way for index.php to know that it has been called via CRON rather than by apache.

If I am barking up the wrong tree completely then would someone please put me out of my misery.
0

#7 User is offline   redguy 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 820
  • Joined: 02-July 10
  • Location:Central Poland

Posted 14 March 2012 - 02:35 PM

you can create components dynamically:
$connection=new CDbConnection($dsn,$username,$password);
$connection->active=true;
Yii::app()->setComponent( 'new_db_component_id', $connection );

...

Yii::app()->new_db_component_id->createCommand()...


you can put that in onBeginRequest or any other place you must decide which db you want access.
You can keep main 'db' connection to your admin database and configure other component to needed database
red
0

#8 User is offline   Termine 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 35
  • Joined: 18-October 11

Posted 14 March 2012 - 03:01 PM

Thanks again redguy. I think I am maybe trying to do the impossible. My code is already written. I.e. I have a bunch of classes that return data etc that all call Yii::app()->db. These methods can be called through the web application. What I want to do now is call the same methods from a script, but for a few different databases. The only possible way to do it (it would seem) is to change the config file connectionString parameter for db before calling the classes. How I go about writing that in a loop I am not sure as this is at the application instantiation level. Maybe I should not be calling Yii::app()->db in my classes and I need to extend CDbConnection to provide more flexible functionality.

In my last app I just called $conObj->makeConnection('dbName']); This was very easy.

Thanks for your help.
0

#9 User is offline   Termine 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 35
  • Joined: 18-October 11

Posted 15 March 2012 - 04:57 AM

Thanks again to both of you as you put me in the right direction along with the forum thread here

The pseudo code below has been tested and is a proof of concept. The code is in a controller action, but could be anywhere.

	$databases = array('db2','db3'); //Fetched from somewhere
		
	foreach($databases as $database){
		
		//Create a new component
		$component=Yii::createComponent(array(	
			'class'=>'CDbConnection',
			'connectionString' => 'mysql:host=localhost;dbname='.$database,
			'emulatePrepare' => true,
			'username' => Yii::app()->db->username,
			'password' => Yii::app()->db->password,
			'charset' => 'utf8',
			'enableParamLogging'=>true,
		   'schemaCachingDuration'=>3600,
		));
		//Override the existing db component
		Yii::app()->setComponent('db',$component);

		$sql="SELECT something FROM sometable";
		$command=Yii::app()->db->createCommand($sql);
		$column = $command->queryColumn();
		var_dump($column);
	}

0

#10 User is offline   Wooi 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 16
  • Joined: 28-June 12

Posted 07 August 2014 - 10:56 PM

View PostTermine, on 15 March 2012 - 04:57 AM, said:

Thanks again to both of you as you put me in the right direction along with the forum thread here

The pseudo code below has been tested and is a proof of concept. The code is in a controller action, but could be anywhere.

	$databases = array('db2','db3'); //Fetched from somewhere
		
	foreach($databases as $database){
		
		//Create a new component
		$component=Yii::createComponent(array(	
			'class'=>'CDbConnection',
			'connectionString' => 'mysql:host=localhost;dbname='.$database,
			'emulatePrepare' => true,
			'username' => Yii::app()->db->username,
			'password' => Yii::app()->db->password,
			'charset' => 'utf8',
			'enableParamLogging'=>true,
		   'schemaCachingDuration'=>3600,
		));
		//Override the existing db component
		Yii::app()->setComponent('db',$component);

		$sql="SELECT something FROM sometable";
		$command=Yii::app()->db->createCommand($sql);
		$column = $command->queryColumn();
		var_dump($column);
	}


Can I use AR in Your method, instead of createCommand?
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

2 User(s) are reading this topic
0 members, 2 guests, 0 anonymous users