Re: Odd SQL Module Issue - Cursor runs repeatedly

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Brian,

I wouldn't expect this to be a problem....

However you should not have any of your own variables named SQLxxx

Variables beginning with SQL are reserved for IBM's use.

Charles

On Wed, Apr 29, 2009 at 1:16 PM, Johnson, Brian (EU)
<Brian.Johnson@xxxxxxxxxxxxxxxxxxx> wrote:
> I have a Procedure that returns the first 50 result rows in a DS.  Once
> I started testing this, I noticed that performance is incredibly slow,
> where the actual SQL statement is quick to run.
>
> When I went into debug mode, I noticed that is was running the SQL Open,
> Select, and Fetch multiple times before returning the proper results.
> (I don't know how many times, I stopped counting.)  Just to be clear, I
> step into the RETURN statement, then instead of returning to the calling
> program, the next step is back up to the open SQL statement.  Then it
> runs again, and again, and again, then eventually after an unknown
> number of runs, it returns my results.
>
> I've even tried restructuring it to fetch one row at a time (up to 50
> times) then returning, but I get the same goofy looping.
>
> Any ideas what the heck is going on?
>
> Thanks,
> Brian
>
>
> CODE:
>
> H Nomain
>
>  * Include prototypes
>  /copy CSMMPROT
>
>  ****************************
>  * Procedure - GetBulkOrders
>  ****************************
>  * Get bulk order information for an customer/article
>
> P GetBulkOrders   b                   export
>
>  * Input Parameters
> D GetBulkOrders   pi          1650
> D  InArt                        18    const
> D  InSize                        5    const
> D  InCust                        7    const
> D  InType                        4    const
> D OutDS           DS                  occurs(50)
> D  OutOrdNum                     6S 0
> D  OutOrdTyp                     5
> D  OutOrdQty                     5S 0
> D  OutRsvQty                     5S 0
> D  OutPrcCd                      3
> D  OutDelWk                      7S 0
> D  OutWhse                       2S 0
>
> D OutInds         DS                  occurs(50)
> D  OutIn                         4b 0 dim(7)
>
> D ElemNum         S              2S 0
> D FldName         S             10
> D FldName2        S             10
>
> D sqlString       S           1000
> D sqlFirm         S              4
> D sqlArnr         S             11
> D sqlStof         S              5
> D sqlKlkd         S              5
>
>
>
>  * Clear output
>
> C                   clear                   OutDS
>
>
>
>  * Get Size Element Number and set Quantity Field Name (BH##51)
>
> C                   eval      ElemNum = GetSizeElem(InArt:InSize)
>
> C                   eval      FldName = 'BH' +
>
> C                                %subst('0' + %char(ElemNum) :
>
> C                                %len(%char(ElemNum))) + '51'
>
> C                   eval      FldName2 = 'RH' +
>
> C                                %subst('0' + %char(ElemNum) :
>
> C                                %len(%char(ElemNum))) + '51'
>
>
>
>  * Set SQL Statement String
>
> C                   eval      SqlString = *blanks
>
> C                   eval      SqlString =
>
> C                              'SELECT                                 '
> +
> C                              '  ORNR51, TPOR51, ' + FldName + ',     '
> +
> C                                 FldName2 + ', PSKD51, GWLT51, MGNR51 '
> +
> C                              'FROM COORDB inner join COORME on       '
> +
> C                              '     firm51=firmme and ornr51=ornrme   '
> +
> C                              '   inner join COORMB on                '
> +
> C                              '     firm51=firm50 and ornr51=ornr50   '
> +
> C                              'WHERE firm51 = ? and arnr51 = ?        '
> +
> C                              '     and stof51 = ? and klkd51 = ?     '
> +
> C                              '     and ' + FldName + ' <> 0          '
> +
> C                              '    and KLNR51 ||                      '
> +
> C                              '     substring(FILLME,6,4) in          '
> +
> C                              '     (SELECT DISTINCT STBULK ||        '
> +
> C                              '      substring(STBULT,1,4)            '
> +
> C                              '      FROM COIPSTR '
>
>
>
>  * Close WHERE CLAUSE
>
> C                   if        InCust <> *blanks or InType <> *blanks
>
> C                   eval      SqlString = %trim(SqlString) +
>
> C                              '      WHERE STBULK = ? and STBULT = ?) '
>
> C                   else
>
> C                   eval      SqlString = %trim(SqlString) +
>
> C                              '      )                                '
>
> C                   endif
>
>  * Set SQL ORDER BY
> C                   eval      SqlString = %trim(SqlString) +
> C                              ' ORDER BY DTPK51 ASC, ORDT50 ASC       '
>
>  * Prepare SQL Statement
> c/exec sql
> c+  prepare getRecords
> c+    from :sqlString
> c/end-exec
>
> c/exec sql
> c+  declare c1 cursor for getRecords
> c/end-exec
>
>  * Set SQL Parameters
> C                   eval      sqlFirm = getFirm()
> C                   eval      sqlArnr = %subst(InArt:1:8)
> C                   eval      sqlStof = %subst(InArt:9:5)
> C                   eval      sqlKlkd = %subst(InArt:14:5)
>
> C                   if        InCust <> *blanks or InType <> *blanks
> c/exec sql
> c+  open c1
> c+    using
> c+      :sqlFirm,
> c+      :sqlArnr,
> c+      :sqlStof,
> c+      :sqlKlkd,
> c+      :InCust,
> c+      :InType
> c/end-exec
>
> C                   else
>
> c/exec sql
> c+  open c1
> c+    using
> c+      :sqlFirm,
> c+      :sqlArnr,
> c+      :sqlStof,
> c+      :sqlKlkd
> c/end-exec
>
> C                   endif
>
> c/exec sql
> c+  fetch next from c1 for 50 rows into :OutDS :OutInds
> c/end-exec
>
> c/exec sql
> c+  close c1
> c/end-exec
>
> C                   return    OutDS
>
> P                 e
>
>
> This message and any attached documents contain information which may be confidential, legally protected or exempt from disclosure under applicable law. These materials are intended only for the use of the intended recipient. If you are not the intended recipient of this transmission you are hereby notified that any use, distribution, disclosure, printing, copying, storage, modification or the taking of any action in reliance upon this transmission is strictly prohibited. If you have received this communication in error, please immediately notify the sender and delete the message from your system and destroy any print-outs and copies.  Polo Ralph Lauren reserves the right to record and monitor email communications through its networks for the purposes of eliminating potential viruses, blocking unsolicited emails, filtering out illegal or offensive content, and for investigating and detecting any unauthorized use of its systems as well as ensuring its effective operation. The!
>  content of this email is not legally binding unless explicitly confirmed by letter. Please note that it is your responsibility to scan this message for viruses.
> --
> This is the RPG programming on the IBM i / System i (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.