Convert character to hexadecimal with SQL

A couple of weeks ago I wrote about using Machine Instruction, MI, procedures in RPG to convert a character string to hexadecimal, and then convert it back again to character. Several people sent me examples of converting character to hexadecimal using a SQL statement, but no-one sent me the statement to convert the hexadecimal to character.

I decided to give an example of how I would do what I did last week in SQL. I embedded the SQL statements into a RPG program as this would be the way I would likely use it.

Below is my example program:

01 **free 02 dcl-s String varchar(15) inz('Simon was here') ; 03 dcl-s Hex varchar(30) ; 04 dcl-s Result like(String) ; 05 exec sql SET :Hex = HEX(:String) ; 06 dsply Hex ; 07 exec sql SET :Result = CHAR(VARBINARY_FORMAT(:Hex)) ; 08 dsply Result ;

Line 1: In 2023 I hope you all are writing completely free format RPG.

Line 2: I am defining this variable length character variable to contain the string I will be converting. I am initializing this variable with the string I want to use.

Line 3: This variable will contain the hexadecimal value of the character string. Therefore, it must be twice the length of the character variable on line 1.

Line 4: I will be using this variable to contain the result of the hexadecimal to character conversion.

Line 5: I use the SQL Set statement to update the variable Hex with the value returned from the HEX SQL scalar function when it converts the characters in the variable String. Notice how both the variable names are prefixed with a colon ( : ), this "tells" the SQL precompiler that this is RPG program variable.

Line 6: I use the Display operation code, DSPLY, to show the converted hexadecimal value.

DSPLY E28994969540A681A24088859985

It took me some time to find a way to convert the hexadecimal value back into character. The CHAR and TO_CHAR scalar functions did not perform the conversion. I found a way by converting the hexadecimal to binary, and then convert that to character.

Line 7: I use the VARBINARY_FORMAT scalar function to convert the hexadecimal string in the variable Hex. Then the CHAR scalar function converts the binary to character.

Line 8: I display the value in the variable Result.

DSPLY Simon was here

Personally I would use these statements in place of calling the MI procedures as being less RPG code IMHO it would be easier for someone else to understand.

 

This article was written for IBM i 7.5, and should work for some earlier releases too.