Win a copy of Svelte and Sapper in Action this week in the JavaScript forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

Elements not found - special character “\” (escape) in fields of type Varchar/String - Criteria like

 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,

Recently, I have been developing and using this tool, however, I have had problems with values ​​to search.

It is necessary to clarify before continue… the character “” (escape) is special in regard to development. Where for each language it could mean something different, where if a string contained the character “” it should consult “\” to ignore the first and put the second escape as is.

For example, if I have a column in the varchar database and I save values ​​with the special character “” (escape) through a java process. Suppose the records in my database are “TEST\00001”, “TEST\00002”, “TEST\00003”, “TEST\00003”, “TEST\00005” … “TEST\74309” when using hibernate to obtain the registration with a Criteria-Restriction.Eq it works correctly, but when using a like it does not find it, since it probably makes the query as is to the database and for this case “” ignores the case and it goes on to the following, leaving the string “TEST0001” to search, which does not find the required value.

So, if I have something like this:


I should filter the first five cases. Remembering that MatchMode.ANYWHERE makes it work like an ilike by adding “%” at the beginning and end of the string.

If the query is made as is to the database:

select column from table where column like '%TEST\0000%';

values ​​are not obtained either.
Works with:

select column from table where column like '%TEST\\0000%';

In order to solve this in my codes I have used a replace of “” with “\” if the string contained it.

Should hibernate consider itself? Or is it up to the developer to receive these types of values? I mean that the hibernate query is supposed to go directly with the exact string that I want to get as a result, I don’t think I should check it in my code and do the following to consider it:


and get the 5 results.

I’m right? Has anyone else faced a similar problem? Can someone help me regarding this?

Because I agree that it could be a data interpretation bug, I mean… I should analyze in my code (if necessary) if a character should be ignored or not if it has the value “” in its string but not make sure that you consider it exactly as it happens. As for sql in the LIKE values, it is clarified that the “” character is special, you can check it by searching for “escape in sql like” and it has been cleared for some time.

In this case, if I received the value “T\Example” and would like to ignore the “E”, I should check this for myself in my code and tell Hibernate that I really want to look for “Texample” because I am sending the exact string to search.



 
Marshal
Posts: 25823
69
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to the Ranch!

Mery Anelo wrote:If the query is made as is to the database:

select column from table where column like '%TEST\0000%';

values ​​are not obtained either.
Works with:

select column from table where column like '%TEST\\0000%';



The first of those two bold-face things is what an SQL query should look like, if for example you typed it into a database query tool.

The second is what a Java String literal should look like to represent the first.

That's why your example

doesn't do what you want. And no, you can't write more code to fix up the problem. You should write this instead:


And also, if you're building a query from data input by the user (not from String literals) then the user should only enter one \ character. The only place it's necessary to use \\ is in String literals in your source code.

Although I don't think I entirely understood your question -- there's this mysterious invisible escape character which you mention several times. If that's significant maybe you could clarify that?
 
Mery Anelo
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi, thanks for the quick response...

My mistake, sory. I put the generic error and don't explain its actual use.
The controls are based on a unique code that the user enters, some of them use the bar to separate certain codes.
For example, stock control. They have products with codes and I do a search to throw these products based on the unique code that the user entered, but the user never uses the complete code just some predetermined part and I must find all the coincidences of it.
I have not had problems with "/", however the use of the backslash "\" was a problem, since when doing a search, to throw the user certain matches, it does not find the values. Debugging this error was that I came to the use of "\\", using the replace but the original string provided by the user has only one "\", like the java string "\", however my criteria needs me to send the string with two "\\".

With my question I was referring to whether I should clearly consider in my code that any chain to look for could have a "\" and replace it by two or there was a better way to solve it using hibernate and take the chain as such without having to replace using a filter that finds all possible matches (like anywhere).
 
Switching from electric heat to a rocket mass heater reduces your carbon footprint as much as parking 7 cars. Tiny ad:
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
    Bookmark Topic Watch Topic
  • New Topic