We help IT Professionals succeed at work.

Whats wrong with my Update SQL statement

bhession
bhession asked
on
428 Views
Last Modified: 2012-05-10
I am trying to update a column in my database using the attached code. I am getting the below error coming up. I think its saying my SQL is incorrect. Anyone have any ideas?

Error: Error retrieving data!
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 '(NAME,PASSWORD,CELL,ADMINISTRATOR,Message,ENGINEER,CONTROL,SHOWOWNER)VALUES('Der' at line 1
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
      at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
      at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
      at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
      at com.mysql.jdbc.Util.getInstance(Util.java:384)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
      at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)
      at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2113)
      at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2409)
      at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2327)
      at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2312)
      at com.google.project.DBManager.UpdateUser(DBManager.java:621)
      at com.google.project.UpdateUser.doPost(UpdateUser.java:72)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
      at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
      at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:857)
      at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
      at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
      at java.lang.Thread.run(Thread.java:619)

public void UpdateUser(User user) {
	try
	{
		System.out.println(user.getID());
		ps = con.prepareStatement("UPDATE Users SET(NAME,PASSWORD,CELL,"
						+ "ADMINISTRATOR,Message,ENGINEER,CONTROL,SHOWOWNER)" 
						+"VALUES(?,?,?,?,?,?,?,?) WHERE idusers ="+user.getID()+"");
	}
	catch(SQLException e)
	{
		System.out.println("Error: Cannot execute query!");
		e.printStackTrace();
		System.exit(1);
	}

	try
	{
		
		
		ps.clearParameters(); // clears previous parameters in there was any
			ps.setString(1, user.getName());
			ps.setString(2, user.getPassword());
			ps.setString(3, user.getCell());
			ps.setString(4, user.getAdministrator());
			ps.setString(5, user.getMessage());
			ps.setString(6, user.getEngineer());
			ps.setString(7, user.getControl());
			ps.setString(8, user.getShowOwner());
			ps.executeUpdate(); 
		
	}
	catch(SQLException e)
	{
		System.out.println("Error: Error retrieving data!");
		e.printStackTrace();
		System.exit(1);
	}
	
}
	
}

Open in new window

Comment
Watch Question

Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
needs to be:
... set col1=value1, col2=value2 ...
Two spaces are missing: between SET and the first parenthesis and between the next parenthesis and VALUES.

Author

Commented:
Objects, I now have the below code (see attached I am still getting a SQL error

SimonDard, can you explain this I am not sure I understand what you mean.
public void UpdateUser(User user) {
	try
	{
		ps = con.prepareStatement("UPDATE users SET NAME ="+user.getName()+","
						+"PASSWORD ="+user.getPassword()+","
						+"CELL ="+user.getCell()+","
						+"ADMINISTRATOR ="+user.getAdministrator()+","
						+"Message ="+user.getMessage()+","
						+"ENGINEER ="+user.getEngineer()+","
						+"CONTROL ="+user.getControl()+","
						+"SHOWOWNER ="+user.getShowOwner()+""
						+"WHERE idusers ="+user.getID()+"");
		ps.executeUpdate(); 
						
	}
	catch(SQLException e)
	{
		System.out.println("Error: Cannot execute query!");
		e.printStackTrace();
		System.exit(1);
	}

Open in new window

CERTIFIED EXPERT

Commented:
give some spaces in between where clause
user.getShowOwner()+" "
                                    +"WHERE idusers ="+user.getID()+"");

can you give out put string of this statement

System.out.println("UPDATE users SET NAME ="+user.getName()+","
+"PASSWORD ="+user.getPassword()+","
+"CELL ="+user.getCell()+","
+"ADMINISTRATOR ="+user.getAdministrator()+","
+"Message ="+user.getMessage()+","
+"ENGINEER ="+user.getEngineer()+","
+"CONTROL ="+user.getControl()+","
+"SHOWOWNER ="+user.getShowOwner()+""
+"WHERE idusers ="+user.getID()+"");
ksivananthVice President

Commented:
>>needs to be:
... set col1=value1, col2=value2 ...
>>

don't do that, it destroys the purpose of preparedstatement. use the way you have done, just have the space before values list,

ps = con.prepareStatement("UPDATE Users SET(NAME,PASSWORD,CELL,"                                    + "ADMINISTRATOR,Message,ENGINEER,CONTROL,SHOWOWNER)"
                                    +" VALUES(?,?,?,?,?,?,?,?) WHERE idusers =\""+user.getID()+"\"");
CERTIFIED EXPERT

Commented:
the original code should be

ps = con.prepareStatement("UPDATE Users SET NAME = ?, PASSWORD = ?, CELL = ?, ADMINISTRATOR = ?, Message = ?, ENGINEER = ?, CONTROL = ?, SHOWOWNER = ? WHERE idusers = ?");

Author

Commented:
See the attached to what I am  currently useding. I also provided a system out to show my update statement.
The below is the output from the console. It should work...

Connecting to the Database......
UPDATE users SET NAME =JoeBlogs,PASSWORD =joe1231234,CELL =123456789,ADMINISTRATOR =1,Message =1,ENGINEER =1,CONTROL =1,SHOWOWNER =1 WHERE idusers =54
Error: Error retrieving data!
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 '(NAME,PASSWORD,CELL,ADMINISTRATOR,Message,ENGINEER,CONTROL,SHOWOWNER) VALUES('Ba' at line 1
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
      at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
      at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
      at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
      at com.mysql.jdbc.Util.getInstance(Util.java:384)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
      at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)
      at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2113)
      at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2409)
      at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2327)
      at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2312)
      at com.google.project.DBManager.UpdateUser(DBManager.java:646)
      at com.google.project.UpdateUser.doPost(UpdateUser.java:72)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
      at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
      at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:857)
      at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
      at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
      at java.lang.Thread.run(Thread.java:619)

