Sounds like an excellent use of Dynamic Varying List SELECT statements. I confess I have yet to see a good working example written in RPG IV! (All of the examples I have come across were written in C).

(I always have this nagging feeling that a we're simply trying to invent a better mousetrap. Consider the existing query tools - even STRSQL - that can be called upon.)


On 03/09/2017 22:38, Mike Jones wrote:
Hi John,

Back in the early 90s, I built a generic F4-prompt subfile program that
would handle any SQL string / any set of columns / data types / sizes.

It was written in C, used the SQLDA populated by the SQL engine, but here's
the kicker, it dynamically allocated the memory and their pointers to hold
the column values (based on SQLDA feedback), so that the receiving memory
exactly matched the data types and sizes about to be returned by the SQL

One way or the other, if you want to handle any number of columns with any
data type and size, I think you still need to dynamically allocate the
memory at run time that will hold the data returned by the SQL engine.

That stuff is not a trivial task, although I'm sure it would be easier
today in some respects versus back then. Working against you is the
increased number of data types present today, which increases the amount of
work to code what you want, assuming you want to support all data types.
Make sure you have a good reason for doing that, versus lesser capable

In my case, I was calling that generic program from dozens of places, and I
could implement a new call to it with about a handful of lines of code. I
did get a lot of mileage from it.


On Sun, Sep 3, 2017 at 1:49 PM, John R. Smith, Jr. <
smith5646midrange@xxxxxxxxx> wrote:

I am trying to write an SQLRPGLE program that can take a dynamic SQL string
(user selected fields) and extract the data from a file. I am using
descriptors because the fields selected will be variable. For example,
may do "select firstname, lastname, age from people" or they may do "select
lastname, firstname, gender from people. I have a lot of this working but
have found that the field I am loading in my program has to be the exact
size of the field in the database.

In the case of firstname (20 chars) and lastname (30 chars) I am using the
below logic.

exec sql

get descriptor :descName VALUE :i

:type = TYPE,

:length = LENGTH,

:result_ind = INDICATOR;

if result_ind >= 0;


when type = '1'; // character

exec sql

get descriptor :descName value:i

:charResult = DATA;

.more data types.



What I am finding is charResult has to be 20A to extract the firstname
but has to be 30A to extract the lastname field. If either field does not
match, I receive an SQL0076 error.

I am having the same problem with my numeric fields. Since age is a 3,0
field, my numericResult has to be a 3,0. If numericResult is anything
then 3,0, I get the SQL0076 which means it won't work for another field
is 5,0.

Can someone tell me what I am missing?

This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives

Please contact support@xxxxxxxxxxxx for any subscription related

Help support by shopping at with our affiliate

As an Amazon Associate we earn from qualifying purchases.

This thread ...


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

This mailing list archive is Copyright 1997-2021 by 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.