Board index » delphi » Case sensitive in SQL Statements??

Case sensitive in SQL Statements??

Hi people,

I am using an Oracle 9 and acessing it with Delphi 5.
When I select the data from a table with TQuery, and its property
RequestLive is FALSE, the result is OK, it returns the data but when I turn
the RequestLive property to TRUE the BDE raises an exception saying that the
table doesn't exist.
For exemple if I try this select:

select * from employee

with RequestLive set to false it works fine but if I set RequestLive to true
it raises the exception.
What is interesting is that when I use

select * from EMPLOYEE

using upper case on the table name, it works OK with both RequestLive true
or false!!!
Does the case matter in the SQL statement??? It shouldn't....

Does anyboy know what's happening??

I saw on SQL Monitor that when I using the BDE with the native Oracle driver
and the RequestLive property is TRUE, someone...or something changes my SQL
statment (maybe the BDE or the Oracle OCI driver).
For the SQL statement above, it turns to

select * from "employee"

and I think this is the problem. The double quotas...

Has anybody seen this before??

Thanks.

 

Re:Case sensitive in SQL Statements??


Wellington R. Pinheiro <wrpinhe...@unidatasp.com.br> wrote in message
news:3bfe76bd_1@dnews...
Quote
> I saw on SQL Monitor that when I using the BDE with the native Oracle
driver
> and the RequestLive property is TRUE, someone...or something changes my
SQL
> statment (maybe the BDE or the Oracle OCI driver).
> For the SQL statement above, it turns to

> select * from "employee"

> and I think this is the problem. The double quotas...

This is the exact reason.

What happens is when you create a table, Oracle adds it to the data
dictionary with the tablename and fieldnames all in uppercase.  With the
double quote, Oracle will do a case sensitive search in the data dictionary
and of course unable to find a match.  I guess without the double quote,
Oracle will uppercase the tablename before the search.

If you want to keep your tablename and fieldname in the data dictionary the
same as you create them, you have to double quote them in your DDL.  But
then you always have to enter your tablename and fieldname the same you
create them and double quote them in your DML, otherwise you will get the
table does not exists error.

Quote
> Has anybody seen this before??

It is a known BDE feature (you like it or not).

HTH,
Ping Kam

Other Threads