• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Query does not work

 
fabio alonso
Ranch Hand
Posts: 48
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I'm working with a query, but there was a doubt before an error of logic

I have a jsp that has a search field. So far so normal, it works ok

But when I put for example as follows:

exemplo 1: Reviewed for sale
exemplo 2: Reviewed for sale and purchase
exemplo 3: Not rated

If I put one of the three sentences above missing a word, the query returns nothing to me already. I just have to put the whole sentence to run.

code:


But, return error :


Anyone know what this is?

 
Malatesh Karabisti
Ranch Hand
Posts: 153
Java Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Seems like this is the problem with your query,I would suggest you execute the query in your database client before trying with java and just print and see what you are getting from request.getParameter().
 
fabio alonso
Ranch Hand
Posts: 48
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So,

So, when I put the following code:


It works okay but the query returns me to send any word that I search.

In this case, I wanted him to return me even when it is not a complete sentence (as quoted in the example above)
 
Shashank Ag
Ranch Hand
Posts: 88
Java Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You are using prepared satement in your code, thats why you are having problems in writing inline queries.
Check your code, there must be some place where you will be taking out request parameter against '?'.
Say String x=request.getParameter(..)

at this point only you should write something like:
if (x!=null) x="%"+x+"%";

This will do the job.
 
fabio alonso
Ranch Hand
Posts: 48
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
yes, i do..

But, did not work. Return empty
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Do you mean that when executing a query like

the query will not return records containing the text 'this is a string' ?

This is actually the expected behaviour. The like operator searches for exact matches, allowing you just to specify wildcard characters for a single character or a sequence of characters, but nothing more. Everything that is not a wildcard character must be present in the string for like operator to find a match.

You should determine whether the database you're using supports special handling for this kind of queries. I know eg. Oracle has a feature called "Oracle Text" that allow full text indexing and searches, though I didn't ever use it. Without this feature the query would be extremely expensive, as for like operators with leading wildcard characters the table has to be fullscanned.

Only if your database does not support full text search natively, you should try to implement it yourself. Some databases might support regular expressions as well, though again standard indexes cannot be used for regexp conditions. With regexp the task might be a bit easier though.
 
fabio alonso
Ranch Hand
Posts: 48
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

The database I use is MySQL. If I run the following query:


Ok, He returns a list of records containing that phrase, but if I put something missing in the sentence, for example:


The database returns nothing to me. This is the problem...because I wanted him to return me the phrases that have at least the words
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, this is how LIKE works.

If you want to return all rows containing all of the words words "this", "is" and "red", you might code it likeIt will return all fields that contain all three substrings somewhere in the field. You'd use OR instead of AND to return rows that contain at least one of the words, of course.

Another thing to try might be - I assume you can interpret this expression yourself.

However, LIKE is not good for identifying words, you might need to use regular expressions.

Moreover, neither LIKE nor regexp can utilize normal indexes. Check MySQL full text search capabilities, otherwise your query will be a big drain on resources if the table is large or the query is run often.

(Note: the links to MySQL documentation are first hits I got from Google. They might not be entirely appropriate, I don't know MySQL and I checked them only superficially. Use them only as starting places, you should get to know these features yourself if you want to employ them.)

 
fabio alonso
Ranch Hand
Posts: 48
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ok.

I go try find something like do it.

Thank you very much
 
fabio alonso
Ranch Hand
Posts: 48
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It did not work,

I tried using FULL TEXT (regular expression), but my innodb table as this (I would need to function as a MyISAM table with regular expression).

Anyone have any other alternative? please...

Thanks
 
Haris Karameh
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I see problem here:

queryTeste.append("FROM status s, fabric f ").append("s.descricao like '%"+request.getParameter("name")+"%' ")
your query misses WHERE keyword, it should be:

queryTeste.append("FROM status s, fabric f WHERE ").append("s.descricao like '%"+request.getParameter("name")+"%' ")

single % acts as wildcard %% acts as literal %, so you may want to filter value of request.getParameter("name") and make sure it does not have any '%' inside. If it does you need to prefix them with another '%' or '\%'
 
fabio alonso
Ranch Hand
Posts: 48
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I do not understand...

" If it does you need to prefix them with another '%' or '\%'" ???
 
Haris Karameh
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
sorry for the confusion.
If your request.getParameter("name") contains '%' characters , they should be escaped - replaced with '\%'. Then you query will work
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic