CODEWITHSUNDEEP
oracle-databaseinformix
hudi
hudi

Reputation: 16535

How to rewrite column raw(18) to informix

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)

Upvotes: 1

Views: 379

Answers (3)

ceinmart
ceinmart

Reputation: 1836

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

Upvotes: 1

hudi
hudi

Reputation: 16535

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

Upvotes: 0

Michał Niklas
Michał Niklas

Reputation: 54322

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.

Upvotes: 0

Related Questions