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 errorYou 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
How to Sort Map Values by Key in Java
Find Oracle Jdbc Driver in Maven Repository
How to Unzip Files Programmatically in Android
Foreign Key Constraints in Android Using SQLite? on Delete Cascade
Division in Java Always Results in Zero (0)
Connect to SQL Server from Linux via Jdbc Using Integratedsecurity (Windows Authentication)
Cannot Run Program "Mvn" Error=2, No Such File or Directory
How to Include Platform-Specific Native Libraries in the .Jar File Using Eclipse
Wrong Elf Class: Elfclass32 (Possible Cause: Architecture Word Width Mismatch)
How to Validate an Xml File Against an Xsd File
Firestore - How to Structure a Feed and Follow System
Android Download Binary File Problems
Select Random Document from Firestore
Find Directory for "Application Data" on Linux and MACintosh
Using Visualvm to Connect to a Remote Jstatd Instance Through a Firewall