Re: SQLRPG question

[Pete Hall <pbhall@xxxxxxxxxx>]

At 13:00 06/20/2001, Loyd Goodbar wrote:
>I'm trying to create a dynamic SQL statement for an intranet based lookup
>application. The actual select portion is fixed, but the where and order by
>portions are dynamic.

The SQL reference has an example. It's Cobol, but should get you going:

MOVE "INSERT INTO EMP_ACT (EMPNO, PROJNO, ACTNO, EMPTIME)
- "VALUES (?, ?, ?, ?)" TO HOLDER.
EXEC SQL PREPARE MYINSERT FROM :HOLDER END-EXEC.
IF SQLCODE = 0
PERFORM DO-INSERT THRU END-DO-INSERT
ELSE
PERFORM ERROR-CONDITION.
DO-INSERT.
MOVE "000010" TO EMP.
MOVE "AD3100" TO PRJ.
MOVE 160 TO ACT.
MOVE .50 TO TIM.
EXEC SQL EXECUTE MYINSERT USING :EMP, :PRJ, :ACT, :TIM END-EXEC.
END-DO-INSERT.
.

If you need to retrieve multiple rows, you need to use a cursor. Here's an 
RPG example that uses a dynamic cursor. The RDBCNNMTH=*RUW, CONNECT and 
DISCONNECT force the cursor to be destroyed so it can be recreated:

C/EXEC SQL SET OPTION RDBCNNMTH=*RUW
C/END-EXEC

C/EXEC SQL DECLARE ARCERT CURSOR FOR CERTCSR
C/END-EXEC

C                   eval      Query =
C                                'SELECT'
C                              + ' <column list here >'
C                              + ' FROM'
C                              +   ' ARCERT'
C                              +   ' JOIN ARRM00 ON RMRES = ARRES'
C                              +              ' AND RMFAC = ARFAC'
C                   if        S2PastDue
C                   eval      Query = Query
C                              + ' WHERE ARCLOD = 0'
C                              + ' AND ARDUE <= ?'
C                   endif

C                   if        S2Closed
C                   eval      Query = Query
C                              + ' WHERE ARCLOD > 0'
C                              + ' AND ARDUE >= ?'
C                              + ' AND ARDUE <= ?'
C                   endif

C                   if        S1DaysLate
C                   eval      Query = Query
C                              + ' ORDER BY ARDUE'
C                   endif

C                   if        S1Resident
C                   eval      Query = Query
C                              + ' ORDER BY ARRES'
C                   endif

C/EXEC SQL CONNECT RESET
C/END-EXEC

C/EXEC SQL PREPARE CERTCSR FROM :Query
C/END-EXEC

C                   if        S2PastDue
C/EXEC SQL
C+ OPEN ARCERT USING :CurCCYMD, :FromLoc,
C+                   :ToLoc, :FromRes, :ToRes
C/END-EXEC
C                   else
C/EXEC SQL
C+ OPEN ARCERT USING :FromDate, :ToDate,
C+                   :FromLoc, :ToLoc, :FromRes, :ToRes
C/END-EXEC
C                   endif

  * Processing loop here
C                   dou       whatever
C/EXEC SQL
C+ FETCH ARCERT INTO :CertRcd
C/END-EXEC

  * Process the data...

C                   enddo

C/EXEC SQL CLOSE ARCERT
C/END-EXEC

C/EXEC SQL DISCONNECT ALL
C/END-EXEC

Pete Hall
pbhall@execpc.com
http://www.execpc.com/~pbhall/

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---