Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax

Question

I am trying to execute the above query through java but I get the following error in line 6:

Exception in thread "main" 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 '? AND lexicon_lemma = ?)' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
at com.mysql.jdbc.Util.getInstance(Util.java:360)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2526)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1446)

I can't find what is the problem with the syntax... Any suggestion? This is my query:

public static void InsertEntry(String lexiconType, String lemma, String lang) throws SQLException {
    String selectSQL = "SELECT id FROM lexicons WHERE (lexicon_type = ? AND lexicon_lemma = ?)";
    PreparedStatement preparedStatement = connection.prepareStatement(selectSQL);
    preparedStatement.setString(1, lexiconType);
    preparedStatement.setString(2, lemma);
    ResultSet rs = preparedStatement.executeQuery(selectSQL);
    int id = -1;
    if (rs.next()) {
        id = rs.getInt(1);
        System.out.println(id);
    } else {
        String insertSQL = "INSERT INTO lexicons "
    + "(lexicon_type,lexicon_lemma,lexicon_lang) VALUES"
    + "(?,?,?)";
        PreparedStatement prepStatement = connection.prepareStatement(insertSQL);
        prepStatement.setString(1, lexiconType);
        prepStatement.setString(2, lemma);
        prepStatement.setString(3, lang);
        prepStatement .executeUpdate();
        prepStatement.close();
    }
}

Your error stack posted is incomplete. Post it with sql statement included as seen in error stack. — Ravinder Reddy
– Ravinder Reddy, Commented Mar 25, 2015 at 9:39

Ravinder Reddy · Accepted Answer · 2015-03-25 09:56:59Z

There is no necessity to pass sql statement variable to executeQuery for PreparedStatement. That is the main issue in your case.

PreparedStatement preparedStatement = connection.prepareStatement(selectSQL);
preparedStatement.setString(1, lexiconType);
preparedStatement.setString(2, lemma);
// error is in the following statement
ResultSet rs = preparedStatement.executeQuery(selectSQL);

As you are resetting the sql to be executed, the statement is not recognized as prepared statement but an instance of parent Statement which is looking for valid inputs in between quotes for stings etc. which are not present. And hence is the Syntax error.

Change:

ResultSet rs = preparedStatement.executeQuery(selectSQL);

To:

ResultSet rs = preparedStatement.executeQuery();

And it should be working.

Refer:

PreparedStatement.html#executeQuery

Abhi · Accepted Answer · 2015-03-25 09:55:44Z

0

Change:

ResultSet rs = preparedStatement.executeQuery(selectSQL);

to

ResultSet rs = preparedStatement.executeQuery();

edited Mar 25, 2015 at 9:55

answered Mar 25, 2015 at 9:26

Abhi

3417 silver badges15 bronze badges

5 Comments

Ravinder Reddy Over a year ago

Why? and what is the difference? And putting '?' in single quotes and using prepared statement is not going to work.

Abhi Over a year ago

In SQL queries, strings are enclosed within a pair of single quotes.

Ravinder Reddy Over a year ago

With java and prepared statement enclosing in single quotes is not correct.

Omen Over a year ago

I have tried it without parenthesis but without enclosing ? in single quotes, but same error as well.

Abhi Over a year ago

sorry my mistake...the error is here in ResultSet rs = preparedStatement.executeQuery(selectSQL); change it as ResultSet rs = preparedStatement.executeQuery(); and no need to add single quotes

Collectives™ on Stack Overflow

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax

2 Answers 2

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related