Bug #69247 MySQL jdbc conncetivity

• Submitted: 15 May 2013 20:49
• Modified: 16 May 2013 0:31
• Reporter: Skand Purohit
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.5
• OS: Windows (Connector used : mysql-connector-java-3.1.12.jar)
• CPU Architecture: Any
• Tags: java, jdbc, MySQL

[15 May 2013 20:49] Skand Purohit

Description:
Unable to Update Data in MYSQL database through jdbc connectivity.  

Error Message :  

Connecting MySql database : 
Connected to Database : sakila
java.sql.SQLException: 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 '? WHERE actor_id = ?' at line 1
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2928)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:2988)
	at com.mysql.jdbc.Statement.executeUpdate(Statement.java:935)
	at com.mysql.jdbc.Statement.executeUpdate(Statement.java:873)
	at com.Mysqlupdate.main(Mysqlupdate.java:49)

How to repeat:
package com;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

public class Mysqlupdate {

	public static void main(String[] args) throws IOException, SQLException {

		Properties prop = new Properties();
		FileInputStream f1= new FileInputStream("D:\\jdbc_properties.txt");

		prop.load(f1);

		System.out.println("Connecting MySql database : ");

		Connection conn = null;
		PreparedStatement ps = null;
		String dbName = "sakila";
		String url = prop.getProperty("jdbc.url");
		String userName = prop.getProperty("jdbc.userName");
		String password = prop.getProperty("jdbc.password");

		try{
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(url+dbName,userName,password);
			System.out.println("Connected to Database : "+ dbName);
			
			
			
			 String sql = "Update ACTOR SET first_name= ? WHERE actor_id = ?"; 
			 
			ps = conn.prepareStatement(sql);          
			
			ps.setString(1,"Johnny " );
			ps.setInt(1,2);
			
			
			ps.executeUpdate(sql);
			sql= "SELECT actor_id,first_name,last_name from actor";
			ResultSet rs = ps.executeQuery(sql);
			while(rs.next()){
				int actor_id = rs.getInt("actor_id");
				String first_name= rs.getString("first_name");
				String last_name= rs.getString("last_name");
				System.out.print("ID = "+actor_id );
				System.out.print("  First Name ="+first_name );
				System.out.println("  Last Name= "+last_name);		
			}
			rs.close();
			ps.close();
			conn.close();
			System.out.println("Disconnected from database");
	
		}catch(SQLException se){
			se.printStackTrace();
		
		}catch(Exception a){
		a.printStackTrace();
		}
		finally {
			try{
				if(ps!=null)
					ps.close();
			}catch(SQLException se2){
				try{
					if(conn!=null)
						conn.close();
				}catch(SQLException se){
					se.printStackTrace();
				}
			}
		}
	}
}
	

Suggested fix:
tried to change query from update to delete but still having the same error message.

[15 May 2013 22:18] Mark Matthews

You want to use ps.executeUpdate(), not ps.executeUpdate(String).

[15 May 2013 22:33] Todd Farmer

Amplifying Mark's comments, executeUpdate() is defined by PreparedStatement, while executeUpdate(String sql) is defined by Statement.  The API docs help explain why SQL with prepared statement placeholders isn't handled as you expected when executeUpdate(String sql) is used:

http://docs.oracle.com/javase/1.4.2/docs/api/java/sql/PreparedStatement.html#executeUpdate...

http://docs.oracle.com/javase/1.4.2/docs/api/java/sql/Statement.html#executeUpdate%28java....

As a separate note, please consider upgrading to a recent version of Connector/J - 3.1.12 is over 7 years old, and many bugs have been fixed since then (along with meaningful features added).

[16 May 2013 0:31] Skand Purohit

Thank you Mark and  Todd it works now . Appreciate your quick response. 
Todd , also I have added the latest my sql java connector jar.