Solved

Whats wrong with my Update SQL statement

Posted on 2010-09-07
12
381 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
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

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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
corrupt Databases 9 79
Database maintenance 36 98
Html split(text) 2 26
Is it possible to create a report that displays pictures in a grid format? 5 19
Read about achieving the basic levels of HRIS security in the workplace.
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…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…

679 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