• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException

 
John Joe
Ranch Hand
Posts: 55
Android Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I want to check all the data in MySQL . If the data already exists, it will display already exists, otherwise it will insert into movie_title table.



The output always shows the movieTitle already exists even though it is not existing. When I check movie_title table, it shows nothing.

Noted that I have two tables, one is movie and another is movie_title.
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can't prepare a statement like this:

That is just using the executeUpdate method from a normal Statement, so you are sending the '?' to the server and it doesn't know what to do with it...it's not valid SQL.

You would need to create a new prepared statement:


However, why not simply insert the movie without the check?
If you want 'title' to be unique then make the column UNIQUE in the database.
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm not sure what's wrong with the statement, but, i would like to comment on the approach.

In order to guarantee a successful INSERT when there's a chance it already exists, the INSERT should include a NOT EXISTS():

This makes it an atomic statement, and it will only attempt the INSERT if it does not exist. Separating the statements allows another action in between, which may causes errors for the INSERT. Further, it may even help the database do it more efficiently, as it is just one statement. To see if it was already there, simply check the result of executeUpdate(). If 0, it already existed.

 
John Joe
Ranch Hand
Posts: 55
Android Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:You can't prepare a statement like this:

That is just using the executeUpdate method from a normal Statement, so you are sending the '?' to the server and it doesn't know what to do with it...it's not valid SQL.

You would need to create a new prepared statement:


However, why not simply insert the movie without the check?
If you want 'title' to be unique then make the column UNIQUE in the database.


I'm so sorry about this...I have made some edit in my post
 
John Joe
Ranch Hand
Posts: 55
Android Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Brian Tkatch wrote:I'm not sure what's wrong with the statement, but, i would like to comment on the approach.

In order to guarantee a successful INSERT when there's a chance it already exists, the INSERT should include a NOT EXISTS():

This makes it an atomic statement, and it will only attempt the INSERT if it does not exist. Separating the statements allows another action in between, which may causes errors for the INSERT. Further, it may even help the database do it more efficiently, as it is just one statement. To see if it was already there, simply check the result of executeUpdate(). If 0, it already existed.



I'm so sorry. I have made some edit in my post.
 
Jesper de Jong
Java Cowboy
Saloon Keeper
Posts: 15644
48
Android IntelliJ IDE Java Scala Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"INSERT into movie_title(title) VALUES (?)"

It looks like you made a mistake with the name of the table. The table is named 'movie', not 'movie_title'.

"INSERT into movie (title) VALUES (?)"
 
John Joe
Ranch Hand
Posts: 55
Android Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jesper de Jong wrote:"INSERT into movie_title(title) VALUES (?)"

It looks like you made a mistake with the name of the table. The table is named 'movie', not 'movie_title'.

"INSERT into movie (title) VALUES (?)"


I'm so sorry if I made you unclear .

I have two tables, one is movie and another is movie_title.

movie table has a column named movie_title, where movie_title table has a column named title.

Hope it is clear enough for you.
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
John Joe wrote:I have two tables, one is movie and another is movie_title.

movie table has a column named movie_title, where movie_title table has a column named title.

Ooh, i missed that. Anyway, your updated sql now uses dynamic sql, as it builds the statement without preparation. Please use the prepare/execute method, it is a safer and cleaner approach.

You can still use one statement for the INSERT:
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you explain your model?

I can't see the purpose of the movie_title table.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic