MySQL String not executing in Java

Question

I am trying and failing to get a String SQL command to execute on an existing mySQL database using a simple test program in Netbeans 8.0.2. Strange thing is the same SQL command executes fine when put directly into the IDE. Appreciate the help and please correct me on any terminology, I'm new and working it out from online tutorials. Thanks

package testdb1;

/**
 *
 * @author x
 */
import java.sql.*;

public class TestDB1 {

    //Driver name and database URL    
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/mynewdatabase?zeroDateTimeBehavior=convertToNull";

    static final String username = "****";
    static final String password = "**********";

    public static void main(String[] args) {

    Connection conn = null;
    Statement stmnt = null;

    try
    {

        System.out.println("Connecting...");
     //Open connection
     conn = DriverManager.getConnection(DB_URL, username, password);

     //Execute query
     System.out.println("Creating statement...");
     stmnt = conn.createStatement();
     String sql = "START TRANSACTION; " +
                  "UPDATE customer_test_accounts " +
                  "SET balance = balance + 1000 " +
                  "WHERE accountnumber = 2; " +
                  "COMMIT; " +
                  "ROLLBACK;";

     stmnt.executeQuery(sql);

Output:

Connecting...
Creating statement...
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 'UPDATE customer_test_accounts SET balance = balance + 1000 WHERE accountnumber =' 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:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4120)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4052)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2788)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2738)
    at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1617)
    at testdb1.TestDB1.main(TestDB1.java:45)
BUILD SUCCESSFUL (total time: 2 seconds)

Answer 1

You are doing JDBC Transactions in a wrong way. Putting all junk in sql string won't work.

Instead use :

con.setAutoCommit(false); Set auto commit to false

When a connection is created, it is in auto-commit mode. This means that each individual SQL statement is treated as a transaction and is automatically committed right after it is executed.

con.commit(); For commit

con.rollback(); For rollback

See Using Transactions and follow examples

Answer 2

just write this simple sql statement and try.... "UPDATE customer_test_accounts SET balance = balance + 1000 WHERE accountnumber = 2"

Answer 3

The problem is quite simple and pointed out by @Jens for java. Same deal for php. Why talk about php you wonder? Concept is the same in that mysql needs to have multiple stmt batches turned on or allowed.

How to execute multiple SQL statements from java

Executing multiple SQL queries in one statement with PHP

Otherwise it bombs and you focus on cleaning the sql string which is not the problem. Again the problem is preparing the connection or stmt to allow such a batch. In the case here, it bombs after the innards reach the first semi-colon regardless of how well constructed it appears in the eyes of a .sql file script running it or it sitting in a stored proc.

Answer 4

You can try the code: String sql = "UPDATE customer_test_accounts " + "SET balance = balance + 1000 " + "WHERE accountnumber = 2"; stmnt.executeUpdate(sql);