Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
lukaseder opened this issue Jun 17, 2024 · 0 comments

Comments

@lukaseder
Copy link

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant