When selecting the ADO.Net data provider for ODBC with the scheme odbc://, you still need to specify the name of the driver and the SQL dialect to use. When use a classical connection-string, the driver is specified in a token and you must specify the fullname. This point is tricky as it’s usually difficult to remember the exact name of the driver. It’s also painful to have to manually change all your connection-strings because a new version of the driver has been released and installed on your client.

DubUrl inspects the Windows registry or the standard odbcinst.ini locations on Linux/macOS to discover installed ODBC driver names. To select the right driver, DubUrl use the second part of the scheme to determine to which database you’re trying to connect. If you want to connect to a Microsoft SQL Server database by the means of ODBC, you can specify the scheme odbc+mssql://. Under the hood, DubUrl is analyzing your registry to identify all the potentials drivers and is extracting all the drivers where the name is corresponding to the pattern described in the 3rd column of the table bellow for the row corresponding to the second part of the scheme used.

List of ODBC drivers

Database Aliases Driver name pattern        
Microsoft SQL Server mssql, ms, sqlserver, mssqlserver ^\bODBC Driver\s([0-9]{1,2}(?:\.[0-9]{1,2}){0,2})\s\bfor SQL Server$        
             
MySQL mysql, my ^\bMySQL ODBC\s([0-9]{1,2}(?:\.[0-9]{1,2}){0,2})\s(ANSI\|Unicode)\s\bDriver$        
             
PostgreSQL pg, pgx, pgsql, postgres, postgresql ^\bPostgreSQL\s(ANSI\|Unicode)(\(x64\))?$        
             
MariaDB maria, mariadb ^\bMariaDB ODBC\s([0-9]{1,2}(?:\.[0-9]{1,2}){0,2})\s\bDriver$        
             
DuckDB duck, duckdb ^\bDuckDB\s\bDriver$        
             
Apache Drill drill ^\bMapR Drill ODBC Driver$        
             
Apache Hive hive ^Cloudera ODBC Driver for Apache Hive$        
             
Apache Spark spark ^Simba Spark ODBC Driver$        
             
Databricks databricks ^Databricks ODBC Driver$        
             
Trino tr, trino ^(Simba)\s\bTrino ODBC Driver$        
             
Microsoft Access accdb, access, msaccess, mdb ^\bMicrosoft Access Driver\s\(\\*\.mdb, \\*\.accdb\)$        
             
Microsoft Excel xls, xlsx, xlsb, xlsm ^\bMicrosoft Excel Driver\s\(\\*\.xls, \\*\.xlsx, \\*\.xlsm, \\*\.xlsb\)$        
             
Text files txt, csv, tsv ^\bMicrosoft Access Text Driver\s\(\\*\.txt, \\*\.csv\)$        
             
QuestDb quest, questdb ^\bPostgreSQL\s(ANSI\|Unicode)(\(x64\))?$        
             
Timescale ts, timescale ^\bPostgreSQL\s(ANSI\|Unicode)(\(x64\))?$        
             

Basic example

Following example shows how to use DubUrl to connect to a PostgreSQL database with the ODBC driver. We suppose that this ODBC driver is installed on your client.

Referencing the package System.Data.Odbc related to the ADO.Net data provider

dotnet add package System.Data.Odbc

Registering the factory of the ADO.Net data provider System.Data.Odbc

DbProviderFactories.RegisterFactory("System.Data.Odbc", System.Data.Odbc.OdbcFactory.Instance);

Open a connection to the database named dbname and hosted on a server named serverName. Note that the scheme of the URL is set to pgsql to specify that the database is a PostgreSQL (other options than pgsql such as pg or postgres could have been used).

using var conn = new ConnectionUrl("odbc+pgsql://{serverName}/{dbName}").Open();

Spark SQL and Databricks SQL

These URL forms are part of the upcoming ODBC provider update in DubUrl.Providers PR #46.

Spark SQL

Install the Simba Spark ODBC Driver. The scheme selects the Thrift transport and TLS. Each example omits the port to use the default.

Scheme Default port TLS Example
odbc+spark+http 10001 Off odbc+spark+http://spark.example.com
odbc+spark+https 10001 On odbc+spark+https://spark.example.com/schema/analytics
odbc+spark+tcp 10000 Off odbc+spark+tcp://spark.example.com/schema/analytics?sasl=0
odbc+spark+tcps 10000 On odbc+spark+tcps://spark.example.com/schema/analytics?sasl=1

The HTTP schemes set ThriftTransport=2 and default to /cliservice. Put a custom endpoint before the optional /schema/<name> suffix. For example, odbc+spark+https://spark.example.com:443/gateway/spark/schema/analytics uses port 443, endpoint /gateway/spark, and default schema analytics. The final /schema/<name> pair is reserved, so an HTTP endpoint that itself ends in that pair is unsupported. Spark Thrift Server defaults

