Re: SchemaCase setting in org.apache.openjpa.jdbc.sql.SQLServerDictionary

[Marc Prud'hommeaux]

Dain-

I've gone ahead and added this to the database notes. Thanks for the  
reminder.


On Jan 30, 2007, at 11:28 AM, Dain Sundstrom wrote:

You should add that to the database specific notes section in the  
documentation before we all forget about this :)

-dain

On Jan 29, 2007, at 11:50 PM, Marc Prud'hommeaux wrote:

William-

I didn't know it was an option to have a SQL Server instance be  
case-sensitive.

Anyway, you can just specify this behavior by setting the  
following property:

   openjpa.jdbc.DBDictionary: schemaCase=preserve

Let us know if the problem still occurs after you try this setting.



On Jan 29, 2007, at 10:49 PM, William Cai wrote:

Hi folks,
I met a problem when running OpenJPA on a case-sensitive MS SQL  
Server
database. Schematool reported below error.

Exception in thread "main"  
com.microsoft.sqlserver.jdbc.SQLServerException:
The database name component of the
object qualifier must be the name of the current  
database.Listening for
transport dt_socket at address: 8111

       at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseErro 
r(Unknown
Source)
       at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets 
(Unknown
Source)
       at  
com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult 
(Unknown
Source)
       at
com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement 
(Unknown
Source)
       at
com.microsoft.sqlserver.jdbc.SQLServerStatement 
$StatementExecutionRequest.executeStatement
(Unknown
Source)
       at com.microsoft.sqlserver.jdbc.CancelableRequest.execute 
(Unknown
Source)
       at  
com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest 
(Unknown
Source)
       at  
com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(Unknown
Source)
       at
com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSet( 
Unknown
Source)
       at  
com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getTables 
(Unknown
Source)
       at  
org.apache.openjpa.lib.jdbc.DelegatingDatabaseMetaData.getTables(
DelegatingDatabaseMetaData.java:32
3)
       at
org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator 
$LoggingConnection$LoggingDatabaseMetaData.ge
tTables(LoggingConnectionDecorator.java:658)
       at  
org.apache.openjpa.lib.jdbc.DelegatingDatabaseMetaData.getTables(
DelegatingDatabaseMetaData.java:32
3)
       at org.apache.openjpa.jdbc.sql.DBDictionary.getTables(
DBDictionary.java:3119)
       at  
org.apache.openjpa.jdbc.schema.SchemaGenerator.generateTables(
SchemaGenerator.java:465)
       at  
org.apache.openjpa.jdbc.schema.SchemaGenerator.generateSchema(
SchemaGenerator.java:329)
       at  
org.apache.openjpa.jdbc.schema.SchemaGenerator.generateSchemas(
SchemaGenerator.java:260)
       at  
org.apache.openjpa.jdbc.schema.SchemaGenerator.generateSchemas(
SchemaGenerator.java:240)
       at org.apache.openjpa.jdbc.schema.SchemaTool.run 
(SchemaTool.java
:1360)
       at org.apache.openjpa.jdbc.schema.SchemaTool.run 
(SchemaTool.java
:1333)
       at org.apache.openjpa.jdbc.schema.SchemaTool.main 
(SchemaTool.java
:1283)

After some invesitigation, I found OpenJPA converted the lower  
case database
name to upper case in
org.apache.openjpa.jdbc.sql.DBDictionary.convertSchemaCase 
(String). (See
below code)  In this function we always convert objectName to  
upper case
unless schemaCase is set to "lower" or "preserve". schemaCase is  
initilized
with the value "upper", and changed in EmpressDictionary,  
MySQLDictionary,
PostgresDictionary, and SybaseDictionary. The problem is we keep  
the value
in SQLServerDictionary.

   protected String convertSchemaCase(String objectName) {
       if (objectName == null)
           return null;

       if (SCHEMA_CASE_LOWER.equals(schemaCase))
           return objectName.toLowerCase();
       if (SCHEMA_CASE_PRESERVE.equals(schemaCase))
           return objectName;
       return objectName.toUpperCase();
   }

One possible solution of my problem is developing a new  
SQLServerDictionary
in which schemaCase is set to "preserve". It should work.  
However, I still
have a couple of questions to the list:

1. Do we need set schemaCase to "preserve" in SQLServerDictionary  
as we did
in SybaseDictionary?
2. Are there any other solutions? e.g. set the value in some  
configuration
file, etc.

Thanks,
William