msyql jdbc problem.

[lee kris]

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:

  
 
public static void updatePersonal( PersonalVO personal, DataSource dataSource)
throws Exception
{
 
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try
{
 
StringBuffer sqlString =
new StringBuffer("update some_db set last_name = ?, middle_name = ?, first_name = ?, gender = ?, ");
sqlString.append(" email_one = ?, email_two = ?, street_address1 = ?, street_address2 = ?, city = ?,");
qlString.append(" state = ?, zipcode = ?, country = ?, phone_number1 = ?, ");
sqlString.append(" phone_number2 = ? where student_id = ?");
 

 
conn = dataSource.getConnection();
pstmt = conn.prepareStatement(sqlString.toString());
 
pstmt.setString(1, personal.getLastName());
pstmt.setString(2, personal.getMiddleName());
pstmt.setString(3, personal.getFirstName());
 
pstmt.setString(4, personal.getEmailOne());
pstmt.setString(5, personal.getEmailTwo());
pstmt.setString(6, personal.getGender());
pstmt.setString(7, personal.getStreetAddress1());
pstmt.setString(8, personal.getStreetAddress2());
pstmt.setString(9, personal.getCity());
pstmt.setString(10, personal.getState());
pstmt.setString(11, personal.getZipCode());
 
pstmt.setString(12, personal.getCountry());
pstmt.setString(13, personal.getPhoneNumber1());
pstmt.setString(14, personal.getPhoneNumber2());
pstmt.setString(15, personal.getStudentId());
 

pstmt.executeUpdate();
} finally
{
if (rs != null)
{
rs.close();
}
if (pstmt != null)
{
pstmt.close();
}
if (conn != null)
{
conn.close();
}
}

the SQL:

 
 
mysql> desc some_db;
+---------------------------+-----------------------+------+-----+---------+-------+
| Field                     | Type                  | Null | Key | Default | Extra |
+---------------------------+-----------------------+------+-----+---------+-------+
| student_id                | varchar(9)            |      | PRI |         |       |
| last_name                 | varchar(25)           |      |     |         |       |
| middle_name               | varchar(20)           | YES  |     | NULL    |       |
| first_name                | varchar(25)           |      |     |         |       |
| gender                    | enum('M','F','m','f') |      |     | M       |       |
| email_one                 | varchar(40)           | YES  |     | NULL    |       |
| email_two                 | varchar(40)           | YES  |     | NULL    |       |
| street_address1           | varchar(50)           | YES  |     | NULL    |       |
| street_address2           | varchar(50)           | YES  |     | NULL    |       |
| city                      | varchar(30)           | YES  |     | NULL    |       |
| state                     | varchar(30)           | YES  |     | NULL    |       |
| zipcode                   | varchar(30)           | YES  |     | NULL    |       |
| country                   | varchar(30)           | YES  |     | NULL    |       |
| phone_number1             | varchar(10)           | YES  |     | NULL    |       |
| phone_number2             | varchar(10)           | YES  |     | NULL    |       |
| program_code              | varchar(4)            | YES  |     | NULL    |       |
| earlier_program_code      | varchar(4)            | YES  |     | NULL    |       |
.
.
.
 
| program_advisor           | tinyint(3) unsigned   | YES  |     | NULL    |       |
| program_advisor           | tinyint(3) unsigned   | YES  |     | NULL    |       |
+---------------------------+-----------------------+------+-----+---------+-------+
33 rows in set (0.00 sec)
 

 

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]

Moving to the JDBC forum.

[Phillip Koebbe]

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]

You have "qlString.append" instead of "sqlString.append". Also, student_id in the table is a varchar. Does personal.getStudentId() return a String?

[lee kris]

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]

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]

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

 
pstmt.setString(15, personal.getStudentId())
 

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