aspose file tools*
The moose likes JDBC and the fly likes pass array to query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "pass array to query" Watch "pass array to query" New topic
Author

pass array to query

phani kon
Ranch Hand

Joined: Apr 06, 2005
Posts: 251
Hi,

How would I pass an array to a query?

for ex:

public Hashtable getMonth(String cat,int[] month, int year) throws

{

need to pass month as a array to the below query

SELECT SUM(aaa), SUM(bbb), SUM(ddd) FROM emp_tbl WHERE AND YEAR(DATE)=2008 AND MONTH(DATE) =").append(month)

}
Can anyone help me in this case please?

Thanks a lot
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31054
    
232

Lakshmi,
You can build the where clause dynamically.

If you had two months, you would want it to look like:
AND MONTH(DATE) in ( ?, ? )
If you had five months, you would want it to look like:
AND MONTH(DATE) in ( ?, ?, ?, ?, ? )

A loop builds this up well at runtime. My example uses the question mark binding variables used in prepared statements. Prepared Statements are generally recommended, but you can use the same technique with your array elements directly.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
phani kon
Ranch Hand

Joined: Apr 06, 2005
Posts: 251
Now I am passing an monthstring (1,2, 3) to the query

StringBuffer query = new StringBuffer("SELECT SUM(AAAAA), SUM(DDDDDDD) ");
query.append(" FROM "+Constants.emp+" WHERE CAT
='").append(category).append("' AND YEAR(DATE)=").append(year);
query.append(" AND MONTH(DATE)IN").append("(").append(monthStr).append(")");

query.append(" AND MONTH(DATE)IN").append("(").append(monthStr).append(")");// I got the mql syntax error at for monthstr
java.sql.SQLException: Syntax error or access violation: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '1,2,3)' at line 1

what was the wrong in the above query. Please let me know
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31054
    
232

Lakshmi,
Try outputting the value of the query variable to see what it built. Then run it through the command line mySql to get the actual error message. Or post it here if you don't have access.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: pass array to query