HeliosDB ODBC Driver v2.0

Production-ready ODBC 3.x driver for HeliosDB, providing universal database connectivity for Windows, Linux, and macOS platforms.

Features

ODBC 3.x Compliant: Full implementation of ODBC 3.x Core and Level 1 functions
Cross-Platform: Windows, Linux, and macOS support
PostgreSQL Wire Protocol: Uses HeliosDB’s PostgreSQL-compatible interface
Comprehensive Type Support: All SQL data types including HeliosDB extensions
Transaction Management: Full ACID transaction support with configurable isolation levels
Prepared Statements: Efficient parameterized query execution
Catalog Functions: Complete metadata discovery for BI tools and query builders
SSL/TLS Support: Secure connections with certificate validation
Connection Pooling: Optional connection pooling for improved performance
Unicode Support: Full UTF-8 and wide character (SQL_WCHAR) support
Thread-Safe: Statement-level thread safety for concurrent operations

System Requirements

All Platforms

CMake 3.15 or higher
C++17 compatible compiler
PostgreSQL client library (libpq) 10.0 or higher

Linux

GCC 7.0+ or Clang 5.0+
unixODBC 2.3.0+ or iODBC 3.52.0+
Development packages: libpq-dev, unixodbc-dev

macOS

Xcode 10.0+ or Command Line Tools
Homebrew (recommended): postgresql, unixodbc

Windows

Visual Studio 2017 or higher (MSVC 19.0+)
PostgreSQL 10+ for libpq.dll
Windows ODBC Driver Manager (built-in)
WiX Toolset 3.11+ (for MSI installer)

Building from Source

Linux/macOS

# Install dependencies (Ubuntu/Debian)
sudo apt-get update
sudo apt-get install build-essential cmake libpq-dev unixodbc-dev

# Install dependencies (macOS with Homebrew)
brew install cmake postgresql unixodbc

# Clone and build
cd /path/to/HeliosDB/odbc-driver
mkdir build && cd build
cmake ..
make -j$(nproc)
sudo make install

Windows

# Using Visual Studio Developer Command Prompt
cd C:\path\to\HeliosDB\odbc-driver
mkdir build
cd build
cmake -G "Visual Studio 16 2019" -A x64 ..
cmake --build . --config Release
cmake --install . --config Release

Build Options

# Build with tests
cmake -DBUILD_TESTS=ON ..

# Build Windows installer
cmake -DBUILD_INSTALLER=ON ..

# Custom installation prefix
cmake -DCMAKE_INSTALL_PREFIX=/usr/local ..

# Debug build
cmake -DCMAKE_BUILD_TYPE=Debug ..

Installation

Linux

# After building
sudo make install

# Configure ODBC driver (copy templates)
sudo cp config/odbcinst.ini.template /etc/odbcinst.ini
sudo cp config/odbc.ini.template /etc/odbc.ini

# Or user-specific configuration
cp config/odbc.ini.template ~/.odbc.ini

macOS

# After building
sudo make install

# Configure ODBC driver
sudo cp config/odbcinst.ini.template /Library/ODBC/odbcinst.ini
cp config/odbc.ini.template ~/.odbc.ini

# Or use GUI tool
/Applications/Utilities/ODBC\ Administrator.app

Windows

Option 1: MSI Installer (Recommended)

# Build MSI (requires WiX Toolset)
cmake --build . --target msi_installer

# Run installer
.\HeliosDB_ODBC_Driver_2.0.0.msi

Option 2: Manual Registration

# Copy DLL to system directory
copy Release\heliodbc.dll "C:\Program Files\HeliosDB\bin\"

# Register driver using ODBC Data Source Administrator
odbcad32.exe
# Or for 64-bit
C:\Windows\System32\odbcad32.exe

Configuration

Data Source Configuration

Linux/macOS: /etc/odbc.ini or ~/.odbc.ini

[HeliosDB]
Description = HeliosDB Database
Driver = HeliosDB ODBC Driver
Server = localhost
Port = 5432
Database = heliosdb
UID = your_username
PWD = your_password
SSLMode = prefer

Windows: ODBC Data Source Administrator

Open ODBC Data Source Administrator (odbcad32.exe)
Go to “System DSN” or “User DSN” tab
Click “Add”
Select “HeliosDB ODBC Driver”
Configure connection parameters:
- Data Source Name: HeliosDB
- Server: localhost
- Port: 5432
- Database: heliosdb
- User: your_username
- Password: your_password
- SSL Mode: prefer

Connection String Format

