Firebird insert hex values into tables (arithmetic exception)

Question

I'm having a problem when I try to insert values into a firebird database. I'm inserting values into the table using hex representations.

I'm doing an insert like this:

INSERT INTO ACCELEROMETER (PHYSICALID, XACCEL, YACCEL, ZACCEL, TIMEEPOCH,
    TIMEOFFSET, TSTAMP)
VALUES (2, 0x12DF,  0x8000, 0x12, 0x45612, 0x0, current_timestamp);

The problem occurs when I try to insert a value that is bigger than 0x7FFF. I know that all firebird variables are signed so whenever I try to insert a value like 0x8000 I'm supposed to get a value -32768, but I keep getting this message:

Engine Code : 335544321 Engine Message :

arithmetic exception, numeric overflow, or string truncation numeric value is out of range

It looks like when I try to insert 0x8000 firebird thinks it is 32768, which would obviously not work because it is bigger than what it is allowed for SMALLINT fields. I was under the impression that if I inserted hex values firebird would see that the most significant bit was set and it would know that this is a negative value. Am I missing something here ??

Thanks,

Here is the table:

RECREATE TABLE accelerometer ( 
  id int not null primary key,
  physicalId int not null, -- foreign key physicalDevice table
  xAccel SMALLINT,
  yAccel SMALLINT,
  zAccel SMALLINT,
  timeEpoch BIGINT,
  timeOffset INTEGER,
  tStamp timeStamp -- time stamp generated by the firebird db at the time a record is entered
);

zhangyangyu zhangyangyu · Accepted Answer · 2013-08-02T22:39:49

Firebird will see the sign bit but not for SMAILLINT. In range 0 .. 7FFFFFF it will be positive and in range 80000000 .. FFFFFFFF it will be negative. The values in the range will be INTEGER. You can prepend zeroes to force the value to be BIGINT. But there is no clue that it will behave the same for SMAILLINT. So the value 0x8000 should be 32768 and beyond the boundary of SMALLINT. You may refer here.

Firebird insert hex values into tables (arithmetic exception)

1 Answers