Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2009
    Posts
    11

    Unanswered: cursor and data fetching when columns are not known

    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.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I suggest you figure out the columns from the catalog.

    Andy

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Have a look at the DESCRIBE statement.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb Use DCLGEN of table1 and INCLUDE...

    Quote Originally Posted by rpkulkarni View Post
    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

  5. #5
    Join Date
    Nov 2009
    Posts
    11
    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.

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs down

    Quote Originally Posted by rpkulkarni View Post
    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

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by rpkulkarni View Post
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •