[SQL] Converting an hex value to a dec value
Marc Rauzier
I am unable to find a scalar function that converts an hexadecimal value
to a numeric value (exemple : how to convert 'FF' to 255).
Can someone help me ?
It must me so obvious that I cannot find it in RTFM !!
--
Cordialement/Best regards
Marc Rauzier
Christian Gstalder
Define a DS with your Hex (Char) field and a binary field that overlapped
the first field.
HTH
D TestDS DS
D Bin 5U 0
D Alpha 2 Overlay(Bin)
--
Christian Gstalder
Marc Rauzier a écrit dans le message ...
Charles R. Pence
> I am unable to find a scalar function that converts an hexadecimal value
>
> Can someone help me ?
>
> It must me so obvious that I cannot find it in RTFM !!
You could create an external function similar to the REXX
X2D (Hexadecimal to Decimal) function
>>--X2D(hexstring--.----.--)--><
'-,n-'
which returns the decimal representation of hexstring. The hexstring
is a string of hexadecimal characters.
The following REXX source will create an MI pgm which can be used
with caveats <eg. not tested beyond simple case, no error checking,
not commented> similar to SELECT X2D('00FF') AS "X2D" FROM QSYS2/QSQPTABL
which should return the INTEGER value result of 255.
*** Beginning of REXX source ***
address "*COMMAND"
"chkobj qgpl/x2d *pgm"
if rc<>0 then do;
MIsrc =,
"DCL SPCPTR InpStr@ PARM; ",
"DCL SPCPTR BinStr@ PARM; ",
"DCL SPCPTR InpInd@ PARM; ",
"DCL SPCPTR BinInd@ PARM; ",
"DCL SPCPTR SQLsta@ PARM; ",
"DCL SPCPTR SQLfnc@ PARM; ",
"DCL SPCPTR SQLsfc@ PARM; ",
"DCL SPCPTR SQLmsg@ PARM; ",
"DCL OL ParmLst( InpStr@ ",
" ,BinStr@ ",
" ,InpInd@ ",
" ,BinInd@ ",
" ,SQLsta@ ",
" ,SQLfnc@ ",
" ,SQLsfc@ ",
" ,SQLmsg@ ) ",
" PARM ",
" EXT ",
" MIN(2); ",
"DCL DD HexStrTm AUTO CHAR(08); ",
"DCL DD HexStrB4 DEF(HexStrTm) CHAR(08); ",
"DCL DD HexStrB0 DEF(HexStrTm) CHAR(04); ",
"DCL DD HexStrB2 DEF(HexStrTm) POS(5) CHAR(04); ",
"DCL DD InpStrTm BAS(InpStr@) CHAR(10); ",
"DCL DD InpStrLn DEF(InpStrTm) BIN(2); ",
"DCL DD InpStrB4 DEF(InpStrTm) POS(3) CHAR(08); ",
"DCL DD InpStrB2 DEF(InpStrB4) POS(1) CHAR(04); ",
"DCL DD BinStrTm BAS(BinStr@) CHAR(04); ",
"DCL DD BinStr4 DEF(BinStrTm) CHAR(04); ",
"DCL DD Bin2Zero AUTO CHAR(04) INIT('0000'); ",
"DCL DD Bin4Chrs AUTO BIN(2) INIT(8); ",
" ENTRY * (ParmLst) EXT; ",
" CMPNV(B) InpStrLn,Bin4Chrs/NEQ(NOTBIN4); ",
" CPYBLA HexStrB4,InpStrB4; ",
" B NOWBIN4; ",
" NOTBIN4: ",
" CPYBLA HexStrB0,Bin2Zero; ",
" CPYBLA HexStrB0,Bin2Zero; ",
" CPYBLA HexStrB2,InpStrB2; ",
" NOWBIN4: ",
" CVTCH BinStr4,HexStrB4; ",
" RTX *; ",
" PEND; "
Size = D2C(Length(MIsrc),4)
"QSYS/CALL QSYS/QPRCRTPG (&MIsrc &Size 'X2D QGPL ' ",
" 'heX2Decimal SQL External Function ' '*NONE' ",
" ' ' ' ' 'QSYSPRT *LIBL' X'00000001' '*ALL' ",
" '*LIST *REPLACE *NOCLRPASA ' X'00000003' ) "
"DSPSPLF QSYSPRT SPLNBR(*LAST)"
address "*EXECSQL"
execsql "set transaction isolation level no commit"
execsql "drop specific function QGPL/X2D "
execsql ,
"create function qgpl/x2d (hexstring varchar (08)) ",
" returns integer language pli specific x2d ",
" deterministic no sql returns null on null input ",
" parameter style sql external name qgpl/x2d "
end;
*** End of REXX source ***
You would probably want to pursue using an ILE routine instead, it can
likely even use the CVTCH; this was just easier for me.
Regards, Chuck
All comments provided "as is" with no warranties of any kind whatsoever.
Marc Rauzier
>Salut Marc,
Salut Christian
>
>Define a DS with your Hex (Char) field and a binary field that
>overlapped the first field.
>
>HTH
>
>D TestDS DS
>D Bin 5U 0
>D Alpha 2 Overlay(Bin)
>
[cut]
I was looking for an SQL function and I am afraid that I need to write
an external function like Charles Pence says in another post.
Marc Rauzier
>Marc Rauzier wrote:
>> I am unable to find a scalar function that converts an hexadecimal
>> value to a numeric value (example : how to convert 'FF' to 255).
>>
>> Can someone help me ?
>>
>> It must me so obvious that I cannot find it in RTFM !!
>
> You could create an external function similar to the REXX
> X2D (Hexadecimal to Decimal) function
>
[cut]
>
>Regards, Chuck
>All comments provided "as is" with no warranties of any kind
>whatsoever.
I thank you Chuck. I was afraid of a response like yours because the
AS/400 is at V3R2 release. But, I will try to transfer the data base
file on a V4R4 machine and use your technique.
Karl Hanson
>
> Marc Rauzier wrote:
> > I am unable to find a scalar function that converts an hexadecimal value
> > to a numeric value (example : how to convert 'FF' to 255).
> >
> > Can someone help me ?
> >
> > It must me so obvious that I cannot find it in RTFM !!
>
> You could create an external function similar to the REXX
> X2D (Hexadecimal to Decimal) function
>
> >>--X2D(hexstring--.----.--)--><
> '-,n-'
>
> You would probably want to pursue using an ILE routine instead, it can
> likely even use the CVTCH; this was just easier for me.
>
Reference to ILE CVTCH:
http://publib.boulder.ibm.com:80/cgi-bin/bookmgr/BOOKS/QBJADR00/1.20
... and inverse CVTHC:
http://publib.boulder.ibm.com:80/cgi-bin/bookmgr/BOOKS/QBJADR00/1.25
These and others in the ILE C/C++ MI Library Reference:
http://publib.boulder.ibm.com:80/cgi-bin/bookmgr/DOCNUM/SC09-2418-00
--
Karl Hanson
B Morris
function cvthc. Here's an RPG IV example, but you can call cvthc from
any ILE language (using binding directory QC2LE):
D cvtch pr EXTPROC('cvtch')
D tgt_hex * VALUE
D src_chars * VALUE
D src_bytes 10i 0 VALUE
D hexval s 2a INZ('FF')
D num_char_ds ds
D num 5i 0 inz(0)
D byte2 1a overlay(num:2)
C callp cvtch (%addr(byte2) : %addr(hexval) : 2)
* now num = 255
Just for the record, in V4R4 RPG IV, you could avoid the V3R2-necessary
data structure, and just use a 1-byte integer or unsigned integer, like
this:
D hexval s 2a INZ('FF')
D uns s 3u 0
D int s 3i 0
C callp cvtch (%addr(uns) : %addr(hexval) : 2)
* now uns = 255
C callp cvtch (%addr(int) : %addr(hexval) : 2)
* now int = -1
Barbara
Marc Rauzier
[cut]
Thank you everybody for your help.