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.