Another problem with syntax for SQL statement

[Rob Petterson]

Well, I think I'm close to cracking this one but I need a little help now.
Basically what I'm doing is I have a form called advancedSearch.jsp, where a user can enter certain search criteria into any of the fields displayed to get a more exact search for a product.
The field names are listed in the params of the method listed below.
I originally had lowYear, highYear, lowPrice and highPrice defined as ints but got a compile error within Tomcat, so I changed them to Strings (I thought that perhaps this was OK seeing as they were being fed into a string format for the SQL statement??(still not sure whether this will work when it comes to doing > or < comparisons though)
Anyway, the result I'm getting is that ALL products are listed when I fill in any one of the fields within advancedSearch.jsp.

 public Coin[] viewCoins(String lowYear, String highYear, String lowPrice, String highPrice,
                            String keyword, String suffix, String country, String type)
                            throws SQLException, Exception
    {
        String error;
        Coin[] retVal = null;
        ArrayList coins = new ArrayList();
        String queryString = ("SELECT * FROM Coin WHERE Year > '" + lowYear + "' OR Year < '" + highYear +
                          "' OR Product_Price > '" + lowPrice + "' OR Product_Price < '"+ highPrice +
                          "' OR Year = '" + keyword + "' OR Product_Price = '" + keyword + "' OR Suffix = '"+ keyword +
                             "' OR Country = '"+ keyword + "' OR Type = '" + keyword +
                             "' OR Product_Category LIKE '%" + keyword  +
                             "%' OR Product_Brief_Desc LIKE '%" + keyword +
                             "%' OR Product_Full_Desc LIKE '%" + keyword +
                         "%' OR Country = '" + country +
                         "' OR Type = '" + type +"'");
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(queryString);
        while (rs.next())
        {
            coins.add(new Coin(rs.getInt(1), rs.getFloat(2), rs.getString(3), rs.getString(4),
                            rs.getString(5),rs.getInt(6),rs.getString(7),rs.getString(8),
                            rs.getString(9),rs.getString(10),rs.getString(11)));
        }
        return (Coin[])coins.toArray(new Coin[coins.size()]);
    }
  

Any ideas?
[ May 04, 2003: Message edited by: Rob Petterson ]

[Irene Loos]

Rob,
When you do "OR", the query will return you all results that match first case OR second. Try to do "AND". You could make your query a little more sophisticated and efficient by adding conditional statements; such as if user enters data in the particular field then you add this field to your query. Of cause you will need to use string buffer to build your string for query and put validation in your jsp that will not allow user to enter empty spaces in the fields in the first and the last characters of their input data.
[ May 04, 2003: Message edited by: Irene Loos ]

[Rob Petterson]

Thanks Irene.
I understand what you're saying about making it a bit more sophisticated by adding conditional statements. I have been for some time (days) now been trying to work out how to do this but have only managed to come up with the method I posted. It's pretty rough I know.
Would it be asking too much for you to get me started with how to code this properly?
Rob.

[Irene Loos]

Try something like this:

 
StringBuffer strBuffer = new StringBuffer();
strBuffer.append("SELECT * FROM Coin WHERE "); 
if (lowYear.length() > 0) {
  strBuffer.append("Year > '" + lowYear);
}
if (highYear.length() > 0) {
 strBuffer.append("AND Year < '" + highYear);
}
 and so on .....
  

At the end of your code put:
String queryString = strBuffer.toString();

[Rob Petterson]

Thanks Irene,
I will try this out in the morning and will let you know how I get on.
Cheers, Rob

[Rob Petterson]

Hi Irene,
I'm getting an error message regarding the syntax of the SQL statements. I've been playing around with it, changing things around but can't get it to work.
If I enter say, 500 in the hightYear field and Charles II in the Keywords field, this is the error message I get:
org.apache.jasper.JasperException: Syntax error or access violation, message from server: "You have an error in your SQL syntax near 'AND Product_Price < '500AND Product_Full_Desc LIKE '%Charles IIAND Country = 'En' at line 1"

Also, I want to be able to put a % on each side of the keyword and suffix variable as well.
I've just realized that I have to put an 'All Coin Type' as the default for the Coin Type drop-down menu field within the form (Obviously all the other types are listed underneath).
I thought maybe that I could put this into the if statement something like:
if (type.equals("All Coin Types"))
{
strBuffer.append("AND Type =//not sure what to put here in order to list all the coins for that country (there's a Country field next to the Coin Type field)
}
Here's the code:

 public Coin[] viewCoins(String origin, String coinType) throws SQLException, Exception
    {
        String error;
        Coin[] retVal = null;
        ArrayList coins = new ArrayList();
        String queryString = ("SELECT * FROM Coin WHERE Country = '" + origin + "' AND Type = '"+ coinType +"'");
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(queryString);
        while (rs.next())
        {
            coins.add(new Coin(rs.getInt(1), rs.getFloat(2), rs.getString(3), rs.getString(4),
                            rs.getString(5),rs.getInt(6),rs.getString(7),rs.getString(8),
                            rs.getString(9),rs.getString(10),rs.getString(11)));
        }
        return (Coin[])coins.toArray(new Coin[coins.size()]);
    }
    public Coin[] viewCoins(String lowYear, String highYear, String lowPrice, String highPrice,
                            String keyword, String suffix, String country, String type)
                            throws SQLException, Exception
    {
        String error;
        Coin[] retVal = null;
        ArrayList coins = new ArrayList();
         StringBuffer strBuffer = new StringBuffer();
         strBuffer.append("SELECT * FROM Coin WHERE ");
           if (lowYear.length() > 0)
            {
             strBuffer.append("Year > '" + lowYear);
            }
           if (highYear.length() > 0)
            {
             strBuffer.append("AND Year < '" + highYear);
            }
           if (lowPrice.length() >0)
            {
             strBuffer.append("AND Product_Price > '" + lowPrice);
            }
           if (highPrice.length() >0)
            {
             strBuffer.append("AND Product_Price < '"+ highPrice);
            }
           
           if (keyword.length() > 0)
            {
             strBuffer.append("AND Product_Full_Desc LIKE '%" + keyword);
            }
           if (suffix.length() > 0)
            {
             strBuffer.append("AND Suffix LIKE '%" + suffix);
            }
           if (country.length() > 0)
            {
             strBuffer.append("AND Country = '" + country);
            }
           if (type.length() > 0) 
            {
             strBuffer.append("AND Type = '" + type);
            }
        String queryString = strBuffer.toString();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(queryString);
        while (rs.next())
        {
            coins.add(new Coin(rs.getInt(1), rs.getFloat(2), rs.getString(3), rs.getString(4),
                            rs.getString(5),rs.getInt(6),rs.getString(7),rs.getString(8),
                            rs.getString(9),rs.getString(10),rs.getString(11)));
        }
        return (Coin[])coins.toArray(new Coin[coins.size()]);
    } 

Thanks, Rob
[ May 05, 2003: Message edited by: Rob Petterson ]

[Irene Loos]

First you need closing single quote ("' ") for all your strings and space before next AND. If you want to add '%' after each string than add "%' ". Try to make sure that your SQL is correct:

public Coin[] viewCoins(String lowYear, String highYear, String lowPrice, String highPrice,                       
String keyword, String suffix, String country, String type) throws SQLException, Exception {
        String error;
        Coin[] retVal = null;
        ArrayList coins = new ArrayList();
        StringBuffer strBuffer = new StringBuffer();
        strBuffer.append("SELECT * FROM Coin WHERE ");
        if (lowYear.length() > 0) {
strBuffer.append("Year > '" + lowYear + "' ");
        }
        if (highYear.length() > 0) {
        strBuffer.append("AND Year < '" + highYear + "' ");
        } 
if (lowPrice.length() >0) {
        strBuffer.append("AND Product_Price > '" + lowPrice + "' ");
        }
        if (highPrice.length() >0) {
        strBuffer.append("AND Product_Price < '"+ highPrice + "' ");
        }
if (keyword.length() > 0) {
        strBuffer.append("AND Product_Full_Desc LIKE '%" + keyword + "' ");
        }
        if (suffix.length() > 0) {
        strBuffer.append("AND Suffix LIKE '%" + suffix + "' ");
        }
        if (country.length() > 0) {
        strBuffer.append("AND Country = '" + country + "' ");
}
        if (type.length() > 0) {
        strBuffer.append("AND Type = '" + type + "' ");
        }        
String queryString = strBuffer.toString(); 
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(queryString);
        while (rs.next()) {
            coins.add(new Coin(rs.getInt(1),rs.getFloat(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getInt(6),rs.getString(7),rs.getString(8),rs.getString(9),rs.getString(10),rs.getString(11)));
}
        return (Coin[])coins.toArray(new Coin[coins.size()]);
}

If you need "All Coin Types" than do not put condition for it. Instead try something like that:

if (!(type.equals("All Coin Types"))) {
    strBuffer.append("AND Type = '" + type + "'");
}

[ May 06, 2003: Message edited by: Irene Loos ]

[Rob Petterson]

Hi Irene,
This is strange. I'm still getting the same SQL error appearing in my application - even after cut and pasting in your corrected code.
I've opened up a Dos screen and navigated to where my DB is and manually typed in a scenario using the code within the if statements eg:
SELECT * FROM Coin WHERE Year > 1200 AND Year <1980 AND AND Product_Price <1500;
It works and gives me all records that match that criteria.
So once again I'm stumped.

 public Coin[] viewCoins(String lowYear, String highYear, String lowPrice, String highPrice,
                         String keyword, String suffix, String country, String type)
                               throws SQLException, Exception
     {
      String error;
      Coin[] retVal = null;
      ArrayList coins = new ArrayList();
      StringBuffer strBuffer = new StringBuffer();
      strBuffer.append("SELECT * FROM Coin WHERE ");
               if (lowYear.length() > 0)
                 {
                   strBuffer.append("Year > '" + lowYear + "' ");
                 }
               if (highYear.length() > 0)
                 {
                   strBuffer.append("AND Year < '" + highYear + "' ");
                 }
               if (lowPrice.length() >0)
                 {
                   strBuffer.append("AND Product_Price > '" + lowPrice + "' ");
                 }
               if (highPrice.length() >0)
                 {
                   strBuffer.append("AND Product_Price < '"+ highPrice + "' ");
                 }
               if (keyword.length() > 0)
                 {
                   strBuffer.append("AND Product_Full_Desc LIKE '%" + keyword + "' ");
                 }
               if (suffix.length() > 0)
                 {
                   strBuffer.append("AND Suffix LIKE '%" + suffix + "' ");
                 }
               if (country.length() > 0)
                 {
                   strBuffer.append("AND Country = '" + country + "' ");
                 }
               if (type.length() > 0)
                 {
                   strBuffer.append("AND Type = '" + type + "' ");
                 }
               if (!(type.equals("All Coin Types")))
                 {
                   strBuffer.append("AND Type = '" + type + "'");
                 }
       String queryString = strBuffer.toString();
       Statement stmt = con.createStatement();
       ResultSet rs = stmt.executeQuery(queryString);
       while (rs.next())
          {
           coins.add(new Coin(rs.getInt(1), rs.getFloat(2), rs.getString(3),
                              rs.getString(4),  rs.getString(5),rs.getInt(6),
                              rs.getString(7),rs.getString(8),rs.getString(9),
                              rs.getString(10),rs.getString(11)));
          }
       return (Coin[])coins.toArray(new Coin[coins.size()]);
   } 

Rob

[ May 06, 2003: Message edited by: Rob Petterson ]

[Irene Loos]

Rob,
I cannot test your code since I do not have access to your database. Make sure that you have all paramenters defined correctly. In your sql all stings have to have single quotes around them and int, long, short, etc do not. Put a brake points on

 ResultSet rs = stmt.executeQuery(queryString);  

and see how is your sql looks like after it was build. Or you can put

System.out.println(queryString);  

after

 String queryString = strBuffer.toString(); 

Copy and run this query to see that you do not have any mistakes.
[ May 06, 2003: Message edited by: Irene Loos ]

[Rob Petterson]

Thanks Irene. I try this out tonight.
Rob

[jenny]

"SELECT * FROM Coin WHERE Year > 1200 AND Year <1980 AND AND Product_Price <1500;"
Hi Rob
You have 2 'AND' statements in your code, also maybe try using
'where Year betweeen 1200 and 1980'
Here is an example of a similar thing I did if it helps...
StringBuffer ssql = new StringBuffer();
ssql.append("SELECT p.PropertyID, p.Town, c.County,p.Bedrooms,p.Type,p.Price FROM tblProperty p, tblCounty c WHERE p.County = c.County");
ssql.append(" AND c.County = ucase('"+vCounty+"')");
ssql.append(" AND p.Town LIKE ucase('"+vArea+"') ");
if (vType.equals("Any"))
{ ssql.append(" AND p.Type in('Flat','House')");}
else
{ ssql.append(" AND p.Type='"+vType+"' ");}
ssql.append(" AND p.Price Between '"+vFrom+"' And '"+vTo+"' ");
ssql.append(" AND p.Status = 'LIVE'");

ssql.append(" OR c.County = ucase('"+vCounty+"')");
ssql.append(" AND p.PostCode LIKE UCase('"+vArea+"') ");

if (vType.equals("Any"))
{ ssql.append(" AND p.Type in('Flat','House')");}
else
{ ssql.append(" AND p.Type='"+vType+"' ");}
ssql.append(" AND p.Price Between '"+vFrom+"' AND '"+vTo+"' ");
ssql.append(" AND p.Status = 'LIVE'");
ssql.append(" ORDER BY p.Price;");
ResultSet rs = stmt.executeQuery(ssql.toString());
Jenny

[Rob Petterson]

thanks Jenny for you input, that AND AND was just a typo from me and not in the code.

[Rob Petterson]

Evening Irene,
I've got it to work! Yee Haa!
I've included the code here to show you what I've added.
I had to convert the first 4 params from strings to ints and floats. Secondly I deleted the single quotes from around these variables.
Thirdly, I hadn't set the field length in the 'keyword' field in the original jsp form, so I was getting a value of null which was giving a NullPointerException.
Everything works now EXCEPT one little thing. You'll notice when reading the code that if you don't give a 'lowYear' value, any other vals that you give will start with AND in the sql query I.e. this:
SELECT * FROM Coin WHERE AND Country = 'English'

 public Coin[] viewCoins(String lowYear, String highYear, String lowPrice, String highPrice,
                         String keyword, String suffix, String country, String type)
                               throws SQLException, Exception
     {
     /** int lowYearVal = new Integer(lowYear).intValue();
      int highYearVal = new Integer(highYear).intValue();
      float lowPriceVal = new Float(lowPrice).floatValue();
      float highPriceVal = new Float(highPrice).floatValue();
      System.out.println(" lowYear " + lowYearVal + " highYear " + highYearVal +
                         " lowPrice " + lowPriceVal + " highPrice " + highPriceVal +
                         " keyword " + keyword + " suffix " + suffix +
                         " country " + country + " type " + type);
       **/
      String error;
      Coin[] retVal = null;
      ArrayList coins = new ArrayList();
      StringBuffer strBuffer = new StringBuffer();
      strBuffer.append("SELECT * FROM Coin WHERE ");
               if (lowYear.length() > 0)
                 {
                   int lowYearVal = new Integer(lowYear).intValue();
                   strBuffer.append("Year > " + lowYearVal + " ");
                 }
               if (highYear.length() > 0)
                 {
                   int highYearVal = new Integer(highYear).intValue();
                   strBuffer.append("AND Year < " + highYearVal + " ");
                 }
               if (lowPrice.length() >0)
                 {
                   float lowPriceVal = new Float(lowPrice).floatValue();
                   strBuffer.append("AND Product_Price > " + lowPriceVal + " ");
                 }
               if (highPrice.length() >0)
                 {
                   float highPriceVal = new Float(highPrice).floatValue();
                   strBuffer.append("AND Product_Price < "+ highPriceVal + " ");
                 }
               if (keyword.length() > 0)
                 {
                   strBuffer.append("AND Product_Full_Desc LIKE '%" + keyword + "%' ");
                 }
               if (suffix.length() > 0)
                 {
                   strBuffer.append("AND Suffix LIKE '%" + suffix + "%' ");
                 }
               if (country.length() > 0)
                 {
                   strBuffer.append("AND Country = '" + country + "' ");
                 }
               /**if (type.length() > 0)
                 {
                   strBuffer.append("AND Type = '" + type + "' ");
                 }**/
               if (!(type.equals("All Coin Types")))
                 {
                   strBuffer.append("AND Type = '" + type + "'");
                 }
       String queryString = strBuffer.toString();
       System.out.println("queryString value is " + queryString);
       Statement stmt = con.createStatement();
       ResultSet rs = stmt.executeQuery(queryString);
       System.out.println("rs = " + rs);
       while (rs.next())
          {
           coins.add(new Coin(rs.getInt(1), rs.getFloat(2), rs.getString(3),
                              rs.getString(4),  rs.getString(5),rs.getInt(6),
                              rs.getString(7),rs.getString(8),rs.getString(9),
                              rs.getString(10),rs.getString(11)));
          }
       return (Coin[])coins.toArray(new Coin[coins.size()]);
   } 

ps
I've really got to get into the habbit of using s.o.p's throughout my code to what vals variables have been given.

Rob

[ May 07, 2003: Message edited by: Rob Petterson ]

[Rob Petterson]

OK, I've just had a flash on insight.
This will sort it out now:

 boolean seenAFieldValue=false;
            if (lowYear.length() > 0)  
               { 
                   int lowYearVal = new Integer(lowYear).intValue();
                   strBuffer.append("Year > " + lowYearVal + " "); 
   seenAFieldValue= true;
                }   
            if (highYear.length() > 0)    
               {     
                  int highYearVal = new Integer(highYear).intValue();
  
  if (seenAFieldValue)    
 {
strBuffer.append(" AND ");
 }
                  strBuffer.append(" Year < " + highYearVal + " "); 
 seenAFieldValue= true;    
               } 
            if (lowPrice.length() >0)    
              { 
                  float lowPriceVal = new Float(lowPrice).floatValue();   
 if (seenAFieldValue)    
 {
strBuffer.append(" AND ");
 }   
                  strBuffer.append(" Product_Price > " + lowPriceVal + " "); 
   seenAFieldValue= true;  
              } 
            if (highPrice.length() >0)      
             {      
               float highPriceVal = new Float(highPrice).floatValue();  
 if (seenAFieldValue)    
 {
strBuffer.append(" AND ");
 }   
               strBuffer.append(" Product_Price < "+ highPriceVal + " "); 
 seenAFieldValue= true; 
             } 
           if (keyword.length() > 0)       
            {      
 if (seenAFieldValue)    
 {
strBuffer.append(" AND ");
 }   
              strBuffer.append(" Product_Full_Desc LIKE '%" + keyword + "%' "); 
 seenAFieldValue= true; 
            }          
          if (suffix.length() > 0)    
            {    
 if (seenAFieldValue)    
 {
strBuffer.append(" AND ");
 }   
               strBuffer.append(" Suffix LIKE '%" + suffix + "%' "); 
 seenAFieldValue= true;  
            }
         if (country.length() > 0)     
            {   
 if (seenAFieldValue)    
 {
strBuffer.append(" AND ");
 }   
              strBuffer.append(" Country = '" + country + "' ");
 seenAFieldValue= true;
             }   
            
          if (!(type.equals("All Coin Types")))    
             {      
 if (seenAFieldValue)    
 {
strBuffer.append(" AND ");
 }   
             strBuffer.append(" Type = '" + type + "'");
 seenAFieldValue= true;      
             }    

    String queryString = strBuffer.toString();     
    System.out.println("queryString value is " + queryString);   
    Statement stmt = con.createStatement();   
    ResultSet rs = stmt.executeQuery(queryString); 
    System.out.println("rs = " + rs);       while (rs.next()) 
         { 
          coins.add(new Coin(rs.getInt(1), rs.getFloat(2), rs.getString(3),  rs.getString(4),  rs.getString(5),rs.getInt(6), 
                                                   rs.getString(7),rs.getString(8),rs.getString(9),   rs.getString(10),rs.getString(11))); 
         }     
  return (Coin[])coins.toArray(new Coin[coins.size()]); 
 

Irene, thankyou for all your help on this. Very much appreciated.

Rob
[ May 08, 2003: Message edited by: Rob Petterson ]

[Irene Loos]

Great job, Rob! Do not forget to remove s.o.p. before you move your code to any kind of "real world" or your console will get a lot IO activity. Good luck.
[ May 08, 2003: Message edited by: Irene Loos ]