Output variables cannot be handled in this way.
You have to define the variables in the D-Specs (you could create them in a
source member and put them as subfields in a data structure).
In your SELECT ... into Statement or your FETCH Statement you have to
hardcode the output variables.
If you define a data structure with all output fields, you just use this
data structure (instead of listing all columns)
In your SELECT ... into statement (Or in your FETCH statement) you just
specify the Datastructure Name (SELECT ... into :DSXXX)
" I'm doing this to reduce the maintenance workload."
For reducing the maintainance workload and for to get the best performance,
you should only select the columns that are really needed!
If something changes you only have to change and recompile the programs that
use the modified or added columns. All other programs that use the
file/table stay untouched!
If you really want to read the complete record, why not using a SELECT * and
defining an external datastructure for the file/table that includes all
columns?
Instead of listing all columns in the SELECT ... INTO or FETCH Statement,
you read the data into the appropriate datastructure.
If something changes, you just have to recompile your program.
BTW if you want to have a list of all columns in your file you can generate
it directly from the SYSCOLUMNS View.
The following query will return a list of all columns in yourTable in the
sequence how they are defined in the table.
Select ListAgg(Column_Name, ', ') Within Group (Order By Ordinal_Position)
from SysColumns
Where Table_Schema = 'YOURSCHEMA'
and Table_Name = 'YOURTABLE'
BTW there is neither a need to generate an external member which includes a
list of all columns nor a member which includes the output fields, nor it is
necessary to use dynamic SQL.
These techniques will not reduce maintenance, au contraire!
It's just a reminder of how incredibly productive DDS is!
Not sure why DDS is more productive than SQL in this case.
If you mean native I/O there is also no difference at least if you are
working with ILE concepts where you have to define your output
datastructures by yourself.
With native I/O, you have to Define your Files in the F-Specs - in SQL it is
a SELECT ... Statement or a DECLARE CURSOR statement).
For receiving the record in a ILE procedure you also have to generate an
(external) Data structure or a Data structure generated with LIKEREC.
This datastructure has to be specified in the READ, WRITE, UPDATE commands.
In SQL you specify the data structure in the INTO or in the FETCH statement.
So where is the difference.
Also when using (embedded SQL) effenctively you should not code as in native
I/O.
I said it before you should only read what you need and you also should not
read one file after the other.
Joining the tables together to get all you want in a single SELECT statement
is much more effective.
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: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of x y
Sent: Friday, 21 March 2025 21:09
To: Midrange Systems Technical Discussion <MIDRANGE-L@xxxxxxxxxxxxxxxxxx>
Subject: Copying code into SQL statements
Instead of SELECT * or listing every column name, I'd like to put the column
names in a source member and "include" it in the main SQLRPGLE compile. The
source members are machine-generated; I'm doing this to reduce the
maintenance workload.
"from_src" is "a,b,c". "into_src" is ":a,:b,:c".
INCLUDE works with SELECT:
SELECT
INCLUDE from_src
INTO...
but the preprocessor won't accept
INTO
INCLUDE into_src
WHERE...
The error is SQL0104 and the preprocessor is looking for a comma or an INTO.
I have my INCFILE set correctly.
Using
FETCH NEXT FROM THIS_CURSOR INTO
into_src...
INCLUDE is tagged as a variable not found.
Am I missing something other than using dynamic SQL? It's just a reminder
of how incredibly productive DDS is!
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
midrange.com
