[Gambas-user] DataView Help

[Denis Crowther]

On 7/12/23 10:38, BB wrote:
> 
> 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) 

The Sqlite documentation says "If a separate thread performs a new 
INSERT on the same database connection" results are unpredictable.

Wouldn't the "on the same database connection" make LastInsertID 
reliable unless you closed the connection first?

> 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.
> 
> 
> 
> 
> ----[ http://gambaswiki.org/wiki/doc/netiquette ]----






-- 
Regards
Denis