Two Laptop Bag*
The moose likes JDBC and the fly likes Another problem with syntax for SQL statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Another problem with syntax for SQL statement" Watch "Another problem with syntax for SQL statement" New topic
Author

Another problem with syntax for SQL statement

Rob Petterson
Ranch Hand

Joined: Jan 23, 2002
Posts: 149
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.

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

Rob Petterson
SCJP
Irene Loos
Ranch Hand

Joined: Apr 15, 2002
Posts: 78
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 ]

Irene Loos
Rob Petterson
Ranch Hand

Joined: Jan 23, 2002
Posts: 149
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
Ranch Hand

Joined: Apr 15, 2002
Posts: 78
Try something like this:

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

Joined: Jan 23, 2002
Posts: 149
Thanks Irene,
I will try this out in the morning and will let you know how I get on.
Cheers, Rob
Rob Petterson
Ranch Hand

Joined: Jan 23, 2002
Posts: 149
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:

Thanks, Rob
[ May 05, 2003: Message edited by: Rob Petterson ]
Irene Loos
Ranch Hand

Joined: Apr 15, 2002
Posts: 78
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:

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

[ May 06, 2003: Message edited by: Irene Loos ]
Rob Petterson
Ranch Hand

Joined: Jan 23, 2002
Posts: 149
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.

Rob

[ May 06, 2003: Message edited by: Rob Petterson ]
Irene Loos
Ranch Hand

Joined: Apr 15, 2002
Posts: 78
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 and see how is your sql looks like after it was build. Or you can put

after

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

Joined: Jan 23, 2002
Posts: 149
Thanks Irene. I try this out tonight.
Rob
jenny
Greenhorn

Joined: Nov 12, 2001
Posts: 1
"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
Ranch Hand

Joined: Jan 23, 2002
Posts: 149
thanks Jenny for you input, that AND AND was just a typo from me and not in the code.
Rob Petterson
Ranch Hand

Joined: Jan 23, 2002
Posts: 149
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'

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
Ranch Hand

Joined: Jan 23, 2002
Posts: 149
OK, I've just had a flash on insight.
This will sort it out now:

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

Rob
[ May 08, 2003: Message edited by: Rob Petterson ]
Irene Loos
Ranch Hand

Joined: Apr 15, 2002
Posts: 78
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 ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Another problem with syntax for SQL statement