-1

I'm working with SQLite for my Android application and after some research I figured out how to do multiple insert transactions using the UNION statement.

But this is quite inefficient. From what I see at http://www.sqlite.org/speed.html, and in a lot of other forums, I can speed up the process using the BEGIN - COMMIT statements. But when I use them I get this error:

Cannot start a transaction within a transaction.

Why? What is the most efficient way of doing multiple insert?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Stack Diego
  • 1,309
  • 17
  • 43

1 Answers1

0

Which JDBC driver are you using? Is there only one that's built into the Android distribution?

The problem is most likely with java.sql.Connection#setAutoCommit(). If the connection already has auto-commit enabled—which you can check with Connection#getAutoCommit()—then your JDBC driver is already issuing the SQL commands to start a transaction before your manual attempt to do, which renders your manual command redundant and invalid.

If you're looking to control transaction extent, you need to disable auto-commit mode for the Connection by calling

connection.setAutoCommit(false);

and then later, after your individual DML statements have all been issued, either commit or roll back the active transaction via Connection#commit() or Connection#rollback().

I have noticed that some JDBC drivers have a hard time coordinating auto-commit mode with PreparedStatement's batch-related methods. In particular, the Xerial JDBC driver and the Zentus driver on which it's based both fight against a user controlling the auto-commit mode with batch statement execution.

seh
  • 14,999
  • 2
  • 48
  • 58