Check sql string in servlet

[violinsoundcool]

You guys are probably going to think I'm crazy, but if you could check my work here, I would be much obliged. I just need to check the java. Here is the sql string that I need produced:

SELECT tdydno, tdload, tltype, tlacctno, taname, tlcymd,
     tlamount, tdstatus, tdsrn 
FROM SCF031J3 
WHERE tdstatus='C' AND ucase (taname) LIKE ucase ('%
       name%') AND ucase(tacity) LIKE ucase ('%city%') 
       AND ucase(tastate) LIKE ucase ('%state%') ORDER BY 
       tlcymd DESC

Here is the java code that I have:

public void doPost(HttpServletRequest request, 
     HttpServletResponse response) throws 
     ServletException, IOException
     {
	try
     {
     Connection opnConn = ConnDB.opnAS400Conn();

     List beanList = new ArrayList();
				
     StringBuffer sql = new StringBuffer(); 
			
     sql.append("SELECT tdydno, tdload, tltype, tlacctno, 
              taname, tlcymd, tlamount, tdstatus, tdsrn FROM 
              SCF031J3 WHERE tdstatus='C' ");           
			
     if(!"".equals(request.getParameter("name")))
     {}
     else
     {
          sql.append("AND ucase(taname) LIKE ucase ('%");
          sql.append(request.getParameter("name"));
          sql.append("%')");
     }
			
     if(!"".equals(request.getParameter("city")))
     {}
     else
     {
          sql.append("AND ucase(tacity) LIKE ucase ('%");
          sql.append(request.getParameter("city"));
          sql.append("%')");
     }
			
     if(!"".equals(request.getParameter("state")))
     {}
     else
     {
          sql.append("AND ucase(tastate) LIKE ucase ('%");
          sql.append(request.getParameter("state"));
          sql.append("%')");
     }
			
     if(!"".equals(request.getParameter("item")))
     {}
     else
     {
          sql.append("AND ucase(tditem) LIKE ucase ('%");
          sql.append(request.getParameter("item"));
          sql.append("%')");
     }
			
     sql.append(" ORDER BY tlcymd DESC");
			
     ResultSet MyQuery = ScrapSearchRS.myRS(sql.toString(), 
          opnConn);
					
     while(MyQuery.next())
     {
          BeanScrapSearch infoBean = new BeanScrapSearch();
          infoBean.setYard(MyQuery.getString("tdydno"));
          infoBean.setLoad(MyQuery.getString("tdload"));
          infoBean.setType(MyQuery.getString("tltype"));
          infoBean.setAcctno(MyQuery.getString("tlacctno"));
          infoBean.setName(MyQuery.getString("taname"));
          infoBean.setDate(MyQuery.getString("tlcymd"));
          infoBean.setAmount(MyQuery.getString("tlamount"));
          infoBean.setStatus(MyQuery.getString("tdstatus"));
          infoBean.setSrn(MyQuery.getString("tdsrn"));
          beanList.add(infoBean);
     }

     request.setAttribute("beanList", beanList);
     getServletContext().getRequestDispatcher
     ("Results.jsp").forward(request,response);
}

catch(Exception e)
{
	e.printStackTrace();
}
}

I've looked it over about 10 times. The sql string returns 23 results when I try it on the 400. I'm pretty sure that the string is okay. I'm just unsure about the code, because the search will return results, they're just incorrect. They have nothing to do with the search criteria entered.

By the way, my server has decided to work pretty well. The only thing is when it is running, everything runs very slowly. However, the application will run now without WDSC hanging up. It takes about 2 full minutes from the time that I select "run on server" to show the results. Is this typical?

[violinsoundcool]

Re: Check sql string in servlet

I think the reason that it's taking so long is that it's pulling every single entry from the file. Somehow, the WHERE clause is not being applied. That's what it looks like, anyway.

[violinsoundcool]

Re: Check sql string in servlet

Well, I fixed it. If anyone else ever has this problem I just replaced each instance of this:

     if(!"".equals(request.getParameter("name")))
     {}
     else
     {
          sql.append("AND ucase(taname) LIKE ucase ('%");
          sql.append(request.getParameter("name"));
          sql.append("%')");
     }

with this:

     if("name"!=null)
     {
          sql.append(" AND ucase(taname) LIKE ucase ('%");
          sql.append(request.getParameter("name"));
          sql.append("%')");
     }

[bryce4president]

Re: Check sql string in servlet

I think I suggested that you do that back in one of the other threads. You can't test "null" like that. When checking the value of a string in java you have you use the "string".equals(variable);

[bryce4president]

Re: Check sql string in servlet

You can check the value that your sql returns by placing your sql in F.R.O.G.

It will show you what the statement should return

[violinsoundcool]

Re: Check sql string in servlet

Well, this seems to be working fine. If it's working now, is there something that might cause a problem in the future?

[kpmac]

Re: Check sql string in servlet

You might want to test the variable to make sure it is not equal to null and blanks.

if(name!=null && !name.equals("")){
}

I would also suggest removing your query logic from the servlet and putting it in a Data Access Object DAO.

[bryce4president]

Re: Check sql string in servlet

He needs to check the value of the request parameter. The request parameter will not be null. It will be "". This is because the form doesn't submit a null value it submits an empty string to the request. So testing !"".equals(request.getParameter("myParm") will be sufficient.

What kpmac means by using a DAO is that you will want to put your request parameters into variables and then pass them to another java class with the appropriate methods to create your "transport object" in your case a bean and return that back to the servlet. I eluded to this in the tutorial but have yet to write up the example for it. Its not really that hard, your servlet ends up just making a call out to the class and that call should return a bean. You just need to create an instance of that same bean type in your servlet to hold the value.

[violinsoundcool]

Re: Check sql string in servlet

So, you're saying that what I had before should work? Because it didn't. The if statements were never found to be true. Consequently, thost sections of the sql string were never appended. As a result, every record in the file would be returned.

[bryce4president]

Re: Check sql string in servlet

Your logic from above is backwards....

if the field is !"".equals() then you WANT it in there. Move the supporting code inside the 'if' and delete the 'else' and you'll see magical things beginning to happen

[violinsoundcool]

Re: Check sql string in servlet

You're right, Bryce (as always). I just have one question. How come both methods yield the same result?

if(!"".equals(request.getParameter("name")))

does the same thing as

if("name"!=null)

I understand what you said about the form not submitting a null value, and it makes sense to me. However, it seems that if it did not submit a null value, then the second method would not work, right?

[bryce4president]

Re: Check sql string in servlet

I don't recall exactly the reason for not needing null. I'm pretty sure there is a "special" case situation. You could run into problems if the user decides to use the "back" button and resubmit with different criteria. The value of your old variable wouldn't be null. It would become blank since null is a matter of existence as opposed to a matter of value.

Say your user enters a city and state. They hit enter and see their results. Then they hit the back button and try by some other zip code thus leaving city and state blank. Well when you resubmit your form your city and state variables won't be null because they DO exist from the previous submission. But the parameter sent from the form will be "".equals()

I'm pretty sure (if memory serves me right) this is why I always just code to the request parameter and not the existence of a variable. Its safer.

[violinsoundcool]

Re: Check sql string in servlet

Cool. I'll definitely side with the safer route.