[Gambas-user] gb.db autoincrement ?

[Benoit Minisini]

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.
> >
> > 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.
>

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.

Regards,

-- 
Benoit Minisini