Solved

Whats wrong with my Update SQL statement

Posted on 2010-09-07
12
383 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 40

Expert Comment

by:gurvinder372
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:gurvinder372
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:
gurvinder372 earned 250 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:gurvinder372
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
Suggested Courses

751 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