I was trying to delete a column in a SQLite database table using JDBC. As there is no such function in SQLite, to achieve this, I used this approach:
- Create a new table (
t1_backup
) with all columns but not the one to be deleted, - Copy all data (except that of the column to be deleted) to the new table,
- Drop the old table (
t1
), - Rename the new table (
t1_backup
-->t1
).
The actual code is as follows:
Connection cn = DriverManager.getConnection("jdbc:sqlite:"+path);
Statement stmt = new Statement();
stmt = cn.createConnection();
stmt.execute("CREATE TABLE t1_backup(id);");
stmt.executeUpdate("INSERT INTO t1_backup SELECT id FROM t1;");
stmt.execute("DROP TABLE t1;");
stmt.execute("ALTER TABLE t1_backup RENAME TO t1;");
The first two statements were executed without a problem. But when the DROP TABLE statement was executed, it gave me java.sql.SQLException: [SQLITE_LOCKED] A table in the database is locked (database table is locked)
.
I have found a solution but I was not sure why it worked. The solution is to create three more Statement objects, connect them to the same database and execute each of the four SQL statements with a different Statement object:
stmt.execute("CREATE ...");
stmt2.executeUpdate("INSERT ...");
stmt3.execute("DROP ...");
stmt4.execute("ALTER ...");
Can this be explained?
The JDBC I am using is the one by Xerial.
Thank you.
Aucun commentaire:
Enregistrer un commentaire