Bug when working with reserved column names

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for Oracle in Delphi and C++Builder
Post Reply
mitjap
Posts: 4
Joined: Fri 08 Aug 2008 14:22

Bug when working with reserved column names

Post by mitjap » Fri 08 Aug 2008 14:44

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
Last edited by mitjap on Tue 12 Aug 2008 09:20, edited 1 time in total.

Plash
Devart Team
Posts: 2844
Joined: Wed 10 May 2006 07:09

Post by Plash » Tue 12 Aug 2008 07:31

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
Posts: 4
Joined: Fri 08 Aug 2008 14:22

Post by mitjap » Tue 12 Aug 2008 07:56

Plash wrote: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
Devart Team
Posts: 2844
Joined: Wed 10 May 2006 07:09

Post by Plash » Wed 13 Aug 2008 08:37

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
Posts: 4
Joined: Fri 08 Aug 2008 14:22

Post by mitjap » Wed 13 Aug 2008 08:51

Plash wrote: 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
Devart Team
Posts: 2844
Joined: Wed 10 May 2006 07:09

Post by Plash » Thu 14 Aug 2008 08:44

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
Posts: 4
Joined: Fri 08 Aug 2008 14:22

Post by mitjap » Thu 14 Aug 2008 09:15

Plash wrote: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
Devart Team
Posts: 2844
Joined: Wed 10 May 2006 07:09

Post by Plash » Mon 18 Aug 2008 13:42

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

Post Reply