| 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 |
|
| Back to top |
|
 |
| 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. |
|
| Back to top |
|
|
| 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 |
|
| Back to top |
|
|
| 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; |
|
| Back to top |
|
|
| 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 ? |
|
| Back to top |
|
|
| 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> |
|
| Back to top |
|
|
|
|
