generic upper case search on postgres and mysql not working

Question

I am trying to do an easy search on a table that can be on any kind of database. The following query is working an the most databases, but I cannot find a solution which works on mysql. The tables in my database are generated by the active objects framework, so I cannot change the names or config of those instances.

Here is the query that works fine on all databases but MySQL:

select * from "AO_69D057_FILTER" where "SHARED" = true AND "CONTAINS_PROJECT" = true AND UPPER("FILTER_NAME") like UPPER('%pr%').

MySql is not able to use the table name in double quotes for some reason. If I use the unquoted table name it works on MySQL but not on Postgres. Postgres is converting the table name to lowercase because it is unquoted. AO is generating the table names in upper case.

I also tried to use an alias, but that can not work because of the evaluation hierarchy of the statement.

Any suggestions how to get rid of the table name problem?

Answer 1

By default double quotes are used to columns. You can change it:

SET SQL_MODE=ANSI_QUOTES;

Here is the documentation about it: http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

Answer 2

I had the same problem. I select the query according to the exception I get. In the first call of the db search, I try without quotes if it fails then I try with quotes. Then I set useQueryWithQuotes variable accordingly so that in future calls I do not need to check the exception. Below is the code snipped I am using.

private Boolean useQueryWithQuotes=null;
private final String queryWithQuotes = "\"OWNER\"=? or \"PRIVATE\"=?";
private final String queryWithoutQuotes = "OWNER=? or PRIVATE=?";

public Response getReports() {
  List<ReportEntity> reports = null;
  if(useQueryWithQuotes==null){
    synchronized(this){
      try {
        reports = new ArrayList<ReportEntity>( Arrays.asList(ao.find(ReportEntity.class, Query.select().where(queryWithoutQuotes, getUserKey(), false))) );
        useQueryWithQuotes = false;
      } catch (net.java.ao.ActiveObjectsException e) {
        log("exception:" + e);
        log("trying query with quotes");
        reports = new ArrayList<ReportEntity>(  Arrays.asList(ao.find(ReportEntity.class, queryWithQuotes, getUserKey(), false)));
        useQueryWithQuotes = true;
      }
    }
  }else{
    String query = useQueryWithQuotes ? queryWithQuotes : queryWithoutQuotes;
    reports = new ArrayList<ReportEntity>(  Arrays.asList(ao.find(ReportEntity.class, query, getUserKey(), false)));
  }
  ...
}