MySQLsyntaxerrorexception Near "" When Trying to Execute Preparedstatement

MySQLSyntaxErrorException near ? when trying to execute PreparedStatement


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 '? or MemberName = ?' at line 1

MySQL doesn't understand the meaning of ? in the SQL query. It's indeed invalid SQL syntax. So somehow it's not been replaced by PreparedStatement. And guess what?

PreparedStatement s = conn.prepareStatement(query);
s.setInt(1, intValue);
s.setString(2, strValue);
rs = s.executeQuery(query); // Fail!

You're overridding the prepared query with the original query! You need to call the argumentless PreparedStatement#executeQuery() method instead of Statement#executeQuery(String).

PreparedStatement s = conn.prepareStatement(query);
s.setInt(1, intValue);
s.setString(2, strValue);
rs = s.executeQuery(); // OK!

Unrelated to the problem, your code is leaking resources. The DB will run out of them after several hours and your application will crash. To fix this, you need to follow the JDBC idiom of closing Connection, Statement and ResultSet in the finally block of the try block where they're been acquired. Check the JDBC basic tutorial for more detail.

How to resolve MySQLSyntaxErrorException in JDBC preparedStatement


.. right syntax to use near ''comment' = '123'

You are getting exception because column name parameterization, which is not correct.

 UPDATE expenses_income SET ? = ?

It should be

 UPDATE expenses_income SET column_name = ?

I also noticed a semicolon ; at the end of the SQL, which should be removed and you don't need to cast Date explicitly. It should be just

UPDATE expenses_income SET column_name = ? WHERE userId = ? AND date = ?

Also, you shouldn't name column name like date, it should be last_updated or something meaningful.

MySQLSyntaxErrorException when trying to execute PreparedStatement

Try this this should work:

    try {
connection.setAutoCommit(true);
String sql = "insert into testtable values(?,?)";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setInt(1, userId);
statement.setString(2, userName);
saveStatus = statement.execute();
} catch (SQLException e) {
e.printStackTrace();
}
return saveStatus;
}

PreparedStatement is precompiled statement. you don't have to supply the sql string while executing.

Why this PreparedStaement throws a mysql error?

Like @Jens told in the comments i have to call executeUpdate() without parameters.

Thx again @Jens!!

Thats the correct code:

@Override
public void save(Planet planet) throws SQLException {
String namePlanet = planet.getName();
float massPlanet = planet.getMass();
boolean habitablePlanet = planet.isHabitable();
sql = "INSERT INTO planeta (nom,massa,habitable) VALUES (?,?,?)";
PreparedStatement preparedStatement = conn.prepareStatement(sql);
preparedStatement.setString(1, namePlanet);
preparedStatement.setFloat(2, massPlanet);
preparedStatement.setBoolean(3, habitablePlanet);
preparedStatement.executeUpdate(); // <--- Here was the error!!

}

I have a problem with the prepared statement in Java - Mysql 8.0

The error is that you are using the parent class's Statement.executeUpdate(String) instead of PreparedStatement.executeUpdate().

The semicolon as statement separator does not belong in a single statement offered to the JDBC.

    String insertGame = "insert into games (game_name, game_genre, game_year) "
+ "values (?, ?, ?)";
try (PreparedStatement statment = connection.prepareStatement(insertGame)) {
statment.setString(1, game.getName());
statment.setString(2, game.getGenre());
statment.setInt(3, game.getYear());
statment.executeUpdate();
}

For the rest closing the statement, like with a try-with-resources, is important.
The above will also close statment when an SQLException is thrown for say duplicate game names.

.MySQL throws an error in a JDBC Prepared statement?

the key here is the error
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 '?,?,?)'

This implies that the actual query to your mysql contains ? and not the intended strings.

The problem is in insertUser.executeUpdate(query);

Passing query as a parameter to executeUpdate() executes the raw query String. According to javadocs, this method should not be called for a PreparedStatement. The proper way to execute the PreparedStatement is to use

insertUser.executeUpdate();

Refer:

https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#executeUpdate(java.lang.String)

https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#executeUpdate()

Also, your PreparedStatement needs to be closed, as they might cause a memory leakage.

SQL query in my JAVA program done using Prepared statement throwing error despite being correct

Your problem seems to be here:

stmt.executeUpdate(sql);

You have already given the raw string query to the statement here:

stmt=conn.prepareStatement(sql);

The PreparedStatement version of executeUpdate() takes no parameters. Looks like you are hitting the version in the Statement interface inherited by PreparedStatement that runs the string passed in as the parameter.

I think it will work if you remove the sql string from the call:

stmt.executeUpdate();



Related Topics



Leave a reply



Submit