Driver={HeliosDB ODBC Driver};Server=localhost;Port=5432;Database=heliosdb;UID=user;PWD=password;

Connection String Parameters

Parameter	Aliases	Description	Default
Driver		ODBC driver name	(required)
Server	Host	Database server hostname	localhost
Port		Server port	5432
Database	DB, DBName	Database name	heliosdb
UID	User, UserName	Username	(required)
PWD	Password	Password
SSLMode		SSL connection mode	prefer
SSLCert		Client certificate file
SSLKey		Client key file
SSLRootCert		CA certificate file
ApplicationName	Application_Name	Application identifier	HeliosDB ODBC Driver
ConnectTimeout	Connect_Timeout	Connection timeout (seconds)	30

SSL Modes

disable: No SSL connection
allow: Try non-SSL first, then SSL
prefer: Try SSL first, then non-SSL
require: SSL required, no verification
verify-ca: SSL required, verify CA
verify-full: SSL required, verify CA and hostname

Usage Examples

C/C++ Application

#include <sql.h>
#include <sqlext.h>
#include <iostream>

int main() {
    SQLHENV env;
    SQLHDBC dbc;
    SQLHSTMT stmt;
    SQLRETURN ret;

    // Allocate environment
    SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
    SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);

    // Allocate connection
    SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);

    // Connect
    ret = SQLDriverConnect(dbc, NULL,
        (SQLCHAR*)"DSN=HeliosDB;UID=user;PWD=password;",
        SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT);

    if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
        std::cout << "Connected successfully!" << std::endl;

        // Allocate statement
        SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);

        // Execute query
        SQLExecDirect(stmt, (SQLCHAR*)"SELECT * FROM users", SQL_NTS);

        // Fetch results
        while (SQLFetch(stmt) == SQL_SUCCESS) {
            SQLCHAR name[256];
            SQLLEN indicator;
            SQLGetData(stmt, 1, SQL_C_CHAR, name, sizeof(name), &indicator);
            std::cout << "Name: " << name << std::endl;
        }

        // Cleanup
        SQLFreeHandle(SQL_HANDLE_STMT, stmt);
        SQLDisconnect(dbc);
    }

    SQLFreeHandle(SQL_HANDLE_DBC, dbc);
    SQLFreeHandle(SQL_HANDLE_ENV, env);
    return 0;
}

Python with pyodbc

import pyodbc

# Connect using DSN
conn = pyodbc.connect('DSN=HeliosDB;UID=user;PWD=password')

# Or using connection string
conn = pyodbc.connect(
    'Driver={HeliosDB ODBC Driver};'
    'Server=localhost;Port=5432;'
    'Database=heliosdb;UID=user;PWD=password'
)

cursor = conn.cursor()

# Execute query
cursor.execute("SELECT id, name, email FROM users WHERE active = ?", (True,))

# Fetch results
for row in cursor:
    print(f"ID: {row.id}, Name: {row.name}, Email: {row.email}")

# Insert data
cursor.execute(
    "INSERT INTO users (name, email) VALUES (?, ?)",
    ("John Doe", "john@example.com")
)
conn.commit()

conn.close()

Excel / Power BI

Open Excel or Power BI Desktop
Data → Get Data → From Other Sources → From ODBC
Select “HeliosDB” DSN or enter connection string
Authenticate with credentials
Select tables and load data

DBeaver / SQL Workbench

Create new connection
Select “Generic” → “ODBC”
Driver: HeliosDB ODBC Driver
Configure connection parameters
Test connection

Supported ODBC Functions

Core Functions (Level 1 Compliance)

SQLAllocHandle, SQLFreeHandle
SQLConnect, SQLDriverConnect, SQLDisconnect
SQLPrepare, SQLExecute, SQLExecDirect
SQLFetch, SQLFetchScroll, SQLGetData
SQLBindParameter, SQLBindCol
SQLNumParams, SQLNumResultCols
SQLDescribeParam, SQLDescribeCol, SQLColAttribute
SQLRowCount, SQLCloseCursor, SQLCancel
SQLEndTran (Commit/Rollback)
SQLGetDiagRec, SQLGetDiagField
SQLSetEnvAttr, SQLGetEnvAttr
SQLSetConnectAttr, SQLGetConnectAttr
SQLSetStmtAttr, SQLGetStmtAttr
SQLGetInfo, SQLGetFunctions, SQLGetTypeInfo

Catalog Functions

SQLTables - List tables
SQLColumns - List columns
SQLStatistics - Get index information
SQLPrimaryKeys - Get primary keys
SQLForeignKeys - Get foreign keys
SQLSpecialColumns - Get row identifiers
SQLProcedures - List stored procedures
SQLProcedureColumns - List procedure parameters
SQLTablePrivileges - Get table privileges
SQLColumnPrivileges - Get column privileges

Advanced Functions

SQLMoreResults - Multiple result sets
SQLBulkOperations - Bulk insert/update/delete
SQLSetPos - Positioned operations
SQLParamData, SQLPutData - Data-at-execution
Descriptor functions (SQLGetDescField, SQLSetDescField, etc.)

Supported Data Types

SQL Type	C Type	Description
SQL_CHAR	SQL_C_CHAR	Fixed-length character string
SQL_VARCHAR	SQL_C_CHAR	Variable-length character string
SQL_LONGVARCHAR	SQL_C_CHAR	Long variable-length string
SQL_WCHAR	SQL_C_WCHAR	Fixed-length Unicode string
SQL_WVARCHAR	SQL_C_WCHAR	Variable-length Unicode string
SQL_WLONGVARCHAR	SQL_C_WCHAR	Long Unicode string
SQL_BINARY	SQL_C_BINARY	Fixed-length binary data
SQL_VARBINARY	SQL_C_BINARY	Variable-length binary data
SQL_LONGVARBINARY	SQL_C_BINARY	Long binary data
SQL_BIT	SQL_C_BIT	Single bit
SQL_TINYINT	SQL_C_STINYINT	8-bit signed integer
SQL_SMALLINT	SQL_C_SSHORT	16-bit signed integer
SQL_INTEGER	SQL_C_SLONG	32-bit signed integer
SQL_BIGINT	SQL_C_SBIGINT	64-bit signed integer
SQL_REAL	SQL_C_FLOAT	32-bit floating point
SQL_FLOAT	SQL_C_DOUBLE	64-bit floating point
SQL_DOUBLE	SQL_C_DOUBLE	64-bit floating point
SQL_NUMERIC	SQL_C_NUMERIC	Exact numeric
SQL_DECIMAL	SQL_C_NUMERIC	Exact numeric
SQL_DATE	SQL_C_TYPE_DATE	Date
SQL_TIME	SQL_C_TYPE_TIME	Time
SQL_TIMESTAMP	SQL_C_TYPE_TIMESTAMP	Timestamp
SQL_TYPE_TIMESTAMP	SQL_C_TYPE_TIMESTAMP	Timestamp with timezone

HeliosDB Extensions

VECTOR: High-dimensional vector types (mapped to SQL_BINARY)
JSONB: Binary JSON (mapped to SQL_LONGVARCHAR)
UUID: Universally unique identifier (mapped to SQL_GUID)

Transaction Management

Auto-Commit Mode (Default)

// Each statement is automatically committed
SQLExecDirect(stmt, (SQLCHAR*)"INSERT INTO users ...", SQL_NTS);
// Automatically committed

Manual Transaction Control

// Disable auto-commit
SQLSetConnectAttr(dbc, SQL_ATTR_AUTOCOMMIT,
                 (SQLPOINTER)SQL_AUTOCOMMIT_OFF, 0);

// Execute statements
SQLExecDirect(stmt, (SQLCHAR*)"INSERT INTO users ...", SQL_NTS);
SQLExecDirect(stmt, (SQLCHAR*)"UPDATE accounts ...", SQL_NTS);

// Commit transaction
SQLEndTran(SQL_HANDLE_DBC, dbc, SQL_COMMIT);

// Or rollback
// SQLEndTran(SQL_HANDLE_DBC, dbc, SQL_ROLLBACK);

// Re-enable auto-commit
SQLSetConnectAttr(dbc, SQL_ATTR_AUTOCOMMIT,
                 (SQLPOINTER)SQL_AUTOCOMMIT_ON, 0);

Isolation Levels

// Set isolation level
SQLUINTEGER isolation = SQL_TXN_READ_COMMITTED;
// Options: SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTED,
//          SQL_TXN_REPEATABLE_READ, SQL_TXN_SERIALIZABLE

SQLSetConnectAttr(dbc, SQL_ATTR_TXN_ISOLATION,
                 (SQLPOINTER)isolation, 0);

Performance Optimization

Prepared Statements

// Prepare once
SQLPrepare(stmt, (SQLCHAR*)"INSERT INTO users VALUES (?, ?, ?)", SQL_NTS);

// Bind parameters
SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, ...);
SQLBindParameter(stmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, ...);
SQLBindParameter(stmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, ...);

