SQL zoned_to_buffer

zoned_to_buffer.sql

-- Packing a numeric into a zoned buffer by using C runtime function.
-- The magic is done by the QXXDTOZ api and not external dependencies
-- 
-- Simply paste this gist into ACS SQL and select "run all" 
--
-- Note: I am using library QUSRSYS. I suggest you put it into your own tool library
-- It is a cool example how far you can go with SQL: Have fun - 
-- (C) Niels Liisberg 2020
--
-- This gist is distributed on an "as is" basis, without warranties 
-- or conditions of any kind, either express or implied.
----------------------------------------------------------------------------------------------

call qcmdexc ('crtsrcpf FILE(QTEMP/C) MBR(C) RCDLEN(200)');
delete from qtemp.c;
insert into qtemp.c (srcdta) values 
    ('#include <xxcvt.h>'),
    ('QXXDTOZ(MAIN.RESULT.DAT, ZONED_TO_BUFFER.DIGITS, ZONED_TO_BUFFER.FRACTION , ZONED_TO_BUFFER.VALUE);'),
    ('MAIN.RESULT.LEN =  ZONED_TO_BUFFER.DIGITS;')
;
 

create or replace function  qusrsys.zoned_to_buffer 
( 
    digits    smallint,
    fraction  smallint,
    value     double
) 
returns varchar(64) ccsid 65535
no external action 
set option output=*print, commit=*none, dbgview = *list
main: begin
    declare result varchar(64) default ''; 
    -- "result" are in the C-code as MAIN.RESULT.DAT and MAIN.RESULT.LEN 
    include qtemp/c(c);
    return result;
end;

-- Usecases: 
values (
   zoned_to_buffer (10 , 2 , -123)
)