Cursor in SPL always return one row
My customer is running IDS 11.50 UC3 on Solaris 10. I am trying to write a procedure that fetches all articles matching a certain criteria. I have been looking at examples in the manual but no luck. The cursor always returns only one row even though the query should return many rows. Here's a piece of the code: IF LangCode = 0 THEN IF (LENGTH(ArticleNoBuf) > 0) THEN LET SearchString = ArticleNoBuf; LET ArtQuery = "SELECT art_no, art_descr "; LET ArtQuery = ArtQuery || "FROM " || DBname || ":art "; LET ArtQuery = ArtQuery || "WHERE art_no[1,3] = ?"; PREPARE stmt_id FROM ArtQuery; DECLARE cur1 CURSOR FOR stmt_id; OPEN cur1 USING SearchString; WHILE (1 = 1) FETCH cur1 INTO ArticleNoBuf, ArticleDescr; IF (SQLCODE != 100) THEN RETURN CompanyID, UserID, OrderNo, OrderLineNo, ArticleNoBuf, ArticleNoCus, ArticleDescr, Quantity, Price, CurrencyCode, InventoryStatus, ErrorCode, ErrorMessage; ELSE EXIT; END IF; END WHILE; END IF; END IF; Cheers Jimmy
If the cursor returns only one row the think about the workaround Insert the output into a table and after the sp is complete read the records from the table (in the calling program) If multiple users might be calling the same stored procedure then call the stored procedure with a unique identifier, insert the records into a table with the identifier and after the sp is complete, read the records from the table with the identifier. Thanks, Sunil Thakkar Sr. Consultant, Core Technology Group | Denver BearingPoint, Management & Technology Consultants. T + 1 720 299 6464 www.bearingpoint.com P please consider the environment before printing this email -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of JIMMY JONSSON Sent: Sunday, March 15, 2009 7:43 AM To: ids@iiug.org Subject: Cursor in SPL always return one row [15138] My customer is running IDS 11.50 UC3 on Solaris 10. I am trying to write a procedure that fetches all articles matching a certain criteria. I have been looking at examples in the manual but no luck. The cursor always returns only one row even though the query should return many rows. Here's a piece of the code: IF LangCode = 0 THEN IF (LENGTH(ArticleNoBuf) > 0) THEN LET SearchString = ArticleNoBuf; LET ArtQuery = "SELECT art_no, art_descr "; LET ArtQuery = ArtQuery || "FROM " || DBname || ":art "; LET ArtQuery = ArtQuery || "WHERE art_no[1,3] = ?"; PREPARE stmt_id FROM ArtQuery; DECLARE cur1 CURSOR FOR stmt_id; OPEN cur1 USING SearchString; WHILE (1 = 1) FETCH cur1 INTO ArticleNoBuf, ArticleDescr; IF (SQLCODE != 100) THEN RETURN CompanyID, UserID, OrderNo, OrderLineNo, ArticleNoBuf, ArticleNoCus, ArticleDescr, Quantity, Price, CurrencyCode, InventoryStatus, ErrorCode, ErrorMessage; ELSE EXIT; END IF; END WHILE; END IF; END IF; Cheers Jimmy ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************** ******************* The information in this email is confidential and may be legally privileged. Access to this email by anyone other than the intended addressee is unauthorized. If you are not the intended recipient of this message, any review, disclosure, copying, distribution, retention, or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. If you are not the intended recipient, please reply to or forward a copy of this message to the sender and delete the message, any attachments, and any copies thereof from your system. ******************************************************************************** *******************
You can use WITH RESUME option. RETURN <list of return values> WITH RESUME; In your example: RETURN CompanyID, ..., ErrorMessage WITH RESUME; Thanks ! Regards, Srini "JIMMY JONSSON" <jimmy.jonsson@pl exor.se> To Sent by: ids@iiug.org ids-bounces@iiug. cc org Subject Cursor in SPL always return one row 15/03/2009 19:13 [15138] Please respond to ids@iiug.org My customer is running IDS 11.50 UC3 on Solaris 10. I am trying to write a procedure that fetches all articles matching a certain criteria. I have been looking at examples in the manual but no luck. The cursor always returns only one row even though the query should return many rows. Here's a piece of the code: IF LangCode = 0 THEN IF (LENGTH(ArticleNoBuf) > 0) THEN LET SearchString = ArticleNoBuf; LET ArtQuery = "SELECT art_no, art_descr "; LET ArtQuery = ArtQuery || "FROM " || DBname || ":art "; LET ArtQuery = ArtQuery || "WHERE art_no[1,3] = ?"; PREPARE stmt_id FROM ArtQuery; DECLARE cur1 CURSOR FOR stmt_id; OPEN cur1 USING SearchString; WHILE (1 = 1) FETCH cur1 INTO ArticleNoBuf, ArticleDescr; IF (SQLCODE != 100) THEN RETURN CompanyID, UserID, OrderNo, OrderLineNo, ArticleNoBuf, ArticleNoCus, ArticleDescr, Quantity, Price, CurrencyCode, InventoryStatus, ErrorCode, ErrorMessage; ELSE EXIT; END IF; END WHILE; END IF; END IF; Cheers Jimmy ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
At the end of statement "return", add the words: "with resume" and it'll return all the rous selected and fetched.... best regards. R Ferronato > To: ids@iiug.org > From: jimmy.jonsson@plexor.se > Subject: Cursor in SPL always return one row [15138] > Date: Sun, 15 Mar 2009 09:43:00 -0400 > > My customer is running IDS 11.50 UC3 on Solaris 10. I am trying to write a > procedure that fetches all articles matching a certain criteria. I have been > looking at examples in the manual but no luck. The cursor always returns only > one row even though the query should return many rows. > > Here's a piece of the code: > > IF LangCode = 0 THEN > > IF (LENGTH(ArticleNoBuf) > 0) THEN > > LET SearchString = ArticleNoBuf; > > LET ArtQuery = "SELECT art_no, art_descr "; > > LET ArtQuery = ArtQuery || "FROM " || DBname || ":art "; > > LET ArtQuery = ArtQuery || "WHERE art_no[1,3] = ?"; > > PREPARE stmt_id FROM ArtQuery; > > DECLARE cur1 CURSOR FOR stmt_id; > > OPEN cur1 USING SearchString; > > WHILE (1 = 1) > > FETCH cur1 INTO ArticleNoBuf, ArticleDescr; > > IF (SQLCODE != 100) THEN > > RETURN > > CompanyID, > > UserID, > > OrderNo, > > OrderLineNo, > > ArticleNoBuf, > > ArticleNoCus, > > ArticleDescr, > > Quantity, > > Price, > > CurrencyCode, > > InventoryStatus, > > ErrorCode, > > ErrorMessage; > > ELSE > > EXIT; > > END IF; > > END WHILE; > > END IF; > > END IF; > > Cheers > Jimmy > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ Show them the way! Add maps and directions to your party invites. http://www.microsoft.com/windows/windowslive/products/events.aspx