aspose file tools*
The moose likes JDBC and the fly likes msyql jdbc problem. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "msyql jdbc problem." Watch "msyql jdbc problem." New topic
Author

msyql jdbc problem.

lee kris
Ranch Hand

Joined: Mar 30, 2005
Posts: 35
hi,
May be this is not the right forum for the following question regarding mysql but if one can check it out for me it is great.
I am using mysql and JDBC for some application that uses the jakarta struts frame work.
mysql works fine with a lot of insert, delete, and what not but when i am trying to update I get some error which has already costed me many hours. Here is some details that might help you help me in detecting this problem.

the sql statement:

The Java code:



the SQL:




upon execution i get the following error:
Syntax error or access violation, message from server:
"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 'where student_id = "xxxxxxxx".

where "xxxxxxxx" is a wrong value for student_id. What syntax error in mysql could be causing this?
Thank you, in advance, for trying to help.
Lee
Jason Menard
Sheriff

Joined: Nov 09, 2000
Posts: 6450
Moving to the JDBC forum.
Phillip Koebbe
Greenhorn

Joined: Jun 22, 2005
Posts: 27
The question that I'm sure a lot of us are wanting to ask:

Have you tested the UPDATE statement in a query tool, such as the mysql command line utility?

Beyond that, are you certain personal.getStudentID() returns a value?

That raises a question that I'd like to pose:

Is there an easy way to see the SQL statement that will be sent to the database once all values are set?

Peace,
Phillip
[ July 02, 2005: Message edited by: Phillip Koebbe ]
Rick Portugal
Ranch Hand

Joined: Dec 17, 2002
Posts: 243
You have "qlString.append" instead of "sqlString.append". Also, student_id in the table is a varchar. Does personal.getStudentId() return a String?


IBM 286, SCJP, SCWCD, EIEIO
lee kris
Ranch Hand

Joined: Mar 30, 2005
Posts: 35
hi guys,
Philip asked " Have you tested the UPDATE statement in a query tool, such as the mysql command line utility?"
Answer: of course, it works nicely and as expected. In fact I have a similar example that works on a different database and works correctly(except that this has many attributs).

Question:"Beyond that, are you certain personal.getStudentID() returns a value?"
Answer: yet personal.getStudentID() return a String.

Rick asked :
"Also, student_id in the table is a varchar. Does personal.getStudentId() return a String? "
Answer: yes, personal.getStudentId() returns a String.

And the "qlString.append" was a typo error otherwise it is"sqlString.append" in the java code.

Thank you guys and would appreciate further comments or suggestions to the original problem.
Have a good day.
Lee
Rick Portugal
Ranch Hand

Joined: Dec 17, 2002
Posts: 243
You have your column orders mixed up. The update statement order is last_name/middle_name/first_name/gender/email one/email two/... but when you do the pstmt.setString's, your order is last_name/middle_name/first_name/email one/email two/gender...

Changing your gender that way isn't allowed, even in Massachusetts!
lee kris
Ranch Hand

Joined: Mar 30, 2005
Posts: 35
hi guys,
Rick you are funny man. I got the error and was able to solve it. It definitely was in the mix-up of things and the major one was

and I honestly do not know how I messed that one up. But anyways thank you guys for trying to help as usual. if any body wants the whole list of codes regarding this and how I corrected it, let me know and I will post most of it.

Lee
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: msyql jdbc problem.