Re: Dynamic SQL Fetch With Packed Data via the SQLDA

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

30p30 is 30 digits, and 30 of them are to the right of the decimal point 
- is that what you really want?

Also, what release are you on? The limits are different for different 
releases.

Vern

RNewton@xxxxxxxxxxxx wrote:
> 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.
>