Exception class EDatabaseError with message 'qryTest: Type mismatch for field 'KEY', expecting: FMTBcdField

[costa]

Hello:

I am having the issue described here: http://forums.devart.com/viewtopic.php?t=20209.

I am using the 6.6.9 version of the driver, Delphi XE8. The same app, when connected to an Oracle 10g database works fine but when connected to an Oracle 12c it barfs on a NUMBER(2) field with the error in the title.

Is there a solution to this, other than refreshing the fields for each dataset that has this issue?

Why is this inconsistency?

Thanks!

[costa]

Just an update, it seems that changing the type to TBCDField and adding the Precision and Size types manually in the dfm file fixes the issue. However, now when I try to connect to Oracle 10g I get an error because it is expecting TFMTBCDField!

What I think it happens is that in Oracle 12c the meta-data returns the correct precision/size for the column while in 10g it doesn't. Before the change, the Precision was 32! Interesting enough, the column comes from a view and it is computed through a case statement expression, however, it is cast to NUMBER(2).

Any other ideas?

[AlexP]

Hello,

We can't reproduce the problem. Please specify the exact version of the Oracle server and SQLConnection parameter values that differ from the default ones.

[costa]

Hey:

I reproduced this issue for you in a separate sample than our app. Here is all the information:
- Oracle 10g version: 10.2.0.4.0 32 bit running on linux RH
- Oracle 12c version: 12.1.0.2.0 64 bit running on Oracle Linux 7
- devart dbexpress Oracle driver 6.6.9
- windows app 32 bit
- Delphi XE8 SP1 running onw windows 7 64 bit SP1

This is the sql that you have to run in both databases before you run the test app:

Code: Select all

--drop table TEST_CODE1_TB;

--drop table TEST_CODE2_TB;

create table TEST_CODE1_TB (CD NUMBER(2) NULL);

create table TEST_CODE2_TB (CD NUMBER(2)  NULL);

INSERT INTO TEST_CODE1_TB VALUES(-20);

INSERT INTO TEST_CODE2_TB VALUES(-10);

COMMIT;

-- drop public synonym TEST_CODE_TB_SYN;

create or replace view test_code_v
as
 with x as (
   select * from TEST_CODE2_TB
 )
SELECT CAST(
    (CASE 
      WHEN T1.CD < 0 THEN T1.CD      
      when t1.cd = 0 then t2.cd
      when t1.cd < 10 then null 
      ELSE cast('-1' as number(2)) 
    END) AS NUMBER(2)) AS Val
  FROM TEST_CODE1_TB T1 cross join x T2;

This particular sql view is contrived however it mirrors somewhat the production code.

- You need to add this in the dbxconnections.ini (add the name of your db and your user name - start with Oracle 10g):

Code: Select all

[TestDB]
BlobSize=-1
DataBase=<tnsnames.ora entry>
DriverName=DevartOracle
ErrorResourceFile=
LocaleCode=0000
Password=
Oracle TransIsolation=ReadCommitted
RoleName=Normal
User_Name=<user name>
LongStrings=True
EnableBCD=True
InternalName=
FetchAll=False
CharLength=0
Charset=
UseQuoteChar=False
UseUnicode=True

- Delphi code:
MainUnit.dfm (replace xxx with the proper information):

Code: Select all

object Form1: TForm1
  Left = 0
  Top = 0
  Caption = 'Form1'
  ClientHeight = 412
  ClientWidth = 852
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  OnCreate = FormCreate
  PixelsPerInch = 96
  TextHeight = 13
  object DBGrid1: TDBGrid
    Left = 288
    Top = 64
    Width = 320
    Height = 120
    DataSource = DataSource1
    TabOrder = 0
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'Tahoma'
    TitleFont.Style = []
  end
  object SQLQuery1: TSQLQuery
    MaxBlobSize = -1
    Params = <>
    SQL.Strings = (
      'select * from test_code_v')
    SQLConnection = SQLConnection1
    Left = 56
    Top = 344
    object SQLQuery1VAL: TFMTBCDField
      FieldName = 'VAL'
      Precision = 32
    end
  end
  object DataSource1: TDataSource
    DataSet = ClientDataSet1
    Left = 80
    Top = 160
  end
  object DataSetProvider1: TDataSetProvider
    DataSet = SQLQuery1
    Left = 80
    Top = 272
  end
  object ClientDataSet1: TClientDataSet
    Aggregates = <>
    Params = <>
    ProviderName = 'DataSetProvider1'
    Left = 80
    Top = 216
  end
  object SQLConnection1: TSQLConnection
    ConnectionName = 'TestDB'
    DriverName = 'DevartOracle'
    Params.Strings = (
      'BlobSize=-1'
      'DataBase=xxx'
      'DriverName=DevartOracle'
      'ErrorResourceFile='
      'LocaleCode=0000'
      'Password='
      'Oracle TransIsolation=ReadCommitted'
      'RoleName=Normal'
      'User_Name=xxx'
      'LongStrings=True'
      'EnableBCD=True'
      'InternalName='
      'FetchAll=False'
      'CharLength=0'
      'Charset='
      'UseQuoteChar=False'
      'UseUnicode=True')
    Connected = True
    Left = 80
    Top = 104
  end
end

MainUnit.pas:

Code: Select all

unit MainUnit;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, DBXDevartOracle, Data.DB, Data.SqlExpr, Data.FMTBcd, Vcl.Grids, Vcl.DBGrids,
  Datasnap.DBClient, Datasnap.Provider;

type
  TForm1 = class(TForm)
    SQLQuery1: TSQLQuery;
    DataSource1: TDataSource;
    DataSetProvider1: TDataSetProvider;
    ClientDataSet1: TClientDataSet;
    DBGrid1: TDBGrid;
    SQLConnection1: TSQLConnection;
    SQLQuery1VAL: TFMTBCDField;
    procedure FormCreate(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.FormCreate(Sender: TObject);
begin
 SQLConnection1.Connected := true;
 ClientDataSet1.Active := true;
end;
end.

I haven't added the project dpr and droj files but basically I created a VCL forms application.

The crux of the issue is that, when using 10g and adding adding all fields to the TSQLQuery component, the driver stores the precision of the VAL column as 32! And here is the interesting thing. It is because of the WITH clause in the view. If you re-write the view query without the with clause, the precision/scale are detected properly, i.e. 2/0. In 12c apparently they fixed it and it works even with the WITH clause.

One more thing, when you test adding the field to the TSQLQuery component make sure you disconnect the TSQLConnection object first. It seems that Delphi caches the field and it doesn't really re-retrieve the field meta-data from the database.

Let me know if now you can reproduce the issue, otherwise I will provide more information if you need it.

Thanks

[AlexP]

To retrieve information about fields, we use the standard Oracle API OCIAttrGet2 with OCI_ATTR_PRECISION and OCI_ATTR_SCALE parameters. Oracle server 10 returns incorrect data in comparison to version 12. Unfortunately, we can't affect this behavior.