1

in my application I have oracle column:

COLUMN_NAME RAW(18) not null

How should I rewrite it to informix ? I found that similar type is byte but I need also create index on this column which is not allowed on byte (in oracle on raw it is possible)

hudi
  • 15,555
  • 47
  • 142
  • 246

3 Answers3

1

I'm supposing are you working with version 12.10 of Informix.
This is little weird since at version 11.70 I'm able to use this same command without need to register the datablade.

Anyway, try this : 

execute function SYSBldPrepare('binaryudt.*','create');

(expression)

           0

1 row(s) retrieved.


CREATE TABLE  bindata_test (int_col integer, bin_col binaryvar) ;
Table created.

At the onstat -m (or select * from sysmaster:sysonlinelog order by offset desc) you should see a message like this :

11:56:57  Unloading Module <$INFORMIXDIR/extend/ifxmngr/ifxmngr.bld>
11:56:57  The C Language Module <$INFORMIXDIR/extend/ifxmngr/ifxmngr.bld> unloaded
ceinmart
  • 1,799
  • 10
  • 16
0

From your comments it seems that you store bytes in this columns. Those bytes create UUID and in code you use bytes encoded in hexadecimal.

To store such data I would use VARCHAR(36). In hex encoding you need two chars for each byte so I used 36 as length (18 * 2).

In this solution your INSERT do not change. SELECT probably also do not change. Indexing is easy and works. For SELECT results you will have to decode hex string into bytes. You can do in in application code, or use stored function.

With other datatype like BYTE indexing may not work. I am also not sure if all drivers (JDBC, ODBC) can use text literal in SQL statements (INSERT/SELECT etc). Some drivers must use PreparedStatement with binary type.

Michał Niklas
  • 53,067
  • 18
  • 70
  • 114
  • there isnt better solution ? I want to avoid this solution because then I should also rewrite my app. Where I have byte[] I need to rewrite it to String and add conversion between byte and string in my app – hudi Mar 18 '14 at 11:26
  • But your INSERT uses string literal, so at least half of work is done. As for SELECT results you will have to decode hex string into bytes. You can do in in application code, or use stored function. – Michał Niklas Mar 18 '14 at 11:34
  • ok and when I dont need to index column just put there some uuid, still is better options to user varchar instead of byte ? – hudi Mar 18 '14 at 11:46
  • If you use `byte` then you probably will have to use PreparedStatement for each INSERT/UPDATE/SELECT. See my question: http://stackoverflow.com/questions/483284/consistent-method-of-inserting-text-column-to-informix-database-using-jdbc-and-o . It is about other Informix blob type, but problem with string literals for binary types is similar. – Michał Niklas Mar 18 '14 at 12:38
  • btw when I must use varchar for raw(18) I will prefer varchar(24) and store it as base64 value not as hex with lenght 36 to save some space – hudi Mar 18 '14 at 14:06
  • Of course you can encode it using base64 but from your comment it seems that you already use hex encoding. If it is not huge database then I would prefer already working string representation. – Michał Niklas Mar 18 '14 at 18:09
0

I found in new version of Informix data type: binaryvar and binary18: http://pic.dhe.ibm.com/infocenter/informix/v121/index.jsp?topic=%2Fcom.ibm.dbext.doc%2Fids_dbxt_386.htm I dont try it yet but I think raw(18) should be replace byt these data types

But I dont know how to create table with with column. When I try:

CREATE TABLE  bindata_test (int_col integer, bin_col binaryvar)

it return error: 

Type (binaryvar) not found.

My database is logged and SELECT name,is_ansi FROM sysmaster:sysdatabases return 0 so it should work

hudi
  • 15,555
  • 47
  • 142
  • 246
  • this is not an answer... please edit your answer including this information with a "EDIT" text before to make clear you have add information later... – ceinmart Apr 08 '14 at 14:54