|
Did you try using commitment control? I'm not sure, but it stands tolist
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?
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
