Re: insert code was(Re: Embedded SQL - performance question)

It's basically a duplicate key error. Hence the RLA CHAIN + UPDATE
afterwards

Error message SQL0803 in QSQLMSG is this:

Message . . . . : Duplicate key value
specified.
Cause . . . . . : A unique index or unique constraint &1 in &2 exists
over
one or more columns of table &3 in &4. The operation cannot be
performed
because one or more values would have produced a duplicate key in the
unique
index or
constraint.
Recovery . . . : Change the statement so that duplicate keys are
not
produced. For information on what rows contain the duplicate key
values,
look at the previously listed messages in the job log (DSPJOBLOG command)
or
press F10 (Display messages in job log) on this
display.

On Thu, May 26, 2011 at 3:00 PM, Gqcy <gmufasa01@xxxxxxxxx> wrote:

SQL code -803
"violation of constraint..."

I would suspect there is a unique key constraint on the file.



On 5/26/2011 4:46 PM, Elbert Cook wrote:

Sorry off topic.

I saw this piece of code on our system yesterday. I'm still not sure what

to

think.

Exec Sql Insert Into myLib/myFile (Pattern, Color, Onhand)

VALUES(:Pattern1,

:Color1, :Qty1);

IF SQLCOD = -803;
Chain (Pattern1: Color1) myFile;
IF %found;
Onhand = Qty1;
Update myFilef %fields(Onhand);
Endif;
Endif;


-----Original Message-----
From: Joe Pluta [mailto:joepluta@xxxxxxxxxxxxxxxxx]
Sent: Thursday, May 26, 2011 5:13 PM
To: RPG programming on the IBM i / System i
Subject: Re: Embedded SQL - performance question

On 5/26/2011 3:16 PM, CRPence wrote:

I expect that SQL should be much faster even on a poorly performing
system; almost as fast as the RLA, esp. if the same index is utilized
for both the SQL and RLA.

I've never found a single-record fetch to be anywhere near as fast as
RLA, and I did exhaustive tests; SQL doesn't catch up until the block
size is upped to about 100 records. I could rerun all those tests, but
until someone shows me some evidence SQL has caught up, I have no reason
to repudiate the old data.

Joe

--
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.