Batch insert into in-memory table

[m]

When trying to insert into a in-memory table I get:

Exception in thread "main" java.sql.BatchUpdateException
at org.h2.jdbc.JdbcStatement.executeBatch(JdbcStatement.java:644)
at H2Setup.main(H2Setup.java:102)

Here the table create :

create memory table my_table (
ID VARCHAR(100) NOT NULL,
NAME VARCHAR(400),
ADDRESS1 VARCHAR(400),
CITY VARCHAR(200),
STABRV VARCHAR(10),
ZIP5 VARCHAR(5),
PHONE VARCHAR(19),
PRIMARY KEY (ID)
);


Connect string:

Connection connh =
DriverManager.getConnection("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1",
"sa", "");


insert is batched

for every 1000 records i do a :

int [] revtal = pstmt.executeBatch();

else

pstmt.addBatch(Msql);


it works fine if i use jdbc:h2:~/test as my url.

Any ideas.

Thanks

[Thomas Mueller]

Hi,

> Exception in thread "main" java.sql.BatchUpdateException
>        at org.h2.jdbc.JdbcStatement.executeBatch(JdbcStatement.java:644)
>        at H2Setup.main(H2Setup.java:102)

That means the operation failed, but the root cause is not listed.
Would it be possible for you to debug H2, and check what the exception
is at JdbcStatement.java, line 635?

try {
result[i] = executeUpdateInternal(sql);
} catch (Exception e) {
logAndConvert(e); // << ======= what kind of
error is this?
//## Java 1.4 begin ##
result[i] = Statement.EXECUTE_FAILED;
//## Java 1.4 end ##
error = true;
}

With an in-memory database it's not so easy otherwise. Unless, I
believe if you append ;TRACE_LEVEL_SYSTEM_OUT=3 to the database URL (I
didn't test it however).

Regards,
Thomas

[m]

So added the debug to my connection URL and it appears that I'm
getting a table not found error:
org.h2.jdbc.JdbcSQLException: Table "my_table" not found; SQL
statement:

I changed the table name above. Im sure the table exists. When I
change my connection url to jdbc:h2:~/test it works fine. I'm using
this command to start my server: java -cp h2*.jar
org.h2.tools.Server.
On Feb 5, 6:25 am, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

[Thomas Mueller]

Hi,

> org.h2.jdbc.JdbcSQLException: Table "my_table" not found

> I changed the table name above. Im sure the table exists.

In 99% of the cases the problem is that the database is in a different
directory. Could you try again, but use the absolute path?

jdbc:h2:/Users/xyz/test

(or whatever the absolute path is)? See also:

http://h2database.com/html/faq.html#database_files

> I'm using this command to start my server

If you are using an embedded database URL, why do you start a server? See

http://h2database.com/html/features.html#connection_modes
http://h2database.com/html/features.html#database_url

I will try to fix the BatchUpdateException so that the 'real'
exception is included.

Regards,
Thomas