Bug when working with reserved column names

Bug when working with reserved column names

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

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

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

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

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

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

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

Postby Plash » Mon 18 Aug 2008 13:42

In the next build of DbxOda we'll add support for the UseQuoteChar option under Delphi 2007.
Plash
Devart Team
 
Posts: 2844
Joined: Wed 10 May 2006 07:09


Return to dbExpress driver for Oracle