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

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

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