AW: Embedded SQL and Parameter Markers

Started a same topic in a German forum.

As Birgitta said, use a static SQL. Correct parameter makers use a ?, as
well to prevent any SQL injections.

I solved it with this statement:
Select ...
where (Field1 = '' or Field1 = :Value1) and (Field2 = '' or Field2 =
:Value2)

Karl

-----Ursprüngliche Nachricht-----
Von: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx] Im Auftrag von
Craig Richards
Gesendet: Donnerstag, 17. Februar 2022 10:15
An: RPG programming on the IBM i (AS/400 and iSeries)
Betreff: Embedded SQL and Parameter Markers

I'm building a few "Work With" screens with filters at the moment
Fairly typical maintenance programs where several of the columns shown in a
subfile have a "filter" field above them for refining the record selection.

Because the number of filters used is variable, I have traditionally
created dynamic SQL by building up a WHERE clause in a string and adding in
any of the filter fields which have been entered on the screen by the user.

I was wondering if there is a reasonable way to do this using parameter
markers.
Given that the number of filters entered is variable, so would the list of
parameter markers be.

To illustrate - I'm doing something like (simplified for brevity):

SqlStm = 'Select ... From ...';

If Filter1 <> *Blanks;
SqlWhere += 'and Field1 = Filter1'
EndIf;

If Filter2 <> *Blanks;
SqlWhere += 'and Field2 = Filter2'
EndIf;

If SqlWhere <> *Blanks;
SqlStm += SqlWhere;
EndIf;

etc.

In this simple example I could end up with:
- Select * from File
- Select *from File where Field1 = Value1
- Select *from File where Field2 = Value2
- Select *from File where Field1 = Value1 and Field2 = Value2

Is there a better way to build this dynamic sql?

Thanks in advance for any suggestions.