This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes MySQL Parameterization not working Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "MySQL Parameterization not working" Watch "MySQL Parameterization not working" New topic
Author

MySQL Parameterization not working

Richard Pinaroc
Greenhorn

Joined: Jun 03, 2011
Posts: 7
Hello All,

I am trying to parameterize my sql statement but the following mysql statement will return no results.

mysql = "select ?,count(*) as count from mytable where ? is not null and ( ? like '%@abc.com' or ? like '%@def.com' ) and myfield = ? and validate_date > date_sub(NOW(), Interval ? ) group by ?";

pstmt = conn.prepareStatement(mysql);
pstmt.setString(1, "field1");
pstmt.setString(2, "field1");
pstmt.setString(3, "field1");
pstmt.setString(4, "field1");
pstmt.setString(5, "test");
pstmt.setString(6, "2 Month");
pstmt.setString(7, "field1");
rs = pstmt.executeQuery();

Boolean hasrows = rs.next();


After narrowing down the problem, here is what I am left with that works.

mysql = "select "+field1+",count(*) as count from mytable where ? is not null and ( "+field1+" like '%@abc.com' or ? like '%@def.com' ) and myfield = ? and validate_date > date_sub(NOW(), Interval "+dateRange+" ) group by "+field1;

pstmt = conn.prepareStatement(mysql);
pstmt.setString(1, "field1");
pstmt.setString(2, "field1");
pstmt.setString(3, "test");
rs = pstmt.executeQuery();

Boolean hasrows = rs.next();

Any ideas or suggestions?

Here is my environment that I am testing on.
JDK 1.6.0_22
MySQL 5.0.77
NetBeans 7.0

Thank you.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

You can't use field names as parameters. (Nor can you use file names.)
Richard Pinaroc
Greenhorn

Joined: Jun 03, 2011
Posts: 7
Thanks Paul...That's what I thought.
 
 
subject: MySQL Parameterization not working
 
Similar Threads
login problem
How to deal with single quote?
preparedStatement with like where clause
connectivity problem
PreparedStatment - Search Page