Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException

 
justin smythhe
Ranch Hand
Posts: 107
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi ! I am using MySQL and Java 6 to learn JDBC. I tried a simple program and got an error.
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 'SELECT CustomerId, nameFROM Customers' at line 1

Please help me to solve it.



I also tried SELECT * which caused the same error.
Another doubt I have is when I use SELECT * and the code below:



Please help.

 
Paul Clapham
Sheriff
Pie
Posts: 20771
30
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
When you get an error message about an error in your SQL, then look at your SQL. This might sound obvious, but often people don't even notice that their SQL is in fact generated by Java code and they assume they know what the code is doing. So that means you should look at the result of the Java code which generates your incorrect SQL. Write it out to the console, or wherever is convenient for debugging.

String s = rs.getString("*");


What could this possibly mean?
 
justin smythhe
Ranch Hand
Posts: 107
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:When you get an error message about an error in your SQL, then look at your SQL. This might sound obvious, but often people don't even notice that their SQL is in fact generated by Java code and they assume they know what the code is doing. So that means you should look at the result of the Java code which generates your incorrect SQL. Write it out to the console, or wherever is convenient for debugging.

String s = rs.getString("*");


What could this possibly mean?


The * means nothing, I know that. But, the other queries are not working too. I am stuck. :(
 
Paul Clapham
Sheriff
Pie
Posts: 20771
30
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You didn't look at the query yet, did you? The one which had the syntax error?
 
justin smythhe
Ranch Hand
Posts: 107
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:You didn't look at the query yet, did you? The one which had the syntax error?


I removed the 'USE Business'. I put the database name in my url. Then, I used
while (rs.next())
{
String s = rs.getString("customerno");
}

Now, I get a new error:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'customerno' in 'field list'

How can this be ? I have that column in my DB. Checked it 3 times.
 
Paul Clapham
Sheriff
Pie
Posts: 20771
30
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It might be that it's in your DB somewhere, but it isn't in the table that you queried. Or if it is, then perhaps your query renamed it. But that's all speculation. Speculation is all we can do because you haven't showed us anything except the error message.
 
Nam Ha Minh
Ranch Hand
Posts: 502
Eclipse IDE Firefox Browser Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
justin smythhe wrote:
Paul Clapham wrote:You didn't look at the query yet, did you? The one which had the syntax error?


I removed the 'USE Business'. I put the database name in my url. Then, I used
while (rs.next())
{
String s = rs.getString("customerno");
}

Now, I get a new error:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'customerno' in 'field list'

How can this be ? I have that column in my DB. Checked it 3 times.


The column name must be specified in the SELECT clause.
 
justin smythhe
Ranch Hand
Posts: 107
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:It might be that it's in your DB somewhere, but it isn't in the table that you queried. Or if it is, then perhaps your query renamed it. But that's all speculation. Speculation is all we can do because you haven't showed us anything except the error message.


The error is 'Unknown column 'customerno' in 'field list' '. That column exists in my customer table.

Here is the code:

 
Paul Clapham
Sheriff
Pie
Posts: 20771
30
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're still making exactly the same error that you made in your original post. And you still haven't taken my advice to look at the SQL query, have you? I'm sure you know System.out.println by now. Use it.

And also: don't ignore the stack trace. It tells you what line of code threw the exception. It looks like you're assuming that the line with "rs.getString" was that line. But was it?
 
justin smythhe
Ranch Hand
Posts: 107
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:You're still making exactly the same error that you made in your original post. And you still haven't taken my advice to look at the SQL query, have you? I'm sure you know System.out.println by now. Use it.

And also: don't ignore the stack trace. It tells you what line of code threw the exception. It looks like you're assuming that the line with "rs.getString" was that line. But was it?


There is this dude who has exactly the same problem. You would not believe !
Here is the issue

SELECT customerno, name + // add space here
Win !

Thanks though.
 
James Boswell
Bartender
Posts: 1051
5
Chrome Eclipse IDE Hibernate
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 'SELECT CustomerId, nameFROM Customers' at line 1

Sometimes exception messages can hide the problem but I don't think that is the case here. When you have SQL syntax errors, it is advisable to pull the SQL out and run it directly against the database (using a tool like SQL developer or MySQL workbench). Fix the problem there and then apply the change in your Java code.
 
Paul Clapham
Sheriff
Pie
Posts: 20771
30
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
justin smythhe wrote:There is this dude who has exactly the same problem. You would not believe !
Here is the issue

SELECT customerno, name + // add space here
Win !

Thanks though.


Of course I would believe it. It was perfectly obvious to me from the beginning.

Please read our FAQ entry BeForthrightWhenCrossPostingToOtherSites which should explain why I will not be looking at your posts in the future.
 
justin smythhe
Ranch Hand
Posts: 107
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:
justin smythhe wrote:There is this dude who has exactly the same problem. You would not believe !
Here is the issue

SELECT customerno, name + // add space here
Win !

Thanks though.


Of course I would believe it. It was perfectly obvious to me from the beginning.

Please read our FAQ entry BeForthrightWhenCrossPostingToOtherSites which should explain why I will not be looking at your posts in the future.


Nooo...please don't do that. I cross posted after I got no reply. I was thinking that the queries would be handled entirely by the API.
 
Paul Clapham
Sheriff
Pie
Posts: 20771
30
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, look. I told you to look at your query. Apparently that advice went over your head.

So. What you had was this:



Did you think that was your query? It isn't. Not quite, anyway. It's a line of Java code which generates your query. And being code, it might have bugs. So when I told you to look at your query, I didn't mean for you to look at the code which generates your query. I meant for you to look at the result of that code. And the way you do that is like so:



So, even though you now know what's wrong, try that and see what comes out. Of course it will be obvious once you see the output, but ask yourself if you would have recognized the problem from the output of that statement if you didn't already know.

I was thinking that the queries would be handled entirely by the API.


Well, yeah, that's true. But "handling" the query does involve throwing exceptions if the query isn't valid SQL, you know. Surely you didn't expect the API (i.e. the JDBC driver) to apply advanced artificial intelligence methods to fix up invalid SQL?
 
Mahtab Alam
Ranch Hand
Posts: 391
1
Java MySQL Database PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Shukran

You solved my headache
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic