jdbc time, date, timestamp

[Alm Yarnykh]

Hello,

I've got database table like this:

CREATE TABLE Device

(

DeviceID             BIGINT NOT NULL,

deviceDate           DATE NULL,

updatedTimestamp     TIMESTAMP NULL,

deviceMode           INTEGER NULL,

timer1Start          TIME NULL,

timer1Stop           TIME NULL,

flowRate             INTEGER NULL,

temperature          FLOAT NULL

)

And java code like this:

JDBCClient client = JDBCClient.createShared(Vertx.vertx(), new JsonObject()

                .put("url", "jdbc:hsqldb:mem:test?shutdown=true")

                .put("driver_class", "org.hsqldb.jdbcDriver"));

    client.getConnection(res -> {

        if (res.succeeded()) {

            connection = new AtomicReference<>(res.result());

            String query = "INSERT INTO Device (deviceId, deviceDate, updatedTimestamp, deviceMode, timer1Start, timer1Stop, flowRate, temperature) VALUES (?,?,?,?,?,?,?,?);";

   Instant date = Instant.now();

JsonArray params = new JsonArray().add(549651730122213L).add(date).add(date).add(1).add(date).add(date).add(1).add(1);

connection.updateWithParams(query, params, res -> {

       if (res.succeeded()) {

        // ...

       }

   }

    }

    java.sql.SQLDataException: data exception: invalid datetime format

    How can I pass parameters for TIME, DATE or TIMESTAMP database fields?

[Paulo Lopes]

Due to the granularity of JSON types you don't have control at that level, however you can always work around it by enforcing a cast in your insert statement

[Alm Yarnykh]

Thank you, Paulo!

I've constructed my update without json parameters into one long query string fiiled with date and time as strings, and it's working now.

And also I've replaced "Timestamps" to "CURRENT_TIMESTAMP".

вторник, 16 февраля 2016 г., 11:06:19 UTC+3 пользователь Paulo Lopes написал: