emmanuel wrote:
So I don't get it.
Does storesUpperCaseIdentifiers()/storesLowerCaseIdentifiers() supposed to influence the way types should be requested through DatabaseMetadata?
If not, I'm lost
If yes, this means Oracle driver is right here, so which driver is broken?
I posted a similar issue yesterday, over in the EJB3 forum:
http://forum.hibernate.org/viewtopic.ph ... 55#2272455
I have a working solution. Let me attempt to explain how I see the problem, and then show you the relevant piece of code that fixes it.
The DatabaseMetadata has several methods that allow you to work out how the database stores identifiers. storesLowerCaseIdentifiers() and storesUpperCaseIdentifiers() tell you in effect how the database handles unquoted mixed-case identifiers. supportsMixedCaseQuotedIdentifiers() and it's siblings tells you whether the database will store identifers precicely if they are quoted.
The Oracle driver is
not broken. In Oracle, identifiers are case sensitive. Most people don't know this because if you don't quote your identifiers in DDL then Oracle will upper-case them (and everyone is extremely used to this). But if you quote them, then Oracle will store the identifier as is. And two identifiers in different cases are not equivelant. This is all a bit theoretical because at the end of the day I've never met anyone (though I'm sure they're out there) that quotes identifiers in Oracle, or uses anything but full upper case. But it can be done. Check the following output from SQL-Plus:
Code:
SQL> desc test
Name Null? Type
---------- -------- ------
TEST_COL NUMBER
test_col NUMBER
The safest/best algorithm, it seems to me, is to hit the meta data multiple times. First time using the exact identifiers handed to the EnumType (that way, you can always be assured a correct answer if you use @Column to annotate the exact column name). If that fails, and the database stores unquoted identifiers as upper-case, try again using upper case identifiers. If that fails and the database stores unquoted identifiers as lower case, try again using lower case identifiers.
There is the chance here that is someone uses the same name, in different case, in the same table that it will produce a bogus answer. But that can be worked around with @Column annotations, or by shooting the developer who thought that'd be a good idea ;)
I can post this as a real patch, but to give you a better idea of what it looks like, here's the reworked method:
Code:
private int guessTypeFromMetaData(DatabaseMetaData metadata) throws SQLException {
ResultSet rs = null;
try {
String username = metadata.getUserName();
int guessedType = Types.NULL;
int numResults = 0;
boolean nonNullType = false;
// Try to find the column information up to three time. First with values as is in case
// the database ever stores mixed case identifiers, or the supplied identifiers match
// perfectly. Then if the metadata is not found, try using lower and upper case
// identifiers - but only if the datbaase reports it would use them.
for (int i=0; i<3 && numResults == 0; ++i) {
if (i == 0) { // First try with the case that was supplied
rs = metadata.getColumns(catalog, schema, table, column);
}
else if (i == 1 && metadata.storesLowerCaseIdentifiers()) {
rs = metadata.getColumns(catalog == null ? catalog : catalog.toLowerCase(),
schema == null ? schema : schema.toLowerCase(),
table.toLowerCase(), column.toLowerCase());
}
else if (i == 2 && metadata.storesUpperCaseIdentifiers()) {
rs = metadata.getColumns(catalog == null ? catalog : catalog.toUpperCase(),
schema == null ? schema : schema.toUpperCase(),
table.toUpperCase(), column.toUpperCase());
}
// try to find
while ( rs.next() ) {
guessedType = rs.getInt( "DATA_TYPE" );
if ( rs.wasNull() ) {
continue; // TODO throw exception?
}
else {
nonNullType = true;
}
numResults++;
if ( username != null) {
String foundSchema = rs.getString( "TABLE_SCHEM" );
// if the username matches the schema, there's no better guess available...
if ( username.equalsIgnoreCase(foundSchema) ) return guessedType;
} else if ( numResults > 1 ) {
// if there's no username and multiple results, there's no point in continuing
throw new HibernateException("Several columns matching in metadata: " + column);
}
}
}
if (numResults == 0) throw new HibernateException("Enum type column not found in metadata: " + column);
if (numResults > 1) throw new HibernateException("Several columns matching in metadata: " + column);
if (!nonNullType) throw new HibernateException("Column without type in metadata!: " + column);
log.trace( "Enum type guessed from metadata: " + guessedType);
return guessedType;
} finally {
try {
if (rs != null) rs.close();
} catch (SQLException e) {
//swallow in purpose
}
}
}
Login
FAQ
Search
