hi all,
i am having problem using cursor in db2
i couldn't use

fetch next from <cursor-name>;

the error message is something like invalid <end of statement>, but if I put "fetch next from <cursor-name> into <some-variable-name>", it builds successfully. So weird, I thought the 'into' keyword is optional if i don't specify variables.

Is there a way I could move the cursor to next row without having to fetch it to host variables? coz the variables is not known until runtime.(dynamic)
I just wanna move next and delete current.

Thanks a lot folks

Can you modify the select statement to always select a known constant?

James Campbell

no, it has to be generic, table are not known until runtime, so do the column name, so the select statement is dynamic.

I just want to make sure I understand this:
- it is acceptable to not fetch the actual rows, meaning that you wouldn't fetch any data from the table
- it is not acceptable to use a constant - because you don't know what it is that you won't be getting.
On second thoughts, no I don't understand.

Looks like you'll have to go down the path of
- creating an SQLDA
- PREPARE S1 OUTPUT INTO :created-sqlda FROM :hv-with-select-stmt-text
- Prepare will your sqlda to specify how many columns will be retrieved, their type and their length. You use this to allocate memory for each fetched columns, and insert the address of this allocated memory into the sqlda. Don't forget to make provision for null indicators if the sqltype indicates that this is required.
- DECLARE C1 CURSOR FOR S1
- OPEN C1
- FETCH C1 USING DESCRIPTOR :created-sqlda

James Campbell

hi jacampbell,

What I mean is, I don't want to fetch any columns, what i want is only deleting the row then move cursor to next row and delete again.
The problem is I don't know what function DB2 provide to move cursor to next row.
The only way is to use fetch. But using fetch require me to know about the columns of table so that I can declare variables to be fetched into, which I can't provide, because I don't even know about the table definition and this stored procedure will be used for a lot of tables.
So I can't use fetch in this case to move the cursor to next row.

How come there is no other way to just move cursor to next row without fetching into variables? I can do it very easily in sql server.

I'm really stuck now, hope you guys can help me. I still can't believe DB2 can't do that, sure got some way to do that.

Thanks a lot

Regarding your doubts:

"it is acceptable to not fetch the actual rows, meaning that you wouldn't fetch any data from the table"
-> yes I wouldn't fetch any data from table, first because I don't need it, what i want is to just delete it using cursor maybe for some 100 records.Second, I can't fetch even I want to, because it's dynamic sql like this:
"select * from " || table-name (the table name is provided at run time and the table definition can be different all the time,some can have 1 column, some can have 3 columns, etc, so I can't declare variable to do fetch)

"it is not acceptable to use a constant - because you don't know what it is that you won't be getting.On second thoughts, no I don't understand."
-> Yes, you are right, I don't know and can't predict what kind of table that will be processed by this stored procedure.

What I want is actually very simple, delete current row and move to next row. That's all. Help plz

I still don't see why, if you can specify "select * from " || table-name and ignore whatever columns are in the table, you can't "select 1 from " || table-name.

And, no, DB2 does not have a method of positioning on a row other than fetching it. The last time I looked the SQL Standard required the INTO clause on a FETCH - and IBM seem to be reluctant to go much beyond the standard.

James Campbell