MQSeries.net :: View topic - How to use Cursor in ESQL

MQSeries.net

Tech Exchange

Education

Certifications

Library

Info Center

SupportPacs

FAQÂ Â

Usergroups

RSS Feed - WebSphere MQ Support

RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » How to use Cursor in ESQL

How to use Cursor in ESQL

« View previous topic :: View next topic »

Author

Message

Harshalw

Posted: Fri Jul 25, 2008 5:56 am Post subject: How to use Cursor in ESQL

Voyager

Joined: 23 Jul 2008
Posts: 77

Hi,

I am using ESQL and WMB 6.1. My query will fetch number of records (Rows) from database and for each row i have a different processing and later on updation. How can i use cursor in Esql for the same or any other methoda used in ESQL. Please let me know.

Thanks

sridhsri

Posted: Fri Jul 25, 2008 6:31 am Post subject:

Master

Joined: 19 Jun 2008
Posts: 297

One method I can think of is to store the result set in a scratch pad like LocalEnvironment. You could then calculate the cardinality of the rows and use a loop to process each row.

Harshalw

Posted: Fri Jul 25, 2008 6:49 am Post subject:

Voyager

Joined: 23 Jul 2008
Posts: 77

can you please elaborate on how i can do that?
I am new to WMB and Esql here

sridhsri

Posted: Fri Jul 25, 2008 7:19 am Post subject:

Master

Joined: 19 Jun 2008
Posts: 297

Alright here goes....

DECLARE count INTEGER 0;
DECLARE I INTEGER 1;

SET OutputLocalEnvironment.ResultSet[] = SELECT * from Database.table;
SET count = CARDINALITY(OutputLOcalEnvironment.ResultSet[]);

WHILE I < count DO
--process each row in the array OutputLocalEnvironment.ResultSet[I]
SET I = I + 1;
END WHILE;

Harshalw

Posted: Fri Jul 25, 2008 7:30 am Post subject:

Voyager

Joined: 23 Jul 2008
Posts: 77

hey thanks for your input

how can we access the column from OutputLocalEnvironment.ResultSet[I]

will it be OutputLocalEnvironment.ResultSet[I].column_name ?

sridhsri

Posted: Fri Jul 25, 2008 8:30 am Post subject:

Master

Joined: 19 Jun 2008
Posts: 297

Harshalw, I gave wrong you some advise. I apologize. The correct and and efficient way of doing this is by using references.

DECLARE count INTEGER 0;
DECLARE I INTEGER 1;
DECLARE ref REFERENCE TO OutputLocalEnvironment.ResultSet;

SET OutputLocalEnvironment.ResultSet[] = SELECT * from Database.table;
--you could inspect carinality before this line
SET ref = MOVE ref FIRSTCHILD;

WHILE LASTMOVE(ref) DO
--process the result set
MOVE ref NEXTSIBLING REPEAT TYPE NAME;
END WHILE;

To answer your question, you. It will be ref.<columnname>

Display posts from previous:

Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » How to use Cursor in ESQL

Jump to:

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Protected by Anti-Spam ACP