Re: Embedded SQL and Parameter Markers

On Thu, Feb 17, 2022 at 12:05 PM Craig Richards <craig@xxxxxxxxxxxxxxxx>
wrote:

Take a look at USING SUBSET on EXECUTE and OPEN with Extended indicators

https://www.ibm.com/support/pages/node/6174207

It allows for exactly what you're asking for...

Build a dynamic statement with a variable number of parameter markers yet
have a single open/execute with all possible variables.

Charles


Thanks Charles - that does look interesting and good to know.
It's definitely more work than using the static method that Birgitta
suggested, in that you have to add in all of the logic to build the correct
statement and set the indicator variables, but I can also see some cases
where it might be useful.

I have to admit I was a bit confused by the link you shared above as it
left me a bit unsure how to code it in an SQLRPGLE program but I
happened upon this link
https://www.itjungle.com/2020/06/15/guru-subset-on-execute-and-open/ which
helped me out.
I did manage to get an example of it working on my machine.

Thanks all for your posts, very interesting and I've learned a lot.

The article you linked to provides a nice list of why this way is prefered..

There were three possible coding solutions, each with its own caveats

- Use static SQL, which (most of the time) will result in the query
engine performing unnecessary and inefficient work.
- Construct a WHERE clause that includes the comparison values, which
results in convoluted, error-prone string handling with the (small)
potential for an SQL injection attack.
- Use an SQL Descriptor, which (although better than the previous
solutions) is not intuitive and requires a lot of attention to detail,
especially for handling the data types of the host variables.

I've long used static with the CASE when trim(:Filter1)='' then 1 else ...

and been told by IBM to quit doing so in order to improve performance.

Given that we just moved from 7.2 to 7.4, I haven't had a chance to try it
out myself...

Charles


Charles