any or *

I've got a little sql question. Let's say you have 4 drop-down boxes, and the user can select different options in all 4 boxes, then hit submit. The values get passed to a servlet which does a query to a database with the restrictions specified in the box. ex: Select system_names from tb__systems where option1=box1 and option2=box2 and option3=box3 and option4=box4; (of course option and box are replaced by real values, I just can't be bothered writing the whole thing). I have managed to get this to work fine, but what if you don't want to narrow it down that much. What if you only want to select 2 options, and leave the other 2 at "any". How do you write a piece of code (I was thinking it would have to be an "if" statement) that checks for this, and makes the query string accordingly? Here's what I had with just 2 boxes: if(env != "any") { query = querybase + "env_identifier=" + env; if(loc != "any") query = query + "and loc_identifier=" + loc; else query = query; }else { if(loc != "any") query = querybase + "loc_identifier = " + loc; else query = "select system_name from tb__systems"; }

(I hope the code came out ok) Any ideas? what am I doing wrong? Thanks, Annette

What if you named each of the drop down boxes something like optionBox1, optionBox2, optionBox3, and optionBOx4. Then you could use : Enumeration enum = request.getParameterNames();StringBuffer query = new StringBuffer("select system_names from tb_systems where 1=1"); // 1=1 is so you can append all additional criteria with an ' and ' rather then having to decide if this is the first criteria in the where clause or not

while (enum.hasMoreElements() ) { String param = (String)enum.nextElement(); if (param.startsWith("optionBox")) { String val = request.getParameter(param); if (!val.equals("defualtvalue")) query.append(" and " + param + " = '" + val + "' "); } } This way you can have as many option boxes as you want as long as they follow the naming scheme and as long as their default value is called the same thing. HTH Brianbcnice@mindspring.com

Ok, I've run into one small problem. The above code given by Brian would work great, except for the "param". param ends up being whatever the option box is called. ex: optionbox1, optionbox2, etc when you append that to the query, it ends up being select system_names from tb__systems where 1=1 and optionbox1=vl and...... is there a way to chop off parts of words? I mean, if I were to call my option boxes things like: optionEnv, optionLoc, optionOwner is there a way to cut off the option part? Kind of like query.append(), only I would be removing the first 6 letters or something. Annette [This message has been edited by Annette L'Heureux (edited February 01, 2001).]

That's exactly what I was looking for. I knew it must exist, because I had seen similar stuff in c++, but I wasn't sure what it was. I spent the entire afternoon yesterday looking for it, but I guess I wasn't looking in the right place. Thanks! Annette

Annette L'Heureux
Ranch Hand

Joined: Dec 07, 2000
Posts: 135

posted Feb 02, 2001 08:24:00

0

Ok, one last problem and I think I've got it solved. Right now I'm getting an error that the method "executeQuery(java.lang.StringBuffer)" is not found in the interface java.sql.Statement because I use stmt.executeQuery(query) (where query is now a StringBuffer) How do I solve this? What is the statements purpose and do I have to use it?

Brian Nice
Ranch Hand

Joined: Nov 02, 2000
Posts: 195

posted Feb 02, 2001 09:34:00

0

executeQuery expects a String as a parameter so you need to convert the StringBuffer to a string. Try: stmt.executeQuery(query.toString() ); Brian

Annette L'Heureux
Ranch Hand

Joined: Dec 07, 2000
Posts: 135

posted Feb 02, 2001 12:42:00

0

thanks! I thought of that too! (I'm actually amazed that I found it on my own!) Everything seems to be working fine now, I'm just working on the formatting! Thanks for all your help Brian! Annette