× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@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 thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.