CODEWITHSUNDEEP
javamysql
TacoVox
TacoVox

Reputation: 141

SQL Query throws exception

I wrote the following code snipped with a SQL query which gets me the storeID of a particular store in the DB:

private int inDB(String storename, String phone, int zipcode)
    {
        try
        {
            String select = "SELECT storeID FROM Store WHERE storename = ? OR phone = ? AND City_zipcode = ?;";

            PreparedStatement pst = dbc.prepareStatement(select);

            pst.setString(1, storename);
            pst.setString(2, phone);
            pst.setInt(3, zipcode);

            ResultSet rs = pst.executeQuery(select);

            while(rs.next())
            {
                return rs.getInt("storeID");
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }

        return -1;
    }

Unfortunately this code throws a massive exception... And I try to figure out for a while whats the problem.

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 phone = ? AND City_zipcode = ?' 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:408)
    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 db.Store.inDB(Store.java:297)

As you can see, I'm using the MySQL db driver. All the other methods which are nearly the same are working. It must be the query which is causing the problems. But when I try to run it in phpMyAdmin it's absolutely no problem.

I hope someone of you can help me out...

Kind regards //Jonas

Upvotes: 0

Views: 71

Answers (2)

Ya Wang
Ya Wang

Reputation: 1808

I do believe that ? is a reserved keyword for mysql...

Read this, http://dev.mysql.com/doc/refman/5.1/en/regexp.html

Upvotes: 0

Bruce
Bruce

Reputation: 8879

Error is here change it

ResultSet rs = pst.executeQuery(select);

TO

ResultSet rs = pst.executeQuery();

you all ready set the query statement to preparedStatement

Upvotes: 3

Related Questions