Multiple Databases Error

I have an application with multiple databases and I can set up my models/controllers/views to work correctly and perform all CRUD applications by using a wrapper class to overwrite the GetDbConnection() as suggested by http://www.yiiframework.com/wiki/123/multiple-database-support-in-yii/

My problem is when I want to get a model from one database when I am in the controller of a model that uses the second database I get the following error: "The table "table_name" for active record class "model_name" cannot be found in the database."

Its a bit hard to explain, so I’ll use an example.

Say I have two databases: blog and advertisements. Blogs has a table post which has been used by gii to generate all the CRUD functions. Likewise advertisements has a table called ads which has been used to generate a model, ads.php and all the CRUD functions.

I can call Post::model()->find("$id") in the Post controller and views and have it return the correct post object. However, if I try to call the same line of code in the ads controller I the error:

The table "post" for active record class "Post" cannot be found in the database.

I think that, for some reason, it is trying to look for the post table in the advertisement database.

Any ideas?

how u r swapping db? post ur config main file. only the currently active component used in gii… if u want to create CRUD using blog then u should use active db connection to blog …

that yii documentation is used to swap databases dynamically … to generate CRUD u just manually change db in config main , and CRUD …

I think that you may get that kind of error if you have established a relation between Post and Ad, for example, Ad BELONGS_TO Post or Ad HAS_MANY Post.

no softark … i think his dynamic switching between db has some problem.

Um, you may be right. :)

However, the point in my argument is that the eager loading of the related model should fail if the related model uses a different db connection, even if the switching is working correctly.

I am not using the database named ‘db’ that gii uses to generate files. I am using two different database connections that are declared in the main.php file. As stated before, the getDBConnection() function has been overwritten for both classes so that the correct database connection is returned. This solution works great except for in the scenario described above.

Here is what my main.php file looks like.




/*  'db'=>array(),  This is not being used  */

          

'db_blog'=>array(

   'connectionString' => 'mysql:host=localhost;dbname=blog',

   'emulatePrepare' => true,

   'username' => 'username',

   'password' => '*****',

   'charset' => 'utf8',

   'class' => 'CDbConnection',

),           


'db_advertisements'=>array(

   'connectionString' => 'mysql:host=localhost;dbname=advertisements',

   'emulatePrepare' => true,

   'username' => 'username',

   'password' => '*****',

   'charset' => 'utf8',

   'class' => 'CDbConnection',

),

I am still not sure what is causing the problem.

Also, I have not yet implemented a relationship between post and advertisements so I do not think that this could be the issue, but thank you for trying.

Would you please post your base class for Post and Ads, and getDbConnection() method of Post and Ads.

I think you have to modify the code in the wiki in order to handle 2 connections, i.e. db_blog and db_advertisements. And probably you have made some error in this point.

I discovered my mistake. I had created two wrapper class for CActiveRecord for both post and advertisement. I overwrote the public function getDbConnection() and had it return the respective db connection for each class. I should have followed the official Yii documentation a little closer. I found the solution on this page: http://www.yiiframework.com/wiki/123/multiple-database-support-in-yii/

The problem was that I was using a static property named $db inside the classes to store the db connection. For some reason if I renamed the variable to something other than $db the correct database connection was returned.

Just for the sake of documenting my mistake, I will publish my old code as well as the corrected code.

Here was the flawed code:




class BlogActiveRecord extends CActiveRecord{


        /**

         * Override the getDbConnection to include the post database. By

         * default, the getDbConnection will only return the database connection

         * named db. Since this application uses many different databases, this

         * function has been overriden so that it returns the correct database

         * connection.

         * 

         * @return DbConnection

         * @throws CDbException 

         */

        public function getDbConnection()

        {

            if(self::$db!==null)

                return self::$db;

            else

            {

                self::$db=Yii::app()->db_blog;

                if(self::$db instanceof CDbConnection)

                    return self::$db;

                else

                    throw new CDbException(Yii::t('yii','Active Record requires a "db" CDbConnection application component.'));

            }

        }

}



Note there are two problems with this code. First, I am using the variable named $db which I believe is reserved. And second, I should have declared the variable as a static variable. I also broke it into a static function (which I believe is optional).

Here is the corrected code:




class BlogActiveRecord extends CActiveRecord{

    

        private static $db_blog;

        /**

         * Override the getDbConnection to include the blog database. By

         * default, the getDbConnection will only return the database connection

         * named db. Since this application uses many different databases, this

         * function has been overriden so that it returns the correct database

         * connection.

         * 

         * @return DbConnection

         * @throws CDbException 

         */

        public function getDbConnection()

        {

            return $this->getPostDbConnection();

        }

        

        

        protected static function getBlogDbConnection()

        {

            if(self::$db_blog!==null)

                return self::$db_blog;

            else

            {

                self::$db_netsec=Yii::app()->db_blog;

                if(self::$db_netsec instanceof CDbConnection)

                    return self::$db_blog;

                else

                    throw new CDbException(Yii::t('yii','Active Record requires a "db" CDbConnection application component.'));

            }

        }

}



I feel kind of silly now that I realize that I should have just followed the Yii documentation closer. Thanks softark and Rajith for your help in trying to get it to work for me.

Congrats you’ve found the problem :) Though I’d like to add a clarification about $db.

It’s not a reserved word, but a default name of database connection application component, so you may use it for ONE of you connections, assigning other names to all additional connections.

And one more minor notice: I don’t see what’s the purpose to create a separate [size=2]getBlogDbConnection() method instead of overwriting getDbConnection() directly. It can be useful only if you have several different [/size][size=2]getANYNAMEDbConnection() written in one parent AR class. When extending such parent, you overwrite getDbConnection() of children classes, calling one of these getANYNAMEDbConnection() methods - like described in the article you provided.[/size]

[size=2]In other case, if you’re initially going to extend AR class from a definite parent with a particular getDbConnection(), you only need to overwrite this method and in parent class only. In this case you’ll have several parent AR classes defined, each with its own getDbConnection(). This way you may forget about getDbConnection() in your children classes extending from a proper parent.[/size]

[size=2]Hope it’s described [/size][size=2]clearly[/size][size=2] [/size][size=2]:)[/size]