Re: Building dynamic SQL with descriptors

[Brian Parkins <goodprophet.bp@xxxxxxxxx>]

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?