By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,506 Members | 1,891 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,506 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
5 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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.