Re: What is wrong?

qgenesist@xxxxxxxxxxx wrote:
This program locks and doesnt debug at begin of proc. what I am trying
to do is send in a workorder 112272 and retrieve the controlling work
order (lets call it 112100) and return that number. As you might be
able to tell, my procedure making skills are non-existant, I will
eventually do this and some other procs and make a service program.

call rtvctl# '112272'


How are you compiling this thing? With no main procedure, I don't think you could be making a program out of it.

If you're only getting one row, you don't need to go through all the bother of declaring a cursor, opening it, doing the fetch, then closing it. You could collapse those four subroutines into one that does this:

C/EXEC SQL
C+ select cjshid
C+ into :ctl#
C+ from edcjlf02
C+ where cjonbr = :wo#
c+ fetch first row only
C/END-EXEC

If you know that CJONBR is unique, you could get rid of the "fetch first row only" clause.

As far as the actual declare/open/fetch/close set that you have, your open is wrong given the way you declared the cursor. The OPEN...USING construction is used when you use a parameter marker in a dynamically prepared cursor definition. Because your cursor is statically declared, you have instead used a host variable (instead of a parameter marker), and your OPEN should not have the USING clause.



H*NOMAIN
H DEBUG
H DFTACTGRP(*NO) ACTGRP(*CALLER)
H OPTION(*SRCSTMT:*NODEBUGIO)

D DS_EDCJPF E DS EXTNAME(edcjpf)

/copy proto,rtvctl#
here it is:
D*RTVCTL# PR 6S 0 EXTPROC ('RTVCTL#')
D RTVCTL# PR 6S 0
D WO# 6S 0
CONST

P RTVCTL# B
D PI 6S 0
D WO# 6S 0 const
D CTL# S 6S 0

C/EXEC SQL
C+ Set Option commit= *none
C/END-EXEC
C/EXEC SQL
C+ Set Option closqlcsr= *endmod
C/END-EXEC

c #main tag
c exsr $dclcsr
c exsr $opncsr
c exsr $rtvctl
C dump
c exsr $rtvstt
c exsr $clscsr

C #end TAG
c eval *inlr = *on
C #rtx TAG
c return ctl#
c $dclcsr begsr
********************************************************
* Declare Cursor with Selection Criteria
********************************************************
C/EXEC SQL
C+ DECLARE CTLWO CURSOR FOR
C+ select cjshid
C+ from edcjlf02
C+ where cjonbr = :wo#
c+ fetch first row only
C/END-EXEC
c #dclcsr tag
c endsr
********************************************************
* Open Cursor
********************************************************
c $opncsr begsr
C/EXEC SQL
C+ OPEN CTLWO USING :wo#
C/END-EXEC
c #opncsr tag
c endsr
*********************************
* Retrieve Controlling Workorder
*********************************
c $rtvctl begsr
C/EXEC SQL
C+ FETCH CTLWO INTO :ctl#
C/END-EXEC
c #rtvctl# tag
c endsr
*********************************
* Retrieve SQL State
*************************************
c $rtvstt begsr
c #rtvstt tag
c endsr
*************************************
* Close Cursor
*************************************
c $clscsr begsr
C/EXEC SQL
C+ CLOSE CTLWO
C/END-EXEC
c #clscsr tag
c endsr

P RTVCTL# E

.

Relevant Pages

  • Re: What is wrong?
    ... Don't you want to use parameter markers on the cursor? ... C/EXEC SQL ... c $dclcsr begsr ... c #dclcsr tag ...
    (comp.sys.ibm.as400.misc)
  • Re: Cursor loop
    ... I've created a stored procedure that loops through a cursor, ... DECLARE curPeriod CURSOR LOCAL for SELECT * FROM tblPeriods ... The problem is that this loop only executes one time, ...
    (comp.databases.ms-sqlserver)
  • Re: Optimize function that uses cursors
    ... > The function can be made recursive as there are no much recursions (we ... > groups and ancestor groups (i.e. the parents of the parents and so on) for ... > a recursive call in the select of the cursor), and in the cursor look it ... > declare @more bit ...
    (microsoft.public.sqlserver.programming)
  • Re: Replace Cursor Procedure with Update Query?
    ... DECLARE @tmpResults TABLE, Create_Date Int, Seq int) ... Can I replace the below cursor based procedure with an update> query? ... Every morning, after the update,> I run the procedure below to update the Sequence field. ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL Procedure Optimisation
    ... Any reason why you did not mention what DBMS product? ... I'm using a cursor to perform updates based on an ID. ... DECLARE @Site_ID NVARCHAR ... due to your EAV design of the source ...
    (comp.databases)