Re: SQL with parameters - need to PREPARE and 'OPEN ... USING'?

[Jeff Young <jyoung0950@xxxxxxxxx>]

Paul,
By Closing the Cursor at the end of the subroutine, and then Opening it at
the start, you are refreshing the values used for the FETCH.  Each Open
refreshes the values to be used.

Jeff Young
Sr. Programmer Analyst

On Wed, May 11, 2016 at 12:18 PM, paultherrien <
paultherrien@xxxxxxxxxxxxxxxxxx> wrote:

> We are on IBM I V7R1.
>
> I have a question:
>
> When using a cursor in SQL with variable values do you NEED to use a
> prepare
> statement with a variable marker, (a question mark (?)), and then execute
> ‘OPEN
> … USING’ with the variable values?
>
> I thought this sort of construct was required when the selection variables
> values were ... well, variable.
>
> I have come across code that I thought would not work….
>
> An SQL declare statement loaded in the *INZSR subroutine….
>
> C/EXEC SQL
> C+
> C+ declare @cursorDft999 Cursor for
> C+ Select zbsrtp from zbsrcefe
> C+ Where zbcmp# = :zbcmp# and zbprcd = :zbprcd
> C+ and zbtrnm = :zbtrnm
> C+ and zbsrcd like '999%'
> C+ Order by zbcrcd desc
> C+
> C/END-EXEC
>
>  And then in a seperate subroutine …
>
>     begsr  $get_zbsrtp;
>
> C/EXEC SQL
> C+ OPEN @cursorDft999
> C/END-EXEC
>
> C/EXEC SQL
> C+ FETCH NEXT FROM @cursorDft999
> C+ into :srtp
> C/END-EXEC
>
> If Sqlcod = *Zeros;
> zbsrtp = srtp;
> Endif;
>
> C/EXEC SQL
> C+ Close @cursorDft999
> C/END-EXEC
>
>   endsr;
>
> At each execution of the subroutine the values of ZBCMP#, ZBPRCD, and
> ZBTRNM
> are different depending on the data being processed.
>
>  I would have sworn this was not going to work as coded above.  I thought
> one
> needed to use the ‘OPEN …. USING’ syntax.
>
>  Any comments appreciated.
>
>  (Feeling a bit dim-witted today)
>
> Paul
> --
> This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
> mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.