File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
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
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: 18989
    
    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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: MySQL Parameterization not working