Home » Database » MDB2 » Bug #9302

[2006-11-10 21:51 UTC] petr dot match at gmail dot com

Description:
------------
This is probably not a bug rather a limitation of RDBMS, which current MDB2 can't handle.

If you work with sequences in MDB2 and you are on Oracle, there is some notable limitation (at least for Oracle version 9i).

Sequence name can not be longer than 30 characters, otherwise you'll get "ORA-00972: identifier is too long" error.

This problem applies to any $mdb2->extended->getBeforeId() calls, if specified table name is long enough (i.e. 29 chars), so when MDB2 computes sequence name by adding default prefix '_seq', the resulted sequence name will have 33 chars = oracle error.

This apply probably to sequence creation too (haven't tested yet).

The most easy solution could be that instead of using parent method MDB2_Driver_Common->getSequenceName($sqn), there would be an RDBMS specific method in oci8 driver class. Following one (in the test script field) works for me.

Test script:
---------------
<?php
/* following - not ideal - but working solution,
* added to MDB2_Driver_oci8 class in file Driver/oci8.php
*/

/**
* adds sequence name formatting to a sequence name
*
* @param string name of the sequence
*
* @return string formatted sequence name
*
* @access public
*/
function getSequenceName($sqn)
{
$emptylength = strlen(sprintf($this->options['seqname_format'], ''));
if ((strlen($sqn) + $emptylength) > 30) { // oracle limit
$sqn = substr($sqn, 0, (30 - $emptylength));
}
return parent::getSequenceName($sqn);
}
// }}}
?>

[2006-11-10 23:31 UTC] petr dot match at gmail dot com

I think that this has something to do with namespaces too. If you want to distinguish between table names and sequence names, the '_seq' suffix is fine. However, usage of this policy can collide with some sort of limitation on Oracle.

When my app is build on Postgres and I need to port it on Oracle, with using the MDB2_COMPATILITY_ALL constant, I would expect it working on Oracle instantly :) (moreover if the db schema have been created with MDB2_Driver_Manager...)

In order to let developer choose any type of db and to have his applications maximally portable, I guess it should be upon middle layer (meaning MDB2) to solve this.

[2006-11-11 19:28 UTC] petr dot match at gmail dot com

Yes, it's clear now :) I must apologize to you, you were right. My solution will do more bad than good.

So, what do you think about informing the developer at the point of sequence creation, that he/she can break some RDBMS limitation on Oracle (info just if created sequence name is longer than 30 chars)? I don't know the MDB2 Manager methods in detail, so maybe is this proposal not possible to do. The idea is, that after deploying the application onto several RDBMS including Oracle, the developer won't be faced with problems of Oracle platform...