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!
Exception class EDatabaseError with message 'qryTest: Type mismatch for field 'KEY', expecting: FMTBcdField
Re: Exception class EDatabaseError with message 'qryTest: Type mismatch for field 'KEY', expecting: FMTBcdField
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?
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?
Re: Exception class EDatabaseError with message 'qryTest: Type mismatch for field 'KEY', expecting: FMTBcdField
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.
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.
Re: Exception class EDatabaseError with message 'qryTest: Type mismatch for field 'KEY', expecting: FMTBcdField
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:
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):
- Delphi code:
MainUnit.dfm (replace xxx with the proper information):
MainUnit.pas:
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
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;
- 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
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
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.
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
Re: Exception class EDatabaseError with message 'qryTest: Type mismatch for field 'KEY', expecting: FMTBcdField
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.