JCSDynamix Blog

dbExpress and Mixed Case Tables

As I've been programming since the dark ages, when Hungarian Notation was popular, I've never dropped the habit of giving my tables mixed case table names such as tblMyTable.  

In a Windows environment, or on database servers that aren't case sensitive this is never a problem.  Also as I religously stick to the convention all my queries and anything I do with a table is always done with the same mixed case convention.

When working with dbExpress and it's components such as TSimpleDataSet which prepare the underlying update/insert query on the dataset automatically however, it can be a problem.  It seems dbExpress's implementation wants to make the entire table name lower case when it updates/inserts data - the normal select query is ok. 

As such the query "select * from tblMyTable" when used with a TSimpleDataSet or TDataSetProvider end up using tblmytable as the table name when any updates are applied (ei.ApplyUpdates).  This fails on a mySQL server running on Unix (being case sensitive).  This is the reason why more recent conventions ofcourse recommened using all lower case for table names, but personally I still find the mixed case easier to read.

It seems single and double quotes don't work as dbExpress already quotes the table.

One option is to use the GetTableName function in TDataSetProvider to set the name back to mixed case, but that's not ideal.

The best solution turns out to be to use the ` quote character around the table names instead.

eg. select * from `tblMyTable` when remain unchanged when any updates are posted.