Home arrow static arrow Java Programming [Archive] - JDBC Problem with Prepared Statement with like operator
Warning: Creating default object from empty value in /www/htdocs/w008deb8/wiki/components/com_staticxt/staticxt.php on line 51
Java Programming [Archive] - JDBC Problem with Prepared Statement with like operator
This topic has 12 replies on 1 page.

Posts:99
Registered: 1/7/03
JDBC Problem with Prepared Statement with like operator  
Jul 26, 2004 11:06 PM



 
Hi,

I am having a problem when i use prepared statement with like operator query.
if i give a select statement with where clause it works fine but it does fetch any rows if i give a like statement with parameters.

The method takes a query as String and its parameter are arraylist and returns the resultset.

public ResultSet executeQuery(String psquery,ArrayList poparams) throws Exception
{
ResultSet mors = null;
PreparedStatement mops = null;
try
{
mops=con.prepareStatement(psquery);
if(poparams != null)
{
for(int i=0;i<poparams.size();i++)
{
Object value = poparams.get(i);
if(value != null)
{
Object obj = value.getClass();
if(obj.toString().equals("class java.lang.Integer"))
{
mops.setInt(i+1,((Integer)poparams.get(i)).intValue());
}
else
{
mops.setString(i+1,(String)poparams.get(i));
}
}
}
}
mors = mops.executeQuery();
}
catch(Exception e)
{
e.printStackTrace();
}
return mors;
}

For eg:
Query: Select * from emp where empname = ?
poparams : new arraylist().add("dani")
Return resultset

Query: Select * from emp where empname like ?
poparams : new arraylist().add("dani")
Returns empty resultset

Any suggestion would be of much help
Thanx in advance.
regards,
Firmin Dani
 

Posts:37
Registered: 7/23/04
Re: JDBC Problem with Prepared Statement with like operator  
Jul 26, 2004 11:30 PM (reply 1 of 12)



 
you can use query as
Select * from emp where empname like '?'
try this one put ? between ' '
 

Posts:5,119
Registered: 16.08.01
Re: JDBC Problem with Prepared Statement with like operator  
Jul 27, 2004 12:06 AM (reply 2 of 12)



 
Yes and use wildcards, otherwise the like clause doesn't make sense:
Query: Select * from emp where empname like '?'poparams : new arraylist().add("dani%") // all that begin with daniReturns empty resultset
 

Posts:99
Registered: 1/7/03
Re: JDBC Problem with Prepared Statement with like operator  
Jul 27, 2004 12:16 AM (reply 3 of 12)



 
hi,
Thanks for the reply. I have tried it all. Its not working either. I jus refered the java site they had also given the same thing what i had done but still not working http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html
Any help would be much appreciated
ragards,
Firmin Dani
 

Posts:1,995
Registered: 12/10/00
Re: JDBC Problem with Prepared Statement with like operator  
Jul 27, 2004 12:51 AM (reply 4 of 12)



 
Here are some things you can try in order to solve the problem
- Simplify the code - try to make your code run without introducing list of parameters and deciding what to put where, test with simple code aimed at setString().
- Test the query in your query analyzer

When using setString you don't need quotes in the query, JDBC manages those for you

P.S. Please post code using [code][/code] tags, they simplify reading a lot

Mike
 

Posts:99
Registered: 1/7/03
Re: JDBC Problem with Prepared Statement with like operator  
Jul 27, 2004 1:50 AM (reply 5 of 12)



 
Hi,
Thanx belly ripper for the advice. I am posting a simplified code so thats easy to understand
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");   Connection con = DriverManager.getConnection("jdbc:odbc:emp","","");     String query = "SELECT EMPNAME FROM EMP WHERE EMPNAME like ?";   PreparedStatement ps = con.prepareStatement(query);   ps.setString(1,"dani%");   ResultSet rs = ps.executeQuery();   while(rs.next())    System.out.println(rs.getString(1));


