Asked  6 Months ago    Answers:  5   Viewed   32 times

I'm trying to execute a query using a PreparedStatement in Java.

I am getting error number 1064 when I try to execute my query (syntax error).

I have tested this in MySQL query browser with substituted values which works fine.

What's wrong with my code?

Here's the relevant code:

String query = "select MemberID, MemberName from members where MemberID = ? or MemberName = ?";
Connection conn = DriverManager.getConnection(DATABASE_URL, USERNAME, PASSWORD);
PreparedStatement s = conn.prepareStatement(query);
s.setInt(1, 2);
s.setString(2, "zen");
ResultSet rs = s.executeQuery(query);

Here's the exception I'm getting:

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

 Answers

28

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.

Tuesday, June 1, 2021
 
Tapha
answered 6 Months ago
25

One thing you can try:

exec("./serialTest   {$inputField}", $output, $status);
echo "status: " . $status;
echo "output: " . implode("n", $output);
Wednesday, March 31, 2021
 
Sidarta
answered 9 Months ago
46

Ensure that your MySQL configuration encoding is defined correctly. Check your settings and the correctness of the modifications with these commands:

show variables like 'character%';

and show variables like 'collation%';

Add these lines to either my.cnf or my.ini:

For MySQL 5.1.nn, and later versions 5.5.29 you just need these two lines:

[mysqld]
character-set-server = utf8
character-set-filesystem = utf8

For MySQL 5.0.nn and older use these settings:

[client]
default-character-set=utf8


[mysql]
default-character-set=utf8


[mysqld]
default-character-set=utf8
character-set-server=utf8

It is probably more convenient to use MySQL-Workbench for your settings. Versions 5+ are excellent.

enter image description here

In your Java program connect like this:

con = DriverManager.getConnection("jdbc:mysql://localhost:3306/myDatabase?useUnicode=true&characterEncoding=UTF-8","user","passwd");
Thursday, June 10, 2021
 
ajaybc
answered 6 Months ago
94

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 '? , ? , DEFAULT , NULL )' at line 1

Those placeholders ? should not appear in the MySQL side at all.

Look here,

sInserim.executeUpdate(sqlCommandInserim);

you're passing the raw SQL string into executeUpdate() instead of executing the PreparedStatement with the set values.

Replace it by

sInserim.executeUpdate();

The executeUpdate(sqlString) should be used on Statement only.


Unrelated to the concrete problem, you should be closing the PreparedStatement in the finally block to prevent resource leaking in case of exceptions. The same applies to Connection, Statement and ResultSet by the way.

Saturday, June 19, 2021
 
vuliad
answered 6 Months ago
64

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.

Tuesday, July 6, 2021
 
msg
answered 5 Months ago
msg
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :  
Share