SQL Help - IBM AS400

This is a discussion on SQL Help - IBM AS400 ; I am just starting to try a little SQL and am using Kevin Forsythe's book on SQL for eServer i5 and iSeries as a reference. I wrote a simple little program to see if I could embed some SQL in ...

+ Reply to Thread
Results 1 to 4 of 4

Thread: SQL Help

  1. SQL Help

    I am just starting to try a little SQL and am using Kevin Forsythe's
    book on SQL for eServer i5 and iSeries as a reference.

    I wrote a simple little program to see if I could embed some SQL in an
    RPG program and it worked fine. It said to use an external
    datastructure to define the fields instead of listing each field
    separately in the "into" portion of the SQL statement, which makes
    sense but when I do this it doesn't work, I know it is something
    stupid but I am frying my brain looking for it.

    D MySQL S 512
    D Cust S 6 0
    D PASSWORD S 10
    *
    D DGWEBUSRDS E DS EXTNAME(DGWEBUSRDS)
    *
    C z-add 100 Cust
    *
    C/EXEC SQL
    C+ SELECT * INTO GWEBUSRDS FROM DGRFILES#/DGWEBUSR WHERE DGUSID =
    C+ :Cust
    C/END-EXEC
    *
    C MOVE XXDINV WORK1 1
    C MOVE XXDORD WORK2 1
    C MOVE XXEORD WORK3 1
    C IF SQLCOD < *ZERO
    C DUMP(A)
    C END
    *
    C seton lr

    I get a negative code in SQLSTT which says no data, but I know it is
    there and it works when I drop the external data structure and just
    specify all the fields for the 'INTO' portion.

    Any help would be great. I put it in debug and set a breakpoint at
    LR, to look at the Work1,2,3 variables to know it isn't working.

  2. Re: SQL Help

    il 21/11/2007 18.05, Scrive kbarkema01@yahoo.com 40640584:
    > I am just starting to try a little SQL and am using Kevin Forsythe's
    > book on SQL for eServer i5 and iSeries as a reference.
    >
    > I wrote a simple little program to see if I could embed some SQL in an
    > RPG program and it worked fine. It said to use an external
    > datastructure to define the fields instead of listing each field
    > separately in the "into" portion of the SQL statement, which makes
    > sense but when I do this it doesn't work, I know it is something
    > stupid but I am frying my brain looking for it.
    >
    > D MySQL S 512
    > D Cust S 6 0
    > D PASSWORD S 10
    > *
    > D DGWEBUSRDS E DS EXTNAME(DGWEBUSRDS)
    > *
    > C z-add 100 Cust
    > *
    > C/EXEC SQL
    > C+ SELECT * INTO GWEBUSRDS FROM DGRFILES#/DGWEBUSR WHERE DGUSID =
    > C+ :Cust
    > C/END-EXEC
    > *
    > C MOVE XXDINV WORK1 1
    > C MOVE XXDORD WORK2 1
    > C MOVE XXEORD WORK3 1
    > C IF SQLCOD < *ZERO
    > C DUMP(A)
    > C END
    > *
    > C seton lr
    >
    > I get a negative code in SQLSTT which says no data, but I know it is
    > there and it works when I drop the external data structure and just
    > specify all the fields for the 'INTO' portion.
    >
    > Any help would be great. I put it in debug and set a breakpoint at
    > LR, to look at the Work1,2,3 variables to know it isn't working.

    Maybe there's something obvious that doesn't appear so.
    For example:
    Is DGWEBUSRDS the same as DGRFILES#/DGWEBUSR?
    If so, why didn't you use DGWEBUSR instead

    btw, you can omit EXTNAME if it's the same as the ds name, in your case,
    the following should be enough and work:

    D DGWEBUSR E DS
    ....
    C/EXEC SQL
    C+ SELECT * INTO GWEBUSR FROM DGRFILES#/DGWEBUSR
    C+ WHERE DGUSID = :Cust
    C/END-EXEC


    --
    Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherAñe joAlcoolInside
    Spaccamaroni andate a cagare/Spammers not welcome/Spammers vão à merda
    Spamers iros a la mierda/Spamers allez vous faire foutre/Spammers loop
    schijten/Spammers macht Euch vom Acker/Spamerzy wypierdalac'

  3. Re: SQL Help

    Please be more specific. What is the negative SQLCODE you get?
    Is it -811? If so more than one record will be returned.

    Check your data if there are duplicates.
    If there are duplicates and you only want the first record, just add
    FETCH FIRST ROW ONLY to your SQL-Statement

    C+ SELECT * INTO GWEBUSRDS
    C+ FROM DGRFILES#/DGWEBUSR
    C+ WHERE DGUSID = :Cust
    C+ Fetch First Row only
    C/END-EXEC

    Birgitta


  4. Re: SQL Help

    On Nov 21, 1:27 pm, "Hau...@sss-software.de"
    wrote:
    > Please be more specific. What is the negative SQLCODE you get?
    > Is it -811? If so more than one record will be returned.
    >
    > Check your data if there are duplicates.
    > If there are duplicates and you only want the first record, just add
    > FETCH FIRST ROW ONLY to your SQL-Statement
    >
    > C+ SELECT * INTO GWEBUSRDS
    > C+ FROM DGRFILES#/DGWEBUSR
    > C+ WHERE DGUSID = :Cust
    > C+ Fetch First Row only
    > C/END-EXEC
    >
    > Birgitta


    The code was -2, like I said it worked fine before I did the external
    data structure.

+ Reply to Thread