Different DataTypes for same Column

[pepe_rules]

Hello,

i've got a problem with UniDAC 4.6(.12), which wasn't there with
UniDAC 4.3. So basically i'm doing this:

Code: Select all

procedure TForm1.FormCreate(Sender: TObject);
var
  ds : TUniQuery;
  f  : TField;
  conn : TUniConnection;
begin
  conn := TUniConnection.Create(nil);
  conn.ProviderName   := 'Oracle';
  conn.Server         := 'BSORACLE10:1521:LOGOMUEN';
  conn.Username       := 'xxx';
  conn.Password       := 'xxx';
  conn.SpecificOptions.Values['Direct'] := 'True';
  conn.SpecificOptions.Values['Schema'] := 'logoadmin';
  conn.SpecificOptions.Values['Charset'] := 'WE8MSWIN1252';
  conn.SpecificOptions.Values['PrecisionLargeint'] := '38';
  conn.Connect;
  ds := TUniQuery.Create(nil);
  ds.Connection          := conn;
  ds.SQL.Text            := 'select PMSPROJEKTID from PMSDATA where PMSDATAID = 39668183';
  ds.FetchRows           := 250;
  ds.Options.FlatBuffers := True;
  ds.UniDirectional      := True;
  ds.SpecificOptions.Values['DeferredLobRead'] := 'True';
  ds.SpecificOptions.Values['AutoClose']       := 'True';
  ds.SpecificOptions.Values['FetchAll']        := 'False';
  ds.Prepared := True;
  ds.Open;
  for f in ds.Fields do begin
    ShowMessage(Format('%s: %d', [f.FieldName, Ord(f.DataType)]));
  end;
end;

The table structure for this table is:

Code: Select all

CREATE TABLE LOGOADMIN.PMSDATA (
    PMSDATAID Number(12) DEFAULT 0 NOT NULL,
    ...
    PMSPROJEKTID Number(12),
    ...
    CONSTRAINT PK_PMSDATA PRIMARY KEY (
      PMSDATAID
    )
)
 TABLESPACE LOGO
 LOGGING
 STORAGE (
    INITIAL         64K
    MAXEXTENTS      2147483645
 )
/
ALTER TABLE LOGOADMIN.PMSDATA ADD CONSTRAINT FK_PMSDATA_PMSPROJEKT FOREIGN KEY (PMSPROJEKTID)
 REFERENCES LOGOADMIN.PMSPROJEKT (PMSPROJEKTID)
 ON DELETE CASCADE
 VALIDATE
/

/
CREATE INDEX LOGOADMIN.IDX_PMSDATA_PMSPROJEKT
 ON LOGOADMIN.PMSDATA(PMSPROJEKTID DESC)
 STORAGE (
    INITIAL         64K
    MAXEXTENTS      2147483645
 )
 LOGGING
 TABLESPACE LOGO_IND
/

So, as you can see, there is an index inserted for the column PMSPROJEKTID.

Now, when i execute the above code with the given SQL

Code: Select all

'select PMSPROJEKTID from PMSDATA where PMSDATAID = 39668183'

the DataType for the column PMSPROJEKTID is ftLargeint.


But when i change the SQL to this query:

Code: Select all

'select PMSPROJEKTID from PMSDATA where PMSPROJEKTID = 39668179'

the DataType of PMSPROJEKTID becomes ftFloat.

I think the reason for this is, that the second query is executed
with the index on PMSPROJEKTID.

Is there a option/possibility to get the DataTyp ftLargeint for the
second query too? I.e. supress the above behaviour?

Thank you very much for your help.
Ricardo Angeli

[AlexP]

Hello,

Thank you for the information. We have reproduced the problem and will investigate the reasons of this error occurrence. As soon as we have any results, we will inform you.

[pepe_rules]

Hello,

do you already have any results on this topic?

With kind regards,
Ricardo Angeli

[AlexP]

Hello,

We have already fixed the problem

[pepe_rules]

Hello,

I'm sorry, but we've updated to UniDAC 5,
and we still get ftFloat as a result, when
the Index is descending on Oracle 10 and Oracle 11 Servers.

Code: Select all

CREATE INDEX LOGOADMIN.IDX_PMSDATA_PMSPROJEKT
 ON LOGOADMIN.PMSDATA(PMSPROJEKTID DESC)
 STORAGE (
    INITIAL         64K
    MAXEXTENTS      2147483645
 )
 LOGGING
 TABLESPACE LOGO_IND
/

Could you please take another look at the problem?

Thank you,
Ricardo Angeli

[AlexP]

Hello,

When using index on defining PRECISION, the OCIAttrGet2 standard OCI method with a OCI_ATTR_PRECISION parameter returns different values for your queries. In the first case, 12 is a correct value set when creating the Number(12) field. In the second case, the same method returns 0, and therefore we set PRECISION = 38. We will report this problem to the Oracle technical support - and as soon as we get any information, we will inform you.
For the time being, to solve the problem, you can use DataTypeMaping to retrieve the same field for both queries.

Code: Select all

 ds.DataTypeMap.AddFieldNameRule('PMSPROJEKTID', ftLargeint);