Connecting symfony2/doctrine to Oracle using tsnames.ora file

[diaspar]

Connecting symfony2/doctrine to Oracle using tsnames.ora file

I am using stymfony 2.0.16 and doctrine to connect to Oracle DB. I am using oci8 as the driver.

I have successfully created the connection using a direct connection to the oracle instance, using
the dbal parameters in config.yml

but now, one client, with a big corporate host environment told me that they did not allow direct connections but we had to use a tsnames.ora to connec to Oracle. This file, tsnames.ora is an standard in oracle world to centralize instances/locations access.

I googled this but I did not find any information on how to do it.

I investigated a little more and I arrived to this file inside vendor folder, doctrine DBAL.

https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Driver/OCI8/Driver.php

The logic is clear, this function creates the dsn if the host parameter exists, otherwise it returns the dbname:

    private function _constructDsn(array $params)
    {
        $dsn = '';
        var_dump($params);
        if (isset($params['host'])) {
            $dsn .= '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)' .
                   '(HOST=' . $params['host'] . ')';

            if (isset($params['port'])) {
                $dsn .= '(PORT=' . $params['port'] . ')';
            } else {
                $dsn .= '(PORT=1521)';
            }

            $dsn .= '))';
            if (isset($params['dbname'])) {
                $dsn .= '(CONNECT_DATA=(SID=' . $params['dbname'] . ')';
            }
            $dsn .= '))';
        } else {
            $dsn .= $params['dbname'];
        }

        return $dsn;
    }

Wich is correct, because the third parameter of oci_connect accepts an easy connect string, or a Connect Name from the tnsnames.ora file, or the name of a local Oracle instance.

Ok, so I opened app/config/config.yml and in the dbal parameters I commented the host dbal parameter and in dbname I inserted my connect name from my file tnsnames.ora. I did not leave blank the host parameter because the logic in doctrine-dbal is isset.

Well, I did not work. It appears that symphony or doctrine inserts the parameter host if its defined in config.yml or not.

I would like to know if someone can point out if this is symphony related or doctrine related and where are the places where this is occurring. I am sure there's a place where symphony config.yml is converted and injected in doctrine and is around that where the host parameter is forced (and made it localhost as default).

And btw, I am 100% sure that my oracle client env is working. I did separated tests with plain php and oci8 and also using a software like Navicat, and both worked fine.

By Now I am using a hack on : https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Driver/OCI8/Driver.php to force my env to work, But I would love to do a PR if someone points me in the right direction.

[fabpot]

ping @beberlei

[beberlei]

Can you try to set the host name to:

host: ~

That explicitly sets it to null, whereas omitting the host leads to "localhost"

[diaspar]

http://www.php.net/manual/en/function.isset.php
Determine if a variable is set and is not NULL.

grrrrrrrrrrrrrrr . You are right beberiel. I always thought that isset only validates that a var/key is defined and empty() checks the value of the var.

Ok, working now as expected. To help others I will share a post with all the info I have found about SF2 and oracle. Please close this issue when you can.

[fabpot]

@diaspar If you can contribute a cookbook entry about using Symfony with Oracle, that would be wonderful.

See http://symfony.com/doc/current/contributing/documentation/overview.html

[diaspar]

Sure, It would take a couple of days. I will do it in english and spanish. There seems to be a big community in spain and latin countries.

[michlschmid]

@beberlei:
How does Doctrine find the correct entry within the TNSNAMES file?
I have several entries inside mine and would need to explicitly use one of them.
Is it done via the name I choose in the configuration?

[nschurmann]

i'm trying to connect symfony2.2 with oracle, how did you manage to connect with?, i'm configuring it with parameters.yml

[csarrazi]

In your parameters.yml, set your database_host to null, and the database_name to the service name in your TNSNAMES file.

e.g.

parameters:
    database_host: ~
    database_name: MYORACLESERVICENAME
    # ...