Dynamic SQL Fetch With Packed Data via the SQLDA

Hi all,

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.

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



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