0

This is my SQL code, a simple transaction. When I run it on MariaDB using run as script it works well, but it doesn't work with run as SQL Statement.

START TRANSACTION;
SELECT * from employee;
insert into Employee (id,name) values (:id,:name)
COMMIT;

I want to use this sql code directly in my Java code:

I am using jdbc url as "jdbc:mariadb://localhost:3306/test_mariadb?user=root&password=<password>";

String sql = "START TRANSACTION;
SELECT * from employee;
insert into Employee (id,name) values (:id,:name)
COMMIT;"

Connection connection = connect();
Statement stmt = connection.createStatement();
ResultSet resultSet = stmt.executeQuery(sql);

I am getting below error:

Caused by: java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'select * from employee;insert into Employee (id,name) values (150,'150_abc');...' at line 1

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Janki
  • 21
  • 5
  • JDBC drivers usually disable multiple statements by default **for security**, ie. to reduce sql injection risks to the db. If you understand the ramifications of allowing it, and ALL queries use bind variables, it can be enabled with the connection param: [allowMultiQueries=true](https://mariadb.com/kb/en/about-mariadb-connector-j/). Having said all that 1) why are you using a transaction for a SELECT statement? 2) See [How to start a transaction with JDBC](https://stackoverflow.com/questions/4940648/how-to-start-a-transaction-in-jdbc). 3) **Never use the "root" account**. Create a new login – SOS Mar 12 '22 at 06:31
  • JDBC drivers don't disable multiple statements for security, but because the API is designed to execute individual statements, so executing multiple statements is outside the scope of the specification, and supporting it by default would mean being non-compliant with the specification. – Mark Rotteveel Mar 12 '22 at 06:50
  • How can we allowMultiQueries=true? for mysql we use it in JDBC url, but in case of MariaDb, How can we use it? – Janki Mar 12 '22 at 09:54
  • @MarkRotteveel - I was always under the impression it was security related, but am open to being wrong. Does it say that in the specs explicitly or is it just implied? Searching hasn't turned up anything relevant yet. – SOS Mar 12 '22 at 18:53
  • @Janki - It should work the same way. Use it as a URL parameter. Give it a try. – SOS Mar 12 '22 at 19:27

0 Answers0