Re: Convert hex string to integer (bigint) in SQL..

Responses inline.

Regards, Chuck

Charles Wilt wrote:

Yeah my preference would be to use the MI function, but I need
this quick & dirty on a production system. So putting together an
ILE RPG program to use as an external stored procedure is out.

And a q&d MI program as external UDF would be better how? Barbara gave information on making RPG utilize the 'cvtch' MI instruction. That could be used as a basis for an external UDF.

ILE CL might be an option, I'll have to see about REXX, not sure
if I can use it or not.

REXX is probably not a good choice. CL is an option only if the SQL feature for external LANGUAGE CL [or by lying] has support for the BIGINT; albeit the CAST from a *DEC declaration in the CLLE should suffice.

Correct me if I'm wrong, but the REXX source you provided a link
to, handles a 8 byte hex string --> 4 byte integer correct?

I don't suppose you could tell me what I'd need to change to
handle a 16 byte hex string --> 8 byte big int could you? :)

Otherwise, I need to learn REXX quickly! :)

Notice that the REXX source is just a means to create both an MI program and the external UDF which defines the interface to that MI program; i.e. the changes that would be required are to the MI program source [which is a literal string assigned to a REXX variable], not really the REXX code itself [except the SQL string processed as *EXECSQL].

That version of the code supports either 4-byte or 2-byte depending on the length of the input string, of either 8-byte or 4-byte [but with no error checking; just assumes if not one then must be the other]. I could try to change the MI source to process only an eight byte integer, and update the external user defined function accordingly; i.e. SQL BIGINT data type.

On Fri, Feb 26, 2010 at 6:32 PM, CRPence wrote:

Although the function _could_ be written in SQL, as in the
example found at the noted link, I would expect using an external
function might be a better option where both a portable function is
not required and the amount of work performed per invocation is best
reduced [as compared to the HexToInt SQL UDF for which I suspect its
implementation is probably not very efficient as compared to using
the MI instruction].
http://groups.google.com/group/comp.sys.ibm.as400.misc/browse_thread/thread/d2312748add3faf3/ca949144a21d9e6b