|
View previous topic :: View next topic
|
| Author |
Message |
nithyaroslin
New User
Joined: 29 Jan 2008
Posts: 14
Location: Chennai
|
|
|
|
Hi,
I have faced this issue in COBOL+DB2+CICS program. I am getting values from online and i need to update the table based on that variable. In one scenario, the same update query works, while for the other it doesn't. The table is not getting updated and i get sqlcode 0 too.
I checked the where clause even, the row is fetched properly. I checked if the table is being updated later in the program too. but it does not update later too. |
|
| Back to top |
|
 |
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Hi Nithya,
WELCOME!
With a little sample codings, people here might able to help you!
Thank You,
Sushanth |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
What you have described is most likely a coding error. Possibly somewhere a command is not being issued or a return code is not properly checked.
Is it possible the column(s) on the WHERE on the update has/have been changed to some other value and the update is actually happening to some different set of rows?
Does the program read via a cursor and then update the fetehed row?
As Sushanth mentions, seeing some of the code may help. |
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
Also, the transaction has to terminate successfully for the update to be committed.
I got mixed up when you say "update query". Updates and queries are 2 different things...
When you post some code, use the little Code button right above the message box. It's easy (click Code, paste, then click Code again) and fun (well, almost). |
|
| Back to top |
|
|
nithyaroslin
New User
Joined: 29 Jan 2008
Posts: 14
Location: Chennai
|
|
|
|
Hi,
Sorry for not posting the sample code earlier and sorry for the confusion made 'update query'
| Code: |
EXEC SQL
UPDATE MERCHANT
WHERE ID = :XXXXXXXX-INPUT.ID
AND DEPT = :XXXXXXXX-INPUT.DEPT
AND CRTE_TMS = :XXXXXXXX-INPUT.CRTE-TMS
END-EXEC.
EVALUATE SQLCODE
WHEN +0
PERFORM 3500-PROCESS-STAT-CHANGES
THRU 3500-PROCESS-STAT-CHANGES-EXIT
WHEN OTHER
MOVE 004 TO RQBLK-MAJ-RET-CODE
MOVE WS-UPDATE TO WS-ERROR-OPERATION
MOVE C-MRCH-USR-AUTH TO WS-ERROR-TABLE1
MOVE C-ID TO WS-KEY-NAME1
MOVE ID OF XXXXXXXX-INPUT
TO WS-KEY-VALUE1
MOVE C-DEPT TO WS-KEY-NAME2
MOVE DEPT OF XXXXXXXX-INPUT
TO WS-KEY-VALUE2
MOVE C-CRTE-TMS TO WS-KEY-NAME3
MOVE CRTE-TMS OF XXXXXXXX-INPUT
TO WS-KEY-VALUE3
PERFORM 8200-HANDLE-SQL-ERROR
END-EVALUATE.
|
Regards,
Nithya[/code] |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Hello Nithya,
| Code: |
UPDATE MERCHANT
WHERE ID = :XXXXXXXX-INPUT.ID
AND DEPT = :XXXXXXXX-INPUT.DEPT
AND CRTE_TMS = :XXXXXXXX-INPUT.CRTE-TMS |
There needs to be a SET in UPDATE statement, i think. Check it out.
Example :-
| Code: |
UPDATE DSN8910.EMP
SET PHONENO='3565'
WHERE EMPNO='000190'; |
Thank You
Sushanth Bobby |
|
| Back to top |
|
|
nithyaroslin
New User
Joined: 29 Jan 2008
Posts: 14
Location: Chennai
|
|
|
|
sorry Sushanth... I somehow missed out the Set clause whilc copying. It is there in the program.
| Code: |
UPDATE MERCHANT
SET AUTH_STAT_CDE = ‘Y’
WHERE ID = :XXXXXXXX-INPUT.ID
AND DEPT = :XXXXXXXX-INPUT.DEPT
AND CRTE_TMS = :XXXXXXXX-INPUT.CRTE-TMS
|
|
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Nithya,
After executing the EXEC SQL, try displaying SQLERRD(3). Like
| Code: |
EXEC SQL
UPDATE MERCHANT
SET AUTH_STAT_CDE = ‘Y’
WHERE ID = :XXXXXXXX-INPUT.ID
AND DEPT = :XXXXXXXX-INPUT.DEPT
AND CRTE_TMS = :XXXXXXXX-INPUT.CRTE-TMS
END-EXEC.
DISPLAY 'SQLERRD(3) =' SQLERRD(3). |
SQLERRD(3) will let you know how many rows were updated.
Thank You,
Sushanth Bobby |
|
| Back to top |
|
|
nithyaroslin
New User
Joined: 29 Jan 2008
Posts: 14
Location: Chennai
|
|
|
|
Hi,
The number of rows updated is 1 when i dispalyed the SQLERRD(3) |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Nithya,
Your scenario is, you are updating a table and you are getting SQLCODE as 0. But the record is not getting updated right.
1) Try a select on the table
| Code: |
SELECT * from MERCHANT
WHERE ID = :XXXXXXXX-INPUT.ID
AND DEPT = :XXXXXXXX-INPUT.DEPT
AND CRTE_TMS = :XXXXXXXX-INPUT.CRTE-TMS |
2)run your program
3)check whether the update has been made
| Code: |
SELECT * from MERCHANT
WHERE ID = :XXXXXXXX-INPUT.ID
AND DEPT = :XXXXXXXX-INPUT.DEPT
AND CRTE_TMS = :XXXXXXXX-INPUT.CRTE-TMS |
Check, if there are any ROLLBACK statement in the following code.
Sushanth Bobby |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
If this is not yet resolved, suggest you place a COMMIT immediately after the update, end the program, and then run a query to ensure the update has happened. This would be for testing only. |
|
| Back to top |
|
|
nithyaroslin
New User
Joined: 29 Jan 2008
Posts: 14
Location: Chennai
|
|
|
|
Hi Shusanth and Dick,
Thank you very much for the support. I really thought of ROLLBACK and then tried retrieving the row from the table immediately after update and found that the row has been updated. 
This ROLLBACK is done in front end(Java and EJB).
Thank you!!!!
Regards,
Nithya |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
You're welcome - thank you for posting the explanation
d |
|
| Back to top |
|
|
|
|
