[DDC-2264] Add support for custom Oracle SID / Service name in PDO_Oracle driver Created: 29/Jan/13  Updated: 29/Jan/13

Status: Open
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: 2.0
Fix Version/s: None

Type: Task Priority: Major
Reporter: Michl Schmid Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: oracle


 Description   

Some Oracle customer databases are set up having different settings for their "DBNAME" and "SID" / "SERVICE" property. (DBNAME != SID)

So, hereing it's currently not possible to connect via the PDO_Oracle driver (Class: Doctrine\DBAL\Driver\PDOOracle\Driver) as it uses the DBNAME value by default as value for SID / SERVICE in the _constructPdoDsn() method. (DBNAME = SID)

A solution would be to add an additional config param like "servicename" and pass it's value into _constructPdoDsn().

An updated version of the method could look like:

private function _constructPdoDsn(array $params)
{
$dsn = 'oci:';
if (isset($params['host']) && $params['host'] != '') {
$dsn .= 'dbname=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)' .
'(HOST=' . $params['host'] . ')';

if (isset($params['port']))

{ $dsn .= '(PORT=' . $params['port'] . ')'; }

else

{ $dsn .= '(PORT=1521)'; }

if (isset($params['servicename']) && $params['servicename'] != '')

{ $servicename = $params['servicename']; }

else

{ $servicename = $params['dbname']; }

if (isset($params['service']) && $params['service'] == true)

{ $dsn .= '))(CONNECT_DATA=(SERVICE_NAME=' . $servicename . ')))'; }

else

{ $dsn .= '))(CONNECT_DATA=(SID=' . $servicename . ')))'; }

} else

{ $dsn .= 'dbname=' . $params['dbname']; }

if (isset($params['charset']))

{ $dsn .= ';charset=' . $params['charset']; }

return $dsn;
}

The only workaround for me is right now to use the "standard" PHP OCI / OCI8 functions with the correct SID / Service in it's DSN.



[DDC-1986] findBy hydration with limit and offset with Oracle database (oci8 driver) Created: 17/Aug/12  Updated: 08/Jan/13

Status: Awaiting Feedback
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: 2.3
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Benjamin Grandfond Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 1
Labels: oracle
Environment:

composer.json require :

"php": ">=5.3.3",
"symfony/symfony": "2.1.*",
"doctrine/orm": ">=2.2.3,<2.4-dev",
"doctrine/doctrine-bundle": "dev-master",
"twig/extensions": "dev-master",
"symfony/assetic-bundle": "dev-master",
"symfony/swiftmailer-bundle": "dev-master",
"symfony/monolog-bundle": "dev-master",
"sensio/distribution-bundle": "dev-master",
"sensio/framework-extra-bundle": "dev-master",
"sensio/generator-bundle": "dev-master",
"jms/security-extra-bundle": "1.2.*",
"jms/di-extra-bundle": "1.1.*",
"twitter/bootstrap": "master",
"friendsofsymfony/rest-bundle": "dev-master",
"doctrine/doctrine-fixtures-bundle": "dev-master"


 Description   

I tried to use the findBy method with limit and offset parameters against an Oracle database using oci8 driver.

The query seems to executed successfully but the hydrator fails when hydrating data as there is a DOCTRINE_ROWNUM column appending the "limit" clause.

Here is the exception thrown : "Notice: Undefined index: DOCTRINE_ROWNUM in [...]/vendor/doctrine/orm/lib/Doctrine/ORM/Internal/Hydration/SimpleObjectHydrator.php line 183"

I was thinking about something like this to fix this issue :

  • add an attribute (platformExtraColumns) to the platform class, storing every column added by methods like doModifyLimitQuery
  • check in hydrator method hydrateRowData if the column exists among the extra columns attribute of the custom platform
  • don't use the column if true

Maybe there is a better approach, what are your thoughts?

 Comments   
Comment by Benjamin Grandfond [ 17/Aug/12 ]

I implemented it in my forks :

https://github.com/benja-M-1/doctrine2/commit/c8d899b14446accf869ddc0043f4235284375755
https://github.com/benja-M-1/dbal/commit/b9423c8d46a2bcdaa5a1f0b26a9a28259b1e44a2

It works for me, but I didn't write unit tests.

Comment by Benjamin Grandfond [ 24/Aug/12 ]

Hi,

Did you have time to have a look at this issue?

Thanks

Comment by Christophe Coevoet [ 24/Aug/12 ]

Please send a pull request when you submit a fix. It is the proper way to submit them for review. When we want to see things waiting for review, we look at the list of pending PRs, not at all comments of the issue tracker to find links in them.

