[Gambas-user] gb.db local change

[BB]

On 10/12/22 11:31 pm, Benoit Minisini wrote:
> Le 10/12/2022 à 13:05, Benoit Minisini a écrit :
>> This is just for your hack, as it should not be done like that:
>>
>> if (!strcmp(THIS->driver->name, "postgresql"))
>>    q_add(" RETURNING *");
>>
>> The correct way is adding a flag in the 'flags' field of the 
>> DB_DATABASE structure that tells if we can add "RETURNING", and set 
>> that flag in the open_database() method of the drivers that support 
>> it (with a test on the database version possibly).
>>
>> Regards,
>>
>
> Maybe that can be supported in standard. I imagine an optional boolean 
> paramter to the Update() method: if set, then Update() returns a new 
> 'Result' object made from the result of the RETURNING clause.
>
> Apparently RETURNING is supported by PostgreSQL, SQLite, and recent 
> versions of MariaDB but just for INSERT.
>
> Regards,
>
Maybe, but here's what I have found out:

* It is not SQL standard syntax, you would have thought it likely but 
Oracle and MicroSomebody have their own syntaxes so who knows whether it 
will ever be.

* SQLite is only since version 3.35.0 (2021-03-12)  and is too recent 
for us (Debian is soooooo slow)

* Postgres has had the whole set since just before Noah gave up shaving. 
Oh OK, I am not sure when but it is mentioned in blogs as far back as 2014.

* MariaDB : "REPLACE ... RETURNING was added in MariaDB 10.5.0 
<https://mariadb.com/kb/en/mariadb-1050-release-notes/> (3 Dec 2019), 
and returns a resultset of the replaced rows." INSERT ... RETURNING" 
since the same release, it does have a DELETE ... RETURNING but the docs 
are imprecise as from when.

* Finally, there are differences and slight nuances in the three 
implementations, Postgres being the most sophisticated*. This is 
probably OK but might cause concern for some.

regards and thank you so much for the hack

bruce

P.S. After some convincing "discussions" by a colleague here, we are 
moving to using Postgres Trigger functions to centralise some of the 
more complex updates that happen like when an order is fulfilled and 
payment has or hasn't been received etc. This is really good as the code 
in the Gambas applications was diverging more and more. Getting the 
changed data back from one UPDATE is so much easier.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.gambas-basic.org/pipermail/user/attachments/20221210/c68ee7c0/attachment.htm>