Win a copy of Modern JavaScript for the Impatient this week in the Server-Side JavaScript and NodeJS forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version

 
Ranch Hand
Posts: 171
4
Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello everyone, I have been trying in vain add records to MySQL db using a java app if my record does not include all the fields in the table which is super annoying. My latest attempt provides me this error:



Here is my code


I have also tried running the query into MySQL directly away from eclipse and i get the same error there as well so I know where the issue is but all the syntaxes i have seen online are exactly the same which is really confusing,
Thank you in advance
 
Marshal
Posts: 70208
280
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Don't catch Exception: you expect an SQLException so catch SQJException.
Why ae you closing something in a catch; it will only be closed if an exception is caught. Close it in a finally or better use try with resources if possible.
What happens if you run the SQL for that prepared statement directly to the database as the command line?
 
Saloon Keeper
Posts: 12251
259
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
GROUP is a keyword in SQL. You will want to quote your identifiers:
 
Rancher
Posts: 187
28
Firefox Browser MySQL Database Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I notice that the error occurs near the word 'group' . Group is a keyword, so that might be causing the error.

To troubleshoot this kind of error, I often log into the MySQL console and try writing the query directly. You might try the same thing.
 
Jj Roberts
Rancher
Posts: 187
28
Firefox Browser MySQL Database Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Stephan van Hulst wrote:GROUP is a keyword in SQL


I'm too slow
 
Yosuf Ibrahim
Ranch Hand
Posts: 171
4
Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Campbell Ritchie wrote:Don't catch Exception: you expect an SQLException so catch SQJException.
Why ae you closing something in a catch; it will only be closed if an exception is caught. Close it in a finally or better use try with resources if possible.
What happens if you run the SQL for that prepared statement directly to the database as the command line?



I get the same error even when i ran the query through MySQL workbench, my issue is not with java it is with the MySQL syntax

Stephan van Hulst wrote:GROUP is a keyword in SQL. You will want to quote your identifiers



Wow thanks man, Quoting my identifiers did not work it gave me another error however when i changed the column name from group to c_group it worked like a charm. You are a life saver mate I had no idea group was a keyword
 
Yosuf Ibrahim
Ranch Hand
Posts: 171
4
Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Campbell Ritchie wrote:Why ae you closing something in a catch; it will only be closed if an exception is caught.



I am afraid of the database being messed up so any error I get the database to close
 
Campbell Ritchie
Marshal
Posts: 70208
280
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Yosuf Ibrahim wrote:. . . I am afraid of the database being messed up so any error I get the database to close

No, you are only closing the database if an exception is thrown. You need to close the connection regardless.
 
Master Rancher
Posts: 4663
49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Campbell Ritchie wrote:

Yosuf Ibrahim wrote:. . . I am afraid of the database being messed up so any error I get the database to close

No, you are only closing the database if an exception is thrown. You need to close the connection regardless.



Depends on the environment.
If it's a desktop app then it's reasonable to only have a single connection, in which case you wouldn't necessarily want to close it.

Obviously, if it's a connection pooled environment then you would, as you say, always want to close it (ie return it back to the pool).
 
Yosuf Ibrahim
Ranch Hand
Posts: 171
4
Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
my bad I only close the connection not the Database as multiple users may be connected at once
 
Campbell Ritchie
Marshal
Posts: 70208
280
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Yosuf Ibrahim wrote:my bad I only close the connection . . .

No, you have misunderstood the problem. You are not closing the connection if the program runs normally. You appear to be closing the database if an exception is caught.
 
Yosuf Ibrahim
Ranch Hand
Posts: 171
4
Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Campbell Ritchie wrote:No, you have misunderstood the problem. You are not closing the connection if the program runs normally. You appear to be closing the database if an exception is caught.



Yeah I know, I have a TODO: for handling these errors later on. I am not going to close the app later on or the connection, the close method is only there while I am writing and testing. Once I am finished this close method will only be used when the user logs out from the app
 
Saloon Keeper
Posts: 22479
151
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"quoting" reserved words to use them as column names in MySQL is possible, but it doesn't use the same quote characters that you would expect.

You should always close database connections as soon as you can and not hold on to them for long periods of time or over long and possibly distributed chunks of logic.

Aside from the fact that it ensures that there's nothing left dangling in your database logic, closing also frees up expensive resources on both the client and server at applicication and OS operating levels.

Since opening a Connection is also expensive, an app that needs to frequently open and close connections should use a Connection Pool. Connection Pools are so important thatthey are a built-in feature to JEE webapp servers, although you can also find Connection Pool libraries that are easy to use for command-line and GUI apps, if needed.

Naturally "as soon as you can" has different meanings when you're attempting to load a million records in a batch operation as opposed to fetching, then updating a student's grade records. But the goal is to not be a small child wandering around with a hammer.
 
Campbell Ritchie
Marshal
Posts: 70208
280
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Yosuf Ibrahim wrote:. . . I have a TODO: for handling these errors later on. . . .

But I didn't know about that TODO. I would prefer to see the code written in full first time without TODOs like that.
 
Yosuf Ibrahim
Ranch Hand
Posts: 171
4
Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Campbell Ritchie wrote:But I didn't know about that TODO. I would prefer to see the code written in full first time without TODOs like that.



My bad sorry about that
 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic