|
midrange.com
Huh? Embedded SQL requires the SQL pre-processor which is part of
the SQL licensed program. You CAN do SQLCLI without the SQL
licensed product, but the APIs are rather hard to get the hang of.
Examples of SQLCLI are in the "Sorcers Guide" redbook.
eric.delong@pmsi-services.com
______________________________ Reply Separator _________________________________
Subject: RE: [Sort records in subfile]
Author: <RPG400-L@midrange.com> at INET_WACO
Date: 6/29/00 9:15 AM
Of course, you don't need SQL installed if you are just embedding SQL in RPG
since this is supported as a native feature in OS/400.
Peter
-----Original Message-----
From: Timura, Patrik (CAP, GCF) [mailto:Patrik.Timura@gecapital.com]
Sent: Thursday, June 29, 2000 4:29 AM
To: 'RPG400-L@midrange.com'
Subject: RE: [Sort records in subfile]
AAARGH we don't have SQL dev kit :-(((( , but many thanks for your tip, it
looks out to be very interesting.
-----Original Message-----
From: rjslaney@us.ibm.com [mailto:rjslaney@us.ibm.com]
Sent: Wednesday, June 28, 2000 5:22 PM
To: RPG400-L@midrange.com
Subject: RE: [Sort records in subfile]
Patrik, in order to do as Brian suggests you would need the SQL Development
Kit (5769-ST1) installed on your system. Basically, SQL would take the place
of a loop that reads your data records. As part of the SELECT statement,
you would code an ORDER BY clause which would place all the records you want
in a "result set" in the order you desire.
Then you would load the subfile as you would normally. To get the
individual records (or rows) from the result set you would use an SQL FETCH
statement followed by an RPG WRITE to the subfile.
Here is a sample program (originally form Brian Parkins) that is used in
the IBM Learning Services cours that teaches how to use Embedded SQL. The
fact that you can create a sorted set of records with a single line of code
is only one of the many powers of SQL.
** Compile options:
** DATFMT(*ISO) DATSEP(-) COMMIT(*NONE) CLOSQLCSR(*ENDMOD)
FEmbed04D CF E Workstn Sfile(Data:Rrn)
D Indicators DS
Based(IndPtr)
D Exit 1N
Overlay(Indicators:03)
D SflEnd 1N
Overlay(Indicators:40)
D SflDspCtl 1N
Overlay(Indicators:85)
D SflDsp 1N
Overlay(Indicators:95)
D Rrn S 5P 0
Inz(1)
D IndPtr S *
D ErrorMsg S 50A Inz('An
error has occurred +
D
- SQLCODE = ') Varying
C Eval IndPtr =
%Addr(*In)
C Write FncKey
C Eval SflEnd = *ON
** Monitor for Errors
C/Exec Sql
C+ WHENEVER SQLERROR GOTO ERRORLBL
C/End-Exec
** Declare Cursor
C/Exec Sql
C+ DECLARE APINVCSR CURSOR FOR
C+ SELECT PONBR, VNDNBR, APINVNBR, POTOTAMT, APNETPAID,
C+ APDUEDATE, APDATEPAID
C+ FROM APINV_PF
C+ ORDER BY PONBR
C/End-Exec
** Open Cursor
C/Exec Sql
C+ OPEN APINVCSR
C/End-Exec
** Fetch first Row
C/Exec Sql
C+ FETCH NEXT FROM APINVCSR
C+ INTO :PONBR, :VNDNBR, :APINVNBR, :POTOTAMT, :APNETPAID,
C+ :APDUEDATE, :APDATEPAID
C/End-Exec
** Load entire Subfile
C Dow SqlCod <> 100
C Write Data
** Fetch next Row
C/Exec Sql
C+ FETCH NEXT FROM APINVCSR
C+ INTO :PONBR, :VNDNBR, :APINVNBR, :POTOTAMT, :APNETPAID,
C+ :APDUEDATE, :APDATEPAID
C/End-Exec
**
C Eval Rrn = Rrn + 1
C Enddo
** Display subfile if file not empty
C Select
C When Rrn > 1
C Eval SflDspCtl = *ON
C Eval SflDsp = *ON
C Dow NOT Exit
C Exfmt Control
C Enddo
C When Rrn <= 1
C Eval SflDspCtl = *ON
C Eval SflDsp = *OFF
C Write Msg
C Exfmt Control
C Endsl
** Close Cursor
C/Exec Sql
C+ CLOSE APINVCSR
C/End-Exec
C Eval *InLR = *ON
C Return
C ErrorLbl Tag
C Eval ErrorMsg =
ErrorMsg +
C
TrimL(%EditC(SqlCod:'1'))
C ErrorMsg Dsply '*REQUESTER'
** Close Cursor
C/Exec Sql
C+ CLOSE APINVCSR
C/End-Exec
C Eval *InLR = *ON
C Return
Bob
Bob Slaney/Atlanta/IBM @ IBMUS (Lotus Notes)
VM id: WTSCPOK.RJSLANEY@VM
Internet id: rjslaney@us.ibm.com
Tieline 596-3022 Direct (770)835-3022
Fax tie 596-3912 Direct (770)835-3912
IBM Learning Services
AS/400 Course Materials Development
IBM Corp.
3100 Windy Hill Road
Atlanta, GA 30339
Internal Zip WE3A1
+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator:
david@midrange.com
+---
+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator:
david@midrange.com
+---
********************************************************************************
************************
Privileged / Confidential Information may be contained in this message. If you
arenot the addressee indicated in this message (or responsible for delivery of
the message to such person), you may not copy or deliver this message to
anyone. In suchcase, you should destroy this message, and please notify us
immediately.
Please advise immediately if you or your employer does not consent to Internet e
ail for messages of this kind. Opinions and other information expressed in this
ssage are not given or endorsed by my firm or employer unless otherwise indicate
by an authorised representative independent of this message.
+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---
+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
