aspose file tools*
The moose likes JDBC and the fly likes com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException " Watch "com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException " New topic
Author

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException

justin smythhe
Ranch Hand

Joined: Jul 06, 2012
Posts: 107
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
Bartender

Joined: Oct 14, 2005
Posts: 18985
    
    8

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

Joined: Jul 06, 2012
Posts: 107
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
Bartender

Joined: Oct 14, 2005
Posts: 18985
    
    8

You didn't look at the query yet, did you? The one which had the syntax error?
justin smythhe
Ranch Hand

Joined: Jul 06, 2012
Posts: 107
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
Bartender

Joined: Oct 14, 2005
Posts: 18985
    
    8

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

Joined: Oct 31, 2011
Posts: 498

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.


PM Me If You Want to get Urgent Help on Java Programming
justin smythhe
Ranch Hand

Joined: Jul 06, 2012
Posts: 107
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
Bartender

Joined: Oct 14, 2005
Posts: 18985
    
    8

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

Joined: Jul 06, 2012
Posts: 107
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

Joined: Nov 09, 2011
Posts: 1030
    
    5

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
Bartender

Joined: Oct 14, 2005
Posts: 18985
    
    8

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

Joined: Jul 06, 2012
Posts: 107
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
Bartender

Joined: Oct 14, 2005
Posts: 18985
    
    8

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

Joined: Mar 28, 2012
Posts: 317

Shukran

You solved my headache


http://www.mahtabalam.net , Oracle Java Programmer , Oracle SQL Expert , Oracle Java Web Component Developer
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException