🔌Setting up an ODBC Connector

Overview¶

You can use the generic ODBC connector for any of your data source compliant with ODBC.

The relevant driver must be installed and configured on your Toucan Toco workspace. see Driver information for more information

Datasource configuration

• First, ask your Toucan Toco contact to install the driver on your instance

Go to the Datahub datasources tab

Mandatory parameters

• Connection string: a string of parameters used to establish a connection between an application and a database. It contains essential information needed to identify and access the data source.

Here are the key aspects of ODBC connection strings:

Format and Structure

Connection strings typically follow this format:

textkeyword1=value1;keyword2=value2;keyword3=value3

Each keyword-value pair is separated by a semicolon, and there's no space between the pairs.

Common Parameters

ODBC connection strings often include the following parameters:

• DRIVER: Specifies the ODBC driver to use

• SERVER or ADDRESS: The server name or IP address

• DATABASE: The name of the database to connect to

• UID (User ID) and PWD (Password): Authentication credentials

• PORT: The port number for the database server (if non-default)

Example Connection Strings

Here are some examples of ODBC connection strings:

1. For SQL Server:

   textDriver={SQL Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

2. For a trusted connection:

   textDriver={SQL Server};Server=myServerAddress;Database=myDataBase;Trusted_Connection=Yes;

3. For a non-default port:

   textDriver={SQL Server};Server=myServerName,myPortNumber;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

This website can serve as a reference for connection strings

Query configuration

To create a dataset, click on "Create a new dataset" on the connection of the connector you just set up

You will land on the query configuration part, to create :

Mandatory parameters

• Query: string type your SQL query in this field.

You can click on Preview to see your results and Validate to validate your query then on Create to create a dataset

Driver installation

• Other driver installation scripts are available for:

  • databricks

  • microsoft sql

  • oracle

• For syntax refer to:

  • databricks

  • microsoft sql

  • oracle

• For reference, you can check pyodbc