RE: Building dynamic SQL with descriptors

Actually, the GET DESCRIPTOR logic that I have built does exactly that. It
handles a variable number of columns, types, sizes, etc. The only part I am
having trouble with is getting the DATA back out of it. I'm just missing
something in how it retrieves it and the field it is putting it in.

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Brian
Parkins
Sent: Sunday, September 3, 2017 5:43 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: Building dynamic SQL with descriptors

John, I am not sure the GET DESCRIPTOR statement will achieve what you want.

From the manual, 'Database: Embedded SQL Programming',

"A SELECT statement with a variable SELECT list (that is, a list of columns
to be returned as part of the query) that runs dynamically requires an SQL
descriptor area (SQLDA). This is because you cannot know in advance how many
or what type of variables to allocate in order to receive the results of the
SELECT."

The SQLDA will give you the flexibility you need. However, not for the
faint-hearted as you'll need to incorporate the use of based-variables,
basing pointers and runtime (temporary) storage allocation. If there are
only a limited number of columns and combinations that the User can select,
you may wish to consider conditioning multiple Static SELECT statements,
rather than building a Dynamic SELECT statement.

Just a thought.

Brian.

PS. I've found that the SQLDA structures generated by the INCLUDE SQLDA
statement to be a little less refined than coding the Descriptor Area from
first principles.

On 03/09/2017 21:49, John R. Smith, Jr. 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