2.4. ODBC

2.4.1. Introduction

The ODBC driver is our best effort to allow you to use quasardb with a standardized API.

We will guide you through the process of:

1. Installing the driver

2. Parameters

3. Adding a datasource

4. Using the driver or dsn with pyodbc

2.4.2. Installation

2.4.2.1. Windows

2.4.3. Setup installer

You can find on our download page a setup installer specific to the odbc driver under ODBC Driver > Executable.

You can choose the folder where you want it installed and that’s it!

Caution: Using the uninstaller will automatically remove the driver and any datasource associated with it.

2.4.4. Zip archive

In the zip archive you will find under bin folder the qdb_odbc_installer.exe.

Open a terminal of your choice, unzip the file then execute the installer:

qdb_odbc_installer.exe --install qdb_odbc_driver \path\to\qdb_odbc_driver.dll

The first parameter being the name of the driver, and the second its path. It is recommended to set the absolute path.

2.4.4.1. Linux

You can find on our download page a package for the odbc driver.

It includes an installer that adds the driver to the system, the odbc driver library as well as our quasardb client library.

2.4.5. Driver Parameters

Here is a list of the most common parameters, you can either set them in the dsn or the connection string you use.

Parameter	Description
LOGDIR	A path to the folder in which you wish our log file to be stored
URI	A cluster URI you wish to connect to
UID	The username you log in with
PWD	The password associated with the user
KEY	The cluster public key file content
DSN	The name of the datasource
DESCRIPTION	A description of this specific datasource

2.4.6. Adding a datasource

The way odbc works on windows and linux change widely, please select the tab corresponding to your operating system.

WindowsLinux

To add a datasource on windows you have two options.

Either add it programatically with odbcconf.exe, with the following commands:

odbcconf.exe CONFIGDSN "qdb_odbc_driver" "DSN=quasardb|Driver=qdb_odbc_driver|URI=qdb://127.0.0.1:2836"

It’s especially useful if you wish to connect to a secured cluster:

odbcconf.exe CONFIGDSN "qdb_odbc_driver" "DSN=quasardb|Driver=qdb_odbc_driver|URI=qdb://127.0.0.1:2836|UID=user_name|PWD=user_private_key|KEY=cluster_public_key"

You can add as many parameters as you wish in the secondary string.

Any unmentionned field will be filled with a default value.

You can find more information about odbcconf.exe on the microsoft documentation page: https://docs.microsoft.com/fr-fr/sql/odbc/odbcconf-exe?view=sql-server-ver15

You can also add it with the windows GUI:

To do so, open the windows search, type “odbc”, you should see and open the follwing program: “ODBC Data Sources (64bit)”:

It should have opened this window:

Click on the Add… button on the right side, and then Finish on the dialog box:

It will automatically add a default datasource.

It is not yet possible to configure it with added GUI windows.

If you wish to modify the recently created datasource, open regedit windows program.

Then open the following registry path:

Computer\HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\qdb_odbc_driver

You should find the parameters aforementioned and be able to modify them to suit your needs.

2.4.7. Using the driver or dsn with pyodbc

Pyodbc is a library commonly used in python to access an odbc driver.

In this example we will use it to display some common operations.

import pyodbc

# Connect to the driver
connection = pyodbc.connect("Driver=qdb_odbc_driver|LogDir=./|URI=qdb://127.0.0.1:2836")

# You can also connect to a dsn
# Assuming you installed a datasource named "qdb_odbc_datasource"
# connection = pyodbc.connect("DSN=qdb_odbc_datasource")

# Avoid encoding errors
connection.setencoding('utf-8')
connection.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8')
connection.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
connection.setdecoding(pyodbc.SQL_WMETADATA, encoding='utf-8')

# The next part assume a timeseries with:
#   * 'ts' as a name
#   * values in the 2020-01-01 to 2020-01-02 range
#   * 'col' as a column with int64 values

# Query data without parameters
query = u"SELECT * FROM 'ts' IN RANGE(2020-01-01, 2020-01-02);"
cursor = connection.cursor()
cursor.execute(query)
for row in cursor.fetchall():
    print(row)

# Query data with parameters
query = u"SELECT * FROM 'ts' IN RANGE(2020-01-01, 2020-01-02) where col=?;"
cursor = connection.cursor()
cursor.execute(query, 1)
for row in cursor.fetchall():
    print(row)

# pyodbc make it possible to retrieve information about all available tables
cursor = odbc_connection.cursor()
for row in cursor.tables():
    print(row)

# Another similar call enables you to get information about the columns of a specific table
cursor = odbc_connection.cursor()
for row in cursor.columns(table='ts'):
    print(row)