Unable to delete csv after using the file with CSVREAD function on Windows

[satheesh Thodupunoori]

Hi Team,

I am using CSVREAD function to dump data from csv into a table using below query.

INSERT INTO  testdomain.`sale_data` (SELECT Column3,Column1,Column2 FROM 

CSVREAD('E:\Temp\nosqldb\saledata_1110.csv',null,'fieldSeparator=\, charset=UTF-8') LIMIT 0,40).

Data got dumped into table but i am no longer able to delete the csv from file system.

I tried running this query from a java program and through web console on windows machine. But I could not delete the file from file system.

My h2 version is 1.3.76 .

can someone please help me here.

Thanks in advance.

Satheesh

[satheesh Thodupunoori]

correction:  My h2 version is 1.3.176 

[Andreas Reichel]

Dear Satheesh.

in my own experience, File locking under Windows has always been problematic. Windows locks files, when reading from it while normally you would expect lock on write only.

After reading the CSV file into H2, shutdown the H2 DB (maybe even the JVM) and open it again. That should release the lock and enable you deleting the CSV file.

H2 1.3.76 is  quite old. If there is any problem with closing the CSV file it is unlikely someone will investigate and/or fix it in this old branch.

Maybe you can try with a recent H2 version 1.4.200 or even pull from GIT. If reproducible, we would have a chance to fix it.

Best regards

Andreas

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/745c7f85-d364-4300-9774-39ba93e4d60fn%40googlegroups.com.

[satheesh Thodupunoori]

Thank you Andreas. We shall try with latest version. 

But after version upgrade, do we still be able to use the same database that was already created?

Satheesh

[Andreas Reichel]

On Wed, 2020-11-11 at 21:31 -0800, satheesh Thodupunoori wrote:

Thank you Andreas. We shall try with latest version. 

But after version upgrade, do we still be able to use the same database that was already created?

Good Morning Sateesh,

maybe try first, if a newer version indeed solves your challenge. If not, open a case with a simple code sample to reproduce.

When the newer version indeed solves your problem, them you should be able to extract the DB to a SQL script using the old 1.3.174 driver. Then you can create a new empty database with the 1.4.200 driver and import that script.

The H2 website has the details.

Best regards

Andreas