how to connect to another db with doctrine on zend framework

Question

I use Zend Framework 1.10 with integration on Doctrine 1.2.

in the application.ini file, i declare the 'dsn' to connect to database.

in my application i need to connect to another db to run some queries.

how can i do it ?

i only need to run query , i don't want to generate all the Models for this DB.

right now in the bootstrap.php i do the default connection :

     protected function _initDoctrine()
        {
            $this->getApplication()->getAutoloader()
                ->pushAutoloader(array('Doctrine', 'autoload'));
            spl_autoload_register(array('Doctrine', 'modelsAutoload'));

            $doctrineConfig = $this->getOption('doctrine');
            $manager = Doctrine_Manager::getInstance();
            $manager->setAttribute(Doctrine::ATTR_AUTO_ACCESSOR_OVERRIDE, true);
            $manager->setAttribute(
              Doctrine::ATTR_MODEL_LOADING,
              $doctrineConfig['model_autoloading']
            );

            Doctrine_Core::loadModels($doctrineConfig['models_path']);

            $conn = Doctrine_Manager::connection($doctrineConfig['dsn'],'doctrine');
            $conn->setAttribute(Doctrine::ATTR_USE_NATIVE_ENUM, true);
            Doctrine_Core::generateModelsFromDb('models', array('doctrine'), array('generateTableClasses' => true));
         return $conn;
}

Answer 1

Just add another dsn for your other DB, and connect to that one using PDO...

As a matter of fact, we defined our databases in the Zend config as follows (using XML), to cater for multiple DB connections :

    <databases>
        <db_one>
            <adapter>pdo_mysql</adapter>
            <params>
                <dbname>...</dbname>
                <username>...</username>
                <password>...</password>
                <host>...</host>
                <port>...</port>
            </params>
        </db_one>
        <db_two>
            <adapter>pdo_mysql</adapter>
            <params>
                <dbname>...</dbname>
                <username>...</username>
                <password>...</password>
                <host>...</host>
                <port>...</port>
            </params>
        </db_two>
    </databases>

(Of course they're not really called db_one and db_two, but have a proper name :p).

edit

You could initialize the DB connections as follows (call this somewhere in your bootstrap) :

private function initDb()
{
    foreach ($this->config->databases as $name => $database) {
        try {
            $db = Zend_Db::factory($database);
            // Hack for MySQL utf8 encoding...
            if ($database->adapter == 'pdo_mysql') {
                $db->getConnection();
                $db->query('SET NAMES utf8');                   
            }
            Zend_Registry::set($name, $db);
        } catch (Zend_Db_Adapter_Exception $e) {
            throw new Application_Exception($e->getMessage());
        } catch (Zend_Exception $e) {
            throw new Application_Exception($e->getMessage());
        }
    }
}

Then, if you want to perform a query on db_two anywhere in your code, you can use :

$db = Zend_Registry::get('db_two');
$stmt = $db->query('select ... from ...');

And use fetch() or fetchAll() on $stmt as you see fit.

BTW You don't have to use Zend_Registry and open all connections on every request of course, so just consider this as an example implementation, not as a guideline on how to solve your problem.

Answer 2

This assumes regular Doctrine without the use of Zend or alike!

It is already there in your code, you just need to add another line with your connection. I suggest http://www.doctrine-project.org/projects/orm/1.2/docs/manual/connections/en as a good read since it deals extensively with this problem. To get an better idea what I am talking about:

$conn = Doctrine_Manager::connection($doctrineConfig['dsn'],'doctrine');

This is a connection with the name doctrine, to make a second connection, simply create another connection with another name like

Doctrine_Manager::connection($doctrineConfig['dsn'],'second_connection');

Now you have two connections, your already known doctrine and the newly created second_connection.

Read the link from above to see how to handle the retrieval of differen connections. When querying the models, you can define what connection you want to use as an optional parameter.

Answer 3

You can also store Doctrine instances in Zend_Registry and retrieve the one you are wanting later.

$conn1 = Doctrine_Manager::connection(...);
Zend_Registry::set('conn1',$conn1);

$conn2 = Doctrine_Manager::connection(...);
Zend_Registry::set('conn2',$conn2);

Then later you can retrieve it by doing the following:

$conn1 = Zend_Registry::get('conn1');
$conn2 = Zend_Registry::get('conn2');