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