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

Thanks Carel

The SQL Ref manual is always open on my PC. 
(I got IBM Bookreader V4R3 with all manuals copied to disk. 
The hardware finally caught up with the bookreader software, 
though I can't seem to find a V5R2 version). 
There are some things in manuals that I just don't get. 
(I must have reached the limit of my meagre intellect). 
One of these is the SQL DESCRIBE statement. 
Your info will enable me attack the issue again.

BTW,  Whew
Your info seems to me that the trick is 'pointers'. 
No wonder I don't get it. Pointers is such a simple concept yet 
I did not get pointers until Leif, Scott and others turned 
on the little light in my head. The only time I use pointers is 
to process the data in user spaces that various APIs load, 
so I have to relearn them. 

I dont think I will use DESCRIBE.  It seems to be something that 
allows you to retrieve a DDS at runtime then map the data into 
a set of predefined fields, in your example using pointers. 
I never bothered to learn what the SQL precompiler actually does, 
but now I get some idea how and why the data structures are set 
up by the pre-compiler (asthe pre-compiler translates the SQL to API 
calls).

Also I have seen some discussion on using ALLOC and pointers 
to have the equivalent of unlimited dynamic array sizes but I 
doubt I will ever use such techniques. 
With RPG II the world was much simpler, now with ILE I need to 
draw a line as to how far I venture into 'real programming' as I 
balance the need to use certain techniques vs what the business actually 
needs. 

Frank Kolmann

PS. Setting up a DDS to map the DSPJRN output and 
CPYF  FMTOPT(*NOCHK), I can do.

>date: Wed, 12 Apr 2006 21:31:09 +0200
>from: "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, allocate enough 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.
>
>With 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.