Symfony: Connecting to External Database

Introduction

In a number of my Symfony applications, I have had to connect to an external Oracle database to get various data. In this case, I am merely querying the database for data and there is no need to create any Entities (or data) in the external database. This article describes how to configure the 2^nd Symfony database connection, and how to connect and query the 2^nd database.

Doctrine Configuration

The image below shows my config.yml file with my Doctrine configuration. In my case, I have an external Oracle database to connect to, and thus OCI8 needs to be installed in order to use PHP to connect to it. This is described in more detail in my Installing OCI8 on RHEL article.

The above configuration is based on information from the Symfony documentation How to Work with multiple Entity Managers and Connections; although, I’m not using the 2^nd connection for any entities. A notable difference in the above configuration is that there is no “mappings” entry specified for the “oracle” connection, because no entities are created/used.

Parameter File

Then, I have the following (sample) parameters entered in my parameters.yml file:

In the above, the “database_name2” is the Oracle SID, and notice I use the OCI8 driver. Port 1521 should be specified, unless for some reason you use a different port.

Establishing a Connection

To use this 2^nd connection, you merely run the following code:

// Get the Oracle database connection.
$db = $this->get('doctrine.dbal.oracle_connection');

The above code gets a Doctrine DBAL connection to the 2nd “oracle” connection as specified in the config.yml file (the connection setting).

Creating a Query

To create a query, simply create a string with your query. Then you’ll need to retrieve the results. In my case, I typically use the “fetchAssoc()” as part of the API, which returns an associative array result. For troubleshooting, it’s a good idea to use print_r to print the array out. So some example code would look like this:

// Get student PIDM.
$q_PIDM = "SELECT SPRIDEN_PIDM FROM SPRIDEN WHERE SPRIDEN_ID='".$ID."'";
$PIDM = $ban->fetchAssoc( $q_PIDM );
print_r($PIDM);

The above gets a student ID from the Oracle database, and then prints it out. It’s a good idea to look at what is printed out to see your results and how you might use those results.

That’s it! With the above information, you should be able to use Symfony to connect to a 2^nd database and query it. Enjoy!

Tagged: Doctrine, OCI8, Oracle, Symfony