And I can tell you that this change has a big issue: it introduces a state in the database platform whereas it is currently stateless. This is likely to cause some issues when using more than 1 query (which is a common use case).

Comment by Benjamin Grandfond [ 29/Aug/12 ]

Hi Christophe thank you for your feedback.

I didn't send a PR because I wanted someone sharing his thoughts about what I suggested in this current issue. However I don't really understand the stateless argument, can you explain a bit more?

Otherwise how would do you proceed to tell Doctrine not to hydrate platform-specific columns?

Comment by Christophe Coevoet [ 29/Aug/12 ]

If you run several queries, they will be affected by the extra columns of previous requests, which is wrong

Comment by Benjamin Eberlei [ 29/Aug/12 ]

I think the ObjectHydrator catches this by skipping undefined columns, i think we might just have overoptimized the SimpleObjectHydrator a little bit.




[DC-946] Oracle Doctrine_RawSql()->count() generates illegal SQL Created: 08/Dec/10  Updated: 06/Aug/12

Status: Open
Project: Doctrine 1
Component/s: Native SQL
Affects Version/s: 1.2.3
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Lars Pohlmann Assignee: Roman S. Borschel
Resolution: Unresolved Votes: 0
Labels: oracle


 Description   

Example RawSQL:

$q = new Doctrine_RawSql();
    $q->select('{k.*}')
          ->from('SHP_MANDANT_KATEGORIE k')
          ->addComponent('k', 'ShpMandantKategorie k')
          ->where( 'k.id_mandant=' . $this->getIdMandant() )
          ->andWhere( 'k.id_parent=' . $this->getIdMandantkategorie() )
          ->andWhere( 'k.aktiv=1' )
          ->orderBy( 'k.sortorder' );

$q->count() generates:

SELECT COUNT(*) as num_results 
FROM (SELECT DISTINCT k.id_mandantkategorie 
              FROM SHP_MANDANT_KATEGORIE k 
              WHERE k.id_mandant=2 AND k.id_parent=1520 AND k.aktiv=1) as results

The illegal Part ist the "as results" at the end...

 Comments   
Comment by Lars Pohlmann [ 06/Aug/12 ]

Hi,

will this ever be corrected?
I just came across the same bug in another project...




[DBAL-444] OraclePlatform getSequenceNextValSQL not handling case/quoting properly on 11g Created: 10/Feb/13  Updated: 04/Apr/13

Status: In Progress
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.3.2
Fix Version/s: 2.4
Security Level: All

Type: Bug Priority: Major
Reporter: Max Milaney Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: oci8, oracle, sequence
Environment:

PHP version 5.4.11
Oracle 11g Instant Client version 11.2.0.3.0
Oracle Database 11g Enterprise Edition version 11.2.0.3.0 (x64)
OCI8 DBAL driver

Attachments: File example.php    

 Description   

I have an installer script that uses ORM SchemaTool to create the entities in the DB and then populates with basic data using basic EM->persist calls via ORM.

Sequence objects are created, and when using the 10g Instant Client everything worked correctly, however, upon upgrade to latest version of the Instant Client Oracle seems to be expecting consistent case for these schema objects. It appears as if they are being created with a quoted name as they are created in lowercase. OraclePlatform::getSequenceNextValSQL, however, generates "SELECT entity_id_seq.nextval FROM DUAL" and this fails with error "General error: 2289 OCIStmtExecute: ORA-02289: sequence does not exist".

Executing "SELECT "entity_id_seq".nextval FROM DUAL" directly on the DB returns the correct value.

I believe this may also impact the code in http://www.doctrine-project.org/jira/browse/DBAL-278

 Comments   
Comment by Max Milaney [ 10/Mar/13 ]

Hi there,
Wondering if there is any update on this? I'm having to use a workaround in my applications.
Cheers,
Max

Comment by Benjamin Eberlei [ 14/Mar/13 ]

Can you maybe show an entity definition with its sequence mapping?

Comment by Max Milaney [ 17/Mar/13 ]

Here you are mate. Please see attachment.

Comment by Benjamin Eberlei [ 04/Apr/13 ]

I cant seem to find the problem, in DBAL "lib/Doctrine/DBAL/Platforms/OraclePlatform.php" on line 171, the sequence statement is created with $sequence->getQuotedName($platform), but this only works if quoting is requrested for the sequence.

How do you actually create the sequence? Your entity doesnt have @GeneratedValue.

What does the create schema command say with "--dump-sql" flag? Is the SQL quoted?




[DBAL-402] Fatal error: Uncaught exception Created: 29/Dec/12  Updated: 08/Apr/13

Status: Open
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Blocker
Reporter: Ruslan Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: dql, oracle


 Description    