public void UpdateUser(User user) {
	try
	{
		
		/*System.out.println("UPDATE users SET NAME ="+user.getName()+","
				+"PASSWORD ="+user.getPassword()+" "
				//+"CELL ="+user.getCell()+","
				//+"ADMINISTRATOR ="+user.getAdministrator()+","
				//+"Message ="+user.getMessage()+","
				//+"ENGINEER ="+user.getEngineer()+","
				//+"CONTROL ="+user.getControl()+","
				//+"SHOWOWNER ="+user.getShowOwner()+""
				+"WHERE idusers ="+user.getID()+"");

		ps = con.prepareStatement("UPDATE users SET NAME ="+user.getName()+","
						+"PASSWORD ="+user.getPassword()+" "
						//+"CELL ="+user.getCell()+","
						//+"ADMINISTRATOR ="+user.getAdministrator()+","
						//+"Message ="+user.getMessage()+","
						//+"ENGINEER ="+user.getEngineer()+","
						//+"CONTROL ="+user.getControl()+","
						//+"SHOWOWNER ="+user.getShowOwner()+" "
						+"WHERE idusers ="+user.getID()+""); */
						
		
		ps = con.prepareStatement("UPDATE Users SET(NAME,PASSWORD,CELL,"                                    + "ADMINISTRATOR,Message,ENGINEER,CONTROL,SHOWOWNER)" 
                +" VALUES(?,?,?,?,?,?,?,?) WHERE idusers =\""+user.getID()+"\"");
		
		
		try
		{
			
			
			ps.clearParameters(); // clears previous parameters in there was any
				ps.setString(1, user.getName());
				ps.setString(2, user.getPassword());
				ps.setString(3, user.getCell());
				ps.setString(4, user.getAdministrator());
				ps.setString(5, user.getMessage());
				ps.setString(6, user.getEngineer());
				ps.setString(7, user.getControl());
				ps.setString(8, user.getShowOwner());
				
				System.out.println("UPDATE users SET NAME ="+user.getName()+","
						+"PASSWORD ="+user.getPassword()+","
						+"CELL ="+user.getCell()+","
						+"ADMINISTRATOR ="+user.getAdministrator()+","
						+"Message ="+user.getMessage()+","
						+"ENGINEER ="+user.getEngineer()+","
						+"CONTROL ="+user.getControl()+","
						+"SHOWOWNER ="+user.getShowOwner()+" "
						+"WHERE idusers ="+user.getID()+"");
				
				ps.executeUpdate(); 
			
		}
		catch(SQLException e)
		{
			System.out.println("Error: Error retrieving data!");
			e.printStackTrace();
			System.exit(1);
		}
						
	}
	catch(SQLException e)
	{
		System.out.println("Error: Cannot execute query!");
		e.printStackTrace();
		System.exit(1);
	}

}

Open in new window

CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
how we can get id throgh jsp/jstl in spring 3..??
justin.sahara@gmail.com

Author

Commented:
Yeah this worked ok, don't understand why it didn't work the other way. Thanks.
CERTIFIED EXPERT

Commented:
thanks for the points

<<Yeah this worked ok, don't understand why it didn't work the other way. Thanks.>>
My experience in mysql tells that update query syntax is different.
also check
http://dev.mysql.com/doc/refman/5.0/en/update.html
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.