Standard way to use ODBC or JDBC to get column metadata of Select statement?

Question

Is there a cross-vendor-compatible way to get the column metadata for an arbitrary SELECT statement in ODBC or JDBC without needing to execute the query?

I am building an analytics tool as a vendor. I cannot assume that the user can create temp tables. Would throwing a TOP or LIMIT on the statement work? Would that guarantee quick return time?

I'm also open to Apache Calcite or other SQL parsing libraries. I don't know if any of these libraries are actually aware of the argument and return types of functions.

I suspect the answer is "no" but which vendors would be easier to support upfront?

Mark Rotteveel · Answer 1 · 2019-08-07T13:34:18.093

In JDBC, you can prepare the statement, and then retrieve the ResultSetMetaData using PreparedStatement.getMetaData():

Connection connection = ...;
try (PreparedStatement pstmt = connection.prepareStatement("your statement")) {
    ResultSetMetaData rsmd = pstmt.getMetaData();
    int columnCount = rsmd.getColumnCount()

    // java.sql.Types type of first column
    int columnType = rsmd.getColumnType(1);
}

However, this method is optional (drivers are allowed to not implement it and - according to the javadoc - it may be expensive in some implementations), so this isn't guaranteed to work with all JDBC drivers.

See the javadoc for more details.

I don't know ODBC in detail, but as far as I know it has similar features.

@GordThompson Good point, I didn't know this method could throw `SQLFeatureNotSupportedException`, because it was introduced before `SQLFeatureNotSupportedException` existed (Java 1.2 vs Java 6). I'll try and rephrase my answer a bit (probably tomorrow). — Mark Rotteveel, Aug 06 '19 at 17:29

score 2 · Accepted Answer · answered Aug 06 '19 at 18:32

Adding on to @Mark's JDBC answer with insights from the ODBC side:

ODBC does have a low-level mechanism named SQLDescribeCol for retrieving column information (when possible). However, it is something that is normally used by those building ODBC interface layers (e.g., pyodbc) or others writing directly to the ODBC API from a C/C++ application.

I don't recall ever seeing an ODBC interface layer that implements something similar to JDBC's PreparedStatement#getMetaData. For example, System.Data.Odbc.OdbcCommand in .NET has an explit .Prepare() method but it does not have a method for retrieving the result set's metadata; you need an OdbcDataReader object for that and you get one by calling .ExecuteReader().

So AFAIK the most "cross-vendor-compatible" way of getting the result set metadata is to wrap the actual query in an outer query that returns no rows, e.g. for ODBC in C# ...

string actual_query = "SELECT many_columns FROM many_tables WHERE many_conditions";
string metadata_query = $"SELECT * FROM ({actual_query}) AS subquery WHERE 1=0";
var cmd = new OdbcCommand(metadata_query, conn);
OdbcDataReader rdr = cmd.ExecuteReader();
DataTable dt = rdr.GetSchemaTable();

... and hope the query optimizer is clever enough to know that it doesn't have to process the "many_conditions" from the subquery because the outer query will never return any rows.

Standard way to use ODBC or JDBC to get column metadata of Select statement?

2 Answers2