Support REMARKS for columns and tables

[sualeh]

Driver version or jar name

Microsoft JDBC Driver 6.4 for SQL Server 6.4.0.0

SQL Server version

Microsoft SQL Server 14.00.3015

Client operating system

Windows 10

Java/JVM version

Java(TM) SE Runtime Environment (build 1.8.0_121-b13)
Java HotSpot(TM) 64-Bit Server VM (build 25.121-b13, mixed mode)

Table schema

CREATE TABLE Publishers
(
  Id INTEGER NOT NULL IDENTITY,
  Publisher VARCHAR(255),
  PRIMARY KEY (Id)
);
EXECUTE SP_ADDEXTENDEDPROPERTY 'MS_Description', 
   'List of book publishers',
   'user', 'dbo', 'table', 'Publishers'
;
EXECUTE SP_ADDEXTENDEDPROPERTY 'MS_Description', 
   'Unique (internal) id for book publisher',
   'user', 'dbo', 'table', 'Publishers', 'column', 'Id'
;
EXECUTE SP_ADDEXTENDEDPROPERTY 'MS_Description', 
   'Name of book publisher',
   'user', 'dbo', 'table', 'Publishers', 'column', 'Publisher'
;

Problem description

DatabaseMetaData.getTables(...) and DatabaseMetaData.getColumns(...) does not return table and column descriptions in the REMARKS column. The REMARKS column should have the description from the extended properties.

Expected behavior and actual behavior

See above.

Repro code

Call DatabaseMetaData.getTables(...) and DatabaseMetaData.getColumns(...) .

[cheenamalhotra]

Hi @sualeh

This is definitely a missing implementation in the driver, we'll work on it and fix it in coming releases.

[ulvii]

Hi @sualeh ,

The driver calls sp_columns and sp_tables for getColumns()/getTables() accordingly. Both of these procedures return null in REMARKS field. The driver behavior is also documented here and here.

I also investigated if we can modify the driver to call something like select * from fn_listextendedproperty on top of sp_columns/sp_tables, but this does not seem like a right idea because there might be multiple extended properties for a column/table.

We decided that REMARKS field is not ideal for retrieving extended properties and suggest calling select * from fn_listextendedproperty as a query from your application instead. Please let me know know if you have any questions.

[sualeh]

The point is not that remarks can be obtained in some other way, but that the Microsoft JDBC driver is not compatible with the JDBC specifications. Here is an idea - create a new driver property for specifying the name of the extended property that should be retrieved for remarks. Then, select just that property, and return in the REMARKS field.

[cheenamalhotra]

HI @sualeh

"Extended properties" are meant to store Key-Value pairs for anything that can be useful for maintaining details about table/column. Adding additional property to the driver for reading these extended properties and finding out description does not sound a robust solution, as:

1. It adds dependency for clients to configure connection property everytime they're reading a description from "Extended properties" for a Table and then for a column - which mean 2 additional properties - leads to reconnect if different tables have different KEY for descriptions. Not all customers will always have same "KEY" for description they want to read.
2. From driver's perspective, if we are fetching 1 property for REMARKS, what about the other extended properties defined, there is no API that could read them - that becomes another issue.

In our point of view, these properties should be treated as "Extended properties" only, not to be mixed with Remarks and must be fetched as Key-value Pair array by calling fn_listextendedproperty in client applications.

Remarks on the other hand, is always returned NULL from SQL Server, and all SQL Server drivers follow same rule, including jTDS Driver, hence JDBC Driver should abide to that as well.

[diederik123]

Hello @cheenamalhotra
The current status of this point is not clear for me. I do not understand your comment from the 8th of May in combination with the changed statuses. Do you want to explain why this issue is closed? Thank you.