// Execute multiple times with different parameters
for (int i = 0; i < 1000; i++) {
    // Set parameter values
    ...
    SQLExecute(stmt); // Executes prepared statement
}

Array Binding (Bulk Operations)

#define ARRAY_SIZE 100

SQLUINTEGER ids[ARRAY_SIZE];
SQLCHAR names[ARRAY_SIZE][256];
SQLULEN rowsProcessed;

// Set array size
SQLSetStmtAttr(stmt, SQL_ATTR_PARAMSET_SIZE,
              (SQLPOINTER)ARRAY_SIZE, 0);
SQLSetStmtAttr(stmt, SQL_ATTR_PARAMS_PROCESSED_PTR,
              &rowsProcessed, 0);

// Bind arrays
SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_ULONG,
                SQL_INTEGER, 0, 0, ids, 0, NULL);
SQLBindParameter(stmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
                SQL_VARCHAR, 255, 0, names, 256, NULL);

// Fill arrays
for (int i = 0; i < ARRAY_SIZE; i++) {
    ids[i] = i + 1;
    sprintf((char*)names[i], "User %d", i + 1);
}

// Execute bulk insert
SQLExecDirect(stmt, (SQLCHAR*)"INSERT INTO users VALUES (?, ?)", SQL_NTS);

Connection Pooling

Connection pooling is configured at the environment level:

// Enable connection pooling (before allocating connections)
SQLSetEnvAttr(SQL_NULL_HENV, SQL_ATTR_CONNECTION_POOLING,
             (SQLPOINTER)SQL_CP_ONE_PER_DRIVER, 0);

// Pool timeout
SQLSetEnvAttr(env, SQL_ATTR_CP_MATCH,
             (SQLPOINTER)SQL_CP_RELAXED_MATCH, 0);

Troubleshooting

Enable Driver Logging

Linux/macOS

Edit /etc/odbcinst.ini or ~/.odbcinst.ini:

[ODBC]
Trace = Yes
TraceFile = /tmp/odbc.log

Windows

Open ODBC Data Source Administrator
Go to “Tracing” tab
Enable tracing
Set log file path

Common Issues

Connection Failed: “could not connect to server”

Verify HeliosDB server is running
Check firewall rules allow port 5432
Verify hostname/IP address is correct
Check PostgreSQL pg_hba.conf allows connections

Driver Not Found

Linux: Run odbcinst -q -d to list installed drivers
Verify driver path in odbcinst.ini is correct
Check library dependencies: ldd /usr/local/lib/libheliosdb_odbc.so

SSL/TLS Errors

Verify SSL certificates are valid and accessible
Check certificate permissions (readable by application user)
Try SSLMode=disable for testing (not recommended for production)

Performance Issues

Use prepared statements for repeated queries
Enable connection pooling
Increase fetch buffer size (SQL_ATTR_ROW_ARRAY_SIZE)
Check query execution plans

Debug Information

// Get detailed error information
SQLCHAR sqlState[6], message[SQL_MAX_MESSAGE_LENGTH];
SQLINTEGER nativeError;
SQLSMALLINT textLength;

SQLGetDiagRec(SQL_HANDLE_STMT, stmt, 1, sqlState, &nativeError,
              message, sizeof(message), &textLength);

printf("SQLSTATE: %s\n", sqlState);
printf("Native Error: %d\n", nativeError);
printf("Message: %s\n", message);

Testing

Run Unit Tests

cd build
ctest --output-on-failure

Run Manual Tests

# Connection test
./tests/odbc_connect_test "DSN=HeliosDB;UID=user;PWD=password"

# Query test
./tests/odbc_query_test "DSN=HeliosDB"

# Catalog test
./tests/odbc_catalog_test "DSN=HeliosDB"

Verify Installation

# List installed drivers
odbcinst -q -d

# List configured DSNs
odbcinst -q -s

# Test connection with isql (unixODBC utility)
isql -v HeliosDB username password

Contributing

Contributions welcome! Please follow these guidelines:

Follow existing code style (C++17, Google C++ Style Guide)
Add unit tests for new functionality
Update documentation
Ensure all tests pass before submitting PR

License

Support

Documentation: https://heliosdb.io/docs/odbc-driver
Issues: https://github.com/heliosdb/heliosdb/issues
Community: https://community.heliosdb.io
Email: support@heliosdb.io

Version History

v2.0.0 (2025-01-XX)

Initial release
ODBC 3.x Core and Level 1 compliance
Full catalog function support
SSL/TLS encryption
Cross-platform support (Windows, Linux, macOS)
Transaction management with all isolation levels
Prepared statement support
Bulk operations
Unicode support