Solved

Whats wrong with my Update SQL statement

Posted on 2010-09-07
12
384 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
Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
This video teaches viewers about errors in exception handling.
Suggested Courses

623 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