Re: Building dynamic SQL with descriptors -- RPG400-L

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.)

Brian.

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
engine.

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
alternatives.

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.

Mike

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,
they
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
I
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;

select;

when type = '1'; // character

exec sql

get descriptor :descName value:i

:charResult = DATA;

.more data types.

endsl;

endif;

What I am finding is charResult has to be 20A to extract the firstname
field
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
other
then 3,0, I get the SQL0076 which means it won't work for another field
that
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,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD