CSVREAD can't be used with parameter

[katzyn]

conn.prepareStatement("SELECT * FROM CSVREAD(?)");

Exception in thread "main" org.h2.jdbc.JdbcSQLDataException: Parameter "fileName" is not set; SQL statement:
SELECT * FROM CSVREAD(?) [90012-200]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:602)
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:435)
	at org.h2.message.DbException.get(DbException.java:200)
	at org.h2.message.DbException.get(DbException.java:176)
	at org.h2.expression.function.Function.getValueForColumnList(Function.java:2757)
	at org.h2.table.FunctionTable.<init>(FunctionTable.java:44)
	at org.h2.command.Parser.readTableFunction(Parser.java:2190)
	at org.h2.command.Parser.readTableFilter(Parser.java:2061)
	at org.h2.command.Parser.parseSelectFromPart(Parser.java:2951)
	at org.h2.command.Parser.parseSelect(Parser.java:3077)
	at org.h2.command.Parser.parseQuerySub(Parser.java:2941)
	at org.h2.command.Parser.parseSelectUnion(Parser.java:2776)
	at org.h2.command.Parser.parseQuery(Parser.java:2746)
	at org.h2.command.Parser.parsePrepared(Parser.java:996)
	at org.h2.command.Parser.parse(Parser.java:971)
	at org.h2.command.Parser.parse(Parser.java:943)
	at org.h2.command.Parser.prepareCommand(Parser.java:871)
	at org.h2.engine.Session.prepareLocal(Session.java:634)
	at org.h2.engine.Session.prepareCommand(Session.java:572)
	at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1186)
	at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:74)
	at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:303)

[dadinn]

It is not obvious how to parse a CSV file in java, and ensure that the values are recognised/converted to the same types as H2 would do.

This feature would be necessary to ensure that a CSV file is parsed exactly the same way as H2 does internally.

[katzyn]

CSVREAD produces a table where all columns have CHARACTER VARYING data type, there is no data type detection inside this function.

[dadinn]

Sorry, my misunderstanding.

So is it the CREATE TABLE statement which casts/parses these chars, and CSVREAD does not do any parsing at all?

[katzyn]

Yes, CSVREAD returns only regular character strings. For example, if you have a CVS file with the following lines

A,B,C
1,"AAA",2.5
2,"BBB",3.7

command

CREATE TABLE TEST AS SELECT * FROM CSVREAD('1.csv');

will create a table with the following definition and content:

CREATE TABLE "PUBLIC"."TEST"(
    "A" CHARACTER VARYING,
    "B" CHARACTER VARYING,
    "C" CHARACTER VARYING
);
INSERT INTO "PUBLIC"."TEST" VALUES
('1', 'AAA', '2.5'),
('2', 'BBB', '3.7');

Alternatively you can create a table with column types you need and insert data into it:

CREATE TABLE TEST(A BIGINT, B VARCHAR(255), C NUMERIC(5, 1));
INSERT INTO TEST SELECT * FROM CSVREAD('1.csv');

In this case H2 will perform conversions to these data types in the INSERT command with regular data type conversion rules for implicit assignments. These rules are similar to cast specification (CAST(something AS dataType)) with some minor differences.

H2 also allows non-standard table definitions with fully declared columns and table subquery:

CREATE TABLE TEST(A BIGINT, B VARCHAR(255), C NUMERIC(5, 1)) AS (SELECT * FROM CSVREAD('1.csv'));

This definition also performs conversion in the same way as INSERT.