RE: Odd SQL Module Issue - Cursor runs repeatedly

Brian,

I believe what you are seeing is normal. Each SQL statement in your source is replaced by the pre-compiler with additional RPG code to make calls to the SQL programs that do the actual work.

When debugging in source view you don't see the extra lines of code and the debugger moves to the next executable statement. What you are seeing is the program moving around in the added SQL code.

Rick

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Johnson, Brian (EU)
Sent: Wednesday, April 29, 2009 12:17 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Odd SQL Module Issue - Cursor runs repeatedly

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.
--

Privileged and Confidential. This e-mail, and any attachments there to, is intended only for use by the addressee(s) named herein and may contain privileged or confidential information. If you have received this e-mail in error, please notify me immediately by a return e-mail and delete this e-mail. You are hereby notified that any dissemination, distribution or copying of this e-mail and/or any attachments thereto, is strictly prohibited.