geometry parameters reported as having java.sql.Types = -157 (and -158 for geography) on SQL Server 2012 and 2014

[jeffersonatsafe]

Driver version or jar name

mssql-jdbc-6.5.0.jre8.jar (preview)

SQL Server version

SQL Server 2012 and SQL Server 2014

Client operating system

Win 10 x64

Java/JVM version

java version "1.8.0_144"

Table schema

CREATE TABLE [jpoetest] ([_creation_instance] varchar(200), [GEOM] geometry)

Problem description

Working with SQL Server 2012 and 2014, when trying to insert to a geometry column, parameter type for geometry columns are returned as -157. (geography columns are -158)

Connection connection = getConnection();
PreparedStatement statement = connection.prepareStatement("INSERT INTO [jpoetest] ([_creation_instance], [GEOM]) VALUES (?, ?)");
ParameterMetaData paramMetaData = statement.getParameterMetaData();
int sqlType = paramMetaData.getParameterType(2);

When talking to SQL Server 2008, paramMetaData.getParameterType() would return java.sql.Types.VARBINARY

I haven't tried SQL Server 2016 and 2017.

I've searched for which sql.Types would -157 map to, but it doesn't seem to be a valid sql.Types value.

Interestingly, SQL Server 2008, 2012, and 2014 all return paramMetaData.getParameterTypeName() correctly. ie. "geometry" or "geography"

Expected behavior and actual behavior

expected: Geometry and Geography parameters should be reported as sql.Types.VARBINARY, consistent with ResultSetMetadata.getColumnType() for these columns.
actual: Geometry parameters are being returned as type -157, and Geography as type -158

Repro code

Connection connection = getConnection();
PreparedStatement statement = connection.prepareStatement("INSERT INTO [jpoetest] ([_creation_instance], [GEOM]) VALUES (?, ?)");
ParameterMetaData paramMetaData = statement.getParameterMetaData();
int sqlType = paramMetaData.getParameterType(2);

[peterbae]

Hi @jpoernomoatsafe, thanks for your investigation. Geometry and Geography are special data types that are supported by Microsoft SQL Server, but not in Java. We have defined these two spatial datatypes in microsoft.sql.Types file and gave them their own unique type code, so the driver can uniquely identify them. When the user calls ParameterMetaData::getParameterType, the driver is correctly returning the type codes for Geometry and Geography (-157 and -158 respectively).

That being said, the driver is currently incorrectly returning VARBYTE when the user calls ResultSetMetaData::getColumnType for spatial datatypes. While the SQL Server is returning Geometry and Geography data represented as VARBYTE (since they're represented as hexadecimal values internally), it should fall under the Geometry and Geography types that the driver has defined. I will create a pull request that fixes this issue soon. Thanks!

[jeffersonatsafe]

I suspected it was somewhat intended when I saw the Type definitions in the source code. Sounds good, Thanks for implementing spatial support!