DatabaseMetaData.getColumns raises "TYPE 'table' is not supported" with serverless Azure Synapse OPENROWSET views

[oscarpg]

Driver version

Error tested in JRE8 drivers 7.4.1, 8.4.1 and 9.2.0. It works on 7.2.2.jre8 driver.

SQL Server version

Microsoft SQL Azure (RTM) - 12.0.2000.8 Feb 12 2021 10:14:20 Copyright (C) 2019 Microsoft Corporation

Client Operating System

Tested in Windows 10 but I do know that it fails at Linux (amd64) as well.

JAVA/JVM version

1.8.181

Table schema

CREATE VIEW OPENROWSETTABLE AS
SELECT
*
FROM OPENROWSET(
BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.csv',
FORMAT = 'CSV',
PARSER_VERSION = '2.0')
WITH (
[date_rep] VARCHAR(100),
[day] VARCHAR(100),
[month] VARCHAR(100),
[year] VARCHAR(100),
[cases] VARCHAR(100),
[deaths] VARCHAR(100),
[countries_and_territories] VARCHAR(100),
[geo_id] VARCHAR(100),
[country_territory_code] VARCHAR(100),
[pop_data_2018] VARCHAR(100),
[continent_exp] VARCHAR(100),
[load_date] VARCHAR(100),
[iso_country] VARCHAR(100),
[daterep] VARCHAR(100)
) as [r];

Problem description

Create a connection to Azure Synapse and execute the following Java code:
connection.getMetaData().getColumns('<catalog>', '<schema>', '<table_name'>, null)

1. Expected behaviour:
   Retrieve the table columns

2. Actual behaviour:
   com.microsoft.sqlserver.jdbc.SQLServerException: TYPE 'table' is not supported.

3. Error message/stack trace:
   com.microsoft.sqlserver.jdbc.SQLServerException: TYPE 'table' is not supported.
   at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262) ~[?:?]
   at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632) ~[?:?]
   at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:602) ~[?:?]
   at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:524) ~[?:?]
   at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7375) ~[?:?]
   at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3206) ~[?:?]
   at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247) ~[?:?]
   at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222) ~[?:?]
   at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:446) ~[?:?]
   at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getColumns(SQLServerDatabaseMetaData.java:653) ~[?:?]

4. Any other details that can be helpful:

JDBC trace logs

Reproduction code

`
public class TestGetColumns {

private static final String uri = "jdbc:sqlserver://host:1433;database=databaseName;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.sql.azuresynapse.net;loginTimeout=30;";
private static final String login = "login";
private static final String password = "password";

private static final String CATALOG = "database";
private static final String SCHEMA = "dbo";
private static final String VIEW_NAME = "OPENROWSETTABLE";

public static void main(String[] args) throws ClassNotFoundException, SQLException {

    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    Connection con = DriverManager.getConnection(uri, login, password);

    ResultSet rs = con.getMetaData().getColumns(CATALOG, SCHEMA, VIEW_NAME, null);

    while (rs.next()) {
        rs.getString(0);
    }
}

}

`

[peterbae]

Hi @oscarpg, I'd like to test this and see if this is reproducible, but the OPENROWSET syntax you've provided (and the ones I'm testing online) all return to tell me that the syntax is incorrect. Could you double-check if your OPENROWSET syntax you've provided doesn't throw an error for you, and I can look into this? Thanks.

[oscarpg]

Hello @peterbae. The syntax error you are getting probably is because you are executing the query in a dedicated SQL Pool, which does not support OPENROWSET(BULK commands. Try to execute it in the serverless one (Built-in SQL Pool).

The JDBC connection must be to azure-synapse-instance-ondemand.sql.azuresynapse.net

[peterbae]

I see. The driver doesn't officially support the serverless Azure Synapse server for now. Also, looking at the error message, this is probably an error that's coming from the server's end / a limitation on the server, and not much to be done from the driver's end. To confirm this, you could test the same functionality on a different driver (such as SqlClient driver) and see if it throws you the same error.

[oscarpg]

Hi @peterbae .
At the first time I also thought it was an error raised by the Azure server, but the getColumns method is working with no issues on driver version mssql-jdbc 7.2.2.jre8 (which has been used as workaround). Regards

[peterbae]

The team will look into this issue once we can setup a serverless Azure Synapse server.