Table name case in CMP sql statements

[cwbrandon]

Hi Dain,

Dumb as this may sound, our customer has an existing SQL Server database that is case sensitive (I've never seen this before). I have an application that is using CMP JBoss-3.0.0RC2, and while developing I've had no issues (since my database isn't case sensitive), but now that I'm trying to test on their site with their database I'm getting errors that the tables aren't found because the generated CMP SQL statements have the table names all lower case. For example in the jbosscmp-jdbc.xml file I have the table-name attribute set as "Persons" but in the SQL statement generated by the container I'm getting "SELECT Per_ID FROM persons WHERE Per_ID=?". Is there anyway you could change this so that the table name would match the case of the table name found in the jbosscmp-jdbc.xml file, instead of making it lowercase?

Thanks!

Chad

[oytan022]

Can u test ur sql query on ur db window not from the bean? If so, u should match the case.

[cwbrandon]

Well yes I can perform the query fine if I change the lowercase "persons" to "Persons" in SQL Server's Query Analyzer. The thing is the container generates the SQL with the table names in all lower case...I don't have control over the generated SQL statements since I'm using CMP.

[dsundstrom]

You will have to set create-table and remove-table to false, but you didn't want these on anyway. It should work then as sql is case insensitive.

[cwbrandon]

Actually with SQL Server you can setup the database as case sensitive, so all SQL executed within it is case sensitive (I didn't previously know any database supported this), so that's my problem. The tables are already there (the database is set to case-sensitive) and I'm writing CMP EJBs for them. I'm not actually generating the tables from the container. (I have both create-table and remove-table set to false). So when the CMP statements are generated, and the table name is lowercase I get an error telling me the table can not be found. For example, one of the table names is Persons and the generated SQL statement foun in the log file uses "persons" instead of Persons. Joss3.0.0alpha used the exact names of the tables found in the jbosscmp-jdbc.xml file so I had no similar problems before.

Thanks,

Chad

[dsundstrom]

Oh. I can see how that happens. The metadata in JDBC driver you are using lies. I check the metadata on startup and convert the table name as required. This allows the same config to be used on several databases, but it relies on the metadata being accurate.

In the future I would like to be able to override the metadata mapping in the JCA wrapper, so you could fix bad drivers.

I don't know what you can do in the mean time. I'll think about it.

[cwbrandon]

Ok, thanks Dain.

[cwbrandon]

I am actually using a different driver than I was with JBoss3.0.0alpha so, so yeah that is probably where the issue is. We haven't purchased a driver yet and have just been using trial or development versions. I'll try switching to a different driver and see if that takes care of the problem. Thanks again.

Chad

[cwbrandon]

Swithchin to a different driver fixed the problem...thanks!

[dsundstrom]

Cool, what driver? Someone else is going to run into this problem.

[cwbrandon]

Well the driver that was giving me problems (lower case table names) was TaveConn25M Type 4 Driver for MS SQL Server from Atinav. I switched to the i-net MERLIA 2000 Version 1.01 for MS SQL Server, and it seems to be generating the correct case for the table names.