× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.