aspose file tools*
The moose likes JDBC and the fly likes Database Problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Database Problem" Watch "Database Problem" New topic
Author

Database Problem

Cezanne Khan
Greenhorn

Joined: Aug 20, 2012
Posts: 18
Hi,

i have a problem with the connectivity. Actually i have a JSP and a Servlet that generates a pie graph using jfree chart. I pass the JSP parameters to Servlet which generates a pie graph. But the query that i have written in Servlet is not working, though it is working in mysql workbench, but not in running mode. Following is the list in JSP and Servlet.

<select name="list">
<option value="Afghanistan">Afghanistan</option>
<option value="Albania">Albania</option>
<option value="Algeria">Algeria</option>
<option value="American Samoa">American Samoa</option>
<option value="Andorra">Andorra</option>
<option value="Angola">Angola</option>
<option value="Antigua and Barbuda">Antigua and Barbuda</option>
<option value="Argentina">Argentina</option>
<option value="Armenia">Armenia</option>
<option value="Aruba">Aruba</option>
<option value="Australia">Australia</option>
<option value="Austria">Austria</option>
<option value="Azerbaijan">Azerbaijan</option>
</select>

<select name="indicator">

<option class="special">Agriculture & Rural Development</option>
<option value="Agricultural machinery tractors">Agricultural machinery tractors</option>
<option value="Agricultural land (sq. km)">Agricultural land (sq. km)</option>
<option value="Agricultural land (% of land area)">Agricultural land (% of land area)</option>
<option value="Arable land (hectares)">Arable land (hectares)</option>
<option value="Arable land (hectares per person)">Arable land (hectares per person)</option>
<option value="Arable land (% of land area)">Arable land (% of land area)</option>
<option value="Agricultural irrigated land (% of total agricultural land)">Agricultural irrigated land (% of total agricultural land)</option>
<option value="Average precipitation in depth (mm per year)">Average precipitation in depth (mm per year)</option>
<option value="Agricultural machinery tractors per 100 sq. km of arable land">Agricultural machinery tractors per 100 sq. km of arable land</option>
<option value="Agriculture value added per worker (constant 2000 US$)">Agriculture value added per worker (constant 2000 US$)</option>
</select>


and this is the Servlet:


JDBCPieDataset dataset = new JDBCPieDataset(connection);

String CountryName=request.getParameter("list");
String SeriesName=request.getParameter("indicator");

String sql = "select y1960, y1961, y1962, y1963, y1964, y1965, y1966, y1967, y1968, y1969, y1970, y1971, y1972, y1973, y1974, y1975, y1976, y1977, y1978, y1979, y1980, y1981, y1982, y1983, y1984, y1985, y1986, y1987, y1988, y1989, y1990, y1991, y1992, y1993, y1994, y1995, y1996, y1997, y1998, y1999, y2000, y2001, y2002, y2003, y2004, y2005, y2006, y2007, y2008, y2009, y2010, y2011 from sadm where Country_Name= '" + CountryName + "' and Series_Name= '" + SeriesName + "'";

JOptionPane.showMessageDialog(null,"unknown");

dataset.executeQuery(sql);

