Two problems with CSV

[Abu Abdulla]

Hi,

I have two issues with CSV.

Here is a sample code to generate the problems:

import java.sql.*;
import org.h2.tools.*;

public class H2
{
public static void main(String[] args){new H2().go();}
void go()
{
try
{
Class.forName("org.h2.Driver");
Connection conn = DriverManager.getConnection("jdbc:h2:./h2db/
h2;create=true", "s", "s");

final Statement s = conn.createStatement();
s.execute("CREATE TABLE t1(index INTEGER, name VARCHAR(50))");
s.execute("INSERT INTO t1 VALUES(1,'bbbb \n aaaa')");
//s.execute("INSERT INTO t1 VALUES(2,'bbbb \" aaaa')");

s.execute("CALL CSVWRITE('test.csv', 'SELECT * FROM t1', 'UTF-8',
'\u00F6', '\u00D6')");

// First Method
s.execute("INSERT INTO t1 SELECT * FROM
CSVREAD('test.csv','NULL','UTF-8','\u00F6','\u00D6')");

// Second Method
/*
final Csv csv = Csv.getInstance();
csv.setFieldDelimiter('\u00D6');
csv.setFieldSeparatorRead('\u00F6');
ResultSet rs = csv.read("test.csv", null, "UTF-8");
while(rs.next())
s.execute("INSERT INTO t1 VALUES("+rs.getInt("index")
+",'"+rs.getString("name")+"')");
*/
}
catch(Throwable e){e.printStackTrace();}
}
}


The code first create a database and insert two rows. each row
contains special charactor (\n, \").
Then We export the db to a file using different separators.
Then we import them again using two methods.
If we use the first method with inserting the first row we will have
this exception:

org.h2.jdbc.JdbcSQLException: Column count does not match; SQL
statement:
INSERT INTO t1 SELECT * FROM
CSVREAD('test.csv','NULL','UTF-8','ِ','?') [21S02-63]
at org.h2.message.Message.getSQLException(Message.java:89)
at org.h2.message.Message.getSQLException(Message.java:93)
at org.h2.message.Message.getSQLException(Message.java:71)
at org.h2.message.Message.getSQLException(Message.java:114)
at org.h2.command.dml.Insert.prepare(Insert.java:186)
at org.h2.command.Parser.prepareCommand(Parser.java:209)
at org.h2.engine.Session.prepareLocal(Session.java:173)
at org.h2.engine.Session.prepareCommand(Session.java:155)
at
org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:961)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:143)
at H2.go(H2.java:22)
at H2.main(H2.java:6)

On the other hand the second method solve this.

In addition if we use the second method with inserting the second row
we will have this exception:
java.lang.ArrayIndexOutOfBoundsException
at java.lang.System.arraycopy(Native Method)
at
java.lang.AbstractStringBuilder.append(AbstractStringBuilder.java:516)
at java.lang.StringBuffer.append(StringBuffer.java:306)
at org.h2.tools.Csv.unEscape(Csv.java:441)
at org.h2.tools.Csv.readValue(Csv.java:389)
at org.h2.tools.Csv.readRow(Csv.java:458)
at org.h2.tools.SimpleResultSet.next(SimpleResultSet.java:188)
at H2.go(H2.java:30)
at H2.main(H2.java:6)

Which I couldn't find a solution for it.

[Thomas Mueller]

Hi,

Sorry for the delay. I found one problem so far: you have used

CSVREAD('test.csv','NULL','UTF-8','\u00F6','\u00D6')

but that means use the column list 'NULL' (only one column). What you
need to write is:
CSVREAD('test.csv',NULL,'UTF-8','\u00F6','\u00D6')

I can now reproduce the second problem, but it does not occur when
running your test. I have changed the test and now I can reproduce it.
I will fix this problem and write more test cases (including random
data). This problem will be fixed in the next release.

Thanks a lot for your help!
Thomas

Class.forName("org.h2.Driver");
Connection conn =

DriverManager.getConnection("jdbc:h2:mem:", "sa", "sa");

final Statement s = conn.createStatement();

s.execute("DROP TABLE IF EXISTS t1, t2");

s.execute("CREATE TABLE t1(index INTEGER, name VARCHAR(50))");
s.execute("INSERT INTO t1 VALUES(1,'bbbb \n aaaa')");

s.execute("INSERT INTO t1 VALUES(2,'bbbb \" aaaa')");

s.execute("INSERT INTO t1 VALUES(3,'bbbb \\ aaaa')");
s.execute("INSERT INTO t1 VALUES(4,'\n')");
s.execute("INSERT INTO t1 VALUES(5,'\"')");
s.execute("INSERT INTO t1 VALUES(6,'\\')");
s.execute("INSERT INTO t1 VALUES(7,' \n')");
s.execute("INSERT INTO t1 VALUES(8,' \"')");
s.execute("INSERT INTO t1 VALUES(9,' \\')");
s.execute("INSERT INTO t1 VALUES(10,'\n ')");
s.execute("INSERT INTO t1 VALUES(11,'\" ')");
s.execute("INSERT INTO t1 VALUES(12,'\\ ')");

s.execute("CALL CSVWRITE('test.csv', 'SELECT * FROM t1',
'UTF-8', '\u00F6', '\u00D6')");
// First Method

s.execute("create table t2 as SELECT * FROM
CSVREAD('test.csv',NULL,'UTF-8','\u00F6','\u00D6')");
// Second Method

final Csv csv = Csv.getInstance();
csv.setFieldDelimiter('\u00D6');
csv.setFieldSeparatorRead('\u00F6');
ResultSet rs = csv.read("test.csv", null, "UTF-8");

while (rs.next()) {
s.execute("INSERT INTO t2 VALUES(" +
rs.getInt("index") + ",'" + rs.getString("name") + "')");