SQL Query that works in MySQL but not from Java

Question

I have a multiple INSERT INTO query

Connection con = null;

String sql = "START TRANSACTION;"
               + "Set autocommit=0;"
               + " INSERT INTO CARS (CARNR) VALUES (" + (getLetzteFahrzeugNr() +1)+ ");"
               + " INSERT INTO KAUFINFO (CARNR) VALUES (" + (getLetzteFahrzeugNr() +1)+ ");"
               + " INSERT INTO POSITIONS (CARNR) VALUES (" + (getLetzteFahrzeugNr() +1)+ ");"
               + " COMMIT;";

System.out.println(sql);           
Statement stmt = null;
ResultSet rs = null;


Integer carnr = null;
stmt = con.createStatement();
stmt.executeUpdate(sql);

although that the query is working perfectly at the MySQL directly but when I call it from the Java code doesn't working and all the time I m receiving the Error.

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 'Set autocommit=0; INSERT INTO CARS (CARNR) VALUES (108); INSERT INTO KAUFINFO (C' 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:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4098)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4030)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2671)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1749)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1666)
at util.DbUtilities.getNewFahrzeugNr(DbUtilities.java:131)
at util.DbUtilities.main(DbUtilities.java:231)

for my research have seen that for an INSERT INTO have to use executeUpdate, before I had it executequery .

This is not one query but several ones; moreover some of them are not even queries but MySQL specific commands. So, obviously, this kind of query cannot be run from JDBC. — fge
– fge, Commented Sep 19, 2015 at 17:37
but when I call it the query to the MySql is working , should I do it seperately ? — JavaMunich
– JavaMunich, Commented Sep 19, 2015 at 17:43

Mureinik · Accepted Answer · 2015-09-19 18:10:44Z

4

The ; is used to separate several statements. When using JDBC, you need to execute each of them separately:

Connection con = ...;
con.setAutoCommit(false);

Statement stmt = con.createStatement();
stmt.executeUpdate("INSERT INTO CARS (CARNR) VALUES (" + (getLetzteFahrzeugNr() +1)+ ")");
stmt.executeUpdate("INSERT INTO KAUFINFO (CARNR) VALUES (" + (getLetzteFahrzeugNr() +1)+ ")");
stmt.executeUpdate("INSERT INTO POSITIONS (CARNR) VALUES (" + (getLetzteFahrzeugNr() +1)+ ")");

con.commit();

Mandatory comment:
Using a PreparedStatement would probably be neater, and more secure.

edited Sep 19, 2015 at 18:10

answered Sep 19, 2015 at 18:03

Mureinik

316k54 gold badges402 silver badges406 bronze badges

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

Collectives™ on Stack Overflow

SQL Query that works in MySQL but not from Java

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related