Re: Dynamic SQL Fetch With Packed Data via the SQLDA

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