Re: Simple SQL in RPG question.

[Rich Duzenbury <rduz-midrange@xxxxxxxxxxxxxxxxxxx>]

On Wed, 2006-03-15 at 10:30 -0600, Mike Troxclaire wrote:
>      C/EXEC SQL
>      C+ DECLARE MYCUR CURSOR FOR SELECT CSTNUM, CSNAME,
>      C+ CSADD1 FROM LIBRARY1/CSTMST WHERE CSTNUM = '   XXXXXXX'
>      C/END-EXEC

Reconsider hard coding the library name in the SQL statement.  It will
be impossible to have a test or development version when the program
only runs against the live database.

Reconsider the use of character literals where you could have a
substitution variable.  Errors will occur in your version if someone
attempts to use 12'34 for the customer number.  In other cases, SQL
injection attacks become possible.

partial example:

     d WMCACCT       e DS                  EXTNAME(WMCACCT)
     d sql             S            500a   varying

      *
 date components are stored in three separate fields,
      * four digit year, two digit month, two digit day
      * select all records on or after a requested date
      /free
         sql = 'SELECT ' +
                  '* '   +
               'FROM ' +
                  'WMCACCT ' +
               'WHERE ' +
                  ' (WAMCY > ? OR ' +
                  '   (WAMCY = ? AND WAMMM > ?) OR ' +
                  '     (WAMCY = ? AND WAMMM = ? AND WAMDD >= ?))';

      /end-free

     C/exec sql
     C+  prepare acct_stmt from :acct_sql
     C/end-exec

     C/exec sql
     C+  declare acct_get cursor for acct_stmt
     C/end-exec

     C/exec sql
     C+  open acct_get using :cy, :cy, :mm, :cy, :mm, :dd
     C/end-exec

     C/exec sql
     C+  fetch acct_get into :WMCACCT
     C/end-exec
     C                   dow       SQLCOD = 0

         --- processing goes here ---

     C/exec sql
     C+  fetch acct_get into :WMCACCT
     C/end-exec
     C                   enddo

     C/exec sql
     C+  close acct_get
     C/end-exec

     C                   eval      *inlr = '1'