On 11-Jun-2010 15:07, Timothy Adair wrote:
After beating my head against this issue, I've searched the
archives and the Internet with no luck. I am attempting to fetch
a record in embedded SQL and this record includes a 7-element
array. I'm fetching from a program-described file (QS36F).
There is no DIMensioned data type in SQL.
I've oversimplified the code below - I just need to get the
basicconcept.
d SchRecord ds
d c@ 1 5 0
d typi 6 6 0
d fschi 7 7 0
d cyci 8 8 0
d arrRT 3 0 dim(7)
sqlstmt = 'select +
substr(K00001,1,5) as c@, +
substr(K00001,6,1) as typi, +
substr(K00001,7,1) as fschi, +
substr(K00001,8,1) as cyci, +
substr(F00001,1,21) as arrRT +
from qs36f/"DS.SCH"
The data types above are declared numeric, but the SQL is
generating CHAR string results. If all positive zoned integer data,
then change the type to S [zoned] or A [alphanumeric]. The latter
part of the record is just a string, just like the former portion;
i.e. each portion separated with the SUBSTR() scalar function.
Replace the DIM with an alpha field of the total length of the
character string that will be extracted with the SQL. Then redefine
that string as contiguous /array/ elements.
<<SNIP>>
The other fields read in properly but "arrRT" reads in as a
single 3-digit field instead of an array of seven 3-digit
elements. I defined it as an array in the DS - why does SQL
ignore that?
I have to do this as dynamic because of the "where" and "order
by" clauses that are needed.
FWiW: some creative solutions may allow embedded to replace the
dynamic.
Any thoughts are appreciated.
I do not have access to the S/36E to use BLDFILE, so the
following example dynamically generates an effective equivalent to
the given example, inserts a sample row, and retrieves the data.
The assumption is that the data is zoned. If packed numeric data in
the file, then the substring has to operation against the HEX() of
the storage, and depending on possible issues with bad decimal data
or sign [negative, preferred positive\negative], the expression can
be complicated enough to justify a UDF or VIEW to encapsulate the
expression.
Anyhow, consider the following code which maps the contiguous
storage of character data as the array using based storage:
<code>
d templib S 10A
d tempfile S 10A
d sqlstmt S 300A
// Format of returned SQL record.
d SchRecord ds
d cX 1 5S 0
d typi 6 6S 0
d fschi 7 7S 0
d cyci 8 8S 0
d RTdata 9 29A
// remap RTdata as an array
d arrRT@ S *
d arrRT S 3S00 dim(7) based(arrRT@)
/free
arrRT@ = %Addr(RTdata);
tempfile = 'RL29';
templib = 'QTEMP';
sqlstmt = 'select +
substr(K00001, 1, 5) as cX +
,substr(K00001, 6, 1) as typi +
,substr(K00001, 7, 1) as fschi +
,substr(K00001, 8, 1) as cyci +
,substr(F00001, 1, 21) as arrRT +
from ' + templib + '/' + tempfile ;
Exec SQL
Set Option Commit=*NONE, DatFmt=*ISO ;
Exec SQL
drop table qtemp/rl29;
Exec SQL
create table qtemp/rl29
(K00001 char(08), F00001 char(21));
Exec SQL
insert into qtemp/rl29
values('55555123', '111222333444555666777');
exec sql
prepare P1 from :sqlstmt;
exec sql
declare C1 cursor for P1;
exec sql
open C1;
exec sql
fetch C1 into :SchRecord;
exec sql
close C1;
*inlr = *on;
/end-free
</code>
Regards, Chuck
midrange.com
