RE: Dynamic SQL fetch into an array

Not sure why your search did not turn up this thread:
http://archive.midrange.com/rpg400-l/200908/msg00313.html

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"The major difference between a thing that might go wrong and a thing that
cannot possibly go wrong is that when a thing that cannot possibly go wrong
goes wrong it usually turns out to be impossible to get at or repair."
-- Douglas Adams

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
--
This is the RPG programming on the IBM i / System i (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.