Mixed case table name - Failed to retrieve metadata for table: 'MixedCaseTableName'

[GoogleCodeExporter]

activejdbc does not work properly with mixed case table names, takes only 
uppercase and lowercase table names into consideration.

Test case to reproduce the problem:

environment:

netbeans 7.2
activejdbc version 1.4.4
jdk 1.7
mysql connector 5.1.21
win7 pro

code:

public static void main(String[] args) {

   Base.open("com.mysql.jdbc.Driver", "jdbc:mysql://localhost:3306/testdb", "username", "password");
   List<User> users = User.findAll();
   Base.close();

}

@Table("User")
@IdName("UserID")
public class User extends Model {}

Output:
1179 [main] WARN org.javalite.activejdbc.Registry - Failed to retrieve metadata 
for table: 'User'. Are you sure this table exists? For some databases table 
names are case sensitive.

Exception in thread "main" org.javalite.activejdbc.DBException: 
org.javalite.activejdbc.InitException: Failed to find table: User

I identified the code responsible for the metadata warning in 
org.javalite.activejdbc.Registry.java

First it tries to get meta data with table name in upper case

ResultSet rs = con.getMetaData().getColumns(null, schema, table.toUpperCase(), 
null);

then if not successfull it tries to get meta data with table name in lower case

rs = con.getMetaData().getColumns(null, schema, table.toLowerCase(), null);

if not succesfull it logs the warning.

Why does it not also try with the table name not modified at all?

private Map<String, ColumnMetadata> fetchMetaParams(String table, String 
dbName) throws SQLException {
        Connection con = ConnectionsAccess.getConnection(dbName);

        String schema = System.getProperty("activejdbc." + dbName + ".schema");

        //try upper case table name first - Oracle uses upper case
        ResultSet rs = con.getMetaData().getColumns(null, schema, table.toUpperCase(), null);
        String dbProduct = con.getMetaData().getDatabaseProductName().toLowerCase();
        Map<String, ColumnMetadata> columns = getColumns(rs, dbProduct);
        rs.close();
        //if upper case not found, try lower case.
        if(columns.size() == 0){
            rs = con.getMetaData().getColumns(null, schema, table.toLowerCase(), null);
            columns = getColumns(rs, dbProduct);
            rs.close();
        }

        if(columns.size() > 0){
            LogFilter.log(logger, "Fetched metadata for table: " + table);
        }
        else{
            logger.warn("Failed to retrieve metadata for table: '" + table
                + "'. Are you sure this table exists? For some databases table names are case sensitive.");
        }

        return columns;
    } 

Original issue reported on code.google.com by xwa...@gmail.com on 3 Sep 2012 at 5:58

[GoogleCodeExporter]

seems you are correct,  this is an oversight

Original comment by i...@polevoy.org on 3 Sep 2012 at 8:07

• Changed state: Started

[GoogleCodeExporter]

this has been fixed and pushed to Snapshot repository:
https://oss.sonatype.org/content/repositories/snapshots/org/javalite/activejdbc/
1.4.5-SNAPSHOT/
Please try, and see if this fixes you issue.
If yes, I will cut a new release

thanks,

Original comment by ipolevoy@gmail.com on 4 Sep 2012 at 4:58

[GoogleCodeExporter]

hello, I tried the snapshot and it works. thanks.

Original comment by xwa...@gmail.com on 4 Sep 2012 at 6:07

[GoogleCodeExporter]

this fix has been pushed out in a new release 1.4.5
- enjoy

Original comment by ipolevoy@gmail.com on 5 Sep 2012 at 2:17

[GoogleCodeExporter]

Original comment by ipolevoy@gmail.com on 5 Sep 2012 at 2:17

• Changed state: Fixed

[GoogleCodeExporter]

I am using activejdbc 1.4.9, it doesn't recognize different case again.

Original comment by yangcolu...@gmail.com on 30 May 2015 at 4:02

[GoogleCodeExporter]

The table name in postgresql is "seer_data_MALEGEN" (it has to be quoted 
because postgresql only allowe lower case table name, any table name different 
need to be quoted).

I used annotation @ [java] Exception in thread "main" 
org.javalite.activejdbc.DBException: org.postgresql.util.PSQLException: ERROR: 
syntax error at or near "'seer_data_MALEGEN'"
     [java]   Position: 16, Query: SELECT  * FROM 'seer_data_MALEGEN' WHERE  CASENUM=? LIMIT 1, params: 14183049
     [java]     at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)
     [java]     at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)
("seer_data_MALEGEN") when define the class of this table.

Then do a select, and this is the error:

 [java] Exception in thread "main" org.javalite.activejdbc.DBException: org.postgresql.util.PSQLException: ERROR: syntax error at or near "'seer_data_MALEGEN'"
     [java]   Position: 16, Query: SELECT  * FROM 'seer_data_MALEGEN' WHERE  CASENUM=? LIMIT 1, params: 14183049
     [java]     at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)
     [java]     at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)

Original comment by yangcolu...@gmail.com on 30 May 2015 at 4:09

[GoogleCodeExporter]

sorry, previous post should be replaced by this post. Here is the error message 
that activejdbc doesn't recognize annotation 

@Table("seer_data_MALEGEN")

it still think the table is seer_data_malegen and cannot find it in postgresql.

     [java] Exception in thread "main" org.javalite.activejdbc.DBException: org.postgresql.util.PSQLException: ERROR: relation "seer_data_malegen" does not exist
     [java]   Position: 16, Query: SELECT  * FROM seer_data_MALEGEN WHERE  CASENUM=? LIMIT 1, params: 14183049
     [java]     at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)
     [java]     at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)
     [java]     at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
     [java]     at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)
     [java]     at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
     [java]     at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273)
     [java]     at org.javalite.activejdbc.DB.find(DB.java:403)
     [java]     at org.javalite.activejdbc.LazyList.hydrate(LazyList.java:305)
     [java]     at org.javalite.activejdbc.LazyList.size(LazyList.java:532)
     [java]     at org.javalite.activejdbc.Model.findFirst(Model.java:1772)
     [java]     at activejdbc.ant.example.seer_data_MALEGEN.findFirst(seer_data_MALEGEN.java)
     [java]     at activejdbc.ant.example.Main.selectSeerDataMalegen(Main.java:49)
     [java]     at activejdbc.ant.example.Main.main(Main.java:31)
     [java] Caused by: org.postgresql.util.PSQLException: ERROR: relation "seer_data_malegen" does not exist
     [java]   Position: 16
     [java]     ... 13 more

BUILD FAILED

Original comment by yangcolu...@gmail.com on 30 May 2015 at 4:14

[GoogleCodeExporter]

Google code project is closed. refer to: https://github.com/javalite/activejdbc

Also, use latest version 1.4.10

Original comment by i...@expresspigeon.com on 30 May 2015 at 4:18