Dynamic SQL needs an additional step at runtime, i.e. the when executing the
prepare statement, a syntax check must be performed.
For static SQL, syntax check is performed at runtime and the first access
plan is stored in the (Service-)Program/Module object.
Access plans for static SQL statements are stored and updated within the
embedded SQL (Service-)Program/Module object.
Access plans for dynamic SQL are NOT stored in the (Service-)Program/Module
object.
When executing dynamic SQL, before SQE the access plan must have been always
built from scratch (if it was not yet not stored in the job cache), while
for static SQL the access plan stored access plan could be taken and
validated from the object.
With SQE it changed, i.e. access plans are first searched in the SQE plan
cache, and only if not found there either generated by scratch or taken and
validated from the program object.
BTW in my experience dynamic SQL is only necessary if the files/tables/view
or libraries/schemas must be managed dynamically.
(Almost) everything else can be done with static SQL, for example in
conjunction with CASE clauses. In this way even dynamic sorts can be handled
with static SQL.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von Mark
Murphy/STAR BASE Consulting Inc.
Gesendet: Tuesday, 16.2 2016 22:13
An: RPG programming on the IBM i (AS/400 and iSeries)
Betreff: Re: RPG SQL: Prepared Statement v. Cursor
Excellent question. However, assuming the real question is concerning
differences between static and dynamic (prepared) SQL, I use static, both in
individual statements and cursors whenever possible because the prepare step
then happens once at compile time vs. every time the statement is prepared
during run time. That said, sometimes it is harder, or even impossible to
use static SQL. For example, when you do not know what the when clause will
be because you are creating a search filter with many possible permutations.
Or if you have a variable term that can not be supplied by a host variable
like table name, though you must be careful when concatenating SQL commands
together so that you do not create a vector for SQL injection. There is
actually a third option as well. That would be CLI. Haven't ever used it,
but there are some comparisons in the knowledge center if you care to go
hunting for them.
Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx
-----darren@xxxxxxxxx wrote: -----
To: "RPG programming on the IBM i \(AS/400 and iSeries\)"
<rpg400-l@xxxxxxxxxxxx>
From: darren@xxxxxxxxx
Date: 02/16/2016 03:52PM
Subject: Re: RPG SQL: Prepared Statement v. Cursor
I am involved in a discussion of the pros & cons of Prepared Statements
and Cursors. Clearly I am over my head.
What are the strengths/weaknesses of these two solutions? Which do you
prefer?
You can prepare a statement with a cursor, so the two aren't mutually
exclusive. Are you asking about prepared vs static statements, or cursor
vs. one record selects?
midrange.com
