Re: Embedded SQL weirdness

Mark Lazarus wrote:

>  Again, this is not the behavior I experienced.  When the program was
> compiled w/ CRTSQLRPG COMMIT( *CHANGE ), the updates did not occur.  After
> compiling w/ COMMIT( *NONE ) it worked.
>

Sorry if I wasn't clear on that message.  Maybe the manual can help here:

"If SQL finds an error while running your DELETE statement, it stops deleting 
data
and returns a negative SQLCODE. If you specify COMMIT(*ALL), COMMIT(*CS),
COMMIT(*CHG), or COMMIT(*RR), no rows in the table are deleted (rows already
deleted by this statement, if any, are restored to their previous values). If
COMMIT(*NONE) is specified, any rows already deleted are not restored to their
previous values.

If SQL cannot find any rows that satisfy the search condition, an SQLCODE of 
+100
is returned."

That last sentence begs the question on the padding issue again.  In the other
message, you also asked if this was different because it was imbedded.  You also
state that you are on V3R2, so...

First:  What cumm pkg are you at?  Not a cop out.  I have seen problems in SQL
that are addressed by later V3R2 cumm pkgs.  Check it out with a SNDPTFORD
SF98320.  This will tell you where you *should*  be with cumm pkgs.

Second:  I would submit to you that if the interactive SQL and imbedded SQL
perform differently, with all other things equal, that is a reportable error and
probably has been previously discovered and fixed.  Hence the 'check on the 
PTFs'
stuff.

Third:  Yeah, I am pretty darn sure about that padding thing.  
Lexicographically,
trailing blanks are usually not counted.  For example, say I have a database
column that is 5 characters long.  Say I have a value in that column (I will use
lower case b for blanks and only uppercase to try an keep this clear) of 'AAbbb'
and another row with the value of 'bAAbb'.  Now obviously, (or maybe not so
obviously) if I look for the value 'AA', the first row hits, the second row does
not.  Why?  because the test case is padded with blanks to the length of the
compared field, so the select predicate becomes 'AAbbb' itself.  So, did you do 
a
MOVEL to get the field built?  Is it really only 2 characters?  Is the data in 
the
table really left justified?  Does it really have two spaces (true, hex '40'
characters)?

Maybe you already thought of all of this.  If it were me, I would still suspect
one of the above questions was not originally answered correctly, but I could be
wrong.

Final suggestion?  Put the program in debug, run the program, look *very* 
closely
at the job log.  Make sure the program is not returning something in the SQLCODE
or SQLSTATE (the latter is more consistent as it is defined by the standard 
while
the former is implementor-defined).

Since you said that the program did not function under commit but appeared to
without commitment control, then I would suspect that some error is being 
returned
to your program from the database manager.

regards, b.

--
===========================================================
R. Bruce Hoffman, Jr. -- IBM Certified AS/400 Administrator

-- The sum of all human knowledge is a fixed constant.
    It's the population that keeps growing!

begin:          vcard
fn:             R. Bruce Hoffman, Jr.
n:              ;R. Bruce Hoffman, Jr.
adr:            991 Lucon Road;;;Schwenksville;PA;19473;USA
email;internet: rbruceh@ibm.net
title:          Independant AS/400 Consultant
tel;work:       610-222-9707
tel;fax:        610-222-9717
x-mozilla-cpt:  ;0
x-mozilla-html: FALSE
version:        2.1
end:            vcard