Re: Embedded SQL with DSPF problem.

Jake,

1) I would remove the ITEM_PF from the F-specs. You do not use native I/O ,
so using "%found(ITEM_PF)" is not working (that line can be removed as
well).
2) Externally descibe the myDS datasturcture.
3) I would test the Function keys before doing the SQL. F12 has not been
defined in DDS.

Perhaps your OS-release do not allow you to FETCH INTO an DS.

Regards,
Carel Teijgeler.

*********** REPLY SEPARATOR  ***********

On 21-9-2006 at 15:38 Jake M wrote:

Once again I am running into some newbie problems. This program is

supposed to kick out a simple report based on the item number given by the
user in

the display file. The SQLRPGLE program compiles but it bombs out when I

try and run it. I have a display file and a SQLRPGLE program which calls
the

display file. I think I am doing something wrong in the coding part

itself. Should I be using an extra CL program with this? I do not know if
this kind

of report generation could be done with only one piece of sqlrpgle code

and a dspf file. If anybody could point me in the right direction, I would

really appreciate it.

ITEMINQSQL.dspf
***************************************************************************

***********************************************

    A                                      DSPSIZ(24 80 *DS3)
    A                                      REF(*LIBL/ITEM_PF)
    A                                      CA03(03)
    A                                      INDARA
    A          R PROMPT
    A            PGMNAM        10A  O  3  7
    A                                  3 35'Item Inquiry'
    A                                      COLOR(WHT)
    A                                  3 64DATE
    A                                      EDTCDE(Y)
    A                                  8 20'Item Number . . . . . .:'
    A            ITMNBR    R     D  I  8 45
    A  40                                  ERRMSG('Item not found on file

pl-

    A                                      ease correct' 40)
    A                                 20  7'Press Enter to continue'
    A                                 21  7'F3=Exit'
    A                                      COLOR(BLU)
***************************************************************************

***************************************************

ITEMINQSQL.sqlrpgle
***************************************************************************

***************************************************

     // Declare Files
remove    FITEM_PF   IF   E           K DISK    Rename(ITEM_PF:RITEMPF)
    FITEMINQSQLCF   E             WORKSTN IndDS(WkstnInd)
    Fqsysprt   o    f  132        printer

    D itemchoice      PR                  EXTPGM('ITEMINQSQL')
    D  itemnum                      20

    D itemchoice      PI
    D  itemnum                      20

    D WkstnInd        DS
    D NotFound               40     40N
    D Exit                   03     03N
    D Cancel                 12     12N

change     D myDS            ds   E               EXTNAME(ITEM_PF)
     /FREE
      PgmNam = 'ITEMINQSQL';
      Exfmt Prompt;
      Dow NOT Exit;
     /END-FREE

    C/EXEC SQL
    C+ Set Option
    C+     Naming    = *Sys,
    C+     Commit    = *None,
    C+     UsrPrf    = *User,
    C+     DynUsrPrf = *User,
    C+     Datfmt    = *iso,
    C+     CloSqlCsr = *EndMod,
    C+     Commit    = *NONE
    C/END-EXEC
    c/exec sql
    c+ declare mainCursor Cursor
    c+   for
    c+ select * from testing/item_pf where itmnbr=:itemnum
    c/end-exec
    c/exec sql
    c+ open mainCursor
    c/end-exec
    c/exec sql
    c+ fetch next
    c+  from mainCursor
    c+  into :myDS
    c/end-exec
     /FREE
remove         If %found(Item_PF); // Item Number valid?
          NotFound = *off;
        Dow NOT Cancel;  // Display details
        except heading;
        dow SQLSTT = '00000';
        except detail;
     /END-FREE
    c/exec sql
    c+ fetch next
    c+  from mainCursor
    c+  into :myDS
    c/end-exec
     /FREE
      enddo;
            If Exit;
              *InLR = *ON;
              Return;
            Endif;
          Enddo;

remove        Endif;
        // No Item record found or F12 pressed - display prompt
        Cancel = *Off;
        NotFound = *on;
        Exfmt Prompt;
      Enddo;
      *InLR = *ON;
     /END-FREE
    Oqsysprt   e            heading     1
    O                                        +   1 'ITEM NUMBER'
    O                                        +  20 'ITEM DESCRIPTION'
    Oqsysprt   e            detail      3
    O                       ITMNBR           +   1
    O                       ITMDESCR         +  20
***************************************************************************

*************************************************************