Fatal error: Uncaught exception 'Doctrine\DBAL\Driver\OCI8\OCI8Exception' with message 'ORA-00904: "T0"."ID": invalid identifier' in /var/www/doctrine/doctrine/lib/vendor/doctrine-dbal/lib/Doctrine/DBAL/Driver/OCI8/OCI8Exception.php on line 28 Doctrine\DBAL\Driver\OCI8\OCI8Exception: ORA-00904: "T0"."ID": invalid identifier in /var/www/doctrine/doctrine/lib/vendor/doctrine-dbal/lib/Doctrine/DBAL/Driver/OCI8/OCI8Exception.php on line 28

Call Stack: 0.0002 665368
1. {main}() /var/www/doctrine/doctrine/tools/sandbox/index.php:0 0.3389 6023864
2. Doctrine\ORM\EntityManager->find() /var/www/doctrine/doctrine/tools/sandbox/index.php:71 0.3514 8264024
3. Doctrine\ORM\Persisters\BasicEntityPersister->load() /var/www/doctrine/doctrine/lib/Doctrine/ORM/EntityManager.php:444 0.3521 8413576 
4. Doctrine\DBAL\Connection->executeQuery() /var/www/doctrine/doctrine/lib/Doctrine/ORM/Persisters/BasicEntityPersister.php:725 0.3532 8625920
5. Doctrine\DBAL\Driver\OCI8\OCI8Statement->execute() /var/www/doctrine/doctrine/lib/vendor/doctrine-dbal/lib/Doctrine/DBAL/Connection.php:635 Variables in local scope (#5): $hasZeroIndex = *uninitialized* $key = *uninitialized* $params = NULL $ret = FALSE $val = *uninitialized*


 Comments   
Comment by Benjamin Eberlei [ 06/Jan/13 ]

Format code sample

Comment by Benjamin Eberlei [ 04/Apr/13 ]

What is wrong here? The error alone is not very helpful




[DBAL-367] Reverse engnering do not work with Oracle DB Created: 18/Oct/12  Updated: 23/Jan/13

Status: Open
Project: Doctrine DBAL
Component/s: Drivers, Schema Managers
Affects Version/s: 2.3
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Zelenin Alexandr Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: Cli, oracle, schematool
Environment:

PHP 5.3.3-1ubuntu9.10 with Suhosin-Patch (cli) (built: Feb 11 2012 06:21:15)
oci8-1.4.7 as PHP extension builded from pecl repository with instantclient-basic-linux.x64-11.2.0.3.0.zip and instantclient-sdk-linux.x64-11.2.0.3.0.zip
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production


 Description    
$ doctrine orm:convert-mapping --filter="ms$ions" xml .

  [Doctrine\DBAL\DBALException]
  Unknown database type binary_float requested, Doctrine\DBAL\Platforms\OraclePlatform may not support it.

cli-config.php:


use Doctrine\Common\Annotations\AnnotationReader;
use Doctrine\Common\Annotations\AnnotationRegistry;

require_once 'Doctrine/Common/ClassLoader.php';

define('APPLICATION_ENV', "development");
error_reporting(E_ALL);

$classLoader = new \Doctrine\Common\ClassLoader('Doctrine');
$classLoader->register();

$config = new \Doctrine\ORM\Configuration();
$config->setProxyDir(__DIR__);
$config->setProxyNamespace('Proxies');

$config->setAutoGenerateProxyClasses((APPLICATION_ENV == "development"));

AnnotationRegistry::registerFile("Doctrine/ORM/Mapping/Driver/DoctrineAnnotations.php");
$reader = new AnnotationReader();
$driverImpl = new \Doctrine\ORM\Mapping\Driver\AnnotationDriver($reader, array(__DIR__ . "/../php/ru/niifhm/bioinformatics/biodb/model"));
$config->setMetadataDriverImpl($driverImpl);

if (APPLICATION_ENV == "development") {
    $cache = new \Doctrine\Common\Cache\ArrayCache();
} else {
    $cache = new \Doctrine\Common\Cache\ApcCache();
}

$config->setMetadataCacheImpl($cache);
$config->setQueryCacheImpl($cache);

$connectionOptions = array(
    'driver'   => 'oci8',
    'host'     => 'host.name',
    'dbname'   => 'db.name',
    'user'     => 'user.name',
    'password' => 'user.password'
);

$em = \Doctrine\ORM\EntityManager::create($connectionOptions, $config);
$platform = $em->getConnection()->getDatabasePlatform();
$platform->registerDoctrineTypeMapping('enum', 'string');

$em->getConfiguration()->setMetadataDriverImpl(
    new \Doctrine\ORM\Mapping\Driver\DatabaseDriver(
        $em->getConnection()->getSchemaManager()
    )
);

$helperSet = new \Symfony\Component\Console\Helper\HelperSet(array(
    'db' => new \Doctrine\DBAL\Tools\Console\Helper\ConnectionHelper($em->getConnection()),
    'em' => new \Doctrine\ORM\Tools\Console\Helper\EntityManagerHelper($em)
));


 Comments   
Comment by Marco Pivetta [ 23/Jan/13 ]

Formatting




[DBAL-345] When inserting decimals into Oracle, getting ORA-01722: invalid number Created: 04/Sep/12  Updated: 17/Sep/12  Resolved: 17/Sep/12

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.2.2
Fix Version/s: 2.3
Security Level: All

Type: Bug Priority: Major
Reporter: Christian Stoller Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: decimal, number, oracle, separator
Environment:

Windows 7 (German), Oracle XE 11.2.0, Doctrine 2.2.2, Symfony 2.0.15


 Description   

When I insert decimals into database I get the following error message:

ORA-01722: invalid number

Oracle wants decimals (e.g. NUMBER(5,2)) separated by comma instead of a dot. I think the reason is that I use the german version of Windows. I tried to modify the following method of \Doctrine\DBAL\Driver\OCI8\OCI8Statement::bindValue() just for testing of course:

    public function bindValue($param, $value, $type = null)
    {
        if (is_float($value)) {
            $value = str_replace('.', ',', (string) $value); // <--
        }
        return $this->bindParam($param, $value, $type);
    }

With this modification I do not get the error anymore.
It would be great if a solution could be found. The strange thing is, if I query "SELECT * from nls_database_parameters where PARAMETER='NLS_NUMERIC_CHARACTERS'" I get ".," which means that a dot is already used as decimal separator. Maybe Oracle preferes the Locale of the OS?!
Maybe you could provide a setting which defines what separator should be used for decimals.

I found some Links that might be interesting regarding this issue:

 Comments   
Comment by Christian Stoller [ 04/Sep/12 ]

I found out that the database session has the parameter 'NLS_NUMERIC_CHARACTERS', too.
When I query it by the following SQL query:

SELECT parameter,value FROM v$nls_parameters WHERE parameter = 'NLS_NUMERIC_CHARACTERS'

I get this result:

,.

With doctrine I can get the parameter with this (in Symfony2):

$conn = $this->getDoctrine()->getEntityManager()->getConnection(); /* @var $conn \Doctrine\DBAL\Connection */
$query = $conn->executeQuery("SELECT parameter,value FROM v\$nls_parameters WHERE parameter = 'NLS_NUMERIC_CHARACTERS'"); /* @var $query \Doctrine\DBAL\Driver\OCI8\OCI8Statement */
$result = $query->fetchAll(\PDO::FETCH_ASSOC);
print_r($result);

In this case I get this output:

Array
(
[0] => Array
(
[PARAMETER] => NLS_NUMERIC_CHARACTERS
[VALUE] => ,.
)

)

And with

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '. '

I can change the parameter.

When I execute the above query before I do my insert with decimal values, everything works as expected
But it would be nicer if I could do that with a configuration.

Comment by Benjamin Eberlei [ 05/Sep/12 ]

There is an Oci8SessionInitListener inside Doctrine DBAL. It does not yet contain the numeric character change. Can you open a pull request on Github DBAL to add this?

Comment by Christian Stoller [ 13/Sep/12 ]

Hi Benjamin.
I have opened a pull request on Githup: https://github.com/doctrine/dbal/pull/197
I hope everything is correct, because this is my first Pull-Request

Comment by Benjamin Eberlei [ 17/Sep/12 ]

A related Github Pull-Request [GH-197] was closed
https://github.com/doctrine/dbal/pull/197




[DBAL-316] Incorrect parameter SERVICE_NAME Created: 07/Aug/12  Updated: 10/Feb/13  Resolved: 10/Feb/13

Status: Resolved
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: 2.3
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Leandro Guimarães Fernandes Assignee: Alexander
Resolution: Invalid Votes: 0
Labels: oracle
Environment:

Windows 7, ZendServer-CE-php-5.4.0-5.6.0-Windows_x86 and Oracle 10


 Description   

At line 63, the driver PDOOracle, the parameter SERVICE_NAME is receiving the value of 'dbname' and not 'service'.

Sorry, my English is bad!

 Comments   
Comment by Alexander [ 10/Feb/13 ]

This is not a bug. Read the code. It checks if the parameter is set and if it's "true".




Generated at Sun May 19 04:59:04 UTC 2013 using JIRA 5.2.7#850-sha1:b2af0c8dc8537b36121c6a579fabbdf79fc919e5.