ODBC 4.0 Principal Architect Michael Pizzo (Microsoft Data Group) gave an overview of what’s new in version 4.0, what problems does it solve and how the ISVs and end users will benefit from its adoption.
Questions left out on the presentation
How has authentication been extended for ODBC 4
ODBC 4 defines new connection string keywords that can be used to specify things like auth type, scope and client key. The application either allows SQLDriverConnect to pop up dialogs/browser windows to collect authentication information or calls SQLBrowseConnect if it wants to control the UI. For an OAuth flow, the driver requests certain information, including a redirect URL, through a parameterized output connection string. The application supplies the information and the driver builds a URL with the required information. The application invokes the URL and provides the callback URL to the driver which then parses out required information like authentication and refresh tokens.
There are also new connection attributes for obtaining, setting, and refreshing credentials of an existing connection.
For more information, see the latest ODBC 4.0 Specification.
Is the ODBC standard the correct place to propose new SQL extensions?
Will there be an effort to correct/clarify the existing odbc spec (especially the documentation)
Yes. Part of our goal with ODBC 4 is to improve interoperability by clarifying and enhancing the existing documentation. If you have particular areas of confusion, errors, or feedback on the current documentation, please open an issue in the ODBC Specification GitHub repo. Note that all submissions to the GitHub repo are covered by the contribution agreement.
Are there any plans to extend ODBC tracing/logging? For example, add hooks to have the DM or driver write to the application’s logs
is there key - value syntax for ROW eg Row (x=1, y='2')
The syntax row constructor syntax is taken from ANSI/ISO SQL 2015 which does not specify a key-value syntax (the name of each field is implementation-dependent). Where multiple implementations support an extension over ANSI/ISO we typically define an escape clause. Or, if the extended syntax is the same across implementations we may just define a SQLGetInfo infoType for returning whether or not that syntax is supported. If you have a particular need for this syntax, please open an issue in the ODBC Specification GitHub repo. Note that all submissions to the GitHub repo are covered by the contribution agreement.
This appears to be database vendors driving the requirements. Are there data consumers involved as well.
Yes. The we are leveraging the driver vendors’ expertise in the breadth of features, behaviors, and semantics across a variety of data sources, and consumers are helping define how they want to interact with these features.
Are these syntax examples (for querying collection-valued columns) being added to the SQL specification?
Most of the new syntax examples are taken directly from ANSI/ISO SQL 2015. ANSI/ISO started adding support for structured- and array-valued columns as early as SQL99, but there is limited support for them in the SQL/Call Level Interface. ODBC 4 builds on SQL and the SQL/CLI whenever possible. Where there are syntax extensions that are not part of ANSI/ISO, we generally represent them as ODBC escape clauses.
If you have a SQL_VARIANT_TYPE column, how does the application know how to bind that? Or can your application only retrieve SQL_VARIANT_TYPE data via SQLGetData (i.e. no pre-bound buffers)?
ODBC 4 introduces a new data-at-fetch mechanism that allows the driver to interrupt a fetch operation to report a column that requires in-line handling. The application handles the column and then continues the fetch operation. For variant columns, the application can bind as string or can specify that they want to handle the column at fetch time using SQLGetData. The application can also bind as an expected type and handle exceptions using the same data-at-fetch mechanism.