Re: Retrieving formatted message in SQL

On 2/15/2019 2:51 PM, dlclark@xxxxxxxxxxxxxxxx wrote:

"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> wrote on 02/15/2019
11:45:13 AM:

I've written a UDTF by myself based on the QMHRTVM API.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

Birgitta, any chance I could get a copy of it for the purpose of
learning by example? I haven't written a UDTF before. I also wonder how
you're using the API from SQL?

I apologise for being so brief; I'm in the middle of a bunch of stuff
:-/ Here is a proof of concept that returns n rows of QRPGLESRC:

h* SQL User Defined Table Function proof of concept
h/copy qrpglesrc,stdhspec
hCCSID(*exact)

* create function buck.SQLUDTF(rows_to_retrieve int)
* returns table
* ( source_sequence numeric (6, 2),
* source_date numeric (6, 0),
* source_data char (100) )
* language rpgle
* parameter style db2sql
* not deterministic
* reads sql data
* returns null on null input
* external action
* not fenced
* program type main
* no final call
* disallow parallel
* no scratchpad
* external name BUCK.SQLUDTF
* cardinality 100;
*
* select * from table(buck.SQLUDTF(4)) as source;

dSQLUDTF pr
d*input
d nbrToRtv_in 10i 0 const
d*output
d seq 6s 2
d date 6s 0
d source_data 100
d*input null map
d nbrToRtv_in_null...
d like(IS_NULL) const
d*output null map
d seq_null...
d like(IS_NULL)
d date_null...
d like(IS_NULL)
d source_data_null...
d like(IS_NULL)
d*SQL feedback
d sqlstate_out 5a
d functname 517 const options(*varsize) varying
d specname 128 const options(*varsize) varying
d errormsg 70 options(*varsize) varying
d calltype 10i 0

dSQLUDTF pi
d*input
d nbrToRtv_in 10i 0 const
d*output
d seq 6s 2
d date 6s 0
d source_data 100
d*input null map
d nbrToRtv_in_null...
d like(IS_NULL) const
d*output null map
d seq_null...
d like(IS_NULL)
d date_null...
d like(IS_NULL)
d source_data_null...
d like(IS_NULL)
d*SQL feedback
d sqlstate_out 5a
d functname 517 const options(*varsize) varying
d specname 128 const options(*varsize) varying
d errormsg 70 options(*varsize) varying
d calltype 10i 0

d* work fields
d SQL_NORMAL s 5 inz('00000')
d SQL_NODATA s 5 inz('02000')
d IS_NULL s 5i 0 inz(-1)
d NOT_NULL s 5i 0 inz(0)
d rows_returned s 10i 0 inz
d r s 10i 0

c/free

exec sql
set option naming = *sys, closqlcsr = *endactgrp, commit = *none,
dynusrprf=*owner;

// External UDTFs like this get called many times for a given SQL
statement
// once for open (-1)
// many times each for fetch (0)
// once for close (1)
// WE tell the database when we're done by setting the outbound
SQLstate

sqlstate_out = SQL_NORMAL;

select;
when calltype = -1;
exsr UDTF_open;
when calltype = 0;
exsr UDTF_fetch;
when calltype = 1;
exsr UDTF_close;
*inlr = *on;
endsl;

// No LR until we're really done done.
return;

//------------------------------------------------------------------
begsr UDTF_open;

// number of rows retrieved so far
r = 0;

exec sql
declare SOURCE_RECORDS cursor for
select SRCSEQ, SRCDAT, SRCDTA
from QRPGLESRC
order by SRCSEQ;

exec sql
open SOURCE_RECORDS;

// if abnormal, forward the SQLSTT to the database manager and quit
// really needs some error handling but this is a demo
if sqlstate <> SQL_NORMAL;
sqlstate_out = sqlstate;
dump(a) 'open()';
*inlr = *on;
endif;

endsr;

//------------------------------------------------------------------
begsr UDTF_fetch;

exec sql
fetch next
from SOURCE_RECORDS
into :seq, :date, :source_data;

// tell the database manager how this FETCH went
sqlstate_out = sqlstt;
r += 1;

if sqlstt = SQL_NORMAL;
rows_returned = sqler3;
// we got rows; set all columns to NOT NULL
seq_null = NOT_NULL;
date_null = NOT_NULL;
source_data_null = NOT_NULL;
else;
if sqlstt = SQL_NODATA;
// return to database manager with NODATA set
// the next call to the UDTF will be to CLOSE
return;
else;
// needs real error handling!
dump(a) 'fetch()';
return;
endif;
endif;

// if we have read the requested number of rows, exit early
if nbrToRtv_in_null = NOT_NULL and
r > nbrToRtv_in;
sqlstate_out = SQL_NODATA;
endif;

if r = 1;
// for kicks, set this row to NULL
seq_null = IS_NULL;
date_null = IS_NULL;
endif;

endsr;


//------------------------------------------------------------------
begsr UDTF_close;
exec sql
close SOURCE_RECORDS;
endsr;