• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

pass array to query

 
phani kon
Ranch Hand
Posts: 251
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34073
335
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
phani kon
Ranch Hand
Posts: 251
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34073
335
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic