AppletTalk.com Java discussions newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  Can I use an array with an IN clause?          AppletTalk.com Forum Index -> Java and Databases View previous topic :: View next topic   Author Message flarosa Guest Posted: Thu May 03, 2007 12:42 am    Post subject: Can I use an array with an IN clause? Hi, I want to execute a prepared statement of the form: SELECT * FROM MyTable WHERE ID IN (1,2,3) Instead of putting in the literal values (1,2,3) I want to substitute an array at runtime. I tried doing this: SELECT * FROM MyTable WHERE ID IN ? I tried passing an array of integers as the parameter, but it didn't work. I can solve the problem by generating a query of the form "WHERE ID IN (?,?,?,?)", but I have to create a new query from scratch each time because the number of integers varies. Is there some general way to do this? Thanks. Back to top Lee Fesperman Guest Posted: Thu May 03, 2007 12:11 pm    Post subject: Re: Can I use an array with an IN clause? flarosa wrote: Quote: Hi, I want to execute a prepared statement of the form: SELECT * FROM MyTable WHERE ID IN (1,2,3) Instead of putting in the literal values (1,2,3) I want to substitute an array at runtime. I tried doing this: SELECT * FROM MyTable WHERE ID IN ? I tried passing an array of integers as the parameter, but it didn't work. That won't work because ? parameters can only be used where a scalar value can occur. Quote: I can solve the problem by generating a query of the form "WHERE ID IN (?,?,?,?)", but I have to create a new query from scratch each time because the number of integers varies. Is there some general way to do this? You'll probably have to provide more details on your application ... where do these lists come from, is it a fixed set, etc.? One general way to do this is to use a lookup table. -- Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com) ============================================================== * The Ultimate DBMS is here! * FirstSQL/J Object/Relational DBMS (http://www.firstsql.com) Back to top Guest Posted: Thu May 03, 2007 1:14 pm    Post subject: Re: Can I use an array with an IN clause? flarosa <frank (AT) franklarosa (DOT) com> writes: Quote: Hi, I want to execute a prepared statement of the form: SELECT * FROM MyTable WHERE ID IN (1,2,3) Instead of putting in the literal values (1,2,3) I want to substitute an array at runtime. I tried doing this: SELECT * FROM MyTable WHERE ID IN ? I tried passing an array of integers as the parameter, but it didn't work. I can solve the problem by generating a query of the form "WHERE ID IN (?,?,?,?)", but I have to create a new query from scratch each time because the number of integers varies. Is there some general way to do this? Don't think so. Someone, please correct me if I'm wrong. But even with your current approach you may run into problems since the number of elements in an IN-list often is limited. The limit varies between databases. Without knowing more about your app it is a bit hard to say what you could do instead. I'm guessing that the records you want from MyTable are somehow related, but your app is hiding this fact from the dbms by just asking for a seemingly random collection of IDs. -- dt Back to top Alfred Guest Posted: Fri May 04, 2007 2:39 am    Post subject: Re: Can I use an array with an IN clause? flarosa wrote: Quote: ... I tried doing this: SELECT * FROM MyTable WHERE ID IN ? That's impossible in your case. Quote: I tried passing an array of integers as the parameter, but it didn't work. I can solve the problem by generating a query of the form "WHERE ID IN (?,?,?,?)", but I have to create a new query from scratch each time because the number of integers varies. No - you have to define more than one Statements. Alfred Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         AppletTalk.com Forum Index -> Java and Databases All times are GMT Page 1 of 1   Jump to: Select a forum Java Newsgroups----------------Java DiscussionsWeb-based java games comp.lang.java 3D Graphics API's for Java Java: Support and criticism Java System Announcements comp.lang.java.api JavaBeans Java and CORBA Java and Databases comp.lang.java.developer Java GUI Toolkits Java Help comp.lang.java.javascript JVM, native methods and hardware comp.lang.java.misc Java Language Programming Security and Java comp.lang.java.setup Java Software Tools comp.lang.java.tech ibm.software.java.linux  Non-English Java Newsgroups----------------Java Language (German)Language Java (Français)Java (Italian)  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.