#396 Problems with DatabaseMetaData.getColumns() with columns names included lower cases

[Andreas]

I found a problem using the getColumns() method. I have a table with a column name which included lowercases.If I access these column name the getColumns() method don't find these field.

When I look in the field file description (DSPFFD) on AS400 I see the field with lowercases are quoted, when I also quoted the column name, then is all fine But that can not be the solution.

tResultSet = _AS400Connection.getMetaData().getColumns("MYCATALOG", "SCHOLZA", "TESTFILE", "fieldMissed"); // NOT FOUND
tResultSet = _AS400Connection.getMetaData().getColumns("MYCATALOG", "SCHOLZA", "TESTFILE", "\\"fieldMissed\\""); // ALL FINE

CREATE TABLE SCHOLZA.TESTFILE (                   
  "FIELD_FOUND" FOR COLUMN FF INTEGER NOT NULL,   
  "fieldMissed" FOR COLUMN FM INTEGER NOT NULL,   
  "missedtoo"   FOR COLUMN MT INTEGER NOT NULL,   
  "alsomissed"                INTEGER NOT NULL,   
  "OK"                        INTEGER NOT NULL    
)                                                                                                           

                            Display File Field Description    
Input parameters                                              
  File  . . . . . . . . . . . . . . . . . . . :  TESTFILE     
    Library . . . . . . . . . . . . . . . . . :  SCHOLZA      
File Information                                              
  File  . . . . . . . . . . . . . . . . . . . :  TESTFILE     
    Library . . . . . . . . . . . . . . . . . :  SCHOLZA      
  File location . . . . . . . . . . . . . . . :  *LCL         
  Externally described  . . . . . . . . . . . :  Yes          
  Number of record formats  . . . . . . . . . :      1        
  Type of file  . . . . . . . . . . . . . . . :  Physical     
  SQL file type . . . . . . . . . . . . . . . :  TABLE        
  File creation date  . . . . . . . . . . . . :  09.11.17     
Record Format Information                                     
  Record format . . . . . . . . . . . . . . . :  TESTFILE     
  Format level identifier . . . . . . . . . . :  4B9769301E4FF
  Number of fields  . . . . . . . . . . . . . :      5        
  Record length . . . . . . . . . . . . . . . :     20        
Field Level Information                                       
            Data        Field  Buffer    Buffer        Field    Column        
 Field      Type       Length  Length  Position        Usage    Heading       
 FF         BINARY       9  0       4         1        Both     FIELD_FOUND   
   Alternative name  . . . . . . . . . . . . :                                
       FIELD_FOUND                                                            
   Default value . . . . . . . . . . . . . . :  None                          
 FM         BINARY       9  0       4         5        Both     "fieldMissed" 
   Alternative name  . . . . . . . . . . . . :                                
       "fieldMissed"                                                          
   Default value . . . . . . . . . . . . . . :  None                          
 MT         BINARY       9  0       4         9        Both     "missedtoo"   
   Alternative name  . . . . . . . . . . . . :                                
       "missedtoo"                                                            
   Default value . . . . . . . . . . . . . . :  None                          
 ALSOM00001 BINARY       9  0       4        13        Both     "alsomissed"  
   Alternative name  . . . . . . . . . . . . :                                
       "alsomissed"                                                           
   Default value . . . . . . . . . . . . . . :  None                          
 OK         BINARY       9  0       4        17        Both     OK            
   Default value . . . . . . . . . . . . . . :  None    

[John Eberhard]

Unfortunately, this is working as designed. I checked the code and the methods in AS400JDBCDatabaseMetadata assume that column names are case insensitive. The driver will convert to the names to uppercase unless they are enclosed within quotes. This behavior was created 17 years ago in 2000. I am not sure why this was added, because it doesn't make sense to me.

Here is the comparison if you would like to see it yourself.

http://jt400.cvs.sourceforge.net/viewvc/jt400/src/com/ibm/as400/access/AS400JDBCDatabaseMetaData.java?r1=1.3&r2=1.4

This behavior is also defined in the javadoc for AS400JDBCDatabaseMetaData (https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzahh/javadoc/com/ibm/as400/access/AS400JDBCDatabaseMetaData.html)

Schema and table names that are passed as input to methods in this class are implicitly uppercased unless enclosed in double-quotes.

We don't dare change this behavior because it could break existing clients. If this is causing you a problem that is hard to work around, we could add a new connection property that would change this behavior. If you would like us to investigate further, please provide the reasoning as to why this is a behavior you cannot cope with.

[Andreas]

Yes, unfortunately, right, works as designed...

A workaround is not difficult, so I can not justify a change, but alternative would be desirable.
I have now learned something new in solving this problem, because I have not known the following methods:

* connection.getMetaData().storesLowerCaseIdentifiers();
* connection.getMetaData().storesLowerCaseQuotedIdentifiers();
* connection.getMetaData().storesMixedCaseIdentifiers();
* connection.getMetaData().storesMixedCaseQuotedIdentifiers();
* connection.getMetaData().storesUpperCaseIdentifiers();
* connection.getMetaData().storesUpperCaseQuotedIdentifiers();

I would find it important that this behavior is clearly documented by the AS400JDBCDatabaseMetaData#getColumns() method as well as in the class description. This is not the case. A reference to "storesUpperCaseIdentifiers ()" would be very helpful.

My solution:

String tQuotedString = quoteStringIfPossible(_Connection, "fieldMissed")
tResultSet = _Connection.getMetaData().getColumns(null, "SCHOLZA", "TESTFILE", tQuotedString); // FOUND NOW (tested with AS400, MySQL and MSSQL)   

private static String quoteStringIfPossible(final Connection aConnection, final String aMixedCaseName) throws SQLException {
    if (aMixedCaseName == null) {
        return null;
    }
    if (aConnection.getMetaData().storesUpperCaseIdentifiers() && !aMixedCaseName.toUpperCase().equals(aMixedCaseName)
        && !aConnection.getMetaData().storesUpperCaseQuotedIdentifiers()) {
        return quoteString(aConnection, aMixedCaseName);
    } else if (aConnection.getMetaData().storesLowerCaseIdentifiers() && !aMixedCaseName.toLowerCase().equals(aMixedCaseName)
        && !aConnection.getMetaData().storesLowerCaseQuotedIdentifiers()) {
        return quoteString(aConnection, aMixedCaseName);
    } else if (aConnection.getMetaData().storesMixedCaseIdentifiers() && !aConnection.getMetaData().storesMixedCaseQuotedIdentifiers()) {
        return quoteString(aConnection, aMixedCaseName);
    }
    return aMixedCaseName;
}

private static String quoteString(final Connection aConnection, final String aMixedCaseName) throws SQLException {
    if (aConnection.getMetaData().getIdentifierQuoteString().trim().length() > 0) {
        String tQuoteString = aConnection.getMetaData().getIdentifierQuoteString();
        if (aMixedCaseName.length() > 2) {
            if (aMixedCaseName.startsWith(tQuoteString) && aMixedCaseName.endsWith(tQuoteString)) {
                return aMixedCaseName;
            }
        }
        return tQuoteString + aMixedCaseName + tQuoteString;
    }
    return aMixedCaseName;
}

Thanks for the quick help.