CODEWITHSUNDEEP
javasql-serverjdbc
Peter
Peter

Reputation: 1260

SQLServer - Determine whether a column is geometry or geography

Is there a way to determine whether a column is "geometry" or "geography" in a Microsoft SQLServer database?

Ideally, I would like to do this programatically using Java/JDBC. I've tried using the java.sql.ResultSetMetaData but I get an incoherent response.

String className = rsmd.getColumnClassName(colID));
System.out.println(className);

Outputs:

"[B"

If there's no way to do it via JDBC/ResultSetMetaData, is there an SQL query I can use instead?

Upvotes: 0

Views: 719

Answers (3)

Peter
Peter

Reputation: 1260

Unfortunately, it is not possible to get this info via the JDBC ResultSetMetaData class. At least not with the latest JDBC driver from Microsoft (sqljdbc_4.0.2206).

The only way to get this information currently is via a query. Example:

String sql = "SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS " +
"WHERE TABLE_NAME='" + tableName + "' AND COLUMN_NAME='" + columnName + "'";

I arrived at this conclusion by decompiling the SQLServerResultSetMetaData.class. The source for the getColumnClassName() method calls this:

this.rs.getColumn(paramInt).getTypeInfo().getSSType().getJDBCType().className();

The key method in this chain is getSSType() which returns an SSType. The SSType class contains mappings between data types in the database and JDBC data types. There is no mapping for "geometry" or "geography". IMHO this is a bug. Here's the source for SSType:

package com.microsoft.sqlserver.jdbc;

enum SSType
{
UNKNOWN(SSType.Category.UNKNOWN, "unknown", JDBCType.UNKNOWN), 
TINYINT(SSType.Category.NUMERIC, "tinyint", JDBCType.TINYINT), 
BIT(SSType.Category.NUMERIC, "bit", JDBCType.BIT),
SMALLINT(SSType.Category.NUMERIC, "smallint", JDBCType.SMALLINT),
INTEGER(SSType.Category.NUMERIC, "int", JDBCType.INTEGER), 
BIGINT(SSType.Category.NUMERIC, "bigint", JDBCType.BIGINT), 
FLOAT(SSType.Category.NUMERIC, "float", JDBCType.DOUBLE), 
REAL(SSType.Category.NUMERIC, "real", JDBCType.REAL), 
SMALLDATETIME(SSType.Category.DATETIME, "smalldatetime", JDBCType.TIMESTAMP),
DATETIME(SSType.Category.DATETIME, "datetime", JDBCType.TIMESTAMP), 
DATE(SSType.Category.DATE, "date", JDBCType.DATE), 
TIME(SSType.Category.TIME, "time", JDBCType.TIME), 
DATETIME2(SSType.Category.DATETIME2, "datetime2", JDBCType.TIMESTAMP), 
DATETIMEOFFSET(SSType.Category.DATETIMEOFFSET, "datetimeoffset", JDBCType.DATETIMEOFFSET), 
SMALLMONEY(SSType.Category.NUMERIC, "smallmoney", JDBCType.DECIMAL), 
MONEY(SSType.Category.NUMERIC, "money", JDBCType.DECIMAL), 
CHAR(SSType.Category.CHARACTER, "char", JDBCType.CHAR), 
VARCHAR(SSType.Category.CHARACTER, "varchar", JDBCType.VARCHAR), 
VARCHARMAX(SSType.Category.LONG_CHARACTER, "varchar", JDBCType.LONGVARCHAR), 
TEXT(SSType.Category.LONG_CHARACTER, "text", JDBCType.LONGVARCHAR), 
NCHAR(SSType.Category.NCHARACTER, "nchar", JDBCType.NCHAR), 
NVARCHAR(SSType.Category.NCHARACTER, "nvarchar", JDBCType.NVARCHAR), 
NVARCHARMAX(SSType.Category.LONG_NCHARACTER, "nvarchar", JDBCType.LONGNVARCHAR), 
NTEXT(SSType.Category.LONG_NCHARACTER, "ntext", JDBCType.LONGNVARCHAR), 
BINARY(SSType.Category.BINARY, "binary", JDBCType.BINARY), 
VARBINARY(SSType.Category.BINARY, "varbinary", JDBCType.VARBINARY), 
VARBINARYMAX(SSType.Category.LONG_BINARY, "varbinary", JDBCType.LONGVARBINARY), 
IMAGE(SSType.Category.LONG_BINARY, "image", JDBCType.LONGVARBINARY), 
DECIMAL(SSType.Category.NUMERIC, "decimal", JDBCType.DECIMAL), 
NUMERIC(SSType.Category.NUMERIC, "numeric", JDBCType.NUMERIC), 
GUID(SSType.Category.GUID, "uniqueidentifier", JDBCType.CHAR), 
SQL_VARIANT(SSType.Category.VARIANT, "sql_variant", JDBCType.VARCHAR), 
UDT(SSType.Category.UDT, "udt", JDBCType.VARBINARY), 
XML(SSType.Category.XML, "xml", JDBCType.LONGNVARCHAR), 
TIMESTAMP(SSType.Category.TIMESTAMP, "timestamp", JDBCType.BINARY);

final SSType.Category category;
private final String name;
private final JDBCType jdbcType;

private SSType(SSType.Category paramCategory, String paramString, JDBCType paramJDBCType)
{
this.category = paramCategory;
this.name = paramString;
this.jdbcType = paramJDBCType;
}

public String toString()
{
return this.name;
}

final JDBCType getJDBCType()
{
return this.jdbcType;
}

boolean convertsTo(JDBCType paramJDBCType)
{
return SSType.GetterConversion.converts(this, paramJDBCType);
}
}

Upvotes: 2

asantaballa
asantaballa

Reputation: 4048

You can get info for all columns from a special system table called INFORMATION_SCHEMA.COLUMNS. Can add where statement for just the table (or columns) you want. Accessible like any other table.

SELECT TOP 1000 * FROM INFORMATION_SCHEMA.COLUMNS

Upvotes: 1

Ben Thul
Ben Thul

Reputation: 32697

Take a look at sys.columns. Join it with sys.types on system_type_id.

Upvotes: 0

Related Questions