• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

pass array to query

 
Ranch Hand
Posts: 251
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic