[Gambas-user] gb.db autoincrement ?

[Doriano Blengino]

Kari Laine ha scritto:
> Hi,
>
> i cannot figure out how the get inserted autoincrement column right after
> addition to table.
>
> I have
> ------------------------
>   rResult3 = $hConn.Create("files")
>
>   rResult3!filename = sFile
>   rResult3!md5sum = MUtils.md5sum(sDir, sFile)
>   rResult3!lastaccess = Stat(sdir &/ sFile, TRUE).LastAccess
>   rResult3!lastchange = Stat(sdir &/ sFile, TRUE).LastChange
>   rResult3!lastmodified = Stat(sdir &/ sFile, TRUE).LastModified
>   rResult3!size = Stat(sdir &/ sFile, TRUE).Size
>
> rResult3.Update
> ----------------------
>
> after this I would have to add record to related table which among others
> need the autoincrement column of the first insert..
>   
I had the same problem you said. In my accounting program I have a table 
"invoices", where each record has an autoincrement field named ID. Every 
invoice has several lines (items), recorded in another table "lines", 
and each line of the invoice refers to its parent invoice by this field 
ID. After having added an invoice with, say:

    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

I don't like this method but, it seems, it's the only possible. It works 
because mysql guarantees that an auto-increment field is always created 
with the value of "maximum_number_in_table + 1" (in other databases this 
could be not true).

In a mono-user environment it works well. In a multiuser environment you 
can get troubles because if another user adds a record just after you 
added one, and before you interrogate the server again, then you get the 
wrong ID. If this is the case (a multi-user case, or several programs 
writing to these tables), then a lock on the table must be implemented.


Cheers...