469,275 Members | 1,782 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,275 developers. It's quick & easy.

Cursor fetching without declaring host variables... dynamic SQL?

Hi all,

In my SQL procedures in DB2 8.2, I current declare several cursors that
return select statements with 10 columns in it.

As a result, assigning host variables becomes a coding mess, as I would
have to declare anywhere from 50-100 host variables just so I can
process them.

Is it possible to fetch the row from a cursor into a single
construct/variable so I can simply access the data by column name?

I'm looking for something similar or works similar to the pseudocode
below:

i.e.

DECLARE CURSOR myCursor AS SELECT c1, c2, c3... FROM myTable;
OPEN myCursor;
FETCH into rowVariable; -- hypothetical, notice I don't have to
define host variables
LOOP
set myVariable1 = rowVariable.c1;
set myVariable2 = rowVariable.c2;
THEN
ITERATE LOOP;
END IF;

Dec 8 '06 #1
5 7987
Patrick wrote:
Hi all,

In my SQL procedures in DB2 8.2, I current declare several cursors that
return select statements with 10 columns in it.

As a result, assigning host variables becomes a coding mess, as I would
have to declare anywhere from 50-100 host variables just so I can
process them.

Is it possible to fetch the row from a cursor into a single
construct/variable so I can simply access the data by column name?

I'm looking for something similar or works similar to the pseudocode
below:

i.e.

DECLARE CURSOR myCursor AS SELECT c1, c2, c3... FROM myTable;
OPEN myCursor;
FETCH into rowVariable; -- hypothetical, notice I don't have to
define host variables
LOOP
set myVariable1 = rowVariable.c1;
set myVariable2 = rowVariable.c2;
THEN
ITERATE LOOP;
END IF;

When you DECLARE the CURSOR, specify WITH RETURN TO CLIENT, and OPEN
the CURSOR, but do not CLOSE it, before the END of the PROCEDURE.

Ther CLIENT will then get an OPEN CURSOR on all the results.

B.

Dec 8 '06 #2
Hm, what if I need to continue processing inside the procedure?
Brian Tkatch wrote:
Patrick wrote:
Hi all,

In my SQL procedures in DB2 8.2, I current declare several cursors that
return select statements with 10 columns in it.

As a result, assigning host variables becomes a coding mess, as I would
have to declare anywhere from 50-100 host variables just so I can
process them.

Is it possible to fetch the row from a cursor into a single
construct/variable so I can simply access the data by column name?

I'm looking for something similar or works similar to the pseudocode
below:

i.e.

DECLARE CURSOR myCursor AS SELECT c1, c2, c3... FROM myTable;
OPEN myCursor;
FETCH into rowVariable; -- hypothetical, notice I don't have to
define host variables
LOOP
set myVariable1 = rowVariable.c1;
set myVariable2 = rowVariable.c2;
THEN
ITERATE LOOP;
END IF;


When you DECLARE the CURSOR, specify WITH RETURN TO CLIENT, and OPEN
the CURSOR, but do not CLOSE it, before the END of the PROCEDURE.

Ther CLIENT will then get an OPEN CURSOR on all the results.

B.
Dec 8 '06 #3
Patrick wrote:
Hm, what if I need to continue processing inside the procedure?
Brian Tkatch wrote:
Patrick wrote:
Hi all,
>
In my SQL procedures in DB2 8.2, I current declare several cursors that
return select statements with 10 columns in it.
>
As a result, assigning host variables becomes a coding mess, as I would
have to declare anywhere from 50-100 host variables just so I can
process them.
>
Is it possible to fetch the row from a cursor into a single
construct/variable so I can simply access the data by column name?
>
I'm looking for something similar or works similar to the pseudocode
below:
>
i.e.
>
DECLARE CURSOR myCursor AS SELECT c1, c2, c3... FROM myTable;
OPEN myCursor;
FETCH into rowVariable; -- hypothetical, notice I don't have to
define host variables
LOOP
set myVariable1 = rowVariable.c1;
set myVariable2 = rowVariable.c2;
THEN
ITERATE LOOP;
END IF;

When you DECLARE the CURSOR, specify WITH RETURN TO CLIENT, and OPEN
the CURSOR, but do not CLOSE it, before the END of the PROCEDURE.

Ther CLIENT will then get an OPEN CURSOR on all the results.

B.
I do not think it is possible then. You must define host variables.

B.

Dec 8 '06 #4
Patrick wrote:
Hi all,

In my SQL procedures in DB2 8.2, I current declare several cursors that
return select statements with 10 columns in it.

As a result, assigning host variables becomes a coding mess, as I would
have to declare anywhere from 50-100 host variables just so I can
process them.

Is it possible to fetch the row from a cursor into a single
construct/variable so I can simply access the data by column name?

I'm looking for something similar or works similar to the pseudocode
below:

i.e.

DECLARE CURSOR myCursor AS SELECT c1, c2, c3... FROM myTable;
OPEN myCursor;
FETCH into rowVariable; -- hypothetical, notice I don't have to
define host variables
LOOP
set myVariable1 = rowVariable.c1;
set myVariable2 = rowVariable.c2;
THEN
ITERATE LOOP;
END IF;
FOR rowVariable AS SELECT c1, c2, c3... FROM myTable DO
myVariable1 = rowVariable.c1;
myVariable2 = rowVariable.c2;
END FOR;

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html
Dec 8 '06 #5
Thanks!!!! This is what I was looking for!
Serge Rielau wrote:
Patrick wrote:
Hi all,

In my SQL procedures in DB2 8.2, I current declare several cursors that
return select statements with 10 columns in it.

As a result, assigning host variables becomes a coding mess, as I would
have to declare anywhere from 50-100 host variables just so I can
process them.

Is it possible to fetch the row from a cursor into a single
construct/variable so I can simply access the data by column name?

I'm looking for something similar or works similar to the pseudocode
below:

i.e.

DECLARE CURSOR myCursor AS SELECT c1, c2, c3... FROM myTable;
OPEN myCursor;
FETCH into rowVariable; -- hypothetical, notice I don't have to
define host variables
LOOP
set myVariable1 = rowVariable.c1;
set myVariable2 = rowVariable.c2;
THEN
ITERATE LOOP;
END IF;
FOR rowVariable AS SELECT c1, c2, c3... FROM myTable DO
myVariable1 = rowVariable.c1;
myVariable2 = rowVariable.c2;
END FOR;

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html
Dec 11 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Aakash Bordia | last post: by
2 posts views Thread by Oliver Corona | last post: by
3 posts views Thread by Anthony Robinson | last post: by
2 posts views Thread by Frank Swarbrick | last post: by
7 posts views Thread by misha | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.