I am modifying a column type from INT to INT8 the server obviously calls
some conversion function and sets the new INT8 type to reflect the same
value.
HOWEVER my inteded use for INT was to store 32 bit values (interpreted by
my processes as unsigned). My intended use for INT8 is the same only 64
bits.

Unfortunately this modification (and conversion) does not maintain the bit
pattern and interpreted unsigned value.

eg:

INT = -1 ( 0xFFFFFFFF )
INT8 still = -1 but I suspect one of two kinds of content either:

data[1] 0x00000000
data[0] 0x00000001
sign = -1 ( if sign means the sign of the bits' absolute value )

OR

data[1] 0xFFFFFFFF
data[0] 0xFFFFFFFF
sign = -1 ( if sign means 'this is how I would like the user (some
program / dbaccess / etc ) to interpret (in 2s comp)' )

BUT........... ( I think )

The bit pattern certainly wonte be

data[1] 0x00000000
data[0] 0xFFFFFFFF
sign = 1

Is there a way, when I do my modify, that I can get informix to treat the
old value in the INT as an unsigned 32 bit number and set the new type INT8
accordingly ?

Andrew H




sending to informix-list

Andrew Hardy wrote:

> I am modifying a column type from INT to INT8 the server obviously
> calls some conversion function and sets the new INT8 type to
> reflect the same value. HOWEVER my inteded use for INT was to store
> 32 bit values (interpreted by my processes as unsigned). My
> intended use for INT8 is the same only 64 bits.

Hi Andrew,

As you know from our discussions about int8 and long long, Informix
thinks both INT and INT8 are signed quantities. That makes it hard to
maintain the fiction in the presence of negative values (32nd bit set).

> Unfortunately this modification (and conversion) does not maintain
> the bit pattern and interpreted unsigned value. eg:
>
> INT = -1 ( 0xFFFFFFFF )
> INT8 still = -1 but I suspect one of two kinds of content either:
>
> data[1] 0x00000000
> data[0] 0x00000001
> sign = -1 ( if sign means the sign of the bits' absolute value )
>
> OR
>
> data[1] 0xFFFFFFFF
> data[0] 0xFFFFFFFF
> sign = -1 ( if sign means 'this is how I would like the user (some
> program / dbaccess / etc ) to interpret (in 2s comp)' )
>
> BUT........... ( I think )
>
> The bit pattern certainly wonte be
>
> data[1] 0x00000000
> data[0] 0xFFFFFFFF
> sign = 1
>
> Is there a way, when I do my modify, that I can get informix to treat the
> old value in the INT as an unsigned 32 bit number and set the new type INT8
> accordingly ?

Let's see:
0xFFFFFFFF is -1 (signed).
0x80000000 is a large in magnitude negative value.

So, I'd expect to do this in two stages:

(a) Convert to DECIMAL(10,0) - big enough for 4GB.
(b) Adjust negative values by adding 2**32 to them.
(c) Convert to INT8 - all values are positive and transfer accurately.

If you must do it all at once, then I guess CASE is your friend (as
long as you've got enough disk space for two copies of the table - in
place alter isn't going to be an option). You then need to create the
new table, and do something along the lines of:

INSERT INTO NewTable
SELECT ...,
CASE WHEN i4 < 0 THEN i4 + 4GB OTHERWISE i4 END,
...
FROM OldTable.

You'd need to do some manual bashing to get the syntax right and
specify 4GB accurately, but you probably get the drift of what I'm
suggesting.

Hmmm...let's reread your question. That's a good point about -1 being
stored in a totally non-obvious way. Your first alternative is
plausible; that's what I'd expect the server to do. But my trickery
with DECIMAL(10,0) does deal with that, so you should be in with at
least a fighting chance.

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/