Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Whats wrong with my Update SQL statement

Posted on 2010-09-07
12
Medium Priority
?
388 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

0
Comment
Question by:bhession
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 92

Expert Comment

by:objects
ID: 33615647
needs to be:
... set col1=value1, col2=value2 ...
0
 
LVL 5

Expert Comment

by:SimonDard
ID: 33615656
Two spaces are missing: between SET and the first parenthesis and between the next parenthesis and VALUES.
0
 

Author Comment

by:bhession
ID: 33615729
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

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 40

Expert Comment

by:Gurvinder Pal Singh
ID: 33615788
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()+"");
0
 
LVL 26

Expert Comment

by:ksivananth
ID: 33616039
>>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()+"\"");
0
 
LVL 40

Expert Comment

by:Gurvinder Pal Singh
ID: 33616057
the original code should be

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

Author Comment

by:bhession
ID: 33616245
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

0
 
LVL 40

Accepted Solution

by:
Gurvinder Pal Singh earned 1000 total points
ID: 33616274
update your line 26-27 with the update statement i posted in my previous reply
0
 

Expert Comment

by:justinsahara
ID: 33616507
how we can get id throgh jsp/jstl in spring 3..??
0
 

Expert Comment

by:justinsahara
ID: 33616539
justin.sahara@gmail.com
0
 

Author Closing Comment

by:bhession
ID: 33617113
Yeah this worked ok, don't understand why it didn't work the other way. Thanks.
0
 
LVL 40

Expert Comment

by:Gurvinder Pal Singh
ID: 33617196
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
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
A solution for Fortify Path Manipulation.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question