Problem Integer Fields in TUniQuery

[ericalves]

I have a problem to add fields in the Uniquery. My UniConnection is as PrecisionInteger = 10. Using Oracle.

I create the table as follows (without registration) and all fields are as IntegerField:

CREATE TABLE TESTE_UNIDAC(
CAMPO1 NUMBER(10),
CAMPO2 NUMBER(10) NOT NULL,
CAMPO3 NUMBER(10) DEFAULT NULL NOT NULL,
CAMPO4 NUMBER(10) DEFAULT 0 NOT NULL,
CAMPO5 NUMBER(10) DEFAULT 0,
CAMPO6 NUMBER(10) DEFAULT NULL);

I create the table as follows and insert some records:

CREATE TABLE TESTE_UNIDAC(CAMPO1 NUMBER(10));
INSERT INTO TESTE_UNIDAC(CAMPO1) VALUES(1234567);
INSERT INTO TESTE_UNIDAC(CAMPO1) VALUES(123456789);
INSERT INTO TESTE_UNIDAC(CAMPO1) VALUES(1234);

The Field CAMPO1 is as IntegerField. Now add another field:

ALTER TABLE TESTE_UNIDAC ADD CAMPO2 NUMBER(10) DEFAULT 0 NOT NULL;

The Field CAMPO2 is as floatfield in UniQuery, can tell me why?

[tobias_cd]

Hi,
I had similar problems with Oracle and I'm still not happy about UniDAC's behavior with this (differences in runtime/design-time). You may try also to add the following line in your code before connecting:
OraClassesUni.IntegerPrecision := 38;

Also I can't get UniDac to detect calculated columns in VIEW's to be integer (with use of persistent fields), though the same works in MS SQL.
If for example you'd have a view with counts as columns, it'd expect float fields instead of integers. Example:

CREATE OR REPLACE VIEW VIEW_TEST
(TOTAL_COUNT)
AS
SELECT COUNT(*) FROM TEST_TABLE

The one column in the view is always treated as ftFloatField with Oracle, but the same under MS SQL Server correctly returns ftIntegerField.

Regards,
Tobias

[bork]

I have a problem to add fields in the Uniquery. My UniConnection is as PrecisionInteger = 10. Using Oracle.

I create the table as follows (without registration) and all fields are as IntegerField:

CREATE TABLE TESTE_UNIDAC(
CAMPO1 NUMBER(10),
CAMPO2 NUMBER(10) NOT NULL,
CAMPO3 NUMBER(10) DEFAULT NULL NOT NULL,
CAMPO4 NUMBER(10) DEFAULT 0 NOT NULL,
CAMPO5 NUMBER(10) DEFAULT 0,
CAMPO6 NUMBER(10) DEFAULT NULL);

I create the table as follows and insert some records:

CREATE TABLE TESTE_UNIDAC(CAMPO1 NUMBER(10));
INSERT INTO TESTE_UNIDAC(CAMPO1) VALUES(1234567);
INSERT INTO TESTE_UNIDAC(CAMPO1) VALUES(123456789);
INSERT INTO TESTE_UNIDAC(CAMPO1) VALUES(1234);

The Field CAMPO1 is as IntegerField. Now add another field:

ALTER TABLE TESTE_UNIDAC ADD CAMPO2 NUMBER(10) DEFAULT 0 NOT NULL;

The Field CAMPO2 is as floatfield in UniQuery, can tell me why?

It seems that this is a problem of Oracle 11:
http://www.devart.com/forums/viewtopic.php?t=17432

Oracle 11g returns precision 38 for the added column. This occurs in Orcale 11g. To resolve this issue we can recommend:
- to use another version of the Oracle server
- to wait when this issue is fixed by the Oracle company
- set PrecisionInteger = 38

Hi,
I had similar problems with Oracle and I'm still not happy about UniDAC's behavior with this (differences in runtime/design-time). You may try also to add the following line in your code before connecting:
OraClassesUni.IntegerPrecision := 38;

Also I can't get UniDac to detect calculated columns in VIEW's to be integer (with use of persistent fields), though the same works in MS SQL.
If for example you'd have a view with counts as columns, it'd expect float fields instead of integers. Example:

CREATE OR REPLACE VIEW VIEW_TEST
(TOTAL_COUNT)
AS
SELECT COUNT(*) FROM TEST_TABLE

The one column in the view is always treated as ftFloatField with Oracle, but the same under MS SQL Server correctly returns ftIntegerField.

The result of the query has the NUMBER data type with precision 38. To interpret NUMBER as Integer you should set:
OraClassesUni.IntegerPrecision := 38;
OraClassesUni.NumberAsInteger := true;

[tobias_cd]

Hello Bork,

thanks for the fast reply. Though I'm not sure right now, but I think I had the same issue with Oracle 10g as well, too. And obviously I can't tell my customers to change their DB installations.

Regards,
Tobias

[bork]

Hello

It is inconvenient to answer two different question in the one topic.

The issue with invalid Precision for added column was found in Oracle 11g only.

The issue with data type of the result of the query SELECT COUNT(*) FROM TEST_TABLE was found in all Oracle versions. But it is not an error, it is an Oracle feature. The result is NUMERIC and you should make a decision which data type in Delphi will be mapped to this data type. By default it is Float (because Delphi Integer data type has small precision). But you can change it by setting global variables: OraClassesUni.IntegerPrecision := 38 and OraClassesUni.NumberAsInteger := true.