The TCP schemes accept sasl=0 for raw binary Thrift and sasl=1 for SASL. You can also use the equivalent ThriftTransport=0 or ThriftTransport=1. If you omit both options, the driver chooses its default. Use the authentication settings required by your server. Simba transport settings

Omit the schema suffix to use the driver default, default for Simba, or pass Schema as a driver option. Catalog selection uses Spark session configuration or SQL-qualified names. The explicit schemes reject query options that conflict with the transport, TLS, or path settings. Simba schema setting

Databricks SQL

Install the Databricks ODBC Driver. Both URL forms default to port 443 and use HTTPS Thrift (ThriftTransport=2, SSL=1). The path selects a warehouse or classic cluster.

Compute Example Generated HTTP path
SQL warehouse odbc+databricks://workspace.example.com/warehouse/0123456789abcdef/main/analytics /sql/1.0/warehouses/0123456789abcdef
Classic cluster odbc+databricks://workspace.example.com/cluster/1234567890123456/0405-060708-example/main/analytics /sql/protocolv1/o/1234567890123456/0405-060708-example

Use the workspace hostname and IDs from the compute resource’s connection details. The examples set the catalog to main and the schema to analytics. Append just /main to set the catalog alone, or omit both names to use the session defaults. A schema segment requires a catalog segment. DubUrl rejects raw HTTP paths and the HTTPPath, Catalog, and Schema query options on this route. Databricks ODBC compute settings

For PAT authentication, append ?AuthMech=3 and pass token as the username and the PAT as the password through runtime credentials.

Apache Hive

Install and register the Cloudera ODBC Driver for Apache Hive, then set up System.Data.Odbc as in the basic example. Use a DubUrl ODBC provider version that includes HiveServer2 support. DubUrl sets HiveServerType=2.

Use odbc+hive://hive.example.com/analytics to select the analytics database. This form defaults to port 10000 and lets you pass transport settings as driver options. Omit the database to use the driver’s default.

Use an explicit scheme to select the transport and TLS settings. The port is optional in each scheme.

Scheme Default port TLS Example
odbc+hive+tcp 10000 Off odbc+hive+tcp://hive.example.com/schema/analytics?sasl=0&AuthMech=0
odbc+hive+tcps 10000 On odbc+hive+tcps://hive.example.com/schema/analytics?sasl=0&AuthMech=0
odbc+hive+http 10001 Off odbc+hive+http://hive.example.com/schema/analytics
odbc+hive+https 10001 On odbc+hive+https://hive.example.com:443/gateway/hive/schema/analytics?AuthMech=3

With an explicit scheme, use the optional /schema/<name> suffix to select the Hive database. HTTP and HTTPS default to the /cliservice endpoint. Put a custom endpoint before the schema suffix, as in the HTTPS example, which uses /gateway/hive. TCP and TCPS accept only the schema suffix in the URL path. Use sasl=0 for raw binary Thrift or sasl=1 for SASL. If you omit both sasl and ThriftTransport, the driver chooses its default.

Match AuthMech to your server configuration. The TCP and TCPS examples use AuthMech=0 for a server configured without authentication. The HTTPS example uses AuthMech=3 for username and password authentication. Supply the username and password through runtime credentials. For HTTPS or TCPS, use TrustedCerts with a URL-encoded path to the trusted certificate file if your server uses a private CA.

Additional options for selecting the right driver

Some driver manufacturer allow you to install multiple version of their drivers in parallel. The version could be a different release (e.g. version 10.0 and version 11.3), different encoding (ANSI or Unicode) or even different architectures (x64 64bits or x86 32 bits). As such more than a single driver can be identified by DubUrl to be a valid candidate. By default, DubUrl use the following conventions when selecting a driver:

  1. The highest version (11.3 is select in place of 10.0)
  2. Only the drivers corresponding to the client architecture are selected
  3. If both ANSI and Unicode drivers are available, then DubUrl select the one supporting a Unicode encoding

This behaviour can be overriden in multiple ways explained here under.

Full name specification

Set the ODBC Driver query option when selecting an exact installed driver. Driver names with spaces or braces are not valid URI scheme components:

odbc+mssql://server/database?Driver=%7BODBC%20Driver%2018%20for%20SQL%20Server%7D

Enforce architecture

If for any reason, you want to enforce the choice of the architecture, you can do it by specifying x64 or x86 in the scheme.

odbc+mysql+x64://serverName/dbName
odbc+mysql+x86://serverName/dbName

Enforce encoding

If for any reason, you want to enforce the choice of the encoding, you can do it by specifying ANSI or Unicode in the scheme.

odbc+mysql+ANSI://serverName/dbName