JFreeChart chart = ChartFactory.createPieChart("Pie Chart", dataset, true, true, true);
chart.setBorderPaint(Color.black);
chart.setBorderStroke(new BasicStroke(10.0f));
chart.setBorderVisible(true);
if (chart != null) {
int width = 500;
int height = 350;


and the error is :

"com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'machinery tractors' at line 1"

Please help me as soon as possible.
William P O'Sullivan
Ranch Hand

Joined: Mar 28, 2012
Posts: 859

Your String is not enclosed within quotes.

WP
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18902
    
    8

A little bit of debugging would tell you something. The debugging statement you need is



When you got an error message telling you that your SQL statement had an error, didn't it occur to you that you should start by looking at that SQL statement?
Cezanne Khan
Greenhorn

Joined: Aug 20, 2012
Posts: 18
William P O'Sullivan wrote:Your String is not enclosed within quotes.

WP


No, i have the quotes, it is like...

String sql = "select y1960, y1961, y1962, y1963, y1964, y1965, y1966, y1967, y1968, y1969, y1970, y1971, y1972, y1973, y1974, y1975, y1976, y1977, y1978, y1979, y1980, y1981, y1982, y1983, y1984, y1985, y1986, y1987, y1988, y1989, y1990, y1991, y1992, y1993, y1994, y1995, y1996, y1997, y1998, y1999, y2000, y2001, y2002, y2003, y2004, y2005, y2006, y2007, y2008, y2009, y2010, y2011 from sadm where Country_Name= ' " + CountryName + " ' and Series_Name= ' " + SeriesName + " ' ";
Cezanne Khan
Greenhorn

Joined: Aug 20, 2012
Posts: 18
Paul Clapham wrote:A little bit of debugging would tell you something. The debugging statement you need is



When you got an error message telling you that your SQL statement had an error, didn't it occur to you that you should start by looking at that SQL statement?


I have a catch statement, that prints the message, which is...


"com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'machinery tractors' at line 1"
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18902
    
    8

Yes. The error message tells you that there's something wrong with your SQL. So look at your SQL.

And no, that bunch of Java code is not your SQL. It's a line of code which produces your SQL. So don't look at the Java code, look at the string which it produces. I already showed you how to look at that string.
Cezanne Khan
Greenhorn

Joined: Aug 20, 2012
Posts: 18
Paul Clapham wrote:Yes. The error message tells you that there's something wrong with your SQL. So look at your SQL.

And no, that bunch of Java code is not your SQL. It's a line of code which produces your SQL. So don't look at the Java code, look at the string which it produces. I already showed you how to look at that string.


Will you please correct it, because i have tried a lot by changing the parameters and quotes, by changing its syntax, but still got the error.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18902
    
    8

By the way: Generating SQL in that way is likely to lead to problems. First of all you have to make sure your quotes match, and you have the right kind of quotes in the right place. And then even if they do match, having more quotes in the string data which you concatenate together can make your quotes be unbalanced again. And also if your string data came from user input, it can be used for SQL injection attacks which can damage your database.

The usual advice here is to use a PreparedStatement, rather than building your SQL from strings like that. But I see that you are working with some kind of utility code which prefers those error-prone strings. So you should look at it to see if it will accept a PreparedStatement, perhaps in some other overloaded method. If so, you should switch to using that other method.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18902
    
    8

Cezanne Khan wrote:Will you please correct it, because i have tried a lot by changing the parameters and quotes, by changing its syntax, but still got the error.


Sure. Just as soon as you show me the SQL.

I already told you how to display it.
Cezanne Khan
Greenhorn

Joined: Aug 20, 2012
Posts: 18
Paul Clapham wrote:By the way: Generating SQL in that way is likely to lead to problems. First of all you have to make sure your quotes match, and you have the right kind of quotes in the right place. And then even if they do match, having more quotes in the string data which you concatenate together can make your quotes be unbalanced again. And also if your string data came from user input, it can be used for SQL injection attacks which can damage your database.

The usual advice here is to use a PreparedStatement, rather than building your SQL from strings like that. But I see that you are working with some kind of utility code which prefers those error-prone strings. So you should look at it to see if it will accept a PreparedStatement, perhaps in some other overloaded method. If so, you should switch to using that other method.


Right, my previous preference was to use the PreparedStatement, but the problem here is that i'm not using the conventional way of connection for connecting to database, rather i am using a library called JFreeChart, and you can see that i have passed the connection parameter to the JDBCPieDataSet. The problem here is that this method doesn't have a PreparedStatement, so that's why i preferred using Strings. What do you say?
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18902
    
    8

I say, if the package requires you to generate SQL in the form of a string, then do that.
Cezanne Khan
Greenhorn

Joined: Aug 20, 2012
Posts: 18
Paul Clapham wrote:I say, if the package requires you to generate SQL in the form of a string, then do that.


Already used the Strings, but not working
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18902
    
    8

Yes. And I've already suggested (several times) how to debug your problem. If you don't accept my advice then it looks like you're stuck.
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1848
    
  16

As Paul says, print out the exact SQL statement that is actually generated and which actually causes the error.

Then copy this statement and execute it via your database's SQL query interface (i.e. not JFreeChart or Java) so you can see if the query works without multiple layers of Java between your SQL string and the database.

If it still doesn't work, then change it and test it via your database's SQL query interface until it's working correctly.

Then put the corrected SQL code back into your Java and test it again.

Watch out for special characters (e.g. single quotes, ampersands etc) inside the strings you use for Country and Series, because these may cause problems when your SQL reaches the database. If possible, test that the query executes successfully for every possible value of Country/Series.

No more Blub for me, thank you, Vicar.
Cezanne Khan
Greenhorn

Joined: Aug 20, 2012
Posts: 18
chris webster wrote:As Paul says, print out the exact SQL statement that is actually generated and which actually causes the error.

Then copy this statement and execute it via your database's SQL query interface (i.e. not JFreeChart or Java) so you can see if the query works without multiple layers of Java between your SQL string and the database.

If it still doesn't work, then change it and test it via your database's SQL query interface until it's working correctly.

Then put the corrected SQL code back into your Java and test it again.

Watch out for special characters (e.g. single quotes, ampersands etc) inside the strings you use for Country and Series, because these may cause problems when your SQL reaches the database. If possible, test that the query executes successfully for every possible value of Country/Series.


Yep, i have done that. Working perfectly with database, but not here.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18902
    
    8

It might be a good idea if you posted that exact SQL statement here. Somebody might see a problem with it.

(Notice that the "Code" button which you use to format code in the forum has an "SQL" option in the drop-down to the left of it, which makes SQL easier to read when it's posted here.)
Cezanne Khan
Greenhorn

Joined: Aug 20, 2012
Posts: 18
Paul Clapham wrote:It might be a good idea if you posted that exact SQL statement here. Somebody might see a problem with it.

(Notice that the "Code" button which you use to format code in the forum has an "SQL" option in the drop-down to the left of it, which makes SQL easier to read when it's posted here.)


"select y1960, y1961, y1962, y1963, y1964, y1965, y1966, y1967, y1968, y1969, y1970, y1971, y1972, y1973, y1974, y1975, y1976, y1977, y1978, y1979, y1980, y1981, y1982, y1983, y1984, y1985, y1986, y1987, y1988, y1989, y1990, y1991, y1992, y1993, y1994, y1995, y1996, y1997, y1998, y1999, y2000, y2001, y2002, y2003, y2004, y2005, y2006, y2007, y2008, y2009, y2010, y2011 from sadm where Country_Name= '" + CountryName + "' and Series_Name= '" + SeriesName + "'";

Here it is.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18902
    
    8

Okay. You're a troll, then. No more answers from me.
Cezanne Khan
Greenhorn

Joined: Aug 20, 2012
Posts: 18
Paul Clapham wrote:Okay. You're a troll, then. No more answers from me.


why? i really need help.
Cezanne Khan
Greenhorn

Joined: Aug 20, 2012
Posts: 18
Cezanne Khan wrote:
Paul Clapham wrote:Okay. You're a troll, then. No more answers from me.


why? i really need help.


Cezanne Khan
Greenhorn

Joined: Aug 20, 2012
Posts: 18
I just changed the query, because JDBCPieDataSet require 2 columns. Now the query is like



When i execute it i got the error, "JDBCPieDataset - unknown data type". What should i do?
Vishal Shaw
Ranch Hand

Joined: Aug 09, 2012
Posts: 179
Hi,

Paul Clapham wrote:
Okay. You're a troll, then. No more answers from me.


why? i really need help.


Basically, from the thread , Paul asked you several times to do this:


But you constantly, ignored this. He is right, if you don't show us your exact query , how can you expect us to solve them. And yes, the error message clearly suggests there is an error in your sql syntax

Regards,
Vishal


Programming is about thinking, NOT coding
Cezanne Khan
Greenhorn

Joined: Aug 20, 2012
Posts: 18
Vishal Shaw wrote:Hi,

Paul Clapham wrote:
Okay. You're a troll, then. No more answers from me.


why? i really need help.


Basically, from the thread , Paul asked you several times to do this:


But you constantly, ignored this. He is right, if you don't show us your exact query , how can you expect us to solve them. And yes, the error message clearly suggests there is an error in your sql syntax

Regards,
Vishal



My apologize. I am new to this forum and don't know much about it. Secondly, i have done the way you and Paul said,

This is the System.out.println(sql); result which is shown on the console.

.
Vishal Shaw
Ranch Hand

Joined: Aug 09, 2012
Posts: 179
select Country_Name, 'y1964' from sadm where Series_Name= 'Agricultural machinery tractors' order by 'y1964' desc


Did you tried running theis exact query in your mysql directly? Also, is y1964 your column name? Then did you realized, that you have quotes around it
Cezanne Khan
Greenhorn

Joined: Aug 20, 2012
Posts: 18
Vishal Shaw wrote:
select Country_Name, 'y1964' from sadm where Series_Name= 'Agricultural machinery tractors' order by 'y1964' desc


Did you tried running theis exact query in your mysql directly? Also, is y1964 your column name? Then did you realized, that you have quotes around it


Yes, i did that, but what i am doing is that passing two values from jsp to a servlet, which then take these values and generate graph. y1964 is the name of a column which is in my database. And i have columns from y1960 to y2011. When a user select a year and any indicator say "Agricultural machinery tractors", then these values are passed to a servlet. I have run the same query in mysql, but i have not given the quotes around it as, mysql doesn't accept quotes, it will treat it something else, but in java, when i wrote the query it is necessary to put quotes, otherwise it give errors.

The sql query is:



And the query written in java is:



where YearName is the year, which in this case is y1964, and SeriesName is the "Agricultural machinery tractors". Both of then have been selected by the user.

Cezanne Khan
Greenhorn

Joined: Aug 20, 2012
Posts: 18
The problem is solved, thanks Vishal and Paul, i found the solution.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Database Problem