EXECUTE IMMEDIATE can only be used for SQL Statements where you do not have
to read through multiple rows (for example for CREATE Statements, mass
UPDATES/DELETES/INSERTS).
So SELECT and DECLARE cursor are not allowed.
If you need to use a Dynamic SELECT Statement, you have to build a String
which includes the complete SELECT Statement (may be with Parameter
markers).
After you have to convert the SQL String containing the SQL SELECT Statement
into a "executable" SQL Statement, i.e. you have to "prepare" it:
PREPARE DynSQL FROM YourHostVar;
In the Declare Cursor Statement you specify the SQL Name instead of the
SELECT-Statement:
DECLARE CsrName CURSOR FOR DynSQL;
After you can Open the cursor, Loop through it and close the cursor again.
If you are dealing with parameter markers, you can replace the parameter
markers with the real values in the OPEN Statement
OPEN CursorName using Var1, Var2, ... VarN
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization ? Education ? Consulting on IBM i
Database and Software Architect
IBM Champion since 2020
"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!"
"Train people well enough so they can leave, treat them well enough so they
don't want to. " (Richard Branson)
"Learning is experience ? everything else is only information!" (Albert
Einstein)
-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
dfreinkel@xxxxxxxxxxxxxxxxx
Sent: Tuesday, 23 January 2024 20:14
To: 'RPG programming on IBM i' <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: How do I do a EXECUTE IMMEDIATE with a declare?
I have not done this in ages and do not remember the details or have a
program as an example.
I need to use a combination of a SQL DECLARE with a EXECUTE IMMEDIATE.
My program will not compile.
Example:
wSql = 'DECLARE FILE scroll cursor for select';
exec sql execute immediate :wSql;
TIA,
Darryl Freinkel
A4G
Telephone: 770.321.8562
--
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.
midrange.com
