Skip to content

Add support for ResultSet.getBlob() calls when working with WKB_BLOB types #37

Closed
@lukaseder

Description

@lukaseder

Run this program:

try (Statement s = connection.createStatement()) {
    s.executeUpdate("install spatial");
    s.executeUpdate("load spatial");
}

try (Statement s = connection.createStatement();
    ResultSet rs = s.executeQuery("select st_aswkb(cast('point (1 2)' as geometry))")
) {
    while (rs.next()) {
        Blob b = null;

        try {
            b = rs.getBlob(1);
            System.out.println(Arrays.toString(b.getBytes(1, (int) b.length())));
        }
        finally {
            if (b != null)
                b.free();
        }
    }
}

It errors as follows:

0
getBlob
null
java.sql.SQLFeatureNotSupportedException: getBlob
	at org.duckdb.DuckDBVector.getBlob(DuckDBVector.java:284)
	at org.duckdb.DuckDBResultSet.getBlob(DuckDBResultSet.java:514)
	at org.jooq.testscripts.JDBC.main(JDBC.java:48)

This is because the WKB_BLOB type isn't supported by org.duckdb.DuckDBColumnType, and thus org.duckdb.DuckDBResultSetMetaData.TypeNameToType(String) can't look up the type name.

A workaround is to cast as ordinary BLOB or use a different function, such as st_ashexwkb:

try (Statement s = connection.createStatement()) {
    s.executeUpdate("install spatial");
    s.executeUpdate("load spatial");
}

try (Statement s = connection.createStatement();
    ResultSet rs = s.executeQuery("select cast(st_aswkb(cast('point (1 2)' as geometry)) as blob)")
) {
    while (rs.next()) {
        Blob b = null;

        try {
            b = rs.getBlob(1);
            System.out.println(Arrays.toString(b.getBytes(1, (int) b.length())));
        }
        finally {
            if (b != null)
                b.free();
        }
    }
}

This prints:

[1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, -16, 63, 0, 0, 0, 0, 0, 0, 0, 64]

According to the docs:
https://duckdb.org/docs/extensions/spatial

"with the type WKB_BLOB (which can be cast to BLOB) instead of GEOMETRY"

This topic isn't documented very thoroughly, but I think it's reasonable to read between the lines that WKB_BLOB is safe to be treated as an ordinary BLOB when reading the value.

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions