View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
0022070 | mantisbt | db oracle | public | 2016-12-25 10:10 | 2016-12-26 19:28 |
Reporter | cproensa | Assigned To | |||
Priority | normal | Severity | major | Reproducibility | always |
Status | new | Resolution | open | ||
Product Version | 1.3.4 | ||||
Summary | 0022070: db_insert_id() errors | ||||
Description | 1) This method, for oracle, issues this query: This can create an incorrect sequence name, becasue ADOdb may have used an alternative name for the sequences and triggers related to autoincrement fields. In mantis side, this ends with a "ORA-00972: identifier is too long" 2) This method may cause concurrency issues, as it's not isolated from other threads issuing concurrent inserts. Proposal: use native ADOdb insert_Id() function. As it reads in the documentation: The value retrieved is connection based, meaning that each concurrent http://adodb.org/dokuwiki/doku.php?id=v5:reference:connection:insert_id [EDIT dregad - break long lines] | ||||
Tags | No tags attached. | ||||
@dregad This means that in order to build a valid query for the sequence, there should exists some way to get the sequence associated to the table. |
|
Not all ADOdb drivers implement that method (the oci8 driver does not - hasInsertId property is false).
Not currently, I don't think. In Oracle, sequences are completely disconnected from tables, and to my knowledge it's not possible to retrieve a sequence's name from a given table / column. Considering that ADOdb creates a trigger linked to the sequence to make the column behave like an autonumber, the best approach might be to retrieve the trigger's metadata and use that to get the sequence via dependencies, something like (I didn't actually test this): <pre> Need to be careful if for some reason there are multiple sequences / triggers on a single table, which is technically possible. References
|
|
As i see it now, the name is currently generated with uniqid(), so there is no way to get to it programatically (without querying the db) I think this could be proposed to ADOdb: make available the short name available, even if it's as a driver specific function. If the name could be generated as some kind of hashed string from the table name, in a repeatable way, all that could be done in the library portability layer. |
|
Note that in mantis, this can be an issue with plugins: resulting sequence name for plugin tables would be: Even with minimal prefixes, like "MP", the 30char limit is easily reached. My scenario is having these fields as foreign keys to other tables. So to insert on 2nd table, i need the inserted id in 1st table. |
|
This is definitely something that should ideally be fixed in ADOdb and not in Mantis, but it's not so simple given the way Oracle is designed. I do not know what the rationale was for using uniqid() instead of some other, more predictable hash to build the sequence name in the first place, that does not make much sense to me. To my knowledge, the library currently does not offer any way of retrieving the sequence name. Implementing Insert_Id() in the oci8 driver using the sequence sounded like a good approach at first, and not so difficult to implement, but thinking about it more I'm concerned that it would likely cause issues in concurrent access scenarios. An alternative approach could be to use an INSERT statement with a dynamic return clause [1], and retrieve that using bind variables e.g. something like But that would not be trivial to implement in a generic and robust way at library level (need to modify the SQL). [1] http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/returninginto_clause.htm#LNPLS01354 |
|
Haven't seen how it's implemented in, for example, pgsql (if it's so)
Thanks for the tip. |
|