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

[Patrick]

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;

[Brian Tkatch]

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.

[Patrick]

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.

[Brian Tkatch]

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.

[Serge Rielau]

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

[Patrick]

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