3.4. ODBC

3.4.1. Abbreviations

• ODBC - Open Database Connectivity

• DSN - Data Source Name

3.4.2. Introduction

The ODBC driver is our way of making it easier for you to use QuasarDB with a standard interface.

3.4.2.1. ODBC

ODBC (Open Database Connectivity) is a standard way to connect and work with databases. ODBC uses drivers to help your applications talk to different database systems. These drivers act as middlemen, translating what your application says into something the database understands.

ODBC is designed to work on many different systems, like Windows and Linux, as long as the right ODBC driver is available for that system. It uses something called a DSN (Data Source Name) to store information about how to connect to a specific database. This information includes where the database is, how to log in, and other connection details.

For a database system to work with ODBC, it needs to have its own ODBC driver.

3.4.2.2. Datasource

A data source is a specific configuration or connection to a QuasarDB. It provides the necessary information to establish a connection to a database, such as the server’s address, database name, authentication credentials, and other connection parameters.

3.4.3. Installation

WindowsLinux

PrerequisiteInstallationAdding Data SourceTest connection

Python Installation:

If you don’t have Python installed, you can follow these steps:

• Visit the Python website at Python website.

• Download the latest version of Python.

• Follow the installation instructions.

QuasarDB Setup:

To set up QuasarDB, follow these steps:

• Go to the QuasarDB download website, which is available at QuasarDB Downloads.

• Download the QuasarDB setup executable designed for Windows.

3.4.4. 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 secret_key/password provided in the private key file
KEY	The cluster public key file content
DSN	The name of the datasource
DESCRIPTION	A description of this specific datasource
MaxInBufferSize	Maximum incoming buffer size for all network operations
MaxParallelism	Number of threads that will be used to execute queries

3.4.5. Configuring Log rotation

Log rotation is like a digital tidying-up tool for your computer’s log files, offering several benefits:

• Stay Organized: It helps keep log files neat and prevents them from becoming too large, saving your computer space.

• Effortless Search: Log rotation organizes log files for easy access, making it simple to find important information.

• Troubleshooting Aid: When issues arise, older log files are available for reference, making it easier to identify and resolve problems.

3.4.5.1. Parameters

• max_log_file_size is the maximum size in bytes of the current log file. The ODBC driver won’t rotate the log if the value is zero. The driver will cut the log file when the actual log size surpasses this option value. This option requires keep_log_file_num to be more than zero. Default to zero.

• keep_log_file_num is the maximum number of the old log files to store. Default to zero.

• log_level is the log level that the ODBC driver will take. The value starts from 0 to 3 (0 = none, 1 = error, 2 = info, 3 = debug). Default to 2 (info).

3.4.5.2. Scenario

Imagine you’re using the QuasarDB ODBC driver on your Windows computer, and you want to ensure that log files are managed efficiently. You prefer to keep log files from becoming too large and want to maintain one old log file as a backup when the log file reaches 64 MiB. Here’s how you can set this up:

Step 1: Access Windows Registry Editor

• Open the Windows Registry Editor by pressing the Windows key, typing regedit in the search bar, and pressing Enter.

Step 2: Navigate to ODBC Driver Settings

• In the Registry Editor, navigate to the settings for the QuasarDB ODBC driver. The path may vary depending on your system and driver installation, but it could look like HKEY_CURRENT_USER\\Software\ODBC\ODBC.INI\quasardb.

  

Step 3: Configure Log Rotation Parameters

• Find the entry for “max_log_file_size” and double-click it to edit the value. Set it to 67108864 to specify that log rotation should occur when the log file reaches 64 MiB.

• Similarly, locate the entry for “keep_log_file_num” and double-click it to edit the value. Set it to 1 to ensure that one old log file is kept as a backup.

Step 4: Save Your Changes

• After making these adjustments, click OK to save your changes.

Step 5: Restart ODBC Driver (if necessary)

• Some changes in the Registry may require you to restart the ODBC driver to take effect. You can do this by restarting the ODBC driver service or application.

3.4.6. PyODBC

3.4.6.1. Using the Driver or DSN with PyODBC

Pyodbc is a library that people often use with Python when they want to work with databases using an ODBC driver. It’s like a bridge that allows Python to understand and use the QuasarDB ODBC driver.

3.4.6.2. Prerequisites

1. Ensure that Python is installed. If not, download it from the Python website.

2. Install the pyodbc library using the following command:

   pip install pyodbc
   

3.4.6.3. Executing the Code

1. Copy the provided code.

2. Open your Python environment or an integrated development environment (IDE).

3. Paste the code into a new Python script.

4. Save the script with a .py file extension, e.g., “quasardb_query.py”.

5. Open your command-line terminal (e.g., Command Prompt on Windows or Terminal on Linux).

6. Navigate to the directory where you saved the script using the “cd” command.

7. Run the script using Python by typing the following command:

   python quasardb_query.py
   

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") # here to be precise to use DSN or driver

# 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 = 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 = connection.cursor()
for row in cursor.columns(table='ts'):
    print(row)

If you wish to customize the code:

• Modify the URI in the code to match your QuasarDB setup.

• Adjust the SQL queries to retrieve specific data from your database.

• Tweak the encoding settings as required.

3.4.7. Extras

3.4.7.1. Multiple versions of the Quasar ODBC driver on the same machine

Prerequisite

Store different versions of the Quasar ODBC driver in separate directories on your machine, such as:

• /path/to/driver_version1

• /path/to/driver_version2

In each directory where you stored the driver versions, ensure you also have the corresponding qdb-api libraries. Place them in the same directory to match the driver versions, like this:

• /path/to/driver_version1

  • libqdb-odbc-driver.so

  • libqdb-api.so

• /path/to/driver_version2

  • libqdb-odbc-driver.so

  • libqdb-api.so

Open the ODBC driver configuration file, typically located at /etc/odbcinst.ini, using your preferred text editor (e.g., sudo nano).

In the driver configuration file, define driver sections for each version of the Quasar ODBC driver. For each driver section, specify the correct path to the qdb-api library associated with that driver version:

For [qdb_odbc_driver_version1], specify the Driver and Setup paths pointing to the corresponding libqdb-odbc-driver.so for version 1 and libqdb-api.so for version 1

[qdb_odbc_driver_version1]
Description=QuasarDB ODBC Driver (Version 1)
Driver=/path/to/driver_version1/libqdb-odbc-driver.so
Setup=/path/to/driver_version1/libqdb-api.so
APILevel=1
ConnectFunctions=YYY
DriverODBCVer=1.0
FileUsage=0
SQLLevel=1
UsageCount=1

For [qdb_odbc_driver_version2], specify the Driver and Setup paths pointing to the corresponding libqdb-odbc-driver.so for version 2 and libqdb-api.so for version 2:

[qdb_odbc_driver_version2]
Description=QuasarDB ODBC Driver (Version 2)
Driver=/path/to/driver_version2/libqdb-odbc-driver.so
Setup=/path/to/driver_version2/libqdb-api.so
APILevel=1
ConnectFunctions=YYY
DriverODBCVer=2.0
FileUsage=0
SQLLevel=1
UsageCount=1

Ensure that the paths in the Driver and Setup lines correctly point to the respective libqdb-odbc-driver.so and libqdb-api.so libraries for each version.

Save the changes to the driver configuration file and exit your text editor. In Nano, you can press Ctrl + O to save and Ctrl + X to exit.

By following these steps, you’ve configured your drivers to use the appropriate qdb-api library paths, preventing conflicts and ensuring compatibility with your Quasar ODBC driver versions.