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)

I'm guessing `accountnumber` is a `varchar` type and take a string type, try `accountnumber= '2'` — Uma Kanth, Jun 11 '15 at 05:50
JDBC is based on a "single statement per execute"-model (although MySQL Connector/J has an option to allow multiple statements in an execute). JDBC handles transaction itself, you **must not start and commit transactions yourself using statements** (but by disabling autocommit and calling `Connection.commit()` or `Connection.rollback()`. By default JDBC uses auto commit, which means that each execute uses its own transaction that is committed (or rolled back) automatically. — Mark Rotteveel, Jun 11 '15 at 13:28

score 0 · Answer 1 · answered Jun 11 '15 at 05:52

0

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

answered Jun 11 '15 at 05:52

guru

1

You think there will be difference between `String str1 = "hey"; String str2 = "guru";` `str1 + str2` and `heyguru` – Uma Kanth Jun 11 '15 at 05:55
Both are the same. They result in the same string. – Uma Kanth Jun 11 '15 at 05:59
@UmaKanth i think his answer makes sense. There is no need for transactions (and the comment about strings baffles me too). Maybe i am dense ;) – AsConfused Jun 11 '15 at 06:29
There is no difference in writing `String str = "this is " + "a string";` and `String str = "this is a string"`. Both are the same, so that can't be the error. – Uma Kanth Jun 11 '15 at 06:32
I think the error is in putting a paragraph of commands together and executing them, no one doubts how to concat – AsConfused Jun 11 '15 at 06:34

Rajesh · Accepted Answer · 2015-06-11T06:39:58.720

0

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

edited Jun 11 '15 at 06:39

answered Jun 11 '15 at 06:09

Rajesh

2,135
1
12
14

Curious why it even needs a transaction anyway. I don't see it – AsConfused Jun 11 '15 at 06:25
may be example shared by OP is different from actual. I have also shared Using Transactions link, so let OP understand - whether transaction needed or not? – Rajesh Jun 11 '15 at 06:40
Even if the update hit multiple rows a transaction is not needed – AsConfused Jun 11 '15 at 06:52
There are times when you do not want one statement to take effect unless another one completes. – Rajesh Jun 11 '15 at 06:55
Agreed. This is not one of those times as there is only one stmt – AsConfused Jun 11 '15 at 07:02
Sorry if the code is misleading. The sql statement will eventually execute a transaction between 2 accounts, however Im just trying to get all the pieces working in their simplest form atm. – Patrick Wallace Jun 12 '15 at 03:49

score 0 · Answer 3 · answered Jun 11 '15 at 06:34

0

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

answered Jun 11 '15 at 06:34

Beniamin

56
2

score 0 · Answer 4 · edited May 23 '17 at 12:22

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.

MySQL String not executing in Java

4 Answers4