Problem in Updating by embedded SQL

[pkbunu]

Hi,

Pls can you suggest on this what to modify in the below codes....Its not updating the desired file

Code

[C/EXEC SQL 
C+ DECLARE C1 DYNAMIC SCROLL CURSOR FOR SELECT UWID FROM NBDPOLICY 
C+ WHERE POLNUM IN (SELECT PPPOLNUM FROM NLADPIPPF WHERE PPERRMSG 
C+ LIKE '%LADPJ%' ) FOR UPDATE OF UWID 
C/END-EXEC 
********* 
C/EXEC SQL 
C+ OPEN C1 
C/END-EXEC 
*********** 
C/EXEC SQL 
C+ FETCH CURRENT FROM C1 
C/END-EXEC 
*********** 
C/EXEC SQL 
C+ 
C+ UPDATE NBDPOLICY SET UWID = 'B05' 
C+ WHERE CURRENT OF C1 
C+ 
C/END-EXEC 
*********** 
C/EXEC SQL 
C+ Close C1 
C/END-EXEC 
*********** 
C Eval *INLr = *On ]

Thanks

[jamief]

Re: Problem in Updating by embedded SQL

try adding this to the top

C/EXEC SQL set Option  DatFmt = *ISO     
c/END-EXEC

[pkbunu]

Re: Problem in Updating by embedded SQL

But why should i add the DATFMT....as i am not using any datfmt ....please advise..

[pkbunu]

Re: Problem in Updating by embedded SQL

more over my requirement is to select say some 5/6 records and want to update the table. Please let me know, where I am missing.....

[jamief]

Re: Problem in Updating by embedded SQL

just put the code in and test please...I can explain the rest later.

The default for compiling SQL is Commit *CHG and the very mean
system will not let you update a file without commitment control
running on it...I just posted a quick example on how to fix that!

jamie

[B.Hauser]

Re: Problem in Updating by embedded SQL

I'd suggest in either way to check either SQLCOD or SQLSTT to decect errors.

To know what error occured you can use the SQL message finder:
http://publib.boulder.ibm.com/infoce...zas2finder.htm

Here are some more information about SQLCOD and SQLSTT copied from the online library:
SQLCODEs and SQLSTATEs are returned in the SQLCA structure (which is automatically inserted into RPG sources by the SQL precompiler) or through the GET DIAGNOSTICS statement. SQLSTATE is the preferred standard return code. It provides application programs with return codes for common error conditions found among the DB2® products. SQLSTATEs are particularly useful when handling errors in distributed SQL applications.

SQLCODE (SQLCOD in RPG)
An SQLCODE is a return code. The return code is sent by the database manager after completion of each SQL statement.

Each SQLCODE that is recognized by a DB2 for i5/OS® application server has a corresponding message in the message file QSQLMSG. The message identifier for any SQLCODE is constructed by appending the absolute value (5 digits) of the SQLCODE to SQ and changing the third character to L if the first character of the SQLCODE is 0. For example, if the SQLCODE is 30070, the message identifier is SQ30070. If the SQLCODE is -0204, the message identifier is SQL0204. Lastly, if the SQLCODE is a 3-digit positive number, a zero is added before the first digit. For example, if the SQLCODE is 551, the message identifier is SQL0551.

SQLSTATE (SQLSTT in RPG)
SQLSTATE provides application programs with common return codes for success, warning, and error conditions that are found among the DB2 products. SQLSTATE values are particularly useful when handling errors in distributed SQL applications. SQLSTATE values are consistent with the SQLSTATE specifications that are contained in the SQL 1999 standard.

An SQLSTATE value is a return code that indicates the outcome of the most recently executed SQL statement. The mechanism used to access SQLSTATE values depends on where the SQL statement is executed. In Java™, SQLSTATE values are returned by using the getSQLState() method. In SQL functions, SQL procedures, SQL triggers, and embedded applications other than Java, SQLSTATE values are returned in the following ways:
The last five bytes of the SQLCA
A stand-alone SQLSTATE variable
The GET DIAGNOSTICS statement
SQLSTATE values are designed so that application programs can test for specific conditions or classes of conditions.

SQLSTATE values are comprised of a two-character class code value, followed by a three-character subclass code value. Class code values represent classes of successful and unsuccessful completion conditions. If you want to use SQLSTATE as the basis of your application's return codes, you can define your own SQLSTATE classes or subclasses using the following guidelines:

SQLSTATE classes that begin with the characters 7 through 9 or I through Z can be defined. Within these classes, any subclass can be defined.

SQLSTATE classes that begin with the characters 0 through 6 or A through H are reserved for the database manager. Within these classes, subclasses that begin with the characters 0 through H are reserved for the database manager. Subclasses that begin with the characters I through Z can be defined.
The class code of an SQLSTATE value indicates whether the SQL statement was executed successfully (class codes 00 and 01) or unsuccessfully (all other class codes).

SQLSTATE is related to SQLCODE. Every SQLSTATE has one or more SQLCODEs associated with it. An SQLSTATE can refer to more than one SQLCODE.

Birgitta

[blind_sql_inj]

Re: Problem in Updating by embedded SQL

you can
use the key word dump
and check the spool file created by program dump
for sqlcode and sqlstate values.

e.g. 
***********
dump;
*********** 
C Eval *INLr = *On

you can use this either,

if sqlstate <> '00000';
dump;
return;

hope this helps.