|
These are just fields defined in the RPG. host variables (fields) are preceeded by a colon in your sql statements.. Thanks, Tommy Holden -----Original Message----- From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of RPower@xxxxxxxxxx Sent: Thursday, May 12, 2005 9:45 AM To: RPG programming on the AS400 / iSeries Subject: 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-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.
midrange.com
