AW: RPGLE SQL Update Cursor problem

Hi,

You select from ITUNTP07, but define your datastructure over ITUNTP01. This
will only work if both file descriptions are an exact match, i.e. the same
fields with the same definition in the same sequence.
If ITUNTP07 is an DDS described logical file, replace it with the physical
file. Because SQL-Queries based where DDS described logical files are
specified, must be executed by the classic query engine and cannot profit
from the improvements and enhancements of the newer SQL Query Engine.
Contrary the rerouting from SQE to CQE may cost between 10 and 15%
performance.

The query optimizer does not care about the DDS described logical file, i.e.
will take the join information, field selects and the select/omit clauses
rewrite the SQL statement based on the physical file.

To avoid that NULL values are moved into a field where NULL values are not
allowed, just specify the fields you want to change in the DECLARE clause,
as FOR UPDATE OF Fields as follows:

C/EXEC SQL 
C+ DECLARE C1 DYNAMIC SCROLL CURSOR FOR SELECT * FROM ITUNTP07 WHERE
C+ URCTRY = 1 and URPGM# = 230 and urclas = 'FF' and URSTAT not in 
C+ ('APP','FTC-B','TERM') 
C+ For Update Of URPSUS, URSUBS, URSSDT, URNFDT, URCHDT, URCHTI, URCHBY
C/END-EXEC  


Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them?  Not training them
and keeping them!"



-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von KJFrey@xxxxxxxxxxxxxxx
Gesendet: Friday, February 09, 2007 16:10
An: rpg400-l@xxxxxxxxxxxx
Betreff: RPGLE SQL Update Cursor problem


I have a program that is using an SQL cursor to retrieve the data I need 
to update.

C/EXEC SQL 
C+ DECLARE C1 DYNAMIC SCROLL CURSOR FOR SELECT * FROM ITUNTP07 WHERE
C+ URCTRY = 1 and URPGM# = 230 and urclas = 'FF' and URSTAT not in 
C+ ('APP','FTC-B','TERM') 
C/END-EXEC  

The program is retrieving the data and placing into a Host variable like 
below

C/EXEC SQL 
C+ FETCH NEXT FROM C1 INTO :Unitpolicy 
C/END-EXEC       

DUnitPolicy     E DS                  EXTNAME(ITUNTP01) 

The problem is when I am updating the fields in the data structure I want 
to perform an update on the Cursor and I 
would like to use WHERE CURRENT OF C1

C/EXEC SQL 
C+ UPDATE ITUNTP07 SET URPSUS = :urpsus, URSUBS = :ursubs, 
C+ URSSDT = :urssdt, URNFDT = :urnfdt, URCHDT = :urchdt, URCHTI = 
C+ :urchti, URCHBY = :urchby WHERE CURRENT OF C1 
C/END-EXEC 

This does not work....I can't figure out what is wrong......here is the 
error messages I am getting

Data mapping error on member ITUNTP07. 
Data mapping error on member ITUNTP07. 
Data mapping error on member ITUNTP07. 
Null values not allowed in column or variable URCTRY. 

Cause . . . . . :   A data mapping error occurred on field  in record 
number 
  0, record format FORMAT0001, member number 1, in member ITUNTP07 file   
  ITUNTP07 in library ITSDEVFIL, because of error code 20. The error codes 
and 
  their meanings follow:  

20 -- A field that is not null capable could not be set to null. 

Cause . . . . . :   One of the following has occurred:  
    -- Column URCTRY is a target column in an UPDATE or INSERT statement 
for 
  table ITUNTP07. Either a null value was specified to be 
  inserted or updated into this column or a value for the column was not   
 
  specified in an INSERT statement and the column does not allow null 
values. 
  The null value was specified in the relative entry number 1 in the 
VALUES 
  list, select list, or SET clause.  

 I am not updating URCTRY.........................I don't understand what 
I am doing wrong.

any suggestions would be greatly appreciated.

Ken J Frey 
IT Project Leader 
Email: KJFrey@xxxxxxxxxxxxxxx 
Office: 410-584-0391 
Fax: 410-584-0391 
Cell: 443-742-0642