× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



...creating SQL statements this way is still a bad idea IMO for the reasons I already mentioned.

Tim
________________________________
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Daniel Gross <daniel@xxxxxxxx>
Sent: 17 February 2022 20:02
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Embedded SQL and Parameter Markers

Yes its a bit of pain, that we don't have something like string interpolation in RPG, and that you cannot change the string quote characters to make it somehow easier.

It would be so nice to write:

string = 'Hello';
number = 15.3;
sqlstmt = c"select ... where field1 = '%{string}' and field2 = %{number}";

A RFE for something like failed last years.

Bye
Daniel


Am 17.02.2022 um 20:26 schrieb Alan Campin <alan0307d@xxxxxxxxx>:

Response by the number.
1. The overhead of the prepare is the re-creating the run time plan. If the
SQL runtime has seen that string before, it will just run the run time plan
in the system cache.
2. I avoid the quotes issue by using a single long string and %ScanRpl. I
know others don't like this.
dcl-c QUOTE '''';
dcl-s SqlStmt VarChar(5000);

SqlStmt = 'Select A, B, C From FileX Where Field1 = ~Q~Y~Q~ And +
Field2 = Case(? As Decimal(3,0))';
SqlStmt = %ScanRpl('~Q~':QUOTE:SqlStmt);

To me, simple and easier. Just one long string.

I agree that dynamic strings can be a pain. I keep thinking there must be a
better way to build dynamic strings. Maybe a function that operates like a
PC library using ODBC. We have a multi-client environment so all of our SQL
strings need to be dynamic. Super pain.


On Thu, Feb 17, 2022 at 10:57 AM Tim Fathers <tim@xxxxxxxxxxxxx> wrote:

I can think of a few good reasons off the top of my head.

1) There's an unnecessary prepare for each run.
2) RDI will no longer syntax check the statement and all quotes have to be
escaped, making it a maintenance nightmare for the next poor bugger
3) You have to properly cleanse the filter strings to ensure O'Reily type
searches don't cause the statement to fail
4) Dynamically building SQL strings is a poor security practice and the
root cause of SQL injection attacks. While there might be limited scope for
this on a green screen with smalled fields available to take advantage of,
its still a bad idea from a security standpoint.



Get Outlook for Android<https://aka.ms/AAb9ysg>
________________________________
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Alan
Campin <alan0307d@xxxxxxxxx>
Sent: Thursday, February 17, 2022 6:58:18 PM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Embedded SQL and Parameter Markers

I am not sure I understand the reluctance to use a dynamic string. Easy to
build and the system will cache the Run Time plan in the system cache for
each statement. One thing that I would suggest is to always use parameter
markers when building the statements. I believe that SQL will try to figure
out what are values and what are text.

If you give it


Select A, B, C From FileX Where Field1 = 12345

it will probably figure out that the statement it needs to cache is.

Select A, B, C From FileX Where Field1 = ?

Better to state explicitly.

Select A, B, C From FileX Where Field1 = Cast(? As Decimal(5,0))

Open CursorFilex Using :Work1;

This should also protect against ejection attacks.

My point to all this is that the SQL Compiler will cache the runtime plans
in the system cache and give you good performance. Not sure that building
all these elaborate case and other things is going to get you anything.
Again, my opinion only.



On Thu, Feb 17, 2022 at 8:41 AM Charles Wilt <charles.wilt@xxxxxxxxx>
wrote:

Assuming you're on 7.3+ and reasonably current on PTFs...

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


On Thu, Feb 17, 2022 at 6:36 AM Craig Richards <craig@xxxxxxxxxxxxxxxx>
wrote:

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.
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.