Unable to query mysql database using jdbc preparedstatement

Question

I am trying to run the below jdbc code to get the object key:

String sqlSelectObjKey = "select OBJ_KEY from obj where OBJ_NM=? and OBJ_TYP_CD='CONN';";


    PreparedStatement statement = null;
    ResultSet rs = null;
    try {           
        statement = connection.prepareStatement(stmtSelectObjKey);
        statement.setString(1, "Postal Connector");

        rs = statement.executeQuery(stmtSelectObjKey);
        while(rs.next()) {
            this.executionLog.setObjKey(rs.getInt("OBJ_KEY"));
        }

    } catch (SQLException e1) {
        e1.printStackTrace();

    } finally {
        if (rs != null) try { rs.close(); } catch (SQLException ignore) {}
        if (statement != null) try { statement.close(); } catch (SQLException ignore) {}
    }

But every time it results in below exception:

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 OBJ_TYP_CD='CONN'' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    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)
    at appMetadata.ConnectorMetadataLoad.insertExecutionLog(ConnectorMetadataLoad.java:67)
    at appCore.PostalCodeInterface.getPostalData(PostalCodeInterface.java:37)
    at appMain.PostalDataConnector.getPostalData(PostalDataConnector.java:19)
    at PostalDemo.main(PostalDemo.java:14)

Removing the place holder and setting the value directly works fine.

I am not sure where the syntax is wrong, I will appreciate if anyone can point out the error.

Yes, removing placeholder and setting value directly in the where clause also works. — BigD
– BigD, Commented Feb 18, 2015 at 11:21
The error tells us that the ? is problematic. It looks like the ? is not replaced by the string argument. — user1907906
– user1907906, Commented Feb 18, 2015 at 11:23
I debugged statement, it seems that it is replacing the placeholder but even then it results in the same error. — BigD
– BigD, Commented Feb 18, 2015 at 11:25
You should perhaps use executeQuery() with no args. As a side note, you should also use a placeholder for the second 'CONN'. — Gyro Gearless
– Gyro Gearless, Commented Feb 18, 2015 at 11:32

Kalaiarasan Manimaran · Accepted Answer · 2015-02-18 11:46:46Z

2

I got Your error, why you are assigning the query again to the preparedstatment.

Your Code:

statement = connection.prepareStatement(stmtSelectObjKey);
statement.setString(1, "Postal Connector");
rs = statement.executeQuery(stmtSelectObjKey);

Code Should be:

statement = connection.prepareStatement(stmtSelectObjKey);
statement.setString(1, "Postal Connector");
rs = statement.executeQuery();

Thats why you are getting syntax error for the ?. Hope this helps you

answered Feb 18, 2015 at 11:46

Kalaiarasan Manimaran

1,6781 gold badge13 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

nano_nano · Accepted Answer · 2015-02-18 11:21:17Z

1

remove the ; in your sql string and try it again.

the ; is used in the most db-tools to seperate sql-strings but it is no standard sql.

answered Feb 18, 2015 at 11:21

nano_nano

12.6k8 gold badges58 silver badges85 bronze badges

Comments

Lrrr · Accepted Answer · 2015-02-18 11:56:28Z

1

instead of

statement.executeQuery(stmtSelectObjKey);

use

statement.executeQuery();

it will work !!

edited Feb 18, 2015 at 11:56

Lrrr

4,8055 gold badges44 silver badges65 bronze badges

answered Feb 18, 2015 at 11:47

Alok kumar

93 bronze badges

Comments

Janny · Accepted Answer · 2015-02-18 13:02:01Z

As you using prepareStatement the execute syntax is like below prepareStatement.executeQuery() So change your code like below

String sqlSelectObjKey = "select OBJ_KEY from obj where OBJ_NM=? and OBJ_TYP_CD='CONN';";


    PreparedStatement statement = null;
    ResultSet rs = null;
    try {           
        statement = connection.prepareStatement(stmtSelectObjKey);
        statement.setString(1, "Postal Connector");

        rs = statement.executeQuery();//Change is here
        while(rs.next()) {
            this.executionLog.setObjKey(rs.getInt("OBJ_KEY"));
        }

    } catch (SQLException e1) {
        e1.printStackTrace();

    } finally {
        if (rs != null) try { rs.close(); } catch (SQLException ignore) {}
        if (statement != null) try { statement.close(); } catch (SQLException ignore) {}
    }

For more information click here

Neeraj Kumar · Accepted Answer · 2017-10-31 11:14:44Z

-1

Replace below query

String sqlSelectObjKey = "select OBJ_KEY from obj where OBJ_NM=? and OBJ_TYP_CD='CONN';";

with

String sqlSelectObjKey = "select OBJ_KEY from obj where OBJ_NM=? and OBJ_TYP_CD='CONN'";

edited Oct 31, 2017 at 11:14

Neeraj Kumar

7792 gold badges18 silver badges37 bronze badges

answered Oct 31, 2017 at 10:46

Alok kumar

93 bronze badges

1 Comment

Murmel Over a year ago

Duplicate of Stefan Beike's answer

Collectives™ on Stack Overflow

Unable to query mysql database using jdbc preparedstatement

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related