Wednesday, August 23, 2023

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.

1 comment:

  1. Hi Simon, maybe consider retiring the DSPLY opcode and use SND-MSG instead. It is available at least back to V7R2 (or is it V7R3? IDK).


To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.