Bug when working with reserved column names

[mitjap]

Hi!

There seems to be a problem with the driver when using TClientDataSet and reserved names.

Example:
1. Create table:
CREATE TABLE TESTX (
ID NUMBER(10, 0),
"COMMENT" VARCHAR2(10)
)

2. Connect and configure TSQLConnection, TSQLQuery, TDataSetProvider and TClientDataSet.

3. Set SQLQuery.SQL.Text to
Select * from TESTX

4. Execute this Delphi lines:
cds.Open;
cds.Append;
cds.FieldByName('id').AsInteger:=1;
cds.FieldByName('comment').AsString:='test';
cds.Post;
cds.ApplyUpdates;

ApplyUpdates raises exception:
Project Project17.exe raised exception class EOraError with message 'ORA-01747: invalid user.table.column, table.column, or column specification

Setting poUseQuoteChar on provider or setting UseQuoteChar=TRUE on SQLConnection does not help.

I hope you can confirm this behaviour.

Looking forward to a fixed dll or a workaround,
Mitja Perko

[Plash]

dbExpress components generates the INSERT statement with non-quoted field names when you call the ApplyUpdates method. The driver just executes this statement. So we cannot fix this problem in the driver.

You should rename the field to use it with dbExpress.

[mitjap]

dbExpress components generates the INSERT statement with non-quoted field names when you call the ApplyUpdates method. The driver just executes this statement. So we cannot fix this problem in the driver.

You should rename the field to use it with dbExpress.

What is than the purpuse of UseQuoteChar parameter? I think that your driver's function GetDatabase returning metadata does not return any quote character so dbExpress components cannot quote fields in SQL.

[Plash]

If you set the UseQuoteChar option to True in Delphi 7, it will help you to solve the problem.

But in Delphi 2007 we do not return a quote character because using a quote character causes some problems. So the UseQuoteChar option cannot help.

[mitjap]

But in Delphi 2007 we do not return a quote character because using a quote character causes some problems.

I have temporarily overrided PSGetQuoteCharW on TSQLQuery to return a quote char. Renaming the field is not an options due to backward compatibility issues.

Can you tell me what problems to watch out for?

Thank you!

[Plash]

If we return a quote character, all object names in SQL must be written in correct case. For example, if you assign the SQL property in the following way

Code: Select all

select * from testx

incorrect INSERT statement is generated:

Code: Select all

insert into "testx"
  ("ID", "COMMENT")
values
  (:1, :2)

[mitjap]

If we return a quote character, all object names in SQL must be written in correct case.

In my opinion this is ok behaviour. If you explicitly want quoting than you should watch out for how you spell object names. If anyone does not like this behaviour, he can easily turn it off by not specifying UseQuoteChar which could be false by default.

[Plash]

In the next build of DbxOda we'll add support for the UseQuoteChar option under Delphi 2007.