Did you try using commitment control? I'm not sure, but it stands to
reason that this would remedy your problem.
Brian May
Project Lead
Management Information Systems
Garan, Incorporated
Starkville, Mississippi
Young i Professionals
http://www.youngiprofessionals.com
"Steven Harrison" <steven.harrison@xxxxxxxxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
09/08/2009 09:46 PM
Please respond to
RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
To
"RPG programming on the IBM i / System i" <rpg400-l@xxxxxxxxxxxx>
cc
Subject
RE: RPG Native IO with SQL unique constraint
Hi Scott,
In Oracle and a number of other SQL implementations (and I believe it's
part of the SQL specification although I'd have to check that?) you can
specify deferred constraints that opposed to being checked after every
SQL statement are checked after a SQL transaction...
This is to allow for instances like I have mentioned. It's perfectly
reasonable to assume that there may be batches of statements that at
some point during a transaction the individual statements may break a
constraint but if you're treating the transaction as an atomic process
then the first and final state can still be correct. It's not something
that is used much but it can sometimes be useful.
In the example of the quote, it's a valid business rule that each quote
line is unique for a product ID and quantity. But if for example you
want to implement optimistic concurrency control using a timestamp
(which requires a "last updated" timestamp field) then the following can
occur:
Initial State:
QuoteID QuoteLineID ProductID
Quantity Last
Update
10000 1 Product1 15
"Timestamp Value"
10000 2 Product1 30
"Timestamp Value"
User 1 loads the screen and performs their edits. (To enforce optimistic
concurrency control it is important that the primary key values are
static so then if user 2 loads the same screen, maybe changes line 1 to
Product2, then when the user 1 attempts to save the above state the
system can flag that changes have been made since the last save and not
overwrite user 2's changes) Anyway, say user 1 enters:
QuoteID QuoteLineID ProductID
Quantity Last
Update
10000 1 Product1 30
"Timestamp Value"
10000 2 Product1 50
"Timestamp Value"
10000 3 Product1 70
NULL (new line)
The above data satisfies the business rule but the process of writing
the data to the database will result in violating the unique constraint
unless you treat the entire update as a transaction. Sure the user could
change the first line to a quantity of 50 instead and leave the 2nd line
with a value of 30 but this puts a lot of limitations on what the user
can and can't do...not a good thing at all.
I haven't had much exposure to iSeries so I don't know if deferred
constraints are implemented in it's version of SQL but I'm pretty
positive nothing of the sort exists in native IO.
So SQL doesn't really let you ignore the constraint but just defer the
check. I'm not sure how you would implement optimistic concurrency
control with data with unique constraints otherwise?
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Scott Klement
Sent: Wednesday, 9 September 2009 10:05 AM
To: RPG programming on the IBM i / System i
Subject: Re: RPG Native IO with SQL unique constraint
Hello,
Regardless, the main issue is native IO with unique constraints.
I'm confused. Are you saying (going back to your original example) that
SQL will let you set the 2nd record to 30 while the 3rd record is
already 30?!
I wouldn't think so!
Therefore, I'm looking at this saying to myself that it's not a question
of Native I/O with constraints. It's a question of having a constraint
that makes sense for your business rules. (And you don't seem to have
that.)
Or does SQL really allow you to ignore the constraint?
midrange.com
