Win a copy of Murach's Python Programming this week in the Jython/Python forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

what is the error about my insert query ?  RSS feed

 
tangara goh
Ranch Hand
Posts: 179
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dear experts,

For the past few hours, I have been battling with this insert statement of mine and I just can't spot the error.

Please help.



Database I am using MYSQL57

And the error message 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 'VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)' at line 1



 
Knute Snortum
Sheriff
Posts: 3545
85
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Do you need a closing paren ")" after tutorSubjects?
 
Paul Clapham
Sheriff
Posts: 22185
38
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I don't see your code, but it looks to me like you didn't use a PreparedStatement to fill in the parameters. It looks like you passed that parametrized query string as is to MySQL instead.

Knute is also right -- you do need to close the parenthesis.
 
tangara goh
Ranch Hand
Posts: 179
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Knute Snortum wrote:Do you need a closing paren ")" after tutorSubjects?

Hi Knute,

Tks to your sharp eye.  But, even after putting in the ")", it is still giving me the same error message

 
Paul Clapham
Sheriff
Posts: 22185
38
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry, but that line of code can't possibly throw the exception you told us about. It would be more useful if you showed us the actual stack trace (copy and paste it) along with the actual code leading up to where the exception occurred. Copy and paste that too.
 
tangara goh
Ranch Hand
Posts: 179
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:Sorry, but that line of code can't possibly throw the exception you told us about. It would be more useful if you showed us the actual stack trace (copy and paste it) along with the actual code leading up to where the exception occurred. Copy and paste that too.


Hi Paul,

Here's the stack trace :










 
Knute Snortum
Sheriff
Posts: 3545
85
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'd try:

INSERT INTO hi5project.tutor (...

space between hi5project.tutor and (
 
tangara goh
Ranch Hand
Posts: 179
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Knute Snortum wrote:I'd try:

INSERT INTO hi5project.tutor (...

space between hi5project.tutor and (


Nope.  Doesn't work.  Still the same error
 
Knute Snortum
Sheriff
Posts: 3545
85
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is the column tutorAge really a String?  If it's an int, then setString() will cause an error. 
 
Ron McLeod
Saloon Keeper
Posts: 1424
206
Android Angular Framework Eclipse IDE Java Linux MySQL Database Redhat TypeScript
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I don't think you should be passing the SQL string here: ps.executeUpdate(qry);

Use: ps.executeUpdate();
 
H Paul
Ranch Hand
Posts: 491
5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If it helps:
As a way of answering to Knute's question:
Check the database table definition (the columns) to see how they are defined.
Use Sql command line or console and do 1 INSERT statement to ensure it's inserted OK.

Then review Java side as questioned and helped by others.
 
tangara goh
Ranch Hand
Posts: 179
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Knute Snortum wrote:Is the column tutorAge really a String?  If it's an int, then setString() will cause an error. 


Hi Knute and Paul as well,

Tks for your sharp-eyes again.

I thought I have made everything String for a start but obviously, it was not the case.

I have made the following ammendments including the moving the query to






But, I am still getting error - java.lang.NumberFormatException: null , which I googled the internet but just can't find an answer

 
Mark Spencers
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Integer.parseInt throws NumberFormatException because argument is null. In jsp you should submit form with input :
 
Dave Tolls
Ranch Hand
Posts: 2504
27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This looks like a webapp, in which case your use of statics inside the DAO is not going to be threadsafe.
You could well end up with two request attempting to use the same connection/statement etc at the same time.
Indeed, you could end up with a query starting with one connection, only to find it closed before it is finished.
 
tangara goh
Ranch Hand
Posts: 179
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:This looks like a webapp, in which case your use of statics inside the DAO is not going to be threadsafe.
You could well end up with two request attempting to use the same connection/statement etc at the same time.
Indeed, you could end up with a query starting with one connection, only to find it closed before it is finished.


Hi Dave,

You are right.  This is a web application.

Could you let me know how to make the static DAO threadsafe ?

Can I put a lock() at the start of the transaction and unlock() after the update is done ?
 
Dave Tolls
Ranch Hand
Posts: 2504
27
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Don't make them static.
Indeed, don't have them as instance variables at all.

Your DAO methods should:
1. get a connection from a connection pool (you shouldn't be creating new connections each time).
2. create your PreparedStatement object and execute it.
3. close everything.

Note that (3) is usually part of a try-with-resources thing:

(note the above may have syntax issues as I just wrote it directly)
 
tangara goh
Ranch Hand
Posts: 179
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:Don't make them static.
Indeed, don't have them as instance variables at all.

Your DAO methods should:
1. get a connection from a connection pool (you shouldn't be creating new connections each time).
2. create your PreparedStatement object and execute it.
3. close everything.

Note that (3) is usually part of a try-with-resources thing:

(note the above may have syntax issues as I just wrote it directly)


Hi Dave,

Before I tried out your suggestion, I just want you to know I'm getting another kind of error now and I really don't know what's going on.



and I do not know what the hell is going on

How do I rectify the errors ?
 
Dave Tolls
Ranch Hand
Posts: 2504
27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
MySQLIntegrityConstraintViolationException: Column 'tutorName' cannot be null

That's saying you are trying to put a null value into that column, and the column is marked as NOT NULL.
 
tangara goh
Ranch Hand
Posts: 179
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:MySQLIntegrityConstraintViolationException: Column 'tutorName' cannot be null

That's saying you are trying to put a null value into that column, and the column is marked as NOT NULL.


How can it be a null value when I have filled up all the required fields in the form ?  I have entered a name into the textbox for Name.
I did not leave any part of the fields empty.
 
Swastik Dey
Rancher
Posts: 1782
14
Android Eclipse IDE Java Java ME
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


Check what you are getting in variable name.  Is it null?
 
Dave Tolls
Ranch Hand
Posts: 2504
27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
tangara goh wrote:
How can it be a null value when I have filled up all the required fields in the form ?  I have entered a name into the textbox for Name.
I did not leave any part of the fields empty.


Log everything.
Especially, as Swastik suggests, the variable that you think shouldn't be null, but is.
And track that value through your server code, if it isn't null when it comes into the servlet.
 
tangara goh
Ranch Hand
Posts: 179
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Swastik Dey wrote:

Check what you are getting in variable name.  Is it null?


Hi Swastik,

I inserted a out.println(name) but the io is not printing out anything....



What could have gone wrong ?

 
Paul Clapham
Sheriff
Posts: 22185
38
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Nothing went wrong. Look at your code. What does it do if the "name" variable is null?
 
Swastik Dey
Rancher
Posts: 1782
14
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Look at Paul's reply, you have no way to check what happens when name is null.  You should have a else condition for if(name!=null), then only you will be able to figure out what's going wrong.
 
Dave Tolls
Ranch Hand
Posts: 2504
27
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You really don't want to be logging to the browser, but anyway...at least you now know that 'name' is null.
So now you need to look at your form, and probably the request that's sent to the server using the developer tools that come with your browser.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!