RE: RPG Native IO with SQL unique constraint

I provided the example without much thought to a real system, just
simply to highlight the issue with unique constraints and native IO.
Maybe I should have said quotes instead or orders. A quote could have
multiple lines for the same product with different quantities and prices
to provide a different price depending on the quantity ordered. Then a
unique constraint would need to be applied across the quoteID, productID
and quantity to ensure multiple prices are not assigned to the same
product and quantity...

Regardless, the main issue is native IO with unique constraints. The
development platform we've been provided does not include the SQL
pre-compiler and therefore embedded SQL is not an option and we are
stuck with Native IO (and we don't really want to utilise the SQL Call
Level Interface APIs). Unique constraints have never been an issue for
me when performing IO using pure SQL so it seems it's like Tim says and
unique constraints and Native IO are not going to mix:( Looks like the
easiest course will be to simply remove the constraint...

Cheers for the advice.

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of MCKENNA, Tim
Sent: Wednesday, 9 September 2009 12:41 AM
To: RPG programming on the IBM i / System i
Subject: RE: RPG Native IO with SQL unique constraint

I agree this sounds more like a design problem than anything else in
this particular case. Orders are sometimes constrained to a single
instance of a product but if multiple lines with the same product are
allowed, there are not usually any other restrictions. The customer
wants 10 now and 10 with a delayed delivery date. A line with 10 units
is added to a backorder that also ships 10 units. I would not want to
explain to the Sales Dept. that customer must choose different
quantities.

The Native I/O problem of updating a set of records instead of as
individual record updates requires commitment control. The problem you
describe should be solved by using embedded SQL and committing the
updates. RPG does have native I/O COMMIT...not sure if it would address
your problem.


-----Original Message-----
From: Charles Wilt [mailto:charles.wilt@xxxxxxxxx]
Sent: Tuesday, September 08, 2009 09:56
To: RPG programming on the IBM i / System i
Subject: Re: RPG Native IO with SQL unique constraint

I'm with Simon.

The constraint as described makes no sense.

In 20+ years, I don't recall ever coming across a unique constraint
that involved a quantity field.

Charles