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.

Sign up for GitHub

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

Jump to bottom

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

Closed
lukaseder opened this issue Jun 17, 2024 · 0 comments · Fixed by #168
Closed

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

lukaseder opened this issue Jun 17, 2024 · 0 comments · Fixed by #168

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.
@lukaseder lukaseder mentioned this issue Jun 17, 2024
Closed
60 tasks
staticlibs added a commit to staticlibs/duckdb-java that referenced this issue Mar 20, 2025
@staticlibs
Support spatial extension types
aa9515a 
This change adds support for the following types from the spatial
extension:

 - `POINT_2D`: as `STRUCT{x: DOUBLE, y: DOUBLE}`
 - `POINT_3D`: as `STRUCT{x: DOUBLE, y: DOUBLE, z: DOUBLE}`
 - `POINT_4D`: as `STRUCT{x: DOUBLE, y: DOUBLE, z: DOUBLE, m: DOUBLE}`
 - `LINESTRING_2D`: as `LIST[STRUCT{x: DOUBLE, y: DOUBLE}]`
 - `POLYGON_2D`: as `LIST[LIST[STRUCT{x: DOUBLE, y: DOUBLE}]]`
 - `BOX_2D`: as `STRUCT{min_x: DOUBLE, max_x: DOUBLE, min_y: DOUBLE, max_y: DOUBLE}`
 - `BOX_2DF`: as `STRUCT{min_x: FLOAT, max_x: FLOAT, min_y: FLOAT, max_y: FLOAT}`
 - `GEOMETRY`: as `BLOB`
 - `WKB_BLOB`: as `BLOB`

 No new Java APIs are added for these types. Instead `java.sql.Array`,
 `java.sql.Struct` and `java.sql.Blob` need to be used to read them
 from a result set or to pass them as bind parameters.

Testing: new test added to cover all spatial types in bind parameters
and in result set.

Fixes: duckdb#37
This was referenced Mar 20, 2025
Merged
Closed
staticlibs added a commit to staticlibs/duckdb-java that referenced this issue Mar 20, 2025
@staticlibs
Support spatial extension types
29b7d6e 
This change adds support for the following types from the spatial
extension:

 - `POINT_2D`: as `STRUCT{x: DOUBLE, y: DOUBLE}`
 - `POINT_3D`: as `STRUCT{x: DOUBLE, y: DOUBLE, z: DOUBLE}`
 - `POINT_4D`: as `STRUCT{x: DOUBLE, y: DOUBLE, z: DOUBLE, m: DOUBLE}`
 - `LINESTRING_2D`: as `LIST[STRUCT{x: DOUBLE, y: DOUBLE}]`
 - `POLYGON_2D`: as `LIST[LIST[STRUCT{x: DOUBLE, y: DOUBLE}]]`
 - `BOX_2D`: as `STRUCT{min_x: DOUBLE, max_x: DOUBLE, min_y: DOUBLE, max_y: DOUBLE}`
 - `BOX_2DF`: as `STRUCT{min_x: FLOAT, max_x: FLOAT, min_y: FLOAT, max_y: FLOAT}`
 - `GEOMETRY`: as `BLOB`
 - `WKB_BLOB`: as `BLOB`

 No new Java APIs are added for these types. Instead `java.sql.Array`,
 `java.sql.Struct` and `java.sql.Blob` need to be used to read them
 from a result set or to pass them as bind parameters.

Testing: new test added to cover all spatial types in bind parameters
and in result set.

Fixes: duckdb#37
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Support spatial extension types staticlibs/duckdb-java
1 participant
@lukaseder