Board index » delphi » Case sensitive SQL object names

Case sensitive SQL object names

Is there any generic way to determine whether SQL object names ( table
names, column names, etc. ) are case-sensitive for the chosen RDBMS ?
 

Re:Case sensitive SQL object names


Quote
Edward Diener wrote:
> Is there any generic way to determine whether SQL object names ( table
> names, column names, etc. ) are case-sensitive for the chosen RDBMS ?

        I think you may be asking the wrong question.  I don't know of any DB
server which is always case-sensitive (though I know of a few which are
always not).

        Servers which offer case-sensitivity as a feature typically do it via
support of the SQL delimited identifiers feature.  Perhaps this is what
you're really asking -- how do you determine if a server supports this
feature?  I don't know of a generic way to do that.

        OTOH, another fair question is "how do I determine if I need to use
delimiters in a query?  The answer is that it's always safe to use them
so long as your case matches the case in the DB.  So if you get a list
of tables/columns from the DB and match case when writing queries, you
can always use the delimiters.

        HTH,

        -Craig

--
Craig Stuntz [TeamB] . Vertex Systems Corp. . Columbus, OH
Delphi/InterBase Weblog : http://delphi.weblogs.com
InterBase Perf. Monitor : http://delphi.weblogs.com/IBPerformanceMonitor
InterBase PLANalyzer 1.1: http://delphi.weblogs.com/IBPLANalyzer

Re:Case sensitive SQL object names


Quote
Craig Stuntz [TeamB] wrote:
> Edward Diener wrote:

>> Is there any generic way to determine whether SQL object names (
>> table names, column names, etc. ) are case-sensitive for the chosen
>> RDBMS ?

> I think you may be asking the wrong question.  I don't know of any DB
> server which is always case-sensitive (though I know of a few which
> are always not).

> Servers which offer case-sensitivity as a feature typically do it via
> support of the SQL delimited identifiers feature.  Perhaps this is
> what you're really asking -- how do you determine if a server
> supports this feature?  I don't know of a generic way to do that.

Yes, this is what I wanted to know.

Quote

> OTOH, another fair question is "how do I determine if I need to use
> delimiters in a query?  The answer is that it's always safe to use
> them so long as your case matches the case in the DB.  So if you get
> a list of tables/columns from the DB and match case when writing
> queries, you can always use the delimiters.

I think this is another situation where the database driver could surface
whether the RDBMS supports something, which in this case would be if the
RDBMS supports case-sensitive SQL delimited identifiers and what they are.
Instead I must either ask my end-user or, if the end-user enters a table
name or column name, insist that it match exactly the name in the database
and use SQL delimited identifiers, which again my end-user must tell me what
they are. It seems from other remarks that double quotes are more or less
the standard when it comes to SQL delimited identifiers. But do I rely on
that for all RDBMSs or ask my end-user again via a property.

I have run into these situations a number of times attempting to create
generic components for DbExpress. Some common RDBMS functionality is not
surfaced by the database driver and presented as part of TSQLConnection as a
property, and I must add properties to my own DbExpress derived components
to find out the functionlity from the end-user. While this is a minor PITA
for me, it is a major one for end-users, who must go scrambling for their
database manual to set the properties correctly.

Am I the only one attempting to write generic components for DbExpress and
finding these situations irksome ? Or is it that all other programmers are
using DbExpress with specific databases and find it easy to just hard-code
the way things work in their modules ?

Re:Case sensitive SQL object names


A quick and dirty way to find out is just to create a temporary table with
some mixed case data names and then try to access it in code with all upper
case or lower case versions of the names. If it fails, then the database is
case sensitive.

kevin

Quote
"Edward Diener" <eddie...@tropicsoft.com> wrote in message

news:3e7a3e86$1@newsgroups.borland.com...
Quote
> Is there any generic way to determine whether SQL object names ( table
> names, column names, etc. ) are case-sensitive for the chosen RDBMS ?

Other Threads