[Gambas-user] gb.db autoincrement ?

[Doriano Blengino]

Benoit Minisini ha scritto:
> On vendredi 17 octobre 2008, Doriano Blengino wrote:
>   
>> Kari Laine ha scritto:
>>     
>>> Hi,
>>>
>>> i cannot figure out how the get inserted autoincrement column right after
>>> addition to table.
>>>
>>>     invoice = db.create("invoices")
>>>     invoice!number = ...
>>>     invoice.date = ...
>>>     ...
>>>     invoice.update
>>>
>>> ...I had to record the lines of the invoice, but first I had to get the
>>> ID autoincrement field. I did that like this:
>>>
>>>     invoice = db.exec("select ID from invoices order by ID desc limit 1")
>>>     print invoice!ID   '<-- this is the autoincrement(ed) field
>>>
>>>       
> In that case, you must use a transaction.
>
> If you create two records in two different tables that are linked (i.e. one 
> should not exist without the other), you must use a transaction. Then you are 
> sure that nobody else will increment the auto-increment field. More 
> precisely, you won't see it.
>   
I never tried transactions: could you be more explicit with an example 
(pseudo code)?

There are two problems. First, how to obtain the correct ID 
autoincrement field inserted in the master table; I think this is done 
through a select, like I did. Second, how to prevent another record be 
inserted in the master table, by another process, before doing the 
query. I am not sure that a transaction prevents this, but if you are, 
tell me it is so.
Last time you talked about this, you speaked about locks, not 
transactions. Using transaction it could go like:

    begin transaction
    add record to invoice table (where invoices.ID is serial/autoincrement)
    query database to know what the last ID number is
    add records to items table, using this known invoices.ID as foreign key
    terminate transaction

But, if I well remember, transaction are meant to update database in a 
"all done, or nothing done" fashion, which means in this case that the 
invoice, and all the children items, are added to the database but: if 
something goes wrong, then nothing is added. This is not the same as to 
say "the invoices table is locked, and only the active transaction can 
update it". Hope I am clear enough.
If you know that transactions solve problems, I trust you.

Regards,
Doriano