× 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,

As you want to use dynamic SQL wirth SQLDA you reference a programme
variable to recieve the date i n with SQLVAR-DS.

you tell the SQLVAR whar the address of that programme variable is and give
it the length of that variable. For zoned and pack data fields you have to
put in avalue that is equal to :(256 * precision) + scale; precision is the
actual length of the numeric field, scale is the number of decimals (if I
do not mix those). So for your 30P9 packed field you have to give an SQL
length of (256 * 30) + 9 = 7689, before fetching a row. It doesnot matter
the length of the column in the result set is actiually shorter.

With regards,
Carel Teijgeler.

*********** REPLY SEPARATOR ***********

On 17-11-2009 at 9:21 RNewton@xxxxxxxxxxxx wrote:

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.


--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.






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.
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.




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.