You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-user@db.apache.org by Wizard of OS <sg...@gmx.net> on 2006/05/08 17:37:05 UTC
Torque/Oracle 10 Incrementation problem
Hi,
just installed happily torque for oracle and declared this table:
<table name="FAN" idMethod="native">
<column name="FAN_ID" required="true" primaryKey="true"
type="INTEGER"/>
<column name="FIRST_NAME" required="true" size="128" type="VARCHAR"/>
<column name="PASSWORD" required="true" size="32" type="VARCHAR"/>
<column name="EMAIL" required="true" size="128" type="VARCHAR"/>
<unique>
<unique-column name="EMAIL"/>
</unique>
</table>
Adding someone once is very fine with torque and ID gets incremented.
now I provoked a unique constraint violation with adding someone else
with the same email address. The DB rejected of course and torque threw
an exception.
Adding another set of data with a distinct email adresses created
instead of ID =2 => ID = 3.
Seems like the failed torque command incremented the ID anyway.
how can I avoid this?
It is extremely unpleasent.
idMethod=idbroker does the same error!
bye
thx in advance
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: Torque/Oracle 10 Incrementation problem
Posted by Wizard of OS <sg...@gmx.net>.
Thomas Vandahl wrote:
> Wizard of OS wrote:
>> first of all, please do *not* send HTML encoded mail to mailinglists
>> nor to others at all, they are a plague.
> My mail reader shows Thomas' message as text/plain.
I made CTRL+U and it showed plain too.
I don't undetstand this, checked some...
Edit: stupid me, I'm so sorry. Your messages *are* plain text.
I've several accs for years in TB, never used HTML, hate it. Don't know
why this acc explicitly was set to HTML composing.
>> I already realized what you have described.
>> I made direct SQL commit and oracle incremented the sequence anyway.
>> A transaction w/ rollback didn't work also. I think that the sequence
>> concept in Oracle is diffent to regular autoincrement.
> MySQL does the same. If you add a record to a table with autoincrement
> on and rollback, the autoincrement value will not be decremented, which
> leaves a "hole" in your primary key sequence.
Why?
This is a bug to me actually.
I don't see a good reason to produce swiss cheese.
Maybe Postgres does different.
Quite strange anyway,
thx for your feedback
Mike
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: Torque/Oracle 10 Incrementation problem
Posted by Thomas Vandahl <tv...@apache.org>.
Wizard of OS wrote:
> first of all, please do *not* send HTML encoded mail to mailinglists nor
> to others at all, they are a plague.
My mail reader shows Thomas' message as text/plain.
> I already realized what you have described.
> I made direct SQL commit and oracle incremented the sequence anyway.
> A transaction w/ rollback didn't work also. I think that the sequence
> concept in Oracle is diffent to regular autoincrement.
MySQL does the same. If you add a record to a table with autoincrement
on and rollback, the autoincrement value will not be decremented, which
leaves a "hole" in your primary key sequence.
Bye, Thomas.
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: Torque/Oracle 10 Incrementation problem
Posted by Wizard of OS <sg...@gmx.net>.
Thomas Fischer wrote:
> Hi,
>
> I'm not sure whether this is possible with the current implementation
> of the Id generation, which uses two steps. In the first step, Torque
> gets the id out of the id table (id method idbroker) or sequence (id
> method native) and in the second step, the insert is done. The only
> possibility I'd see is to use a rollback to restore the state of the
> database before the error. I guess this is not possible ussing the id
> broker, for as far as I know, it uses a separate db connection and
> thus runs in another transaction as the insert. I'm not so sure about
> sequences, but as you said it would jump ids,it seems that sequences
> live outside transactiond and thus never get restored to their
> previous value if a rollback is issued.
>
> I'm afraid I do not know an easy way to achieve what you want.
>
> Thomas
>
Hi Thomas,
first of all, please do *not* send HTML encoded mail to mailinglists nor
to others at all, they are a plague.
I already realized what you have described.
I made direct SQL commit and oracle incremented the sequence anyway.
A transaction w/ rollback didn't work also. I think that the sequence
concept in Oracle is diffent to regular autoincrement.
thx
anyway
PS: idbroker fails too
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: Torque/Oracle 10 Incrementation problem
Posted by Thomas Fischer <tf...@apache.org>.
Hi,
I'm not sure whether this is possible with the current implementation of
the Id generation, which uses two steps. In the first step, Torque
gets the id out of the id table (id method idbroker) or sequence (id
method native) and in the second step, the insert is done. The only
possibility I'd see is to use a rollback to restore the state of the
database before the error. I guess this is not possible ussing the id
broker, for as far as I know, it uses a separate db connection and thus
runs in another transaction as the insert. I'm not so sure about
sequences, but as you said it would jump ids,it seems that sequences live
outside transactiond and thus never get restored to their previous value
if a rollback is issued.
I'm afraid I do not know an easy way to achieve what you want.
Thomas
On Mon, 8 May 2006, Wizard of OS wrote:
> Hi,
>
> just installed happily torque for oracle and declared this table:
>
> <table name="FAN" idMethod="native">
> <column name="FAN_ID" required="true" primaryKey="true" type="INTEGER"/>
> <column name="FIRST_NAME" required="true" size="128" type="VARCHAR"/>
> <column name="PASSWORD" required="true" size="32" type="VARCHAR"/>
> <column name="EMAIL" required="true" size="128" type="VARCHAR"/>
> <unique>
> <unique-column name="EMAIL"/>
> </unique>
> </table>
>
> Adding someone once is very fine with torque and ID gets incremented.
> now I provoked a unique constraint violation with adding someone else with
> the same email address. The DB rejected of course and torque threw an
> exception.
> Adding another set of data with a distinct email adresses created instead of
> ID =2 => ID = 3.
> Seems like the failed torque command incremented the ID anyway.
>
> how can I avoid this?
> It is extremely unpleasent.
> idMethod=idbroker does the same error!
>
> bye
>
> thx in advance
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org