SQL Contains: Escaping query operators

Navigation
3

I had a query running for some time (mounted by JPA) when suddenly I was given an exception:

  

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.orm.hibernate3.HibernateJdbcException: JDBC exception on Hibernate data access: SQLException for SQL [n / a]; SQL state [99999]; error code [29902]; could not extract ResultSet; nested exception is org.hibernate.exception.GenericJDBCException: could not extract ResultSet       at org.springframework.web.servlet.FrameworkServlet.processRequest (FrameworkServlet.java:973)

In summary, it gives me SQL state [99999]; error code [29902]; which is a problem in the parameters.

The query that is mounted (roughly) and that gives problems is this: 

select * from mytabla where CONTAINS(campo1, 'BT');

After a bit of google-fu I discovered that BT is a query operator of the contains and, as in that case, it does not have the parameters it should, because it gives the pete.

This error can be reproduced by looking for NOT , DIFMERGE , ABOUT , AND .. and many others that come in that link.

Question :

Is there any way to " escape " the word BT (or any function of those), so that oracle is thought to be a word and not a function?


The contains I put it like this: 

return builder.greaterThan(
        builder.function("CONTAINS", Integer.class, exp, builder.literal(((String) param.getValue()).toUpperCase())),
        0);

EDIT: For trying something I tried to code the word BT in unicode, but it does not work either, it gives the same error.

  

select * from myTable WHERE CONTAINS (field1, UNISTR ('\ 0042 \ 0054')) > 0

    
asked by Daniel Faro 15.09.2016 в 12:17
source

2 answers

1

Because you do not try 

> select * from myTabla WHERE campo1 like '%bt%'

This will look for everything that is similar to "bt"

    
answered by 05.10.2016 в 06:55
1

{texto}

The keys escape to a complete string, taking it as literal.

* Special Characters in Oracle Text Queries . 

select * from mytabla where CONTAINS(campo1, '{BT}');


Considerations:

  • The complete string must be included inside the keys. Queries with texts such as {BT}%fin are taken as 2 different tokens.

  • To escape a } within the keys, use }} .

  • If you only want to escape a single character, you can use \ . Example: hola\! (otherwise the ! would be interpreted as soundex).

answered by 24.06.2017 в 11:33
Paint a pixel with WebGL Generate button board by code in Android Studio