Informix

IDS 14.10.FC7W1 on RHEL 8.5

I did a search in the forum for 'binary18' and 'binaryvar', both returned "No results", so I may be out of luck, but here goes.

I want to store data in a binary18 column.  I created a varchar(38) variable that I used to create a string representation of the data to be stored, along with a "0x" prefix.  I created a function to take a varchar input and convert it to binary18.  I used string manipulation and concatenation functions to build the value that I wanted, then assigned the final result to the binary18 variable.  This works as expected.

I then attempted to write a companion function to take a binary18 input and convert it to char or varchar.  This does NOT work.  Depending on how I go about it, I get a few different errors.  If I try to simply assign the binary18 value to a char(38) or varchar(38) column, I get -9634 'No cast from binary18 to char'.  If I try to use RIGHT() or LEFT() to strip out the first or last couple of bytes, I get -674 'Routine (right) can not be resolved'.  If I try SUBSTR(), I get -1260 'It is not possible to convert between the specified types'.  

I understand the meaning of these various errors.  I also see in the Database Extensions Guide this note on restrictions of the binary18/binaryvary datatypes:

• • The only arithmetic operations that are supported are the bitwise operators: bit_and(), bit_or(), bit_xor(), and bit_complement().
  • The LIKE and MATCHES conditions are not supported.

The restrictions do not state anything about not being able to do string manipulation or cast to a char/varchar value.  Since there is a defined cast from char/varchar to binary18, I was expecting the reverse to also be defined.  Obviously, it's not.

So my question - is there anything that you can do with binary18/binaryvar datatypes other than the four bit_* functions listed above?  I really wanted to be able to subdivide the value into smaller chunks.  Ideally, we could retrieve individual bytes, or blocks of bytes.  Or simply copy it to a varchar and play with the bytes there (but we already know that this does not work).

Anyone using these datatypes?

Thanks.

------------------------------
Mark Collins
------------------------------

#Informix