Re: SQL DESCRIBE was( SQLRPG - prepare, declare, no cursor)

["Carel Teijgeler" <coteijgeler@xxxxxxxxx>]

Frank,

In order to retrieve data with SQL from different files you can use the 
descriptor SQLDA. (see  Appendices C and D from the SQL eference Manual)

First Prepare the SQL statement (can be a dynamic one)
Use Describe and set SQLN= 0 and SQLD=0 before running; SQLD gives the number 
on return.
Use Describe and set SQLN=SQLD, allocateenough array elements for the array 
within the SQLDA DS, length should be (SQLND * 80) + 16.
Now you know the attributes (data type (SQL version) and length of each column.

You do not need to do that.

Whith SQLDA you give the address of the host variable to receive the data 
(pointers). You can retrieve the data from a packed field, length 7 no decimals 
in a host variable defined as 30 P 9 decimals 

I use this technique in a generic dynamic SQL programme.

For retrieving data and use in the SQLDA I use a DS with an internal array, for 
each datatype a subfield like:

D              DS
D ResultSet                                1024      DIM(32) (or whatever)
D    CharVal                                   32       OVERLAY(ResultSet:1)
D    PackedVal                               30P 9 OVERLAY(ResulSet:*NEXT)
D    ZonedVal                                 30S 9  OVERLAY(ResultSet:*NEXT)
etc.

and give the SQLDA the address of an arry subfield (%addr(PackedVal(Ix)))

Hope this helps a little.

Regards,
Carel Teijgeler

*********** REPLY SEPARATOR  ***********

On 12-4-06 at 11:42 Frank.Kolmann@xxxxxxxxxx wrote:

>It may seem a lot of work, it all depends on what you are trying to do.
>I use SQL like this for powerful user driven search functions. Constructing 
>the WHERE clause can be tricky.
>One limitation I found was to not push this technique too far. I tried to 
>construct a generic SQL so that it would work on any file.
>I did not succeed because the SQL precompiler constructs data structures based 
>on the file it finds at compile time.
>I tried to process the disk output of DSPJRN for any file that was journalled. 
> The journal captures file data in one big field in the 
>journal output and I tried to map that data into a DS based on the journaled 
>file. The SQL looked fine and everything compiled but
>it just did not work as the DSPJRN output is different for each file. This may 
>be possible using SQL APIs or the DESCRIBE >statement but both of these 
>techniques are beyond my present skill.
>
>Does anyone know how to use the DESCRIBE SQL statement.