General question to professional SQL-Programming with SAL

Discussion forum about all things Gupta, OpenText and the community.
Geert_Schröder
Germany
Posts: 328
Joined: 01 May 2019, 08:04
Location: Germany

General question to professional SQL-Programming with SAL

Post by Geert_Schröder » 11 May 2013, 19:08

Hello Gupta-community,

Here my question: Is it possible to write safe/professional transactions without using SqlGetModifiedRows ?

Question is to be seen in context of a multiuser-system that does lots of inserts, updates, deletes - for example to move articles or machines from one stock to another. (Used Databases are Oracle or SqlServer.)

A programmer who is working on this gupta/SAL-based system told me first that he doesn't know of the existence of a function "SqlGetModifiedRows" and now that he heard from me about it says that he thinks that it is not necessary to use it since he thinks if no Sql-Error arises all SQL will be probably successfully done.

What is your opinion about the question if SqlGetModifiedRows should be used or not ?

Thanks in advance
Geert Schröder

FRBhote
India
Posts: 2221
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Re: General question to professional SQL-Programming with SAL

Post by FRBhote » 13 May 2013, 06:40

I don't think this would really be of any use to anyone - what good does it do to know the number of INSERT, UPDATE, or DELETE statements done? If you need to do something based upon an INSERT, UPDATE, or DELETE then a trigger would be the right thing.

RainerE
Germany
Posts: 2278
Joined: 27 Apr 2021, 12:40
Location: Germany

Re: General question to professional SQL-Programming with SAL

Post by RainerE » 13 May 2013, 10:38

To write multi user safe transactions with SQLBase as backend, you have to update/delete using the rowid in the where clause:
1) select rowid, yourcol1, ... from yourtable into :sRowid, ...
2a) update yourtable set yourcol1 = ..., ... where rowid = :sRowid (the rowid selected previously)
2b) delete from yourtable where rowid = :sRowid (the rowid selected previously)
3) further statements
...
n) commit
This is called 'optimistic locking'.

You should set the isolation level to 'RL' too.


If I remember my tests with MS Sql Server as backend correctly, you you might have to use SqlGetModifiedRows() to ensure a delete (don't know about updates) has deleted the number of rows you expected. There you should use a timestamp column for multiuser update/deletes. But if I remember correctly, a delete with a modified timestamp will not delete the row and will not pop up an error.


We use SqlGetModifiedRows() with SQLBase only in special cases. E.g. in a conversion routine which converts many records. Here we use SqlGetModifiedRows() for logging purposes.

Regards,
Rainer

Geert_Schröder
Germany
Posts: 328
Joined: 01 May 2019, 08:04
Location: Germany

Re: General question to professional SQL-Programming with SAL

Post by Geert_Schröder » 13 May 2013, 23:00

Hello Feroz and Rainer,

thanks for your answers.

Reading feroz answer I see that my description was probably to short.
In most cases rows are processed on a one by one basis, so the question is if the update or delete of 1 row was realy done.
This might happen in a longer loop over some positions of a kind of order.

Simplified example: update article_stock set quantity = quantity + 10 where stock_no = 25 and article_no = 345;

This can have the result of "1 rows updated" or "0 rows updated" if there is an entry for the key-combination or not.
Rows in article_stock might be deleted by other parallel running programs (this is a simplified example for something consisting out of more than one table in reality). If second is the case I personaly would prefer to rollback the whole transaction since uncorrect data is produced otherwise: article vanishes in one stock and does not arive in the destination stock. (similar to money transaction....money vanishes on one bank account and does not arive at the expected destination account might also be not desirable.)
Aim should be for example that article quantities after a year of movements are as correct as possible.

Best would be to have something as a rowid or timestamp in the condition as Rainer wrote, and this would be
absolutely necessary if the update would be absolute such as:
update article_stock set quantity = 100 where stock_no = 25 and article_no = 345 and rowid = :read_rowid;
(and rowid should then change automatically by the dbserver ... or if this does not exist one might use a timestamp-column)

BUT usualy the commands in that system are doing relative changes to the records as in the simplified example (and no rowid or timestamp is used).

Doing an update/delete of a single row and thinking it was done since no sqlerror arises is not correct in my eyes since "0 rows updated" does not throw a sqlerror. (And: insert into tab1 select ... from tab2 ... might be the same in producing the result "0 rows inserted")
I think one always has to control such 1row-DML-commnds if they were realy successfully done with SqlGetModifiedRows() to have all data written in the database. What is your opinion to these lines.
Best Regards
Geert

RainerE
Germany
Posts: 2278
Joined: 27 Apr 2021, 12:40
Location: Germany

Re: General question to professional SQL-Programming with SAL

Post by RainerE » 14 May 2013, 09:26

Hi,

some comments:

1) If you move articles from one stock to another I assume you increase one stock and decrease the other.
In this case I would do both in one transaction.
2) In most times I was able to select the data (in both/all stocks in your case) before updating it.
If this is posssible, the rowid would do the trick.
Note that where rowid = :sRowid is sufficient, you do not need further where clauses in the update (only in the select).
3) An other way would be to add 'check exists' to the end of the update statement.
In this case updating 0 rows would lead to an error.
4) In you scenario, SqlGetModifiedRows() can be helpful too, that's correct.
And if you have to delete data and cannot use rowid, it seems a good way for me.

=> Many ways (and there are other ways too I think) to do the same: Ensure multiuser access.

Regards,
Rainer

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests