Board index » delphi » Delphi is case sensitive with Oracle table names and fields name

Delphi is case sensitive with Oracle table names and fields name

I can I prevent Delphi to be case sensitive to the tables and fields names
in SQL string property of the TQuery components?
I mean that the following statement does not work:

select hiredate
    from employee

where this does work:

select HIREDATE
     from EMPLOYEE

 

Re:Delphi is case sensitive with Oracle table names and fields name


The problem is not Delphi, rather it is Oracle.  Oracle is case sensitive
and will interpret all of your SQL very literally.

Quote
Fabio VITALE wrote in message <6lqfoa$c...@forums.borland.com>...
>I can I prevent Delphi to be case sensitive to the tables and fields names
>in SQL string property of the TQuery components?
>I mean that the following statement does not work:

>select hiredate
>    from employee

>where this does work:

>select HIREDATE
>     from EMPLOYEE

Re:Delphi is case sensitive with Oracle table names and fields name


Quote
On Tue, 16 Jun 1998 17:39:46 GMT, david.arc...@chase.com wrote:
>This is not true.  If you set requestlive = True on the TQuery component ..
>you are required to type the field and table names in uppercase.  However if
>requestlive is set to false you can type it any way you want (upper, lower,
>or knit caps).      This would seem to indicate, to me, that this is a BDE issue
>not and Oracle issue.

>In article <6m1f89$2...@forums.borland.com>,
>  "Stuart Rich" <stur...@midcoast.com> wrote:

>> The problem is not Delphi, rather it is Oracle.  Oracle is case sensitive
>> and will interpret all of your SQL very literally.

Wrong again...

Or, from a positive standpoint, right again...

In short, Oracle is case sensitive, and doesn't interpret all of your SQL very
literally: all object names are converted to uppercase - for our convenience ;-)
- *unless quoted*! Continue reading only if you want an explanation of - or are
just curious about - this 'mystery'.

First, look at the following proof of the above

Using Sql*Plus:
SQL> create table "TestTable" ( "Dummy" varchar2(10) ); <--- table created
SQL> select * from testtable; <--- error
SQL> select * from "TestTable"; <--- OK ('no rows selected' at this point)
SQL> desc "TestTable"
 Name                            Null?    Type
 ------------------------------- -------- ----
 Dummy                                    VARCHAR2(5)

Now, let's come back to Delphi and the BDE.

When you issue a query for a read only result set, the string is passed as is
and treated as anyone you would issue with Sql*Plus.

So, why is it that you have to uppercase the object names when you want a live
result set? In order to make that possible, the BDE transforms your query (so,
it's not really passthrough anymore), because there's one more item appended:
the invariant ROWID! To do that, the BDE fires a few queries to the system
tables in order to retrieve column names, indexes and the like and in that
process, the object names become stringvars (quoted!) as part of the where
clause and, for a table created the standard way (without quotes), this
preparatory work results in 'no row selected', hence the impossibility for the
BDE to complete its mission! Strictly speaking not necessary when the query is
fully qualified (all wanted columns named, i.e., because all what's needed is to
append ',ROWID'), but in this case, we're hit by a 'one does it all' procedure:
'select * from table' cannot be converted to 'select *,ROWID from table' ('FROM
keyword not found where expected').

How does this fit in your projects? If you created the 'TestTable' above with
the one 'Dummy' field, follow this small demo too:
- start Delphi and a new project;
- drop a query on the blank form;
- select your Oracle alias in the database property;
- set the SQL property to read 'select * from TestTable' (without quotes);
- set RequestLive to True;
- set Active to True.
IT WORKS! And that's because the string going to the rdbms actually reads
'SELECT "Dummy", "ROWID" FROM "TestTable"'...

Now, if you set Active to False, RequestLive to False and Active back to True,
you will see it fail, because TestTable upon arrival will be converted to
TESTTABLE and that table does not exist! To get a read only (!!!) result set,
you will have to quote the object names, or, in this case, enter 'select * from
"TestTable"', just like you would with Sql*Plus.

So you were curious!? I hope you enjoyed the explanation. But if you use it -
because you've seen enough uppercased headers in Sql*Plus, for instance - and
your dba or other members in your project team get crazy, please don't blame
me... 8-D

Happy programming!

Jasper

Re:Delphi is case sensitive with Oracle table names and fields name


Jasper,

Thanks you for your clear, concise and totally accurate response.  I will just
add a couple of points:

1. While it is possible, it is not common practice in the "Oracle World" to
create object names with anything other than uppercase.

2. The extra overhead of the RequestLive=True (additional queries generated by
the BDE: to get column names for instance) should deter this practice for any
person concerned with performance.

David

In article <3587f143.2356...@news.euro.net>,
  dirks...@euronet.nl (Jasper Stil) wrote:

Quote

> On Tue, 16 Jun 1998 17:39:46 GMT, david.arc...@chase.com wrote:

> >This is not true.  If you set requestlive = True on the TQuery component ..
> >you are required to type the field and table names in uppercase.  However if
> >requestlive is set to false you can type it any way you want (upper, lower,
> >or knit caps). This would seem to indicate, to me, that this is a BDE
issue
> >not and Oracle issue.

> >In article <6m1f89$2...@forums.borland.com>,
> >  "Stuart Rich" <stur...@midcoast.com> wrote:

> >> The problem is not Delphi, rather it is Oracle.  Oracle is case sensitive
> >> and will interpret all of your SQL very literally.

> Wrong again...

> Or, from a positive standpoint, right again...

> In short, Oracle is case sensitive, and doesn't interpret all of your SQL very
> literally: all object names are converted to uppercase - for our convenience
;-)
> - *unless quoted*! Continue reading only if you want an explanation of - or
are
> just curious about - this 'mystery'.

> First, look at the following proof of the above

> Using Sql*Plus:
> SQL> create table "TestTable" ( "Dummy" varchar2(10) ); <--- table created
> SQL> select * from testtable; <--- error
> SQL> select * from "TestTable"; <--- OK ('no rows selected' at this point)
> SQL> desc "TestTable"
>  Name                            Null?    Type
>  ------------------------------- -------- ----
>  Dummy                                    VARCHAR2(5)

> Now, let's come back to Delphi and the BDE.

> When you issue a query for a read only result set, the string is passed as is
> and treated as anyone you would issue with Sql*Plus.

> So, why is it that you have to uppercase the object names when you want a live
> result set? In order to make that possible, the BDE transforms your query (so,
> it's not really passthrough anymore), because there's one more item appended:
> the invariant ROWID! To do that, the BDE fires a few queries to the system
> tables in order to retrieve column names, indexes and the like and in that
> process, the object names become stringvars (quoted!) as part of the where
> clause and, for a table created the standard way (without quotes), this
> preparatory work results in 'no row selected', hence the impossibility for the
> BDE to complete its mission! Strictly speaking not necessary when the query is
> fully qualified (all wanted columns named, i.e., because all what's needed is
to
> append ',ROWID'), but in this case, we're hit by a 'one does it all'
procedure:
> 'select * from table' cannot be converted to 'select *,ROWID from table'
('FROM
> keyword not found where expected').

> How does this fit in your projects? If you created the 'TestTable' above with
> the one 'Dummy' field, follow this small demo too:
> - start Delphi and a new project;
> - drop a query on the blank form;
> - select your Oracle alias in the database property;
> - set the SQL property to read 'select * from TestTable' (without quotes);
> - set RequestLive to True;
> - set Active to True.
> IT WORKS! And that's because the string going to the rdbms actually reads
> 'SELECT "Dummy", "ROWID" FROM "TestTable"'...

> Now, if you set Active to False, RequestLive to False and Active back to True,
> you will see it fail, because TestTable upon arrival will be converted to
> TESTTABLE and that table does not exist! To get a read only (!!!) result set,
> you will have to quote the object names, or, in this case, enter 'select *
from
> "TestTable"', just like you would with Sql*Plus.

> So you were curious!? I hope you enjoyed the explanation. But if you use it -
> because you've seen enough uppercased headers in Sql*Plus, for instance - and
> your dba or other members in your project team get crazy, please don't blame
> me... 8-D

> Happy programming!

> Jasper

-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/   Now offering spam-free web-based newsreading

Other Threads