MSSQL Geometry/Geography types with JDBC fails with class [B?

[sproket]

Driver version

9.4.0.jre16

SQL Server version

Microsoft SQL Server 2019 (RTM-GDR) (KB4583458) - 15.0.2080.9 (X64) Nov 6 2020 16:50:01 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit)

Client Operating System

Windows 10 Pro 10.0 (Build 19041: )

JAVA/JVM version

openjdk version "17.0.1" 2021-10-19
OpenJDK Runtime Environment (build 17.0.1+12-39)
OpenJDK 64-Bit Server VM (build 17.0.1+12-39, mixed mode, sharing)

Table schema

Cities table from WideWorldImporters sample database

Problem description

See:
https://stackoverflow.com/questions/70213493/mssql-geometry-geography-types-with-jdbc-fails-with-class-b

1. Expected behaviour:

I expect resultSet.getObject and statement.setObject to work with geometry types.

2. Actual behaviour:

class [B cannot be cast to class com.microsoft.sqlserver.jdbc.Geometry ([B is in module java.base of loader 'bootstrap'; com.microsoft.sqlserver.jdbc.Geometry is in unnamed module of loader 'app')

3. Error message/stack trace:

see 2.

4. Any other details that can be helpful:

I used the sample from here:

https://docs.microsoft.com/en-us/sql/connect/jdbc/spatial-data-types-sample?view=sql-server-ver15

And modified it slightly to use Statement and ResultSet instead of SQLServerPreparedStatement and SQLServerResultSet
and used setObject and getObject instead of setGeometry and getGeometry.

JDBC trace logs

N/A

Reproduction code

See the sample link

[lilgreenbird]

hi @sproket

thank you we'll investigate and get back to you.

[mprins]

please read the contract on PreparedStatement
notably:

If arbitrary parameter type conversions are required, the method setObject should be used with a target SQL type.

and void setObject(int parameterIndex, Object x)

Sets the value of the designated parameter using the given object.

and use void setObject(int parameterIndex, Object x, int targetSqlType)

Sets the value of the designated parameter with the given object.

[sproket]

@mprins That's what I'm doing. I read the value with resultset getObject and may later want to cast set it on a statement with setObject.

The issue seems to be something with the module system where the driver is not seeing the type but instead just an arbitrary byte array.

I faced a similar issue playing with MS-Access which has a non SQL standard "attachments" type. The UCanAccess driver works fine this way. I can get it out as an object and see the class name and then cast it and manipulate it, then send it back with setObject.

http://ucanaccess.sourceforge.net/site.html

[sproket]

I see I could use Geometry.STGeomFromWKB to manually convert. OK - it might be better to do the converts for me but as long as I can do it I guess though I won't be able to support these with my library then.

[VeryVerySpicy]

Hi @sproket,
Currently retrieving Spatial Datatypes with getObject(int) is not supported as the returned type from the server is simply Varbinary and not Geometry.
So getGeometry must be used in this case.

[sproket]

How would I re-serialize it to pass a change back with parameters?

[VeryVerySpicy]

I'm not sure I fully understand. If you make a change to the geometry object data and then want to send that change, you would use setGeometry.
Replicating this behavior with get/setObject is not available right now, though we could add it as an enhancement down the road.

[sproket]

I don't want to have to use the microsoft concrete classes directly. JDBC is there for a reason - so we can code to the interface.

[VeryVerySpicy]

I agree it would be good to be able to use get/setObject on Spatial Datatypes and we will add this as an enhancement down the road.