Re: Dynamic SQL Fetch With Packed Data via the SQLDA

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