|
midrange.com
The host-vars (:custno, :custadr .....) must be defined.
d customer_ds e ds extname(customer)
or
by an F-statement for file CUSTOMER.
If you want to retrieve every field from CUSTOMER change the select to:
c SqlBuildSelec begsr
c eval SqlSelect =
c 'select '
c + ' *'
c + ' from'
c + ' customer'
c endsr
and the read to:
c SqlRead begsr
c/exec sql
c+ fetch SqlCur into into :customer_ds
c/end-exec
Brgds
Helge Bichel
Copenhagen
Denmark
hbi@xxxxxxx
-----Oprindelig meddelelse-----
Fra: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Pa vegne af RPower@xxxxxxxxxx
Sendt: 12. maj 2005 16:45
Til: RPG programming on the AS400 / iSeries
Emne: Re: SV: Embedded SQL
Thank you very much. Just one question, what do we fetch the records
into? I see you have
*****************************************************************
* Read the "next" record from the SQL result *
*****************************************************************
c SqlRead begsr
c/exec sql
c+ fetch SqlCur into :custno
c+ , :custname
c+ , :custadr1
c+ , :custadr2
c+ , :custdatstr
c+ , :custdatend
c/end-exec
c endsr
where are custno, custname, etc defined?
Ron Power
Programmer
Information Services
City Of St. John's, NL
P.O. Box 908
St. John's, NL
A1C 5M2
Tel: 709-576-8132
Email: rpower@xxxxxxxxxx
Website: http://www.stjohns.ca/
___________________________________________________________________________
Success is going from failure to failure without a loss of enthusiasm. -
Sir Winston Churchill
"Helge Bichel" <hbi@xxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
12/05/2005 11:38 AM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
To
"RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
cc
Subject
SV: Embedded SQL
I give You an example right here, the prgram is not complete but
snippets taken from a real one.
Brgds
Helge Bichel
Copenhagen
Denmark
hbi@xxxxxxx
*****************************************************************
* Sql vars
*****************************************************************
d SqlStmt s 2000A varying
d SqlSelect s 2000A varying
d SqlWhere s 2000A varying
d SqlOrder s 2000A varying
d SqlOk c '00000'
d SqlNoMoreRows...
d c '02000'
*----------------------------------------------------------+
* Construct Sql where condition
*----------------------------------------------------------+
* Build a WHERE Entry for alfa field
d BuildSqlWhereAlf...
d pr 256 varying
d pField 128 value
d pValue 64 value
d pOperator 2 value options(*nopass)
* Build a WHERE Entry for num field
d BuildSqlWhereNum...
d pr 256 varying
d pField 128 value
d pValue 64 value
d pOperator 2 value options(*nopass)
* Add a Sql Where Entry to Sql Where statment
d BuildSqlWhereEntry...
d pr 3000 varying
d pSqlWhere 3000 value varying
d pSqlWhereEntry...
d 256 value varying
d pOperator 3 value options(*nopass)
* Add a Sql Order Entry to Sql Order statment
d BuildSqlOrderEntry...
d pr 3000 varying
d pSqlOrder 3000 value varying
d pSqlOrderEntry...
d 256 value varying
c exsr SqlClear
c exsr SqlBuild
c exsr SqlRun
c dou wCounter = 12
c exsr SqlRead
c if SqlStt <> SqlOk
C leave
c endif
c enddo
*****************************************************************
* Build Sql
*****************************************************************
c Sq1Build begsr
c exsr SqlBuildSelec
c exsr SqlBuildWhere
c exsr SqlBuildOrder
c eval SqlStmt = SqlStmt + SqlSelect
c if SqlWhere <> *blanks
c eval SqlStmt = SqlStmt +
c ' where ' + SqlWhere
c endif
c if SqlOrder <> *blanks
c eval SqlStmt = SqlStmt +
c ' order by ' + SqlOrder
c endif
c endsr
*****************************************************************
* Build Sql Select
*****************************************************************
c SqlBuildSelec begsr
c eval SqlSelect =
c 'select'
c + ' custno'
c + ', custname'
c + ', custadr1'
c + ', custadr2'
c + ', custdatstr'
c + ', custdatend'
c + ' from customer '
c endsr
*****************************************************************
* Build Sql Where
*****************************************************************
c SqlBuildWhere begsr
c clear SqlWhere
c eval SqlWhere = BuildSqlWhereEntry(
c SqlWhere:'custadr1 = rgtask')
* selection custno
c if s_custno <> 0
c eval SqlWhere =
c BuildSqlWhereEntry(
c SqlWhere:
c BuildSqlWhereNum
c ('custno':%char(s_custno))
c )
c endif
* selection custadr1
c if s_custadr1 <> *blanks
c eval SqlWhere =
c BuildSqlWhereEntry(
c SqlWhere:
c BuildSqlWhereAlf('custadr1':s_custadr1)
c )
c endif
* selection start date
c if s_start_date <> 0
c eval SqlWhere =
c BuildSqlWhereEntry(
c SqlWhere:
c BuildSqlWhereNum
c ('custdatstr':%char(s_start_date):'>=')
c )
c endif
* selection end date
c if s_end_date <> 0
c eval SqlWhere =
c BuildSqlWhereEntry(
c SqlWhere:
c BuildSqlWhereNum
c ('custdatend':%char(s_end_date):'<=')
c )
c endif
c
c endsr
*****************************************************************
* Build Sql Order
*****************************************************************
c SqlBuildOrder begsr
c clear SqlOrder
c if s_custno <> *zero
c eval SqlOrder =
c BuildSqlOrderEntry(
c SqlOrder:'custno')
c endif
c if SqlOrder = *blanks
c eval SqlOrder =
c BuildSqlOrderEntry(
c SqlOrder:'custname')
c endif
c endsr
*****************************************************************
* RUN the SQL, STD SQL in RPG statements
*****************************************************************
c SqlRun begsr
c/exec sql
c+ prepare Sql from :SqlStmt
c/end-exec
c/exec sql
c+ declare SqlCur cursor for Sql
c/end-exec
c/exec sql
c+ open SqlCur
c/end-exec
c endsr
*****************************************************************
* clear SqlCur
*****************************************************************
c SqlClear begsr
c/exec sql
c+ close SqlCur
c/end-exec
c endsr
*****************************************************************
* Read the "next" record from the SQL result *
*****************************************************************
c SqlRead begsr
c/exec sql
c+ fetch SqlCur into :custno
c+ , :custname
c+ , :custadr1
c+ , :custadr2
c+ , :custdatstr
c+ , :custdatend
c/end-exec
c endsr
*****************************************************************
*-----------------------------------------------------+
* Build a WHERE Entry for alfa field
*-----------------------------------------------------+
p BuildSqlWhereAlf...
p b
d pi 256 varying
d pField 128 value
d pValue 64 value
d pOperator 2 value options(*nopass)
d wOperator s 2
d wOperatorDefault...
d c const('=')
d wWild c const('*')
d wWildSql c const('%')
d q c const('''')
d wSqlWhere s 256 varying
d wSqlSearch s 64 varying
c clear wSqlWhere
c if %scan(wWild:pValue:1) > 0
* V5.R1*
c***** eval wsqlSearch =
c***** %xlate(wWild:wWildSql:pValue:1)
* V5.R1*
* V4.R5
c eval wsqlsearch = pvalue
c wWild:wWildsqlxlate wsqlsearch:1 wsqlsearch
* V4.R5
c eval wSqlWhere =
c ' ' + %trim(pField) + ' like '
c + q +%trim(wsqlSearch) + q
c else
c if %parms > 2
c eval wOperator = pOperator
c else
c eval wOperator = wOperatorDefault
c endif
c eval wSqlWhere =
c ' ' +%trim(pField)
c + ' ' + wOperator + ' '
c + q +%trim(pValue) + q
c endif
c return wSqlWhere
p e
*-----------------------------------------------------+
* Build a WHERE Entry for num field
*-----------------------------------------------------+
p BuildSqlWhereNum...
p b
d pi 256 varying
d pField 128 value
d pValue 64 value
d pOperator 2 value options(*nopass)
d wOperator s 2
d wOperatorDefault...
d c const('=')
d q c const('''')
d wSqlWhere s 256 varying
c clear wSqlWhere
c if %parms > 2
c eval wOperator = pOperator
c else
c eval wOperator = wOperatorDefault
c endif
c eval wSqlWhere =
c ' ' +%trim(pField)
c + ' ' + wOperator + ' '
c + %trim(pValue)
c return wSqlWhere
p e
/eject
*-----------------------------------------------------+
* Add a Sql Where Entry to Sql Where statment
*-----------------------------------------------------+
p BuildSqlWhereEntry...
p b
d pi 3000 varying
d pSqlWhere 3000 value varying
d pSqlWhereEntry...
d 256 value varying
d pOperator 3 value options(*nopass)
d wSqlWhere s 3000 varying
d wOperator s 3
d wOperatorDefault...
d c const('AND')
c if %parms > 2
c eval wOperator = pOperator
c else
c eval wOperator = wOperatorDefault
c endif
c eval wSqlWhere = pSqlWhere
c if wSqlWhere <> *blanks
c eval wsqlWhere =
c wSqlWhere + ' ' + wOperator + ' '
c endif
c eval wSqlWhere =
c wSqlWhere + pSqlWhereEntry
c return wSqlWhere
p e
*-----------------------------------------------------+
* Add a Sql Order Entry to Sql Order statment
*-----------------------------------------------------+
p BuildSqlOrderEntry...
p b
d pi 3000 varying
d pSqlOrder 3000 value varying
d pSqlOrderEntry...
d 256 value varying
d wSqlOrder s 3000 varying
c eval wSqlOrder = pSqlOrder
c if wSqlOrder <> *blanks
c eval wsqlOrder =
c wSqlOrder + ',' + ' '
c endif
c eval wSqlOrder =
c wSqlOrder + pSqlOrderEntry
c return wSqlOrder
p e
-----Oprindelig meddelelse-----
Fra: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Pa vegne af RPower@xxxxxxxxxx
Sendt: 12. maj 2005 14:08
Til: RPG programming on the AS400 / iSeries
Emne: Embedded SQL
Guys,
fellow developer here is learning SQL.
Whilst the book he is using is technically and shows us all the nice
little things, it fails to show the entire picture. Would someone out
there be willing to share a program using SQL so that we might look at a
real example? TIA,
Ron Power
Programmer
Information Services
City Of St. John's, NL
P.O. Box 908
St. John's, NL
A1C 5M2
Tel: 709-576-8132
Email: rpower@xxxxxxxxxx
Website: http://www.stjohns.ca/
___________________________________________________________________________
Success is going from failure to failure without a loss of enthusiasm. -
Sir Winston Churchill
--
This is the RPG programming on the AS400 / iSeries (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.
--
This is the RPG programming on the AS400 / iSeries (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.
--
This is the RPG programming on the AS400 / iSeries (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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2025 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.
