Rewrite stored procedure as an SQL UDF

[kadler]

A UDF does not require an SQL pre-compiler or embedded SQL.

[alanseiden]

@kadler We were thinking UDF rather than UDTF, since XMLSERVICE returns a single value (XML string). What do you think?
Incidentally, we PHP Toolkit maintainers were discussing the advantages of UDF over the current output parameter (flaky, as you well know) / result set tactics: zendtech/IbmiToolkit#131 and we would be able to simplify PHP Toolkit logic with a UDF transport. @NattyNarwhal @chukShirley

[kadler]

Not sure why I said UDTF. Probably because all the SQL services I wrote were UDTFs and also I'm used to this other kind of UDF.

But yes, since it returns a single value it should be a scalar UDF. Something like select XMLSERVICE('<xml>input here</xml>') from sysibm.sysdummy1.

[NattyNarwhal]

Some thoughts:

• The code is not really idiomatic RPG, but idiomatic C written in RPG. Who else is calling strlen and memset from RPG? I suspect any new modules writing are worth just writing in C instead (keeping RPG modules for compat, and because the calling convention is free between them.
• The calling conventions for returning VARCHARs from external functions is unclear in the documentation, and the examples for writing external UDFs are either simplistic or frighteningly complex. I'm not sure on allocation/lifetime responsibility, in particular.

[kadler]

The code is not really idiomatic RPG, but idiomatic C written in RPG.

Any XMLSERVICE replacement we would write in C. Hardly anyone in IBM has RPG skills outside the compiler team and it's pretty clear that the theory of getting more outside contributions due to being written in RPG has not panned out (some of that is due to it being byzantine Tonycode, but still).

The calling conventions for returning VARCHARs from external functions is unclear in the documentation

VARCHARs for UDFs with "LANGUAGE C" is a standard C null terminated string (though oddly VARGRAPHICs are not null-terminated wchar_t or SQLWCHAR strings, but structs with length prefix).

I'm not sure on allocation/lifetime responsibility, in particular.

The return value of a UDF is not the return value of a function, but instead is a parameter passed to you just like other parameters. This parameter is a buffer of the size specified by SQL create function statement and its lifetime is owned by the caller. If you need to store data between calls to OPEN/FETCH/CLOSE you can use a scratchpad - either of a required size or allocate the memory and store the pointer in the scratchpad. You can also enable final call which adds two more steps: FIRST/FINAL which happen once per statement (while OPEN/FETCH/CLOSE happen per-invocation of the UDF within a statement).