Re: Problems with auto data transform when modifying column INT to INT8.

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/