Doc issue? What is the proper way of using JDBC PreparedStatement with STRUCT and/or MAP?

[wernerdaehn]

I have looked into the code and the DuckDBConnection class has methods like createMap() and createStruct(). The DuckDBPreparedStatement.setObject() does not reveal anything either.

This is what I ended up with for Map:

CREATE TABLE "commit" ("commit_id" VARCHAR, "producer_name" VARCHAR, "topic_name" VARCHAR,
  "commit_epoch_ns" BIGINT, "record_count" INTEGER, "max_offsets" MAP(VARCHAR, BIGINT));

insertstmt.setObject(fieldpos, conn.createMap("MAP(VARCHAR,BIGINT)", new HashMap<>()));

Would be good to add examples to the JDBC client documentation.

[staticlibs]

@wernerdaehn

Thanks for the report! Yes, I agree that documentation about createMap() (and other DuckDB-specific extensions like createAppender()) is lacking. The intention about such extensions is to cover them with JavaDoc and publish it to be used in addition to generic JDBC JavaDoc, but this is not yet done.

Full example with MAP insert/fetch just if someone stumble upon the same topic:

try (DuckDBConnection conn = DriverManager.getConnection("jdbc:duckdb:").unwrap(DuckDBConnection.class)) {
    try (Statement stmt = conn.createStatement()) {
        stmt.execute("CREATE TABLE tab1 (col1 int, col2 MAP(INT, VARCHAR))");
    }

    try (PreparedStatement ps = conn.prepareStatement("INSERT INTO tab1 VALUES (?, ?)")) {
        Map<Integer, String> map = new HashMap<Integer, String>();
        map.put(41, "foo");
        map.put(42, "bar");

        Map<Integer, String> mapParam = conn.createMap("MAP(INT, VARCHAR)", map);
        ps.setObject(1, 1);
        ps.setObject(2, mapParam);
        ps.execute();
    }

    try (Statement stmt = conn.createStatement();
         ResultSet rs = stmt.executeQuery("SELECT * FROM tab1")) {
        while (rs.next()) {
            System.out.println(rs.getInt(1));
            Map<Integer, String> mapFetched = (Map) rs.getObject(2);
            System.out.println(mapFetched);
        }
    }
}

It is easier with createStruct and createArrayOf because these ones are generic JDBC methods. Example of their usage can be seen in spatial extension tests.

PS: Appender improvements are planned, it is the next major task in JDBC, but it is not making it into upcoming 1.3 release.