1

i'm getting this error message with my query.. i'm using openjpa and sql server for my database...

this is my query: 

public static List<ProcesosGeneralEntity> getALLbyProductor(String productor){
           Query q = entityManager.createQuery("select a from ProcesosGeneralEntity a where a.productor like :productor ");
            q.setParameter("productor", '%'+productor+'%');
            List<ProcesosGeneralEntity>resultado=q.getResultList();
            List<ProcesosGeneralEntity>result2=new ArrayList<ProcesosGeneralEntity>(resultado);
            return result2;

        }
user2018726
  • 638
  • 2
  • 12
  • 23
  • 1
    It sounds like OpenJPA is escaping a product string incorrectly, as one source I found states that the escape character for Oracle Database is `\`, so to escape a quote you do `\'`, and to escape a backslash you do `\\`. Whereas in SQL Server, the escape character for a single quote is another quote, as in `''`. So it sounds like you have a `productor` string that contains a single `\`, which OpenJPA is escaping to `\\`. The solution may be that you need to specify that your `openjpa.jdbc.DBDictionary` configuration is set to `sqlserver`. – Aaron Friel Jul 18 '13 at 21:20

1 Answers1

2

Just in case my comment above is correct, I am submitting this as a tentative or possible answer.

Different database software behaves slightly differently, the ANSI SQL Standard does not cover all behavioral quirks of SQL, and so things like escape characters in strings differ between implementations. In SQL Server, escaping a quote mark is done with another quote mark, so to print the string "Alice's dog", one needs to use use, in SQL, the string 'Alice''s dog'. In Oracle Database, the escape character is a backslash, so to print that same string, you instead use 'Alice\'s dog'. Escape characters themselves need to be able to be printed, so in Oracle Database, to print the string "R2\D2", you need to enter the string 'R2\\D2'.

The problem you are having appears to be that it thinks it is talking to an Oracle Database, and thus defaulted to the latter behavior, and used \\ to quote a single \, instead of leaving it be. SQL Server then had a hiccup on this option or some-such. I'm not sure why it threw it back, to be honest.

Regardless, according to the OpenJPA manual's section 4. Database Support - Chapter 4. JDBC you need to specify the correct DBDictionary. The DBDictionary specifies settings like which escape characters to use in which cases, and other non-standard options that are not uniform across all database systems supported.

The solution appears to be that in the configuration file for your software, you must specify something like:

<property name="openjpa.jdbc.DBDictionary" value="sqlserver"/>

Aaron Friel
  • 1,065
  • 5
  • 11
  • 1
    you are right, thank you!! i had the dictionary set to "mysql" i changed it to : now i do not get the error message :)! – user2018726 Jul 18 '13 at 22:00