Using Host Array in Open Cursor in Embedded SQL

Question

I have this embedded SQL code:

EXEC SQL
  OPEN MY_CURSOR USING :a1
END-EXEC

Where a1 is Array with 20 items, but I don't know in forward how many Host Variables I will need. It can be from 1 to 20. If I use it in this way, and actually I am using only 5 items from array, I got this error:

SQLCODE: -10000, SQLSTATE: 24000, SQLERRMC: Invalid Cursor State

Is there any possibility how to in OPEN CURSOR section send number of used items in array? Or I must use array with exact number of items?

I suppose it begs the question why are you using a cursor anyway — Charlieface, Feb 18 '21 at 14:50
In this case you possibly want to use a plain `SELECT`, for example have a look at [SQL Queries](https://www.tutorialspoint.com/cobol/cobol_database_interface.htm) (search for `SELECT`). — Simon Sobisch, Feb 18 '21 at 20:18
But I am receiving multiple rows from this SELECT, am I able to do it without using cursor? — Pavel Matras, Feb 19 '21 at 07:44

score 0 · Answer 1 · answered Feb 24 '21 at 10:36

The host variables in the "OPEN CURSOR" statement refer to parameters in the where clause.

Not data is returned on OPEN.

Instead you get each individual row by issuing a "FETCH INTO :var1, :var2 ..." much the same a single row select.

See docs here: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/fetch-transact-sql?view=sql-server-ver15

Using Host Array in Open Cursor in Embedded SQL

1 Answers1