This code returns an empty result set.

regards,
Firmin Dani
 

Posts:5,119
Registered: 16.08.01
Re: JDBC Problem with Prepared Statement with like operator  
Jul 27, 2004 2:06 AM (reply 6 of 12)



 
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");   Connection con = DriverManager.getConnection("jdbc:odbc:emp","","");     String query = "SELECT EMPNAME FROM EMP WHERE EMPNAME like '?'";   // '?' <-!!!!PreparedStatement ps = con.prepareStatement(query);   ps.setString(1,"dani%");   ResultSet rs = ps.executeQuery();
 

Posts:99
Registered: 1/7/03
Re: JDBC Problem with Prepared Statement with like operator  
Jul 27, 2004 2:15 AM (reply 7 of 12)



 
Hi,

I have tried all posible options. This is like passing a string literal '?' but Java automatically takes care when you say PreparedStatement.setString() so you dont pass string like what you have mentioned. Anyway i tried that also it shows null pointer exception when u run the code.

regards,
Firmin Dani
 

Posts:99
Registered: 1/7/03
Re: JDBC Problem with Prepared Statement with like operator  
Jul 27, 2004 2:15 AM (reply 8 of 12)



 
Hi,

I have tried all posible options. This is like passing a string literal '?' but Java automatically takes care when you say PreparedStatement.setString() so you dont pass string like what you have mentioned. Anyway i tried that also it shows null pointer exception when u run the code.

regards,
Firmin Dani
 

Posts:3,081
Registered: 2/15/99
Re: JDBC Problem with Prepared Statement with like operator  
Jul 27, 2004 2:47 AM (reply 9 of 12)



 
Also being discussed in the cross post http://forum.java.sun.com/thread.jsp?thread=541479&forum=48&message=2625553

Please don't cross post.
 

Posts:99
Registered: 1/7/03
Re: JDBC Problem with Prepared Statement with like operator  
Jul 27, 2004 4:52 AM (reply 10 of 12)



 
Hi,

Even if I cross post none of them seems to come up with a solution. Further the site doent restrict people to do cross post and that shouldnt be problem with you.

Regards,
Firmin Dani.
 

Posts:21,719
Registered: 98-02-20
Re: JDBC Problem with Prepared Statement with like operator  
Jul 27, 2004 5:02 AM (reply 11 of 12)



 
Even if I cross post none of them seems to come up with a solution. Further the site doent restrict
people to do cross post and that shouldnt be problem with you.

Regards,
Firmin Dani.

The site doesn't restrict it per se, but you have to understand that you're wasting everyone's time when you do.

Let's say somebody on the other thread comes up with a working solution, but no one who continues to bang away on this thread knows about it. You walk away with your answer, but folks here continue to do their best to solve your problem. You're wasting their time - get it?

If you really want to increase your audience by posting to more than one forum, decide which one matches the subject matter the best and post its URL as a link in other forums. I think that's considered acceptable.

Like every other society, there are norms of behavior that you're expected to follow. Maybe the software won't enforce it, but the other volunteers in the community on whom you depend for answers will. Folks might decide that you're rude and refuse to help you in the future.

So for your own good - don't cross-post.

%
 

Posts:1,995
Registered: 12/10/00
Re: JDBC Problem with Prepared Statement with like operator  
Jul 27, 2004 5:13 AM (reply 12 of 12)



 
It is not the site that can/should restrict people to crosspost. It is ethics of the community, we don't work here, we don't earn from this forum, we are a community for ideas/information/knowledge... interchange. And multiple posts are a pollution in the knowledge base, they also reduce the effectiveness of the participators.

You got enough information and ideas and posted no feedback/debug information besides 'it is still not working'.

If you want to improve your life here read this article called [url http://www.catb.org/~esr/faqs/smart-questions.html]How To Ask Questions The Smart Way[/url] and you will understand why crossposting is evil.

Mike
 
This topic has 12 replies on 1 page.