Re: Opening a cursor with a variable number of parameter markers...

Hi Rob

I'm going to throw a couple ideas at you, one that I have used, the other I've just read about but think it might work.

1. SQL CLI - that's the SQL Call Level Interface. It has functions similar to ODBC functions. I've used it to dynamically build statements when I don't know what I'm working with coming in. You can have several different cursors over the same table, as I recall.

2. SQLDA - I have been reading about that recently, and if I remember rightly, it is similar to CLI, where you specify all the stuff with API calls, with SQLDA you fill arrays and tell the thing how many and all that.

There's a specific manual for SQL Call Level Interface, for SQLDA you will want to look at the SQL Programming manual, I think - or the Embedded SQL Programming manual - both have examples for different programming languages.

I have more confident that CLI can work, just be sure not to use it in the same program with RPG-embedded SQL - I think I ran into problems with that, some kind of use of the same system resources, maybe.

HTH
Vern

On 12/30/2020 9:27 PM, Robert Rogerson wrote:

Happy New Year to all.

I need to open a cursor but the number of parameter markers is not known
until run time.
I'm doing this right now with a SELECT statement in RPG but wondering if
this can be softcoded in some way. What I have is...

// We now need to open the statsCursor with the correct number of parameter
markers
Select;
When fileCount = 1;
Exec sql
OPEN statsCursor USING :fromTs, :toTs;
When fileCount = 2;
Exec sql
OPEN statsCursor USING :fromTs, :toTs, :fromTs, :toTs;
When fileCount = 3;
Exec sql
OPEN statsCursor USING :fromTs, :toTs, :fromTs, :toTs, :fromTs,
:toTs;
Endsl;

Currently this is coded for 3 files, but this can easily be 10 or more
files at run time.

Do I have any other options for OPENing the cursor?

Thanks,

Rob