DELETE throws a java.lang.IndexOutOfBoundsException

[Steve]

Hi,

I have an array of ID's and I want execute a delete statement which deletes a row if their ID can be found in my ID array.

I tried the following statement, which should be work but I an IndexOutOfBoundsException will be thrown.

DELETE FROM MYTABLE WHERE ID IN (SELECT TEMPORARY_ID FROM TABLE(TEMPORARY_ID LONG=?)

Exception:

 org.h2.jdbc.JdbcSQLException: General error: "java.lang.IndexOutOfBoundsException: Index: 1, Size: 0"; SQL statement:

DELETE FROM FEATURE_1 WHERE ID IN (SELECT TEMPORARY_ID FROM TABLE(TEMPORARY_ID LONG=?)) [50000-176]

at org.h2.message.DbException.getJdbcSQLException(DbException.java:344)

at org.h2.message.DbException.get(DbException.java:167)

at org.h2.message.DbException.convert(DbException.java:294)

at org.h2.command.Command.executeUpdate(Command.java:262)

at org.h2.jdbc.JdbcPreparedStatement.execute(JdbcPreparedStatement.java:199)

Do I something wrong? Or Do you know a better solution for that?

[Noel Grandin]

It's working for me, something like:

Statement stat = conn.createStatement();
stat.execute("CREATE TABLE t1 (c1 INT)");
stat.execute("INSERT INTO t1 SELECT X FROM SYSTEM_RANGE(1, 10);");
prep = conn.prepareStatement("DELETE FROM t1 WHERE c1 IN (SELECT x from TABLE(x INT = ?))");
prep.setObject(1, new Integer[] { 1, 2 });
assertEquals(2, prep.executeUpdate());
rs = stat.executeQuery("SELECT COUNT(*) FROM t1");
rs.next();
assertEquals(8, rs.getInt(1));

works fine.

[Thomas Mueller]

Hi,

Could you send the complete stack trace please (including all "caused by").

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Steve]

Thank you for your fast response!

As requested - the complete stack trace. ... I replaced my class & package names with more meaningful ones.

org.h2.jdbc.JdbcSQLException: General error: "java.lang.IndexOutOfBoundsException: Index: 1, Size: 0"; SQL statement:

DELETE FROM FEATURE_1 WHERE _ID_$$_GMSC_$$ IN (SELECT GEOMETRYID FROM TABLE(GEOMETRYID LONG=?)) [50000-176]

at org.h2.message.DbException.getJdbcSQLException(DbException.java:344)

at org.h2.message.DbException.get(DbException.java:167)

at org.h2.message.DbException.convert(DbException.java:294)

at org.h2.command.Command.executeUpdate(Command.java:262)

at org.h2.jdbc.JdbcPreparedStatement.execute(JdbcPreparedStatement.java:199)

at my.application.plugin.MyDatabaseWrapper.synchronize(MyDatabaseWrapper.java:242)

at my.application.plugin.MyDatabaseWrapperTest.myTestMethod(MyDatabaseWrapper.java:894)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:483)

at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)

at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)

at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)

at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)

at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)

at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)

at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)

at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)

at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)

at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)

at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)

at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)

at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)

at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)

at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)

at org.junit.runners.ParentRunner.run(ParentRunner.java:309)

at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)

at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)

Caused by: java.lang.IndexOutOfBoundsException: Index: 1, Size: 0

at java.util.ArrayList.rangeCheck(ArrayList.java:653)

at java.util.ArrayList.get(ArrayList.java:429)

at org.h2.result.LocalResult.next(LocalResult.java:236)

at org.h2.index.IndexCursor.nextCursor(IndexCursor.java:295)

at org.h2.index.IndexCursor.next(IndexCursor.java:273)

at org.h2.table.TableFilter.next(TableFilter.java:361)

at org.h2.command.dml.Delete.update(Delete.java:71)

at org.h2.command.CommandContainer.update(CommandContainer.java:79)

at org.h2.command.Command.executeUpdate(Command.java:254)

... 29 more

An Additional question: Which would you suggest to delete thousand of rows?

*) DELETE FROM MyTable WHERE ID IN (SELECT x FROM TABLE(x LONG=?))

or

*) for each id one delete statement -> DELETE FROM MyTable WHERE ID=?

Am Freitag, 21. November 2014 14:04:19 UTC+1 schrieb Thomas Mueller:

Hi,

Could you send the complete stack trace please (including all "caused by").

Regards,

Thomas

On Fri, Nov 21, 2014 at 1:51 PM, Noel Grandin <noelg...@gmail.com> wrote:

It's working for me, something like:

        Statement stat = conn.createStatement();
        stat.execute("CREATE TABLE t1 (c1 INT)");
        stat.execute("INSERT INTO t1 SELECT X FROM SYSTEM_RANGE(1, 10);");
        prep = conn.prepareStatement("DELETE FROM t1 WHERE c1 IN (SELECT x from TABLE(x INT = ?))");
        prep.setObject(1, new Integer[] { 1, 2 });
        assertEquals(2, prep.executeUpdate());
        rs = stat.executeQuery("SELECT COUNT(*) FROM t1");
        rs.next();
        assertEquals(8, rs.getInt(1));

works fine.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.