[Gambas-user] DataView Help

[BB]

On 12/7/23 6:05 am, Gianluigi wrote:
> Il 11/07/23 21:11, Christof Thalhofer ha scritto:
>> Am 11.07.23 um 15:56 schrieb Benoit Minisini:
>>
>>> This is a feature of the underlying database system, so it's up to 
>>> it to
>>> make that feature reliable! :-)
>>
>> Hmm, I disagree. For instance with Postgresql it would only be 
>> absolutely reliable if this:
>>
>> >      DataSource1.Save
>> >      i = DataSource1.Connection.Exec(sSQL)[0]
>>
>> would have been encapsulated inside a transaction.
>>
>> If 'students_id' is a serial, then the correct procedure would be to:
>>
>> ---
>>
>> Start a transaction with "Begin;".
>>
>> Get the next serial number of 'students_id' with
>>
>> "select nextval(pg_get_serial_sequence('students', 'students_id')) as 
>> new_id;"
>>
>> Store the tuple together with students_id = new_id.
>>
>> Commit the transaction with "Commit;".
>>
>> If an error happened rollback the transaction with "Rollback;".
>>
>> ---
>>
>> Other RDBMS may have other commands but act the same.
>>
>> Alles Gute
>>
>> Christof Thalhofer
>>
>>
>> ----[ http://gambaswiki.org/wiki/doc/netiquette ]----
>
> Hi Christof,
>
> you should talk about 'LastInsertId' and the current project is a 
> small test on SQLite not on a data-server.
>
> Regards
>
> Gianluigi
>
>
> ----[ http://gambaswiki.org/wiki/doc/netiquette ]----

I don't believe using transaction blocks is the answer here. 
Transactions will guarantee the integrity of the enclosed set of queries 
but does not guarantee that they are all "atomic" from the point of view 
of the database. Christof's algorithm, while logically and technically 
correct looks to me to be suspicious on busy databases. Multiple and 
non-resolvable collisions could occur even for just two connections.

The "correct" way to to this in postgresql is to use INSERT/UPDATE 
RETURNING which will return the serial for the inserted record. However 
this is NOT standard SQL. Note also that INSERT ... RETURNING was added 
in MariaDB 10.5.0 
<https://mariadb.com/kb/en/mariadb-1050-release-notes/>, I don't know if 
it is supported properly by MySQL. The RETURNING syntax has been 
supported by SQLite since version 3.35.0 (2021-03-12) and is modelled on 
the postgresql one.

In sqlite the LastInsertID is the ID of the last inserted record, which 
as has been pointed out, is not necessarily the ID of the record "just" 
inserted by "this" user connection. The way to do this in sqlite (AFAIK) 
is to use the "select using all known columns" approach and it is up to 
the developer to know whether this will constitute a unique "key".

N.B. This will not be guaranteed for ODBC connections as it depends on 
the underlying dbms.


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.gambas-basic.org/pipermail/user/attachments/20230712/11a5efda/attachment-0001.htm>