PreparedStatement is not able to insert data having dateTime in record.

[DipalPrajapati]

While implementing a job in Java using JDBC api , I am facing issue with PreparedStatement(INSERT)

query = "Insert into db.tablename values (?,parseDateTimeBestEffortOrNull`(?))";

ru.yandex.clickhouse.except.ClickHouseException: ClickHouse exception, code: 27, host: clickhouse-distributed.clickhouse.svc.kubernetes.local, port: 8443; Code: 27, e.displayText() = DB::Exception: Cannot parse input: expected '\n' before: 'ortOrNull(2021-11-10T05:21:53\n': (at row 1)

Row 1: Column 0, name: field , type: FixedString(150), parsed text: "+256300000009" Column 1, name: lastTimeAvailable, type: DateTime, parsed text: "arseDateTimeBestEff" ERROR: garbage after DateTime: "ortOrNull(" ERROR: DateTime must be in YYYY-MM-DD hh:mm:ss or NNNNNNNNNN (unix timestamp, exactly 10 digits) format.`

[zhicwu]

Hi @DipalPrajapati, it seems you're using setObject for the second parameter? Could you use setTimestamp or setString instead? It's a limitation of current implementation, but this has been addressed(see example below) in the upcoming new driver.

try (ClickHouseConnection conn = newConnection(new Properties());
        PreparedStatement stmt = conn.prepareStatement(
                "Insert into test_datetime_insert values (?,parseDateTimeBestEffortOrNull(?))")) {
    conn.createStatement().execute("drop table if exists test_datetime_insert;"
            + "create table test_datetime_insert(s String, d DateTime32)engine=Memory");
    stmt.setString(1, "String");
    stmt.setString(2, "2021-11-11 12:23:34");
    stmt.addBatch();
    stmt.setString(1, "Timestamp");
    stmt.setTimestamp(2, Timestamp.from(Instant.now()));
    stmt.addBatch();
    stmt.setString(1, "LocalDate");
    stmt.setObject(2, LocalDateTime.now());
    stmt.addBatch();

    stmt.executeBatch();
}

[DipalPrajapati]

setString,setTimestamp,setObject any of these is not working and keep on giving the same error. can you try stmt.setString(2, "2021-11-11 12:23"); ?

[DipalPrajapati]

I tried with the latest version <groupId>ru.yandex.clickhouse</groupId> <artifactId>clickhouse-jdbc</artifactId> 0.3.1

ru.yandex.clickhouse.except.ClickHouseException: ClickHouse exception, code: 27, host: clickhouse-distributed.clickhouse.svc.kubernetes.local, port: 8443; Code: 27, e.displayText() = DB::Exception: Cannot parse input: expected '\n' before: '56300000009\t2021-11-10T05:21:53\n': (at row 9)

Row 8: Column 0, name: field, type: FixedString(150), parsed text: "+256300000007" Column 1, name: time, type: DateTime, parsed text: "2021-11-10T13:07:26"

Row 9: Column 0, name: field, type: FixedString(150), parsed text: "+256300000008" Column 1, name: time, type: DateTime, parsed text: "2021-11-10T11:29<LINE FEED>+2" ERROR: garbage after DateTime: "5630000000" ERROR: DateTime must be in YYYY-MM-DD hh:mm:ss or NNNNNNNNNN (unix timestamp, exactly 10 digits) format.

As you can see why does it read <LINE FEED>+2 if SECOND part is not available.

[bill-mao]

I read clickhouse-jdbc source code, send part is as same as sql as follows

INSERT into table tbname
(strCol, dateCol)
FORMAT TabSeparated 
'sd'	'2020-10-01'

when i try with insert values , it works. I guess it is problem of clickhouse.

[bill-mao]

I read clickhouse-jdbc source code, send part is as same as sql as follows

INSERT into table tbname
(strCol, dateCol)
FORMAT TabSeparated 
'sd'	'2020-10-01'

when i try with insert values , it works. I guess it is problem of clickhouse.

Here it is : https://github.com/ClickHouse/ClickHouse/issues/10301

[zhicwu]

In legacy JDBC driver, it uses TabSeparated format, but this has been changed in the new driver(since 0.3.2). It's now RowBinary, and you have two workarounds as I mentioned at here.

[bill-mao]

It really helps, thank you.