Re: Dynamic SQL Fetch With Packed Data via the SQLDA -- RPG400-L

Carel,

Aha, that makes perfect sense. I missed that in the docs I guess, I was
just looking at the SQLLEN in SQLVAR as information rather than something
I should be setting. So when I describe the SQL statement, it is loaded
with the format of what to expect from that particular field, then at the
same point where I set the pointer to my storage variable, I should change
SQLLEN to match the definition of my storage var instead. Again, that
makes good sense; I'm making that change now.

Thanks for the help Carel! That was a great explanation. Just what I
needed. I used BIFs (%len and %decpos) instead of hard coding the size of
the storage variable. Just makes it more generic so it can be used for any
size storage bin.

So here is what I have now... (this works perfectly). Thanks again!

*Numeric Data Type Holders
d packedDec s 30p 9 dim(200) inz
d zonedDec s 30s 9 dim(200) inz

//Loop through each column and define where to store the data from
the
// result set when doing a fetch
for i = 1 by 1 to SQLD;
SQLVAR = SQL_VAR(i);
select;
when SQLTYPE = 484 or SQLTYPE = 485; //Packed Decimal
SQLDATA = %addr(packedDec(i));
SQLLEN = (256 * %len(packedDec(i))) + %decpos(packedDec(i));
when SQLTYPE = 488 or SQLTYPE = 489; //Zoned Decimal
SQLDATA = %addr(zonedDec(i));
SQLLEN = (256 * %len(zonedDec(i))) + %decpos(zonedDec(i));
endsl;

//Save the changes to the data allocation setup
SQL_VAR(i) = SQLVAR;
endfor;

//Loop through each column in the fetched result set
for i = 1 by 1 to SQLD;
SQLVAR = SQL_VAR(i);
//Convert all numerics to be stored as a single data type
(float32)
select;
when SQLTYPE = 484 or SQLTYPE = 485; //Packed
Decimal
dataRow(i).safloat = packedDec(i);
when SQLTYPE = 488 or SQLTYPE = 489; //Zoned Decimal
dataRow(i).safloat = zonedDec(i);
endsl;
//Write my dataRow ds to the storage table
endfor;

Robert Newton
EDPS
Electronic Data Processing Services
System Owner
804-353-1900 x2256
rnewton@xxxxxxxxxxxx

From:
"Carel Teijgeler" <coteijgeler@xxxxxxxxx>
To:
rpg400-l@xxxxxxxxxxxx
Date:
11/17/2009 01:37 PM
Subject:
Re: Dynamic SQL Fetch With Packed Data via the SQLDA
Sent by:
rpg400-l-bounces@xxxxxxxxxxxx

Robert,

As you want to use dynamic SQL wirth SQLDA you reference a programme
variable to recieve the date i n with SQLVAR-DS.

you tell the SQLVAR whar the address of that programme variable is and
give
it the length of that variable. For zoned and pack data fields you have to
put in avalue that is equal to :(256 * precision) + scale; precision is
the
actual length of the numeric field, scale is the number of decimals (if I
do not mix those). So for your 30P9 packed field you have to give an SQL
length of (256 * 30) + 9 = 7689, before fetching a row. It doesnot matter
the length of the column in the result set is actiually shorter.

With regards,
Carel Teijgeler.

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

On 17-11-2009 at 9:21 RNewton@xxxxxxxxxxxx wrote:

Carel,

Thank you for the explanation. I think you're right that I was
misunderstanding how to define the packed field on the D spec. I thought
that by specifying a 30p30, that the field had a precision of 30 and

scale

of 30, but it sounds like instead it's merely a number with 30 decimal
positions and that's it. That explains the behavior. I will define it as
30p 9 and see how that does.

UPDATE: Just defined as a 30p 9 but I still get the same problem. I don't

have to define a packed number with every possible scale and precision do

I?

Thanks for the help!

Robert Newton
EDPS
Electronic Data Processing Services
System Owner
804-353-1900 x2256
rnewton@xxxxxxxxxxxx

From:
"Carel Teijgeler" <coteijgeler@xxxxxxxxx>
To:
rpg400-l@xxxxxxxxxxxx
Date:
11/16/2009 06:27 PM
Subject:
Re: Dynamic SQL Fetch With Packed Data via the SQLDA
Sent by:
rpg400-l-bounces@xxxxxxxxxxxx

Robert,

I use dynamic SQL and the SQLDA regularly this way. But you make some
misassumptions.

When SQL returns this metadata for a packed filed PAGES - SQLTYPE:485,
SQLLEN:7936
it should be calculated as 7936/256 = 31, no remainder so no decimals.
This
is correct, as you did not assign a specific length for this column in

the

result set. If the field length is 7P9 (which is strange: more decimals
than the actual length), then the SQLLEN should have been 1801.

I see you defined a return variable of 30P30, which means 30 decimals
only,
no digits before the decimal point. I would declare the result variable

30P9 or something like that, then the result would be (for a value of 59)
59.000000000.

It does not matter the actual retrieve length of the data and the length
of
the return variable., SQL will use that. So to start with assign a length
for the coulmns in the result set. (DECIMAL(SUM(myPages),7,3) as PAGES),
use a result variable, that has less decimals and space for digits left
off
the decimal point.

With regards,
Carel Teijgeler

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

On 16-11-2009 at 17:38 RNewton@xxxxxxxxxxxx wrote:

I'm using the SQLDA to fetch from dynamic SQL where the result set is

not

known until run time. This is working fine for most data types

except for packed numeric data. I am currently setting the destination

fields to 30p30 to catch the largest possible values, but when I run the

program the values are getting offset when fetched from the SQL

statement
result set into those fields.

How should I define my storage field for packed or zoned data?

The following statement is my current example:

SELECT myCategory as CATEGORY,
COUNT(myCategory) as STATEMENTS,
SUM(myPages) as PAGES
FROM myTable
GROUP BY myCategory

An excerpt from my program:
===========================

d packedDec s 30p30 dim(200) inz
d zonedDec s 30s30 dim(200) inz

...

for i = 1 by 1 to SQLD;
SQLVAR = SQL_VAR(i);
select;
when SQLTYPE = 484 or SQLTYPE = 485; //Packed Decimal
SQLDATA = %addr(packedDec(i));
when SQLTYPE = 488 or SQLTYPE = 489; //Zoned Decimal
SQLDATA = %addr(zonedDec(i));
endsl;
SQL_VAR(i) = SQLVAR;
endfor;

...
=============================

When I run the program for the example statement, the field definitions

in
the SQLDA are like so:

CATEGORY - SQLTYPE:453, SQLLEN:1
STATEMENTS - SQLTYPE:496, SQLLEN:4
PAGES - SQLTYPE:485, SQLLEN:7936

According to the documentation, the SQLLEN field for packed decimal

fields
holds the precision and scale in the 1st and second bytes. So that tells

me the length of the packed decimal field that is holding the result of

the SUM from the SQL statement is 7p 9. So it should fit fine in my work

field of 30p30, right?

When I run the example above and the value in the packed field of the

result set (the result of the SUM scalar) is 59, I end up with a value in

my 30p30 field of .000000000000000000000000000059

If anyone can offer insight, I would greatly appreciate it.

--
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.

This communication and any transmitted documents are intended to be
confidential. If there is a problem with this transmission, please

contact

the sender. If the reader of this message is not the intended recipient,
or the employee or agent responsible to deliver it to the intended
recipient, you are hereby notified that any dissemination, distribution

copying of this communication is strictly prohibited.
--
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.