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?

Possible duplicate of [mysql double-quoted table names](http://stackoverflow.com/questions/13884854/mysql-double-quoted-table-names) — Diogo Medeiros, Jul 20 '16 at 16:10

score 0 · Accepted Answer · answered Jul 20 '16 at 15:57

0

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

answered Jul 20 '16 at 15:57

Diogo Medeiros

328
4
12

Thank you Diogo, but I can't change anything as my app is running on Customer installed instances that I don't know of. – Carsten Hilber Jul 20 '16 at 20:07
From http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html: "each application can set its session SQL mode to its own requirements." – NovaDenizen Jul 20 '16 at 20:47
1

First of all thanks for the hint Diogo. Since I use ActiveObjects as an ER mapper, I have no access to the session and the db connection. I used a hack to override this situation, I am getting the DB connection settings of the mapper before it tries to execute the query. When doing that I can ask for an ConnectionInfo Object which tells me which phisical DB is used underneath. If it is MySQL I set the property you stated in your comment. This works although it is rather dirty. I filed a bug for AO to solve it from ER mapper site: https://ecosystem.atlassian.net/browse/AO-3371 – Carsten Hilber Jul 21 '16 at 14:35

score 0 · Answer 2 · answered Sep 22 '21 at 11:47

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)));
  }
  ...
}

generic upper case search on postgres and mysql not working

2 Answers2