johnf wrote:
> On Wednesday 18 April 2007 10:41, Joe Brown wrote:
>> Oracle, Postgeres and possible other databases use sequences for
>> sequential number generation for pk type scenarios. Postgresql has
>> added the serial/bigserial datatypes which use triggers for
>> autoincrementing the serial columns. But this still requires a select
>> statement to acquire the inserted value.
>>
>
>> In the past you had/have to select nextval('my_sequence') prior to
>> inserting. The populate the pk field with the value and perform the
>> insert, or write your own triggers.
>>
>> We can select curval('my_sequence') form the database to determine the
>> value last retrieved from the most recent trigger auto-population of
>> serial datatype, however, this requires a sequence name be known.
>> Although this can be determined progmatically, I'd like to be able to
>> manually set the sequence name property for the pk field and have select
>> nextval('my_sequence') sql executed to populate the pk filed, prior to
>> the insert.
The firebird support indeed determines the sequence name
programatically to be able to set a new pk prior to insert a new record.
If using sequences is a standard in postgresql too, it should be
possible to do the same implementation for postgresql.
If you need to set your pk differently as by the standard,
you should do this yourself in your bizobj.
>
> Return the value most recently returned by nextval in the current
> session.
> This function is identical to currval, except that instead of taking the
> sequence name as an argument it fetches the value of the last sequence that
> nextval was used on in the current session. It is an error to call lastval if
> nextval has not yet been called in the current session.
>
>> The argument might be that this can be handled by hand in the newrecord
>> method. This however, is often used for both databases, so I think it
>> would be prudent to add a 'sequence' type property to the bizobject and
>> incorporate population of the pk filed via the sequence.
>>
>> The bizObject would then store the last inserted pk value for later
>> reference by child records.
>>
>> -Joseph
>
> I'm not sure I agree. I can see advantages but surely not all DB's work the
> same as Postgres and Oracle. In the case of FireBird sequences work
> differently (UWE might provide more info). And for the moment I don't see
> why dabo's cursor.lastrowid() wouldn't meet most expectations.
>
In Firebird there is no mechanism which provides the pk after the
insert. That's why the firebird implementation sets the pk prior to the
insert. (see dbFirebird.pregenPK())
I don't know what's your gain if you can get the last inserted pk in
postgresql, because i think even in the same session it is possible
to get the wrong pk.
Imagine a trigger using the same sequence object during your insert
(in your session).
That's why i prefer the firebird method to set the pk prior of the insert.
Uwe
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/dabo-users/[EMAIL PROTECTED]
