INTEGER inside user-defined datatype gives overflow if values > 2147483647

Discussion of open issues, suggestions and bugs regarding ODAC (Oracle Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
jdorlon
Posts: 202
Joined: Fri 05 Jan 2007 22:07

INTEGER inside user-defined datatype gives overflow if values > 2147483647

Post by jdorlon » Wed 07 Oct 2020 19:00

Hello Devart,

If I create a table like this, then Oracle has no problems with the large integer and neither does ODAC.

Code: Select all

CREATE TABLE A_INT_TEST
(
  COL1  NUMBER,
  COL2  INTEGER
);
Insert into A_INT_TEST
   (COL1, COL2)
 Values
   (3, 2147483648);
COMMIT;
If the INTEGER datatypes are inside a user-defined type, TSmartQuery has no problem as long as the values are less than 2147483647.

Code: Select all

drop TABLE A_MSG_IN purge;

drop type T_ID_OBJ;

CREATE OR REPLACE TYPE T_ID_OBJ is object 
( id integer,
  id_type_id integer, 
  bu_id integer);
/

CREATE TABLE A_MSG_IN
(
  COL1  NUMBER,
  COL2  T_ID_OBJ
);


SET DEFINE OFF;
Insert into A_MSG_IN
   (COL1, COL2)
 Values
   (1, T_ID_OBJ(1,2,3));
Insert into A_MSG_IN
   (COL1, COL2)
 Values
   (2, T_ID_OBJ(2,2147483647,2147483647));
COMMIT;
However, If I add a value greater than 2147483647, TSmartQuery now throws errors when I try to select the value and display it in a TDBGrid:

Code: Select all

Insert into A_MSG_IN
   (COL1, COL2)
 Values
   (3, T_ID_OBJ(4,2147483648,2147483648));
COMMIT;
I have tried mapping oraInteger to ftLargeInt but that did not help.

It seems like a safe fix is to comment out lines 1199....1203 in OraObjects.pas, so that Attribute.Datatype is set to dtLargeInt instead of dtInteger in TOraType.DescribeAttribute.

Is that safe?

Thank you.

jdorlon
Posts: 202
Joined: Fri 05 Jan 2007 22:07

Re: INTEGER inside user-defined datatype gives overflow if values > 2147483647

Post by jdorlon » Thu 08 Oct 2020 12:03

More info:

The max value for INT64 is 9223372036854775807,so even if we make the change that I suggested above, we can still cause an error in ODAC by inserting these values:

Insert into A_MSG_IN
(COL1, COL2)
Values
(4, T_ID_OBJ(4, 9223372036854775808,9223372036854775808));


Also, while we are in there....I noticed that OCI_TYPECODE_SMALLINT is missing from the CASE statement in TOraType.DescribeAttribute. As far as I can tell SMALLINT is the same as INTEGER in Oracle except in name. https://docs.oracle.com/cd/E11882_01/ap ... LNOCI16332

I now think it is best to comment out the ftInteger and ftLargeInt sections of that if...then, and treat these datatypes as ftFloat with scale of 0. It seems to work well if I do that. Please let me know what you think.

Thank you.

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: INTEGER inside user-defined datatype gives overflow if values > 2147483647

Post by MaximG » Tue 13 Oct 2020 08:39

Thank you for the information. The suggested changes can resolve the issue, but will affect other components in other cases. We'll try to find a different solution and consider the possibility to add DataTypeMapping for object types in future versions of the product.

jdorlon
Posts: 202
Joined: Fri 05 Jan 2007 22:07

Re: INTEGER inside user-defined datatype gives overflow if values > 2147483647

Post by jdorlon » Tue 13 Oct 2020 12:02

Ok, we will try this work around for now, but if you implement another solution, please update this thread.

Thanks,

John

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: INTEGER inside user-defined datatype gives overflow if values > 2147483647

Post by MaximG » Mon 19 Oct 2020 19:42

We'll notify you once we extend the functionality.

Post Reply