Re: Dynamic sql embedded

[Michael Daly <mdaly@xxxxxxxxxx>]

    Dquery            S           1024A
...
    C                   if        (company <> 0)
    C                   eval      query = 'SELECT ' +
    C                             'PENAME,PEADR1,PESS#,PEEMP# ' +
    C                             'FROM PRMSTP ' +
    C                             'WHERE (PENAME LIKE ''' + csgrpkwda +
    C                             ''') AND (PECO# = ''' + co#0 + 
    C                             ''') ORDER BY PENAME'
    C                   else
    C                   eval      query = 'SELECT ' +
    C                             'PENAME,PEADR1,PESS#,PEEMP# ' +
    C                             'FROM PRMSTP ' +
    C                             'WHERE (PENAME LIKE ''' + csgrpkwda +
    C                             ''') ORDER BY PENAME'
    C                   endif
    C/Exec Sql
    C+  Declare C1 Cursor For DynSqlStmt
    C/End-Exec
    C/Exec Sql
    C+  Prepare DynSqlStmt From :Query
    C/End-Exec
    C/Exec Sql
    C+  Open C1
    C/End-Exec
    C/Exec Sql
    C+  Fetch C1 into :InName, :InSub
    C/End-Exec
     *
    C                   Dow       SQLSTT < '02000'
...
    C/Exec Sql
    C+  Fetch C1 into :InName, :InSub
    C/End-Exec
    C                   ENDDO
    C/Exec Sql
    C+  Close C1
    C/End-Exec


Hello James,

> Hi everyone,
>
> I've been struggling with embedded SQL in RPG.  I need to create
> dynamic statements.  Here's what I'm trying to get to work:
>
> Dquery            S           1024A
> ...
> C                   if        (company <> 0)
> C                   eval      query = 'DECLARE C1 CURSOR FOR SELECT '
> +
> C                             'PENAME,PEADR1,PESS#,PEEMP# ' +
> C                             'FROM PRMSTP ' +
> C                             'WHERE (PENAME LIKE :csgrpkwda) AND ' +
> C                             '(PECO# = :co#0) ' +
> C                             'ORDER BY PENAME'
> C                   else
> C                   eval      query = 'DECLARE C1 CURSOR FOR SELECT '
> +
> C                             'PENAME,PEADR1,PESS#,PEEMP# ' +
> C                             'FROM PRMSTP ' +
> C                             'WHERE (PENAME LIKE :csgrpkwda) ' +
> C                             'ORDER BY PENAME'
> C                   endif
> C/exec sql
> C+ PREPARE DYNSQLSTMT FROM :query
> C+ EXECUTE DYNSQLSTMT
> C/end-exec
> C                   eval      query = 'OPEN C1'
> C/exec sql
> C+ PREPARE DYNSQLSTMT FROM :query
> C+ EXECUTE DYNSQLSTMT
> C/end-exec
> I can't even get this (or any other incantation I've tried) to
> compile. Does someone have some sample code of using a dynamic SQL
> statement to populate a subfile?
>
> James Rich
>
> It's not the software that's free; it's you.
> - billyskank on Groklaw