What is wrong? - IBM AS400

This is a discussion on What is wrong? - IBM AS400 ; 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 ...

+ Reply to Thread
Results 1 to 18 of 18

Thread: What is wrong?

  1. What is wrong?

    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'




    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


  2. Re: What is wrong?

    It's been a long day, so take this with a grain of salt...

    Don't you want to use parameter markers on the cursor?
    C/EXEC SQL
    C+ DECLARE CTLWO CURSOR FOR
    C+ select cjshid
    C+ from edcjlf02
    C+ where cjonbr = ? -- <<<<<------------
    c+ fetch first row only
    C/END-EXEC

    Of course, if you are only reading on row, you probably don't need a cursor
    at all.

    Sam


    wrote in message
    news:bc9140d2-b9d9-4eed-9770-72c38177125d@s8g2000prg.googlegroups.com...
    > 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'
    >
    >
    >
    >
    > 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
    >




  3. Re: What is wrong?

    qgenesist@hotmail.com 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
    >


  4. Re: What is wrong?

    Saml wrote:
    > It's been a long day, so take this with a grain of salt...
    >
    > Don't you want to use parameter markers on the cursor?
    > C/EXEC SQL
    > C+ DECLARE CTLWO CURSOR FOR
    > C+ select cjshid
    > C+ from edcjlf02
    > C+ where cjonbr = ? -- <<<<<------------
    > c+ fetch first row only
    > C/END-EXEC


    I think you cannot use a parameter marker in a
    statically declared cursor like that. Parameter
    markers are used in dynamically constructed cursor
    definitions that you then PREPARE into a SQL variable;
    the SQL variable is then referenced in the DECLARE
    statement:

    /free
    rpg_var = 'select col_a from table_1 where col_b = ?';
    /end-free
    c/exec sql
    c+ prepare sql_var from :rpg_var
    c/end-exec
    c/exec sql
    c+ declare my_cursor for sql_var
    c/end-exec
    c/exec sql
    c+ open my_cursor using :wo#
    c/end-exec


    >
    > Of course, if you are only reading on row, you probably don't need a cursor
    > at all.


    Exactly right:


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


    > wrote in message
    > news:bc9140d2-b9d9-4eed-9770-72c38177125d@s8g2000prg.googlegroups.com...
    >> 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'
    >>
    >>
    >>
    >>
    >> 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
    >>

    >
    >


  5. Re: What is wrong?

    Hi,

    I assume your problem is not embedded SQL, but the ILE concepts.

    Is there a main procedure in your program, that calls this procedure?
    If not I cannot imagine how you got your program compiled.
    If yes, you have to call the program and not the procedure from
    outside to debug.
    Your procedure is not exported, that means it can only be called from
    the main procedure or an other procedure that is located in the same
    souce.
    If the procedure is not called from within your source, it will never
    be called.
    Procedures cannot be called directly from a command line, i.e. they
    must be called from a program's main procedure or a procedure located
    in either a service program or a module that is bound to a program.

    Yet an other comment!

    Obviously you are not yet on release V5R4.
    In V5R4 only a single SET OPTION-Statement is allowed.
    If several SET OPTION statements are coded your programm/module won't
    compile.

    To prevent future compile errors, just change the SET OPTION-
    Statements as follows:

    C/EXEC SQL
    C+ Set Option Commit = *NONE, CloSQLCsr = *ENDMOD
    C/END-Exec

    BTW CloSQLCsr is not a good solution, at least not if your programm is
    called repeatedly from the same job. With *ENDMOD the ODP (open data
    path) get deleted as soon as the module ends. The next time the
    program/procedure gets called the complete optimization must be
    performed, which is the most time consuming part of executing an SQL
    statement. If *ENDACTGRP is specified, the ODP stays open after the
    second execution (in the same activation group), i.e. for all
    subsequent calls no optimization will be performed, but only the data
    in the OPD actualized.

    Birgitta

  6. Re: What is wrong?

    On Jan 24, 12:29*am, "Hau...@sss-software.de"
    wrote:
    > Hi,
    >
    > I assume your problem is not embedded SQL, but the ILE concepts.
    >
    > Is there a main procedure in your program, that calls this procedure?
    > If not I cannot imagine how you got your program compiled.
    > If yes, you have to call the program and not the procedure from
    > outside to debug.
    > Your procedure is not exported, that means it can only be called from
    > the main procedure or an other procedure that is located in the same
    > souce.
    > If the procedure is not called from within your source, it will never
    > be called.
    > Procedures cannot be called directly from a command line, i.e. they
    > must be called from a program's main procedure or a procedure located
    > in either a service program or a module that is bound to a program.
    >
    > Yet an other comment!
    >
    > Obviously you are not yet on release V5R4.
    > In V5R4 only a single SET OPTION-Statement is allowed.
    > If several SET OPTION statements are coded your programm/module won't
    > compile.
    >
    > To prevent future compile errors, just change the SET OPTION-
    > Statements as follows:
    >
    > C/EXEC SQL
    > C+ Set Option *Commit = *NONE, CloSQLCsr = *ENDMOD
    > C/END-Exec
    >
    > BTW CloSQLCsr is not a good solution, at least not if your programm is
    > called repeatedly from the same job. With *ENDMOD the ODP (open data
    > path) get deleted as soon as the module ends. The next time the
    > program/procedure gets called the complete optimization must be
    > performed, which is the most time consuming part of executing an SQL
    > statement. If *ENDACTGRP is specified, the ODP stays open after the
    > second execution (in the same activation group), i.e. for all
    > subsequent calls no optimization will be performed, but only the data
    > in the OPD actualized.
    >
    > Birgitta


    Birgitta,

    I have lusted after your SQL skills for many years, I had hoped you
    would answer some of this.

    I am on 540 and it does compile, but I will make the corrections. I
    am making some poor decisions now, because I don't understand (and
    they are not intuitively obvious) what the mechanics of this are, for
    example, I wrote a pretty big program that created a table and a eva
    index (whatever) and then read the index using RPG, and everything
    compiled and I get 4101 at runtime. What good is that?

    To the others, you will see that NOMAIN is commented as is one thing
    in the proto. the call stack is in some statement in the program
    (past the pep) that correlates to nothing, and is prior (must be some
    sort of prolog code to the begin proc statement) I cant get at it.

    I do appreciate the help.

  7. Re: What is wrong?

    qgenesist@hotmail.com wrote:
    > On Jan 24, 12:29 am, "Hau...@sss-software.de"
    > wrote:
    >> Hi,
    >>
    >> I assume your problem is not embedded SQL, but the ILE concepts.
    >>
    >> Is there a main procedure in your program, that calls this procedure?
    >> If not I cannot imagine how you got your program compiled.
    >> If yes, you have to call the program and not the procedure from
    >> outside to debug.
    >> Your procedure is not exported, that means it can only be called from
    >> the main procedure or an other procedure that is located in the same
    >> souce.
    >> If the procedure is not called from within your source, it will never
    >> be called.
    >> Procedures cannot be called directly from a command line, i.e. they
    >> must be called from a program's main procedure or a procedure located
    >> in either a service program or a module that is bound to a program.
    >>
    >> Yet an other comment!
    >>
    >> Obviously you are not yet on release V5R4.
    >> In V5R4 only a single SET OPTION-Statement is allowed.
    >> If several SET OPTION statements are coded your programm/module won't
    >> compile.
    >>
    >> To prevent future compile errors, just change the SET OPTION-
    >> Statements as follows:
    >>
    >> C/EXEC SQL
    >> C+ Set Option Commit = *NONE, CloSQLCsr = *ENDMOD
    >> C/END-Exec
    >>
    >> BTW CloSQLCsr is not a good solution, at least not if your programm is
    >> called repeatedly from the same job. With *ENDMOD the ODP (open data
    >> path) get deleted as soon as the module ends. The next time the
    >> program/procedure gets called the complete optimization must be
    >> performed, which is the most time consuming part of executing an SQL
    >> statement. If *ENDACTGRP is specified, the ODP stays open after the
    >> second execution (in the same activation group), i.e. for all
    >> subsequent calls no optimization will be performed, but only the data
    >> in the OPD actualized.
    >>
    >> Birgitta

    >
    > Birgitta,
    >
    > I have lusted after your SQL skills for many years, I had hoped you
    > would answer some of this.
    >
    > I am on 540 and it does compile, but I will make the corrections. I
    > am making some poor decisions now, because I don't understand (and
    > they are not intuitively obvious) what the mechanics of this are, for
    > example, I wrote a pretty big program that created a table and a eva
    > index (whatever) and then read the index using RPG, and everything
    > compiled and I get 4101 at runtime. What good is that?
    >
    > To the others, you will see that NOMAIN is commented as is one thing
    > in the proto.


    Get rid of your begin procedure and end procedure
    statements.


    the call stack is in some statement in the program
    > (past the pep) that correlates to nothing, and is prior (must be some
    > sort of prolog code to the begin proc statement) I cant get at it.
    >
    > I do appreciate the help.


  8. Re: What is wrong?

    An EVI [Encoded Vector Index] is used for query access only; i.e.
    there is no row level access available. Review of DSPFD for the INDEX
    will show that it is not read-capable. This is because the rows are not
    available in that type of index.

    Regards, Chuck
    --
    All comments provided "as is" with no warranties of any kind
    whatsoever and may not represent positions, strategies, nor views of my
    employer

    qgenesist@hotmail.com wrote:
    > <>
    > they are not intuitively obvious) what the mechanics of this are, for
    > example, I wrote a pretty big program that created a table and a eva
    > index (whatever) and then read the index using RPG, and everything
    > compiled and I get 4101 at runtime. What good is that?
    > <>


  9. Re: What is wrong?

    OK, new deal:

    H*NOMAIN (note that this is commented)
    H DEBUG
    H DFTACTGRP(*NO) ACTGRP(*CALLER)
    H OPTION(*SRCSTMT:*NODEBUGIO)
    D DS_EDCJPF E DS EXTNAME(edcjpf)

    /copy proto,rtvctl#

    P*RTVCTL# B (commented this
    run)
    D RTVCTL# PI 6S 0
    D WO# 6S 0 const
    D CTL# 6S 0

    C/EXEC SQL
    *NB: (I am on V5R4M0 and it refuses one line)
    C+ Set Option commit= *none
    C/END-EXEC
    C/EXEC SQL
    C+ Set Option closqlcsr= *endmod
    C/END-EXEC

    c #main tag
    c exsr $rtvctl
    C dump
    c exsr $rtvstt
    C #end TAG
    c eval *inlr = *on
    C #rtx TAG
    c return ctl#

    ***********************************************
    * Retrieve Controlling Workorder
    ***********************************************
    c $rtvctl begsr
    C/EXEC SQL
    C+ select cjshid
    C+ into :ctl#
    C+ from edcjlf02
    C+ where cjonbr = :wo#
    c+ fetch first row only
    C/END-EXEC
    c #rtvctl# tag
    c endsr
    *************************************
    * Retrieve SQL State
    *************************************
    c $rtvstt begsr
    c #rtvstt tag
    c endsr

    P*RTVCTL# E (commented this run)

    No compile.
    main cannot return value

    so uncomment all:
    rmv actgrp
    makes a program

    5 QEXCPML QSYS /00A6
    RTVCTL# MCNV _QRNP_PEP_RTVCTL#
    RTVCTL# MCNV 2100000001 RTVCTL#

    break is at #Main and at b of proc no hit
    stuck forever

    crtrpgmod does not understand sql.


    how do I make this a procedure that I can call and call again from a
    service program and how do I test it one proc at a time and debug it?




  10. Re: What is wrong?

    On Jan 24, 10:22*am, qgenes...@hotmail.com wrote:
    > OK, new deal:


    Try this. You can change the SQL to match your actual database; I had
    to do something that would work here. This works - I ran it exactly
    as it appears below in debug and verified that it ended normally.



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

    D gizmo s 6s 0

    D RTVCTL# PR 6S 0
    D WO# 6S 0 const

    c eval gizmo = rtvctl#(123456)
    c eval *inlr = *on

    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, closqlcsr = *endmod
    C/END-EXEC

    c #main tag
    c exsr $select
    C dump

    C #rtx TAG
    c return ctl#
    c $select begsr
    ************************************************** ******
    * Select value directly into return variable
    ************************************************** ******
    C/EXEC SQL
    C+ select zoned(987654)
    C+ into :ctl#
    C+ from sysibm/sysdummy1
    C+ where :wo# = :wo#
    c+ fetch first row only
    C/END-EXEC
    c #select tag
    c endsr

    P RTVCTL# E




    >
    > H*NOMAIN *(note that this is commented)
    > H DEBUG
    > H DFTACTGRP(*NO) ACTGRP(*CALLER)
    > H OPTION(*SRCSTMT:*NODEBUGIO)
    > D DS_EDCJPF * * E DS * * * * * * * * *EXTNAME(edcjpf)
    >
    > */copy proto,rtvctl#
    >
    > P*RTVCTL# * * * * B * * * (commented this
    > run)
    > D RTVCTL# * * * * PI * * * * 6S 0
    > D *WO# * * * * * * * * * * * * *6S 0 const
    > D *CTL# * * * * * * * * * * * * *6S 0
    >
    > C/EXEC SQL
    > *NB: (I am on V5R4M0 and it refuses one line)
    > C+ Set Option commit= *none
    > C/END-EXEC
    > * C/EXEC SQL
    > C+ Set Option closqlcsr= *endmod
    > C/END-EXEC
    >
    > c * * #main * * *tag
    > c * * * * * * * * * exsr * * *$rtvctl
    > C * * * * * * * * * dump
    > c * * * * * * * * * exsr * * *$rtvstt
    > C * * #end * * * * *TAG
    > c * * * * * * * * * eval * * **inlr * * = *on
    > C * * #rtx * * * * *TAG
    > c * * * * * * * * * return * *ctl#
    >
    > ************************************************
    > ** Retrieve Controlling Workorder
    > ************************************************
    > c * * $rtvctl * * * begsr
    > C/EXEC SQL
    > C+ * * select cjshid
    > C+ * * into :ctl#
    > C+ * * from edcjlf02
    > C+ * * where cjonbr = :wo#
    > c+ * * fetch first row only
    > C/END-EXEC
    > c * * #rtvctl# * * *tag
    > c * * * * * * * * * endsr
    > **************************************
    > ** Retrieve SQL State
    > **************************************
    > c * * $rtvstt * * * begsr
    > c * * #rtvstt * * * tag
    > c * * * * * * * * * endsr
    >
    > P*RTVCTL# * * * * E * * (commented this run)
    >
    > No compile.
    > main cannot return value
    >
    > so uncomment all:
    > rmv actgrp
    > makes a program
    >
    > *5 *QEXCPML * *QSYS * * * * * * * * * * /00A6
    > * *RTVCTL# * *MCNV * * * * * * * * * * * ** *_QRNP_PEP_RTVCTL#
    > * *RTVCTL# * *MCNV * * * * *2100000001 * * * *RTVCTL#
    >
    > break is at #Main and at b of proc no hit
    > stuck forever
    >
    > crtrpgmod does not understand sql.
    >
    > how do I make this a procedure that I can call and call again from a
    > service program and how do I test it one proc at a time and debug it?


    I guess there's a lesson in this for all of us about remote
    debugging :-(

    1. Put your begin and end procedure statements back (un-comment)
    2. In the main procedure (preceding your begin-proc for RTVCTL#),
    code a CALLP to your RTVCTL# procedure;
    3.

  11. Re: What is wrong?

    You try to generate the module with CRTRPGMOD?

    This won't work. If you want to create a module with embedded SQL, you
    have to use CRTSQLRPGI (the same command as for a program).
    If you use PDM with option 14 a program is generated and option 15
    will generate a module, with the same command. Only the object type in
    the compile command is either set to *MODULE or *PGM.

    The precompiler comments out the SQL statements and replaces them with
    API calls. After either CRTRPGMOD or CRTBNDRPG is called to generate a
    module or program from the newly generated source.

    Birgitta

  12. Re: What is wrong?

    On Jan 24, 12:23*pm, "Hau...@sss-software.de"
    wrote:
    > You try to generate the module with CRTRPGMOD?


    No; CRTSQLRPGI with OBJTYPE(*PGM), using opt 14 from PDM. The compile
    succeeded using the code I posted, and the program ran successfully.
    Note that the program main procedure has no prototype or interface,
    and the call to the program passes no parameter. The main procedure
    calls the original poster's internal procedure using the prototype.


    >
    > This won't work. If you want to create a module with embedded SQL, you
    > have to use CRTSQLRPGI (the same command as for a program).
    > If you use PDM with option 14 a program is generated and option 15
    > will generate a module, with the same command. Only the object type in
    > the compile command is either set to *MODULE or *PGM.
    >
    > The precompiler comments out the SQL statements and replaces them with
    > API calls. After either CRTRPGMOD or CRTBNDRPG is called to generate a
    > module or program from the newly generated source.
    >
    > Birgitta



  13. Re: What is wrong?

    The /EXEC SQL and /END-EXEC are not RPG, they are tags to denote what
    a pre-compiler is supposed to convert into RPG. They are directives to
    the SQL precompiler, that the upcoming and prior source is to be
    interpreted as embedded SQL source, rather than as RPG source. Use
    CRTSQLRPGI OBJTYPE(*MODULE) to create an SQLRPG module.

    The SQL request to [CREATE PROCEDURE or] CREATE FUNCTION can be used
    to generate a callable interface to a module packaged into a service
    program by CRTSRVPGM; as an external user defined function. Then the
    function can be invoked for testing, by something as simple as:
    SELECT MYUDF(123456) FROM SYSIBM/SYSDUMMY1

    Since the module is doing nothing more than SQL, it could probably be
    written instead, as a SQL User Defined Function; i.e. instead of using
    EXTERNAL NAME on the CREATE FUNCTION to identify where the procedure is
    located, specify LANGUAGE SQL along with the SQL procedural statements,
    and the SQL will compile into a service program with a procedure name
    matching [or representing] the function name being created.

    For example, something like the following:

    CREATE FUNCTION RtvCtl(WoNbr NUMERIC(6, 0))
    RETURNS NUMERIC(6, 0) LANGUAGE SQL
    READS SQL DATA DETERMINISTIC NO EXTERNAL ACTION
    BEGIN
    DECLARE CtlNbr NUMERIC (6, 0);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN NULL;

    select cjshid
    into CtlNbr
    from edcjlf02
    where cjonbr = WoNbr
    fetch first row only ;

    RETURN CtlNbr;
    END


    The above create statement would log:
    Service program RTVCTL created in library XX.
    Object RTVCTL in XX type *MODULE deleted.

    Then testing the SQL with the following, for example:
    select rtvctl(171819) from qsys2.qsqptabl

    Regards, Chuck
    --
    All comments provided "as is" with no warranties of any kind
    whatsoever and may not represent positions, strategies, nor views of my
    employer

    qgenesist@hotmail.com wrote:
    > <>
    >
    > crtrpgmod does not understand sql.
    >
    > how do I make this a procedure that I can call and call again from a
    > service program and how do I test it one proc at a time and debug it?


  14. Re: What is wrong?

    On Jan 24, 12:43*pm, CRPence wrote:
    > * *The /EXEC SQL and /END-EXEC are not RPG, they are tags to denote what
    > a pre-compiler is supposed to convert into RPG. *They are directives to
    > the SQL precompiler, that the upcoming and prior source is to be
    > interpreted as embedded SQL source, rather than as RPG source. *Use
    > CRTSQLRPGI OBJTYPE(*MODULE) to create an SQLRPG module.


    The original poster isn't having trouble with the embedded SQL. His
    problem is ILE and RPGLE concepts: he has a subprocedure in his code,
    and no way of calling it. Look again.

    [...]

  15. Re: What is wrong?

    Understood. However the quoted snippet of my most recent response,
    snipped too much of that response; look again ;-) The quoted snippet to
    which I responded, should have made it clear that I was responding first
    to the comment that the "crtrpgmod does not understand sql." And second
    about how to setup to call the procedure to test and debug -- using SQL.

    Anyhow, although obviously CRTRPGMOD is not going to like /EXEC SQL,
    if the OP did not have a problem with CRTRPGMOD, then one has to wonder
    why the quoted comment was even written? Perhaps my post was for
    naught, with regard to progressing toward resolution of the issue, but
    it did address the quoted comment with respect to the given source.

    Besides, my not-so-ulterior motive was to get the OP to consider that
    there was possibly little reason to make an effectively pure SQL
    procedure from RPG when it could be done simply instead, as a purely SQL
    routine; as coded, an SQL function. :-)

    Regards, Chuck
    --
    All comments provided "as is" with no warranties of any kind
    whatsoever and may not represent positions, strategies, nor views of my
    employer

    Jonathan Ball wrote:
    > On Jan 24, 12:43 pm, CRPence wrote:
    >> The /EXEC SQL and /END-EXEC are not RPG, they are tags to denote what
    >> a pre-compiler is supposed to convert into RPG. They are directives to
    >> the SQL precompiler, that the upcoming and prior source is to be
    >> interpreted as embedded SQL source, rather than as RPG source. Use
    >> CRTSQLRPGI OBJTYPE(*MODULE) to create an SQLRPG module.

    >
    > The original poster isn't having trouble with the embedded SQL. His
    > problem is ILE and RPGLE concepts: he has a subprocedure in his code,
    > and no way of calling it. Look again.
    >
    > [...]


  16. Re: What is wrong?

    On Jan 24, 3:33*pm, CRPence wrote:
    > * *Understood. *However the quoted snippet of my most recent response,
    > snipped too much of that response; look again ;-) *The quoted snippet to
    > which I responded, should have made it clear that I was responding first
    > to the comment that the "crtrpgmod does not understand sql." *And second
    > about how to setup to call the procedure to test and debug -- using SQL.
    >
    > * *Anyhow, although obviously CRTRPGMOD is not going to like /EXEC SQL,
    > if the OP did not have a problem with CRTRPGMOD, then one has to wonder
    > why the quoted comment was even written? *Perhaps my post was for
    > naught, with regard to progressing toward resolution of the issue, but
    > it did address the quoted comment with respect to the given source.
    >
    > * *Besides, my not-so-ulterior motive was to get the OP to consider that
    > there was possibly little reason to make an effectively pure SQL
    > procedure from RPG when it could be done simply instead, as a purely SQL
    > routine; as coded, an SQL function. *:-)
    >
    > Regards, Chuck
    > --
    > * All comments provided "as is" with no warranties of any kind
    > whatsoever and may not represent positions, strategies, nor views of my
    > employer
    >
    >
    >
    > Jonathan Ball wrote:
    > > On Jan 24, 12:43 pm, CRPence wrote:
    > >> * *The /EXEC SQL and /END-EXEC are not RPG, they are tags to denotewhat
    > >> a pre-compiler is supposed to convert into RPG. *They are directives to
    > >> the SQL precompiler, that the upcoming and prior source is to be
    > >> interpreted as embedded SQL source, rather than as RPG source. *Use
    > >> CRTSQLRPGI OBJTYPE(*MODULE) to create an SQLRPG module.

    >
    > > The original poster isn't having trouble with the embedded SQL. *His
    > > problem is ILE and RPGLE concepts: *he has a subprocedure in his code,
    > > and no way of calling it. *Look again.

    >
    > > [...]- Hide quoted text -

    >
    > - Show quoted text -


    And indeed, you have given me some thought. All of you have been
    helpful and I now have my little guy working. As I have stated, this
    a trivial example...

    Now, Mr Pence ------

    so I type all your code into a source member and create it how?

    then you are saying in some sundry RPG pgm I can do

    eval input = rtvctl#(wo#)
    as if it were a procedure of any sort?

    I don't care how I get there, but I do want to have more expressive
    ability available to me.

    Unfortunately, after many years in the RPG world, I had to do COBOL,
    and the way the interfaces were done and written were clunky and quite
    a bit different than this, so now I am catching up.

    Ron

  17. Re: What is wrong?

    The SQL source could be placed in an SQLRPG and performed, or more
    typically entered in an interactive script processor like STRSQL or the
    iNav Run SQL utility, or in a batch capable script processor like
    RUNSQLSTM which does accept as input a source member.

    I was thinking more about using SQL so I did not consider the RPG
    aspect as much; probably why Jonathon pointed me back to ILE RPG, since
    although the procedure might be SQL, the main may not. I am not sure
    about invoking the function except by SQL. Thus instead of EVAL, if in
    an SQL program, using EXEC SQL for SET or VALUES INTO syntax:
    EXEC SQL SET :input :ind = RtvCtl(WOnbr)
    EXEC SQL VALUES (RtvCtl(WOnbr) INTO :input :ind;

    http://publib.boulder.ibm.com/infoce...mstsetinto.htm
    http://publib.boulder.ibm.com/infoce...mstvalinto.htm
    http://publib.boulder.ibm.com/infoce...tch2refvar.htm

    P.S. I avoid '#' references. Variant characters should be avoided
    whenever possible. Especially for SQL:
    http://publib.boulder.ibm.com/infoce...mstch2iden.htm

    Regards, Chuck
    --
    All comments provided "as is" with no warranties of any kind
    whatsoever and may not represent positions, strategies, nor views of my
    employer

    qgenesist@hotmail.com wrote:
    >> <>

    >
    > <>
    >
    > so I type all your code into a source member and create it how?
    >
    > then you are saying in some sundry RPG pgm I can do
    > eval input = rtvctl#(wo#)
    > as if it were a procedure of any sort?
    >
    > I don't care how I get there, but I do want to have more expressive
    > ability available to me.
    >
    > Unfortunately, after many years in the RPG world, I had to do COBOL,
    > and the way the interfaces were done and written were clunky and quite
    > a bit different than this, so now I am catching up.


  18. Re: What is wrong?

    qgenesist@hotmail.com wrote:
    > On Jan 24, 3:33 pm, CRPence wrote:
    >> Understood. However the quoted snippet of my most recent response,
    >> snipped too much of that response; look again ;-) The quoted snippet to
    >> which I responded, should have made it clear that I was responding first
    >> to the comment that the "crtrpgmod does not understand sql." And second
    >> about how to setup to call the procedure to test and debug -- using SQL.
    >>
    >> Anyhow, although obviously CRTRPGMOD is not going to like /EXEC SQL,
    >> if the OP did not have a problem with CRTRPGMOD, then one has to wonder
    >> why the quoted comment was even written? Perhaps my post was for
    >> naught, with regard to progressing toward resolution of the issue, but
    >> it did address the quoted comment with respect to the given source.
    >>
    >> Besides, my not-so-ulterior motive was to get the OP to consider that
    >> there was possibly little reason to make an effectively pure SQL
    >> procedure from RPG when it could be done simply instead, as a purely SQL
    >> routine; as coded, an SQL function. :-)
    >>
    >> Regards, Chuck
    >> --
    >> All comments provided "as is" with no warranties of any kind
    >> whatsoever and may not represent positions, strategies, nor views of my
    >> employer
    >>
    >>
    >>
    >> Jonathan Ball wrote:
    >>> On Jan 24, 12:43 pm, CRPence wrote:
    >>>> The /EXEC SQL and /END-EXEC are not RPG, they are tags to denote what
    >>>> a pre-compiler is supposed to convert into RPG. They are directives to
    >>>> the SQL precompiler, that the upcoming and prior source is to be
    >>>> interpreted as embedded SQL source, rather than as RPG source. Use
    >>>> CRTSQLRPGI OBJTYPE(*MODULE) to create an SQLRPG module.
    >>> The original poster isn't having trouble with the embedded SQL. His
    >>> problem is ILE and RPGLE concepts: he has a subprocedure in his code,
    >>> and no way of calling it. Look again.
    >>> [...]- Hide quoted text -

    >> - Show quoted text -

    >
    > And indeed, you have given me some thought. All of you have been
    > helpful and I now have my little guy working. As I have stated, this
    > a trivial example...
    >
    > Now, Mr Pence ------
    >
    > so I type all your code into a source member and create it how?
    >
    > then you are saying in some sundry RPG pgm I can do
    >
    > eval input = rtvctl#(wo#)
    > as if it were a procedure of any sort?
    >
    > I don't care how I get there, but I do want to have more expressive
    > ability available to me.
    >
    > Unfortunately, after many years in the RPG world, I had to do COBOL,
    > and the way the interfaces were done and written were clunky and quite
    > a bit different than this, so now I am catching up.


    I guess it's hard to know how to suggest you proceed
    without knowing more about what it is you're trying to
    achieve. If you have just a one-off requirement to get
    the controlling work order number for some
    (subordinate?) work order, then I wouldn't bother with
    a separate program or service program at all; just put
    the code in the one program that needs to do this.

    But if you're trying to develop a routine that could be
    used at multiple points in your overall application to
    bring back the controlling work order number, then
    there are a couple of avenues open. One is to put the
    SQL statement - a simple SELECT INTO..., without the
    cursor fol-de-rol - into a module that exports an ILE
    procedure. You then could bind this module with your
    other programs, or perhaps put it in a service program
    that is bound with the other programs, and invoke the
    procedure as you indicate above:

    eval input = rtvctl#(wo#)

    Note that this is an ILE procedure, /not/ a SQL
    procedure (or function).

    Another would be to create an SQL function, either
    using the SQLP language or SQLRPGLE. Then, in your
    application programs (assumed to be SQLRPGLE), you
    could code

    c/exec sql
    c+ set :input :ni = mylib.sqlrtvctl#(:wo#)
    c/end-exec

    Notes:
    1. This is an SQL function, not an RPGLE procedure
    2. The host variable :ni is a null indicator; you
    don't always need them, but it's a good idea,
    as it prevents costly error messages from being
    thrown.

+ Reply to Thread