• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Database Problem

 
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 859
IBM DB2 Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Your String is not enclosed within quotes.

WP
 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 18
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 18
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 18
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 18
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I say, if the package requires you to generate SQL in the form of a string, then do that.
 
Cezanne Khan
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Cezanne Khan
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 18
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Okay. You're a troll, then. No more answers from me.
 
Cezanne Khan
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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



why? i really need help.
 
Cezanne Khan
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 18
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Ranch Hand
Posts: 179
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Cezanne Khan
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 179
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 18
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 18
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The problem is solved, thanks Vishal and Paul, i found the solution.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic