microsoft/mssql-jdbc

ResultSetMetaData#getTableName(int) failure

rsmckinney opened this issue · 9 comments

rsmckinney commented

Driver version

12.4.1.jre8

SQL Server version

16.0.1000.6

Client Operating System

Windows 10

JAVA/JVM version

1.8

Table schema

CREATE TABLE country (
  country_id INT NOT NULL IDENTITY ,
  country VARCHAR(50) NOT NULL,
)

Problem description

ResultSetMetaData#getTableName(int) always returns "", never returns the table name corresponding with selected column.

Note, I've seen #753, but I don't think the explanation there is sufficient as setting selectMethod=cursor, does not work.

I've also read in very old posts that setting the prepared statement to be scrollable, updatable enables table names, but that doesn't appear to work either.

Code example.

    String sql = "SELECT country_id FROM country";
    try( PreparedStatement ps = c.prepareStatement( sql, TYPE_SCROLL_SENSITIVE, CONCUR_UPDATABLE ) )
    {
      ResultSetMetaData md = ps.getMetaData();
      String tableName = md.getTableName( 1 );  // this returns "" instead of "country"
    }

JDBC url:

jdbc:sqlserver://localhost;database=sakila;integratedSecurity=true;encrypt=true;trustServerCertificate=true;selectMethod=cursor

Expected behavior

getTableName(int) should return the name of a selected column's table, particularly if making that determination is straightforward as in most cases.

Actual behavior

getTableName(int) always returns the empty string.

Any other details that can be helpful

I am building a JDBC-based analysis tool, which performs static analysis of all forms of SQL, including DDL and queries. Due to this issue I've hit a wall with SQL Server in terms of query analysis, other DBs/drivers I've tested with work fine in terms of getTableName() etc. I would appreciate any type of workaround you may have. Thank you.

rsmckinney commented
Jeffery-Wasty commented

Yes I saw that afterwards, thanks. I'm currently not able to replicate this with the same conditions but will continue to look into it.

rsmckinney commented

@Jeffery-Wasty are you able to get the table name from ResultSetMetaData#getTableName(int) using that query?

Jeffery-Wasty commented

Yes, it works for me with the same table, SQL version, driver and java version.

rsmckinney commented

@Jeffery-Wasty Interesting. Did you use a similar JDBC URL? How is your JDBC connection configured? Thanks

Jeffery-Wasty commented

I used the same connection URL you have above, just adding port number, and changing DB.

Jeffery-Wasty commented

Ok, I'm able to replicate this now, but am still looking into this. Will update when I have more.

  • 👍1
Jeffery-Wasty commented

Hi @rsmckinney,

I've been able to revisit this issue after some time away.

In the code you provided the ResultSetMetaData is created from the PreparedStatement, but until the PreparedStatement is executed there is no result set, so no metadata to fetch. Adding a ps.execute() or ps.executeQuery() works to resolve the issue with selectMethod=cursor in the connection string (similar to issue 753).

rsmckinney commented

@Jeffery-Wasty Thanks for looking into this. My use-case involves building a code generator based on JDBC metadata -- the idea is to know the types of things before the query executes. Consequently, executing the query becomes a chicken/egg problem for my use-case.

Most JDBC drivers can retrieve metadata from the prepared statement since most databases compile the query in the course of preparing the statement. I would think SQL Server is no different here. Perhaps the way it works with the JDBC/ODBC layer is limited somehow?

But hold on... there is metadata without having to execute the query. For instance, column types etc. are available. Could this just be an oversight somehow with the driver?

Otherwise, I suppose for SQL Server I'll have to parse the SQL somehow to infer the column/table relationships. The query column's table is vital.

Anyhow, thanks again for looking into this.