cursor and data fetching when columns are not known

rpkulkarni · #1 (**permalink**) 12-10-09, 06:21

How to fetch the data from cursor where number of columns returned and their data type is unknown.
Here is the example

Code:

declare c1 cursor for select * from table1;
fetch c1 into .....

What can replace ..... if I do not know the schema of table1.

My DB2 version : DB2 universal database 9.5

Please advise.

ARWinner · #2 (**permalink**) 12-10-09, 08:10

I suggest you figure out the columns from the catalog.

Andy

stolze · #3 (**permalink**) 12-10-09, 10:27

Have a look at the DESCRIBE statement.

Lenny77 · #4 (**permalink**) 12-10-09, 11:36

Quote:

Originally Posted by rpkulkarni

How to fetch the data from cursor where number of columns returned and their data type is unknown.
Here is the example

Code:

declare c1 cursor for select * from table1;
fetch c1 into .....

What can replace ..... if I do not know the schema of table1.

My DB2 version : DB2 universal database 9.5

Please advise.

You can make DCLGEN of table1 and INCLUDE this DCLGEN in your program.

Compiler will find something like this:

Code:

EXEC SQL DECLARE table1 TABLE    
( column1 ............................
)
01  DCLGEN-TABLE1.  
    10 COLUMN1........................

Then you can

Code:

fetch c1 into :DCLGEN-TABLE1

Lenny

rpkulkarni · #5 (**permalink**) 12-10-09, 12:15

My Question is about the variable names and their data type.

Code:

OPEN C1; FETCH c1 into var1, var2 ...

I can get the column names of the table. But for fetching data into variables var1, var2 etc. I need to define them before using them in FETCH statements. How this can be done dynamically? I hope I am able to convey my problem correctly.

Please advise.

Lenny77 · #6 (**permalink**) 12-10-09, 12:24

Quote:

Originally Posted by rpkulkarni

My Question is about the variable names and their data type.

Code:

OPEN C1; FETCH c1 into var1, var2 ...

I can get the column names of the table. But for fetching data into variables var1, var2 etc. I need to define them before using them in FETCH statements. How this can be done dynamically? I hope I am able to convey my problem correctly.

Please advise.

I shown to you how. You can't understand....

I can't help you anymore.

Lenny

stolze · #7 (**permalink**) 12-11-09, 11:24

Quote:

Originally Posted by rpkulkarni

My Question is about the variable names and their data type.

Code:

OPEN C1; FETCH c1 into var1, var2 ...

I can get the column names of the table. But for fetching data into variables var1, var2 etc. I need to define them before using them in FETCH statements. How this can be done dynamically? I hope I am able to convey my problem correctly.

Please advise.

You can fetch into a SQLDA and then process the SQLDA entries. It is a bit more involved than directly fetching into a host variable, but it's not complicated either.

Internet Services

Web Development

Digital Marketing

Consumer Tech