Re: Embedded SQL Problem SQLSTATE 02000

[Alan Campin <alan0307d@xxxxxxxxx>]

Just curious. Why dynamically prepare the SQL statement?

On Mon, Jan 4, 2010 at 3:13 PM, <DLee@xxxxxxxx> wrote:

> Hi
>
> I'm having a problem selectiong records from the file CLMPAY
> I build this sql statement that I call SQLQRY
>
> SELECT  CMCOCD, CMAGNT, CMPOLM, CMPOLS, CMCLMT, CMFILE, CMPC
> HK, CMPCHS, CMCOVC, CMPAYE, CMPAMT, CMPSTD FROM SWFILES/CLMP
> AY WHERE CMCOCD='?' ORDER BY CMPSTD, CMCOCD, CMAGNT, CMCOVC,
>  CMPOLM, CMFILE, CMPCHK
>
> Host variable NMCOCD looks like 'AN'  and used to select out a specific
> company.
> After SQLOPN is executed sqlstate is 00000
> After SQLGET is executed sqlstate  is 02000 and sqlcode is 100, and no
> data is retreived.
> I have a query program that shows there are many records with company code
> of 'AN'
>
> I Could use some help in understanding how to correct this so I actually
> find the records.
> The job is to gather some data from this file and output to another
> iseries file.
>
>  D CMDTL         E DS                           EXTNAME(CLMPAY)
>
>  C                   EXSR      SQLPRP
>  C                   EXSR      SQLOPN
>  C                   EXSR      SQLGET
>
>  C     SQLPRP        BEGSR
>  C/EXEC SQL
>  C+  PREPARE SQLSTM FROM :SQLQRY
>  C/END-EXEC
>  C                   ENDSR
>
> C     SQLOPN        BEGSR
> C/EXEC SQL
> C+  DECLARE CM CURSOR FOR SQLSTM
> C/END-EXEC
>  C/EXEC SQL
>  C+  OPEN CM USING :NMCOCD
>  C*  :NMPSTD
>  C/END-EXEC
>  C                   ENDSR
>
> C     SQLGET        BEGSR
> C/EXEC SQL
> C+  FETCH CM INTO :CMDTL
> C/END-EXEC
> C                   DOW       SQLSTT     =    *ZEROS
> C*  Do some process and get the next record.
> C                   EXSR      SQLGET
> C                   ENDDO
>
>
> Regards,
>
>
>
> SWBC
> 9311 San Pedro Ave., Suite 600
> San Antonio, TX 78216
>
>  Visit our website at www.swbc.com
>
> --
> This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.