CODEWITHSUNDEEP
javamysqljdbc
Rajasekhar
Rajasekhar

Reputation: 807

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException while deleting a record in mysql

Following code is raising Syntax Error exception I was using Ajax calls in the back end to do CRUD operations on tables 

public class CrudDao {
    private Connection connection;
    private String table;

    public CrudDao(Connection con, String table) {
        this.connection = con;
        this.table = table;

    }

    public void deleteUser(int RECORDID) {
        try {

            PreparedStatement preparedStatement = connection
                    .prepareStatement("delete from " + table
                            + "where USERID=?");
             preparedStatement.setInt(1, RECORDID);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e);
        }
    }


}

In the above code i have written only delete method which is throwing the com.mysql.jdbc.exceptions.MySQLSyntaxErrorException

the trace of the exception is 

com.mysql.jdbc.exceptions.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 'USERID=102' at line 1
{"Result":"OK"}
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3176)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1153)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1404)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1318)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1303)
    at com.symp.CrudDao.deleteUser(CrudDao.java:33)
    at com.symp.CRUDController.service(CRUDController.java:82)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1040)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:315)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:745)

Upvotes: 0

Views: 401

Answers (2)

Sajan Chandran
Sajan Chandran

Reputation: 11487

You need to have space between table name and your where clause.

"delete from " + table + " where USERID=?"

Currently your sql script looks like

delete from tablenamewhere USERID=102

Upvotes: 2

RobP
RobP

Reputation: 9522

try changing

        PreparedStatement preparedStatement = connection
                .prepareStatement("delete from " + table
                        + "where USERID=?");

to

        PreparedStatement preparedStatement = connection
                .prepareStatement("delete from " + table
                        + " where USERID=?");

Your table name and the keyword where were running together with no space between!

Upvotes: 1

Related Questions