Often queries come in two variants - either with or without FOR UPDATE.
While Oracle has FOR READ ONLY / FOR FETCH ONLY in principal, procob only allows FOR UPDATE. Neither the complete locking option nor the UPDATE may be a host variable.
PostgreSQL has none of the "read only" - "ignore me" options (but interestingly more locking options).
Therefore existing EXEC SQL usages that needs statements with both locking and no locking will need to have their statements duplicated and using the right one depending on some condition.
As this unnecessarily increases the LOC in the original code and much more in the generated one and also comes with the culprit that a change in the statements has to be done in both places, I'd like GixSQL to include an "extension" to specify the lock mode using a host variable.
Maybe FOR :lockmode which is then translated by GixSQL - "READ ONLY" would mean drop that clause completely, "NO KEY UPDATE" would be changed to "UPDATE" on Oracle.
Often queries come in two variants - either with or without
FOR UPDATE.While Oracle has
FOR READ ONLY/FOR FETCH ONLYin principal, procob only allowsFOR UPDATE. Neither the complete locking option nor theUPDATEmay be a host variable.PostgreSQL has none of the "read only" - "ignore me" options (but interestingly more locking options).
Therefore existing
EXEC SQLusages that needs statements with both locking and no locking will need to have their statements duplicated and using the right one depending on some condition. As this unnecessarily increases the LOC in the original code and much more in the generated one and also comes with the culprit that a change in the statements has to be done in both places, I'd like GixSQL to include an "extension" to specify the lock mode using a host variable.Maybe
FOR :lockmodewhich is then translated by GixSQL - "READ ONLY" would mean drop that clause completely, "NO KEY UPDATE" would be changed to "UPDATE" on Oracle.