Skip to main content

ODBC

About 21 min

ODBC

1. Feature Introduction

The IoTDB ODBC driver provides the ability to interact with the database via the standard ODBC interface, supporting data management in time-series databases through ODBC connections. It currently supports database connection, data query, data insertion, data modification, and data deletion operations, and is compatible with various applications and toolchains that support the ODBC protocol.

Note: This feature is supported starting from V2.0.8.2.

2. Usage Method

It is recommended to install using the pre-compiled binary package. There is no need to compile it yourself; simply use the script to complete the driver installation and system registration. Currently, only Windows systems are supported.

2.1 Environment Requirements

Only the ODBC Driver Manager dependency at the operating system level is required; no compilation environment configuration is needed:

Operating SystemRequirements and Installation Method
Windows1. Windows 10/11, Server 2016/2019/2022: Comes with ODBC Driver Manager version 17/18 built-in; no extra installation needed.
2. Windows 8.1/Server 2012 R2: Requires manual installation of the corresponding version of the ODBC Driver Manager.

2.2 Installation Steps

  1. Contact the Tianmou team to obtain the pre-compiled binary package.
    Binary package directory structure:
    ├── bin/
│   ├── apache_iotdb_odbc.dll
│   └── install_driver.exe
├── install.bat
└── registry.bat
  2. Open a command line tool (CMD/PowerShell) with Administrator privileges and run the following command: (You can replace the path with any absolute path)
    install.bat "C:\Program Files\Apache IoTDB ODBC Driver"
    The script automatically completes the following operations:
    • Creates the installation directory (if it does not exist).
    • Copies bin\apache_iotdb_odbc.dll to the specified installation directory.
    • Calls install_driver.exe to register the driver to the system via the ODBC standard API (SQLInstallDriverEx).
  3. Verify installation: Open "ODBC Data Source Administrator". If you can see Apache IoTDB ODBC Driver in the "Drivers" tab, the registration was successful.

2.3 Uninstallation Steps

  1. Open Command Prompt as Administrator and cd into the project root directory.
  2. Run the uninstallation script:
    uninstall.bat
    The script will call install_driver.exe to unregister the driver from the system via the ODBC standard API (SQLRemoveDriver). The DLL files in the installation directory will not be automatically deleted; please delete them manually if cleanup is required.

2.4 Connection Configuration

After installing the driver, you need to configure a Data Source Name (DSN) to allow applications to connect to the database using the DSN name. The IoTDB ODBC driver supports two methods for configuring connection parameters: via Data Source and via Connection String.

2.4.1 Configuring Data Source

Configure via ODBC Data Source Administrator

  1. Open "ODBC Data Source Administrator", switch to the "User DSN" tab, and click the "Add" button.

  2. Select "Apache IoTDB ODBC Driver" from the pop-up driver list and click "Finish".

  3. The data source configuration dialog will appear. Fill in the connection parameters and click OK:

    The meaning of each field in the dialog box is as follows:

    AreaFieldDescription
    Data SourceDSN NameData Source Name; applications refer to this data source by this name.
    Data SourceDescriptionData Source description (optional).
    ConnectionServerIoTDB server IP address, default 127.0.0.1.
    ConnectionPortIoTDB Session API port, default 6667.
    ConnectionUserUsername, default root.
    ConnectionPasswordPassword, default root.
    OptionsTable ModelCheck to use Table Model; uncheck to use Tree Model.
    OptionsDatabaseDatabase name. Only available in Table Model mode; grayed out in Tree Model.
    OptionsLog LevelLog level (0-4): 0=OFF, 1=ERROR, 2=WARN, 3=INFO, 4=TRACE.
    OptionsSession TimeoutSession timeout time (milliseconds); 0 means no timeout. Note: The server-side queryTimeoutThreshold defaults to 60000ms; exceeding this value requires modifying server configuration.
    OptionsBatch SizeNumber of rows fetched per batch, default 1000. Setting to 0 resets to the default value.

  4. After filling in the details, you can click the "Test Connection" button to test the connection. Testing will attempt to connect to the IoTDB server using the current parameters and execute a SHOW VERSION query. If successful, the server version information will be displayed; if failed, the specific error reason will be shown.

  5. Once parameters are confirmed correct, click "OK" to save. The data source will appear in the "User DSN" list, as shown in the example below with the name "123".

    To modify the configuration of an existing data source, select it in the list and click the "Configure" button to edit again.

2.4.2 Connection String

The connection string format is semicolon-separated key-value pairs, for example:

Driver={IoTDB ODBC Driver};server=127.0.0.1;port=6667;uid=root;pwd=root;database=testdb;isTableModel=true;loglevel=2

Specific field attributes are introduced in the table below:

Field NameDescriptionOptional ValuesDefault Value
DSNData Source NameCustom data source name-
uidDatabase usernameAny stringroot
pwdDatabase passwordAny stringroot
serverIoTDB server addressIP address127.0.0.1
portIoTDB server portPort number6667
databaseDatabase name (only effective in Table Model mode)Any stringEmpty string
loglevelLog levelInteger value (0-4)4 (LOG_LEVEL_TRACE)
isTableModel / tablemodelWhether to enable Table Model modeBoolean type, supports multiple representations:
1. 0, false, no, off: set to false;
2. 1, true, yes, on: set to true;
3. Other values default to true.		true
sessiontimeoutmsSession timeout time (milliseconds)64-bit integer, defaults to LLONG_MAX; setting to 0 will be replaced with LLONG_MAX. Note: The server has a timeout setting: private long queryTimeoutThreshold = 60000; this item needs to be modified to get a timeout time exceeding 60 seconds.LLONG_MAX
batchsizeBatch size for fetching data each time64-bit integer, defaults to 1000; setting to 0 will be replaced with 10001000

Notes:

  • Field names are case-insensitive (automatically converted to lowercase for comparison).
  • Connection string format is semicolon-separated key-value pairs, e.g., Driver={IoTDB ODBC Driver};server=127.0.0.1;port=6667;uid=root;pwd=root;database=testdb;isTableModel=true;loglevel=2.
  • For boolean fields (isTableModel), multiple representation methods are supported.
  • All fields are optional; if not specified, default values are used.
  • Unsupported fields will be ignored and a warning logged, but will not affect the connection.
  • The default server interface port 6667 is the default port used by IoTDB's C++ Session interface. This ODBC driver uses the C++ Session interface to transfer data with IoTDB. If the C++ Session interface on the IoTDB server uses a non-default port, corresponding changes must be made in the ODBC connection string.

2.4.3 Relationship between Data Source Configuration and Connection String

Configurations saved in the ODBC Data Source Administrator are written into the system's ODBC data source configuration as key-value pairs (corresponding to the registry HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI under Windows). When an application uses SQLConnect or specifies DSN=DataSourceName in the connection string, the driver reads these parameters from the system configuration.

The priority of the connection string is higher than the configuration saved in the DSN. Specific rules are as follows:

  1. If the connection string contains DSN=xxx and does not contain DRIVER=..., the driver first loads all parameters of that DSN from the system configuration as base values.
  2. Then, parameters explicitly specified in the connection string will override parameters with the same name in the DSN.
  3. If the connection string contains DRIVER=..., no DSN parameters will be read from the system configuration; it will rely entirely on the connection string.

For example: If the DSN is configured with Server=192.168.1.100 and Port=6667, but the connection string is DSN=MyDSN;Server=127.0.0.1, then the actual connection will use Server=127.0.0.1 (overridden by connection string) and Port=6667 (from DSN).

2.5 Logging

Log output during driver runtime is divided into "Driver Self-Logs" and "ODBC Manager Tracing Logs". Note the impact of log levels on performance.

2.5.1 Driver Self-Logs

  • Output location: apache_iotdb_odbc.log in the user's home directory.
  • Log level: Configured via the loglevel parameter in the connection string (0-4; higher levels produce more detailed output).
  • Performance impact: High log levels will significantly reduce driver performance; recommended for debugging only.

2.5.2 ODBC Manager Tracing Logs

  • How to enable: Open "ODBC Data Source Administrator" → "Tracing" → "Start Tracing Now".
  • Precautions: Enabling this will greatly reduce driver performance; use only for troubleshooting.

3. Interface Support

3.1 Method List

The driver's support status for standard ODBC APIs is as follows:

ODBC/Setup APIFunction FunctionParameter ListParameter Description
SQLAllocHandleAllocate ODBC Handle(SQLSMALLINT HandleType, SQLHANDLE InputHandle, SQLHANDLE *OutputHandle)HandleType: Type of handle to allocate (ENV/DBC/STMT/DESC);
InputHandle: Parent context handle;
OutputHandle: Pointer to the returned new handle.
SQLBindColBind column to result buffer(SQLHSTMT StatementHandle, SQLUSMALLINT ColumnNumber, SQLSMALLINT TargetType, SQLPOINTER TargetValue, SQLLEN BufferLength, SQLLEN *StrLen_or_Ind)StatementHandle: Statement handle;
ColumnNumber: Column number;
TargetType: C data type;
TargetValue: Data buffer;BufferLength: Buffer length;
StrLen_or_Ind: Returns data length or NULL indicator.
SQLColAttributeGet column attribute information(SQLHSTMT StatementHandle, SQLUSMALLINT ColumnNumber, SQLUSMALLINT FieldIdentifier, SQLPOINTER CharacterAttribute, SQLSMALLINT BufferLength, SQLSMALLINT *StringLength, SQLLEN *NumericAttribute)StatementHandle: Statement handle;
ColumnNumber: Column number;
FieldIdentifier: Attribute ID;
CharacterAttribute: Character attribute output;
BufferLength: Buffer length;
StringLength: Returned length;
NumericAttribute: Numeric attribute output.
SQLColumnsQuery table column information(SQLHSTMT StatementHandle, SQLCHAR *CatalogName, SQLSMALLINT NameLength1, SQLCHAR *SchemaName, SQLSMALLINT NameLength2, SQLCHAR *TableName, SQLSMALLINT NameLength3, SQLCHAR *ColumnName, SQLSMALLINT NameLength4)StatementHandle: Statement handle;
Catalog/Schema/Table/ColumnName: Query object names;

NameLength*: Corresponding name lengths.
SQLConnectEstablish database connection(SQLHDBC ConnectionHandle, SQLCHAR *ServerName, SQLSMALLINT NameLength1, SQLCHAR *UserName, SQLSMALLINT NameLength2, SQLCHAR *Authentication, SQLSMALLINT NameLength3)ConnectionHandle: Connection handle;
ServerName: Data source name;
UserName: Username;
Authentication: Password;
NameLength*: String lengths.
SQLDescribeColDescribe columns in result set(SQLHSTMT StatementHandle, SQLUSMALLINT ColumnNumber, SQLCHAR *ColumnName, SQLSMALLINT BufferLength, SQLSMALLINT *NameLength, SQLSMALLINT *DataType, SQLULEN *ColumnSize, SQLSMALLINT *DecimalDigits, SQLSMALLINT *Nullable)StatementHandle: Statement handle;
ColumnNumber: Column number;
ColumnName: Column name output;
BufferLength: Buffer length;
NameLength: Returned column name length;
DataType: SQL type;
ColumnSize: Column size;
DecimalDigits: Decimal digits;
Nullable: Whether nullable.
SQLDisconnectDisconnect database connection(SQLHDBC ConnectionHandle)ConnectionHandle: Connection handle.
SQLDriverConnectEstablish connection using connection string(SQLHDBC ConnectionHandle, SQLHWND WindowHandle, SQLCHAR *InConnectionString, SQLSMALLINT StringLength1, SQLCHAR *OutConnectionString, SQLSMALLINT BufferLength, SQLSMALLINT *StringLength2, SQLUSMALLINT DriverCompletion)ConnectionHandle: Connection handle;
WindowHandle: Window handle;InConnectionString: Input connection string;
StringLength1: Input length;
OutConnectionString: Output connection string;
BufferLength: Output buffer;
StringLength2: Returned length;
DriverCompletion: Connection prompt method.
SQLEndTranCommit or rollback transaction(SQLSMALLINT HandleType, SQLHANDLE Handle, SQLSMALLINT CompletionType)HandleType: Handle type;
Handle: Connection or environment handle;
CompletionType: Commit or rollback transaction.
SQLExecDirectExecute SQL statement directly(SQLHSTMT StatementHandle, SQLCHAR *StatementText, SQLINTEGER TextLength)StatementHandle: Statement handle;
StatementText: SQL text;TextLength: SQL length.
SQLFetchFetch next row in result set(SQLHSTMT StatementHandle)StatementHandle: Statement handle.
SQLFreeHandleFree ODBC handle(SQLSMALLINT HandleType, SQLHANDLE Handle)HandleType: Handle type;
Handle: Handle to free.
SQLFreeStmtFree statement-related resources(SQLHSTMT StatementHandle, SQLUSMALLINT Option)StatementHandle: Statement handle;
Option: Free option (close cursor/reset parameters, etc.).
SQLGetConnectAttrGet connection attribute(SQLHDBC ConnectionHandle, SQLINTEGER Attribute, SQLPOINTER Value, SQLINTEGER BufferLength, SQLINTEGER *StringLength)ConnectionHandle: Connection handle;
Attribute: Attribute ID;
Value: Returned attribute value;
BufferLength: Buffer length;
StringLength: Returned length.
SQLGetDataGet result data(SQLHSTMT StatementHandle, SQLUSMALLINT Col_or_Param_Num, SQLSMALLINT TargetType, SQLPOINTER TargetValue, SQLLEN BufferLength, SQLLEN *StrLen_or_Ind)StatementHandle: Statement handle;
Col_or_Param_Num: Column number;
TargetType: C type;
TargetValue: Data buffer;
BufferLength: Buffer size;
StrLen_or_Ind: Returned length or NULL flag.
SQLGetDiagFieldGet diagnostic field(SQLSMALLINT HandleType, SQLHANDLE Handle, SQLSMALLINT RecNumber, SQLSMALLINT DiagIdentifier, SQLPOINTER DiagInfo, SQLSMALLINT BufferLength, SQLSMALLINT *StringLength)HandleType: Handle type;
Handle: Handle;
RecNumber: Record number;
DiagIdentifier: Diagnostic field ID;
DiagInfo: Output info;
BufferLength: Buffer;
StringLength: Returned length.
SQLGetDiagRecGet diagnostic record(SQLSMALLINT HandleType, SQLHANDLE Handle, SQLSMALLINT RecNumber, SQLCHAR *Sqlstate, SQLINTEGER *NativeError, SQLCHAR *MessageText, SQLSMALLINT BufferLength, SQLSMALLINT *TextLength)HandleType: Handle type;
Handle: Handle;
RecNumber: Record number;
Sqlstate: SQL state code;
NativeError: Native error code;
MessageText: Error message;
BufferLength: Buffer;
TextLength: Returned length.
SQLGetInfoGet database information(SQLHDBC ConnectionHandle, SQLUSMALLINT InfoType, SQLPOINTER InfoValue, SQLSMALLINT BufferLength, SQLSMALLINT *StringLength)ConnectionHandle: Connection handle;
InfoType: Information type;
InfoValue: Return value;
BufferLength: Buffer length;
StringLength: Returned length.
SQLGetStmtAttrGet statement attribute(SQLHSTMT StatementHandle, SQLINTEGER Attribute, SQLPOINTER Value, SQLINTEGER BufferLength, SQLINTEGER *StringLength)StatementHandle: Statement handle;
Attribute: Attribute ID;
Value: Return value;
BufferLength: Buffer;
StringLength: Returned length.
SQLGetTypeInfoGet data type information(SQLHSTMT StatementHandle, SQLSMALLINT DataType)StatementHandle: Statement handle;
DataType: SQL data type.
SQLMoreResultsGet more result sets(SQLHSTMT StatementHandle)StatementHandle: Statement handle.
SQLNumResultColsGet number of columns in result set(SQLHSTMT StatementHandle, SQLSMALLINT *ColumnCount)StatementHandle: Statement handle;
ColumnCount: Returned column count.
SQLRowCountGet number of affected rows(SQLHSTMT StatementHandle, SQLLEN *RowCount)StatementHandle: Statement handle;
RowCount: Returned number of affected rows.
SQLSetConnectAttrSet connection attribute(SQLHDBC ConnectionHandle, SQLINTEGER Attribute, SQLPOINTER Value, SQLINTEGER StringLength)ConnectionHandle: Connection handle;
Attribute: Attribute ID;
Value: Attribute value;
StringLength: Attribute value length.
SQLSetEnvAttrSet environment attribute(SQLHENV EnvironmentHandle, SQLINTEGER Attribute, SQLPOINTER Value, SQLINTEGER StringLength)EnvironmentHandle: Environment handle;
Attribute: Attribute ID;
Value: Attribute value;
StringLength: Length.
SQLSetStmtAttrSet statement attribute(SQLHSTMT StatementHandle, SQLINTEGER Attribute, SQLPOINTER Value, SQLINTEGER StringLength)StatementHandle: Statement handle;
Attribute: Attribute ID;
Value: Attribute value;
StringLength: Length.
SQLTablesQuery table information(SQLHSTMT StatementHandle, SQLCHAR *CatalogName, SQLSMALLINT NameLength1, SQLCHAR *SchemaName, SQLSMALLINT NameLength2, SQLCHAR *TableName, SQLSMALLINT NameLength3, SQLCHAR *TableType, SQLSMALLINT NameLength4)StatementHandle: Statement handle;
Catalog/Schema/TableName: Table names;
TableType: Table type;
NameLength*: Corresponding lengths.

3.2 Data Type Conversion

The mapping relationship between IoTDB data types and standard ODBC data types is as follows:

IoTDB Data TypeODBC Data Type
BOOLEANSQL_BIT
INT32SQL_INTEGER
INT64SQL_BIGINT
FLOATSQL_REAL
DOUBLESQL_DOUBLE
TEXTSQL_VARCHAR
STRINGSQL_VARCHAR
BLOBSQL_LONGVARBINARY
TIMESTAMPSQL_BIGINT
DATESQL_DATE

4. Operation Examples

This chapter mainly introduces full-type operation examples for C#, Python, C++, PowerBI, and Excel, covering core operations such as data query, insertion, and deletion.

4.1 C# Example

Here is the C# code with all comments and string literals translated into English:

```csharp
/*******
Note: When the output contains Chinese characters, it may cause garbled text.
This is because the table.Write() function cannot output strings in UTF-8 encoding
and can only output using GB2312 (or another system default encoding). This issue
may not occur in software like Power BI; it also does not occur when using the Console.WriteLine function. 
This is an issue with the ConsoleTable package.
*****/

using System.Data.Common;
using System.Data.Odbc;
using System.Reflection.PortableExecutable;
using ConsoleTables;
using System;

/// <summary>Executes a SELECT query and outputs the results of fulltable in table format</summary>
void Query(OdbcConnection dbConnection)
{
    try
    {
        using (OdbcCommand dbCommand = dbConnection.CreateCommand())
        {
            dbCommand.CommandText = "select * from fulltable";
            using (OdbcDataReader dbReader = dbCommand.ExecuteReader())
            {
                var fCount = dbReader.FieldCount;
                Console.WriteLine($"fCount = {fCount}");
                
                // Output header row
                var columns = new string[fCount];
                for (var i = 0; i < fCount; i++)
                {
                    var fName = dbReader.GetName(i);
                    if (fName.Contains('.'))
                    {
                        fName = fName.Substring(fName.LastIndexOf('.') + 1);
                    }
                    columns[i] = fName;
                }
                
                // Output content rows
                var table = new ConsoleTable(columns);
                while (dbReader.Read())
                {
                    var row = new object[fCount];
                    for (var i = 0; i < fCount; i++)
                    {
                        if (dbReader.IsDBNull(i))
                        {
                            row[i] = null;
                            continue;
                        }
                        row[i] = dbReader.GetValue(i);
                    }
                    table.AddRow(row);
                }
                table.Write();
                Console.WriteLine();
            }
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.ToString());
    }
}

/// <summary>Executes non-query SQL statements (such as CREATE DATABASE, CREATE TABLE, INSERT, etc.)</summary>
void Execute(OdbcConnection dbConnection, string command)
{
    try
    {
        using (OdbcCommand dbCommand = dbConnection.CreateCommand())
        {
            try
            {
                dbCommand.CommandText = command;
                Console.WriteLine($"Execute command: {command}");
                dbCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine($"CommandText error: {ex.Message}");
            }
        }
    }
    catch (OdbcException ex)
    {
        Console.WriteLine($"Database error: {ex.Message}");
    }
    catch (Exception ex)
    {
        Console.WriteLine($"Unknown error occurred: {ex.Message}");
    }
}

var dsn = "Apache IoTDB DSN";
var user = "root";
var password = "root";
var server = "127.0.0.1";
var database = "test";
var connectionString = $"DSN={dsn};Server={server};UID={user};PWD={password};Database={database};loglevel=4";

using (OdbcConnection dbConnection = new OdbcConnection(connectionString))
{
    Console.WriteLine($"Start");
    try
    {
        dbConnection.Open();
    }
    catch (Exception ex)
    {
        Console.WriteLine($"Login failed: {ex.Message}");
        Console.WriteLine($"Stack Trace: {ex.StackTrace}");
        dbConnection.Dispose();
        return;
    }
    Console.WriteLine($"Successfully opened connection. database name = {dbConnection.Driver}");
    
    Execute(dbConnection, "CREATE DATABASE IF NOT EXISTS test");
    Execute(dbConnection, "use test");
    Console.WriteLine("use test Execute complete. Begin to setup fulltable.");

    Execute(dbConnection, "CREATE TABLE IF NOT EXISTS fullTable (time TIMESTAMP TIME, bool_col BOOLEAN FIELD, int32_col INT32 FIELD, int64_col INT64 FIELD, float_col FLOAT FIELD, double_col DOUBLE FIELD, text_col TEXT FIELD, string_col STRING FIELD, blob_col BLOB FIELD, timestamp_col TIMESTAMP FIELD, date_col DATE FIELD) WITH (TTL=315360000000)");
    
    string[] insertStatements = new string[]
    {
        "INSERT INTO fulltable VALUES (1735689600000, true, 100, 10000000000, 36.5, 128.689, 'Device operating normally', 'DeviceA-Room1', '0x506C616E7444617461', 1735689600000, '2026-01-04')",
        "INSERT INTO fulltable VALUES (1735689660000, false, 101, 10000000001, 36.6, 128.789, 'Device operating normally', 'DeviceA-Room1', '0x506C616E7444617461', 1735689660000, '2026-01-04')",
        "INSERT INTO fulltable VALUES (1735689720000, true, 102, 10000000002, 36.7, 128.889, 'Device operating normally', 'DeviceA-Room1', '0x506C616E7444617461', 1735689720000, '2026-01-04')",
        "INSERT INTO fulltable VALUES (1735689780000, false, 103, 10000000003, 36.8, 128.989, 'Device temperature high alarm', 'DeviceA-Room1', '0x506C616E7444617462', 1735689780000, '2026-01-04')",
        "INSERT INTO fulltable VALUES (1735689840000, true, 104, 10000000004, 36.9, 129.089, 'Device status returned to normal', 'DeviceA-Room1', '0x506C616E7444617461', 1735689840000, '2026-01-04')",
        "INSERT INTO fulltable VALUES (1735689900000, false, 105, 10000000005, 37.0, 129.189, 'Device operating normally', 'DeviceB-Room2', '0x506C616E7444617463', 1735689900000, '2026-01-04')",
        "INSERT INTO fulltable VALUES (1735689960000, true, 106, 10000000006, 37.1, 129.289, 'Device operating normally', 'DeviceB-Room2', '0x506C616E7444617463', 1735689960000, '2026-01-04')",
        "INSERT INTO fulltable VALUES (1735690020000, false, 107, 10000000007, 37.2, 129.389, 'Device humidity low alarm', 'DeviceB-Room2', '0x506C616E7444617464', 1735690020000, '2026-01-04')",
        "INSERT INTO fulltable VALUES (1735690080000, true, 108, 10000000008, 37.3, 129.489, 'Device status returned to normal', 'DeviceB-Room2', '0x506C616E7444617463', 1735690080000, '2026-01-04')",
        "INSERT INTO fulltable VALUES (1735690140000, false, 109, 10000000009, 37.4, 129.589, 'Device operating normally', 'DeviceC-Room3', '0x506C616E7444617465', 1735690140000, '2026-01-04')",
        "INSERT INTO fulltable VALUES (1735690200000, true, 110, 10000000010, 37.5, 129.689, 'Device operating normally', 'DeviceC-Room3', '0x506C616E7444617465', 1735690200000, '2026-01-04')",
        "INSERT INTO fulltable VALUES (1735690260000, false, 111, 10000000011, 37.6, 129.789, 'Device voltage unstable alarm', 'DeviceC-Room3', '0x506C616E7444617466', 1735690260000, '2026-01-04')",
        "INSERT INTO fulltable VALUES (1735690320000, true, 112, 10000000012, 37.7, 129.889, 'Device status returned to normal', 'DeviceC-Room3', '0x506C616E7444617465', 1735690320000, '2026-01-04')",
        "INSERT INTO fulltable VALUES (1735690380000, false, 113, 10000000013, 37.8, 129.989, 'Device operating normally', 'DeviceD-Room4', '0x506C616E7444617467', 1735690380000, '2026-01-04')",
        "INSERT INTO fulltable VALUES (1735690440000, true, 114, 10000000014, 37.9, 130.089, 'Device operating normally', 'DeviceD-Room4', '0x506C616E7444617467', 1735690440000, '2026-01-04')",
        "INSERT INTO fulltable VALUES (1735690500000, false, 115, 10000000015, 38.0, 130.189, 'Device operating normally', 'DeviceD-Room4', '0x506C616E7444617467', 1735690500000, '2026-01-04')",
        "INSERT INTO fulltable VALUES (1735690560000, true, 116, 10000000016, 38.1, 130.289, 'Device signal interrupted alarm', 'DeviceD-Room4', '0x506C616E7444617468', 1735690560000, '2026-01-04')",
        "INSERT INTO fulltable VALUES (1735690620000, false, 117, 10000000017, 38.2, 130.389, 'Device operating normally', 'DeviceE-Room5', '0x506C616E7444617469', 1735690620000, '2026-01-04')",
        "INSERT INTO fulltable VALUES (1735690680000, true, 118, 10000000018, 38.3, 130.489, 'Device operating normally', 'DeviceE-Room5', '0x506C616E7444617469', 1735690680000, '2026-01-04')",
        "INSERT INTO fulltable VALUES (1735690740000, false, 119, 10000000019, 38.4, 130.589, 'Device operating normally', 'DeviceE-Room5', '0x506C616E7444617469', 1735690740000, '2026-01-04')",
        "INSERT INTO fulltable VALUES (1735690790000, false, 119, 10000000019, 38.4, 130.589, 'Device operating normally', 'DeviceE-Room5', '0x506C616E7444617469', 1735690740000, '2026-01-04')"
    };
    
    foreach (var insert in insertStatements)
    {
        Execute(dbConnection, insert);
    }
    Console.WriteLine("fulltable setup complete. Begin to query.");
    
    Query(dbConnection); // Execute query and output results
}

4.2 Python Example

  1. To access ODBC via Python, install the pyodbc package:
    pip install pyodbc
  2. Full Code:
Here is the complete Python code with all comments and string literals translated into English:

```python
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Apache IoTDB ODBC Python example
Use pyodbc to connect to the IoTDB ODBC driver and perform operations such as query and insert.
For reference, see examples/cpp-example/test.cpp and examples/BasicTest/BasicTest/Program.cs
"""

import pyodbc

def execute(conn: pyodbc.Connection, command: str) -> None:
    """Executes non-query SQL statements (such as USE, CREATE, INSERT, DELETE, etc.)"""
    try:
        with conn.cursor() as cursor:
            cursor.execute(command)
            # INSERT/UPDATE/DELETE require commit; session commands such as USE do not.
            cmd_upper = command.strip().upper()
            if cmd_upper.startswith(("INSERT", "UPDATE", "DELETE")):
                conn.commit()
            print(f"Execute command: {command}")
    except pyodbc.Error as ex:
        print(f"CommandText error: {ex}")

def query(conn: pyodbc.Connection, sql: str) -> None:
    """Executes a SELECT query and outputs the results in table format"""
    try:
        with conn.cursor() as cursor:
            cursor.execute(sql)
            col_count = len(cursor.description)
            print(f"fCount = {col_count}")

            if col_count <= 0:
                return

            # Get column names (if the name contains '.', take the last segment, consistent with C++/C# samples).
            columns = []
            for i in range(col_count):
                col_name = cursor.description[i][0] or f"Column{i}"
                if "." in str(col_name):
                    col_name = str(col_name).split(".")[-1]
                columns.append(str(col_name))

            # Fetch data rows
            rows = cursor.fetchall()

            # Simple table output
            col_widths = [max(len(str(col)), 4) for col in columns]
            for i, row in enumerate(rows):
                for j, val in enumerate(row):
                    if j < len(col_widths):
                        col_widths[j] = max(col_widths[j], len(str(val) if val is not None else "NULL"))

            # Print header
            header = " | ".join(str(c).ljust(col_widths[i]) for i, c in enumerate(columns))
            print(header)
            print("-" * len(header))

            # Print data rows
            for row in rows:
                values = []
                for i, val in enumerate(row):
                    if val is None:
                        cell = "NULL"
                    else:
                        cell = str(val)
                    values.append(cell.ljust(col_widths[i]) if i < len(col_widths) else cell)
                print(" | ".join(values))

            print()

    except pyodbc.Error as ex:
        print(f"Query error: {ex}")

def main() -> None:
    dsn = "Apache IoTDB DSN"
    user = "root"
    password = "root"
    server = "127.0.0.1"
    database = "test"
    connection_string = (
        f"DSN={dsn};Server={server};UID={user};PWD={password};"
        f"Database={database};loglevel=4"
    )

    print("Start")

    try:
        conn = pyodbc.connect(connection_string)
    except pyodbc.Error as ex:
        print(f"Login failed: {ex}")
        return

    try:
        driver_name = conn.getinfo(6)  # SQL_DRIVER_NAME
        print(f"Successfully opened connection. driver = {driver_name}")
    except Exception:
        print("Successfully opened connection.")

    try:
        execute(conn, "CREATE DATABASE IF NOT EXISTS test")
        execute(conn, "use test")
        print("use test Execute complete. Begin to setup fulltable.")

        # Create the fulltable table and insert test data
        execute(
            conn,
            "CREATE TABLE IF NOT EXISTS fullTable (time TIMESTAMP TIME, bool_col BOOLEAN FIELD, "
            "int32_col INT32 FIELD, int64_col INT64 FIELD, float_col FLOAT FIELD, "
            "double_col DOUBLE FIELD, text_col TEXT FIELD, string_col STRING FIELD, "
            "blob_col BLOB FIELD, timestamp_col TIMESTAMP FIELD, date_col DATE FIELD) "
            "WITH (TTL=315360000000)",
        )
        insert_statements = [
            "INSERT INTO fulltable VALUES (1735689600000, true, 100, 10000000000, 36.5, 128.689, 'Device operating normally', 'DeviceA-Room1', '0x506C616E7444617461', 1735689600000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735689660000, false, 101, 10000000001, 36.6, 128.789, 'Device operating normally', 'DeviceA-Room1', '0x506C616E7444617461', 1735689660000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735689720000, true, 102, 10000000002, 36.7, 128.889, 'Device operating normally', 'DeviceA-Room1', '0x506C616E7444617461', 1735689720000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735689780000, false, 103, 10000000003, 36.8, 128.989, 'Device temperature high alarm', 'DeviceA-Room1', '0x506C616E7444617462', 1735689780000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735689840000, true, 104, 10000000004, 36.9, 129.089, 'Device status returned to normal', 'DeviceA-Room1', '0x506C616E7444617461', 1735689840000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735689900000, false, 105, 10000000005, 37.0, 129.189, 'Device operating normally', 'DeviceB-Room2', '0x506C616E7444617463', 1735689900000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735689960000, true, 106, 10000000006, 37.1, 129.289, 'Device operating normally', 'DeviceB-Room2', '0x506C616E7444617463', 1735689960000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735690020000, false, 107, 10000000007, 37.2, 129.389, 'Device humidity low alarm', 'DeviceB-Room2', '0x506C616E7444617464', 1735690020000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735690080000, true, 108, 10000000008, 37.3, 129.489, 'Device status returned to normal', 'DeviceB-Room2', '0x506C616E7444617463', 1735690080000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735690140000, false, 109, 10000000009, 37.4, 129.589, 'Device operating normally', 'DeviceC-Room3', '0x506C616E7444617465', 1735690140000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735690200000, true, 110, 10000000010, 37.5, 129.689, 'Device operating normally', 'DeviceC-Room3', '0x506C616E7444617465', 1735690200000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735690260000, false, 111, 10000000011, 37.6, 129.789, 'Device voltage unstable alarm', 'DeviceC-Room3', '0x506C616E7444617466', 1735690260000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735690320000, true, 112, 10000000012, 37.7, 129.889, 'Device status returned to normal', 'DeviceC-Room3', '0x506C616E7444617465', 1735690320000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735690380000, false, 113, 10000000013, 37.8, 129.989, 'Device operating normally', 'DeviceD-Room4', '0x506C616E7444617467', 1735690380000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735690440000, true, 114, 10000000014, 37.9, 130.089, 'Device operating normally', 'DeviceD-Room4', '0x506C616E7444617467', 1735690440000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735690500000, false, 115, 10000000015, 38.0, 130.189, 'Device operating normally', 'DeviceD-Room4', '0x506C616E7444617467', 1735690500000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735690560000, true, 116, 10000000016, 38.1, 130.289, 'Device signal interrupted alarm', 'DeviceD-Room4', '0x506C616E7444617468', 1735690560000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735690620000, false, 117, 10000000017, 38.2, 130.389, 'Device operating normally', 'DeviceE-Room5', '0x506C616E7444617469', 1735690620000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735690680000, true, 118, 10000000018, 38.3, 130.489, 'Device operating normally', 'DeviceE-Room5', '0x506C616E7444617469', 1735690680000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735690740000, false, 119, 10000000019, 38.4, 130.589, 'Device operating normally', 'DeviceE-Room5', '0x506C616E7444617469', 1735690740000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735690790000, false, 119, 10000000019, 38.4, 130.589, 'Device operating normally', 'DeviceE-Room5', '0x506C616E7444617469', 1735690740000, '2026-01-04')",
        ]
        for insert_sql in insert_statements:
            execute(conn, insert_sql)
        print("fulltable setup complete. Begin to query.")
        query(conn, "select * from fulltable")
        print("Query ok")
    finally:
        conn.close()

if __name__ == "__main__":
    main()

4.3 C++ Example

Here is the complete C++ code with all comments and string literals translated into English:

```cpp
#define WIN32_LEAN_AND_MEAN
#include <windows.h>

#include <sql.h>
#include <sqlext.h>
#include <iostream>
#include <string>
#include <vector>
#include <chrono>
#include <ctime>

#ifndef SQL_DIAG_COLUMN_SIZE
#define SQL_DIAG_COLUMN_SIZE  33L
#endif

// Error handling function (core functionality preserved)
void CheckOdbcError(SQLRETURN retCode, SQLSMALLINT handleType, SQLHANDLE handle, const char* functionName) {
    if (retCode == SQL_SUCCESS || retCode == SQL_SUCCESS_WITH_INFO) {
        return;
    }

    SQLCHAR sqlState[6];
    SQLCHAR message[SQL_MAX_MESSAGE_LENGTH];
    SQLINTEGER nativeError;
    SQLSMALLINT textLength;
    SQLRETURN errRet;
    errRet = SQLGetDiagRec(handleType, handle, 1, sqlState, &nativeError, message, sizeof(message), &textLength);

    std::cerr << "ODBC Error in " << functionName << ":\n";
    std::cerr << "  SQL State: " << sqlState << "\n";
    std::cerr << "  Native Error: " << nativeError << "\n";
    std::cerr << "  Message: " << message << "\n";
    std::cerr << "  SQLGetDiagRec Return: " << errRet << "\n";

    if (retCode == SQL_ERROR || retCode == SQL_INVALID_HANDLE) {
        exit(1);
    }
}

// Simplified table output - displays basic data only
void PrintSimpleTable(const std::vector<std::string>& headers, 
                     const std::vector<std::vector<std::string>>& rows) {
    // Print header row
    for (size_t i = 0; i < headers.size(); i++) {
        std::cout << headers[i];
        if (i < headers.size() - 1) std::cout << "\t";
    }
    std::cout << std::endl;
    
    // Print separator line
    for (size_t i = 0; i < headers.size(); i++) {
        std::cout << "----------------";
        if (i < headers.size() - 1) std::cout << "\t";
    }
    std::cout << std::endl;
    
    // Print data rows
    for (const auto& row : rows) {
        for (size_t i = 0; i < row.size(); i++) {
            std::cout << row[i];
            if (i < row.size() - 1) std::cout << "\t";
        }
        std::cout << std::endl;
    }
    std::cout << std::endl;
}

/// Executes a SELECT query and outputs the results of fulltable in table format
void Query(SQLHDBC hDbc) {
    SQLHSTMT hStmt = SQL_NULL_HSTMT;
    SQLRETURN ret = SQL_SUCCESS;
    
    try {
        // Allocate statement handle
        ret = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt);
        if (!SQL_SUCCEEDED(ret)) {
            CheckOdbcError(ret, SQL_HANDLE_DBC, hDbc, "SQLAllocHandle(SQL_HANDLE_STMT)");
            return;
        }
        
        // Execute query
        const std::string sqlQuery = "select * from fulltable";
        std::cout << "Execute query: " << sqlQuery << std::endl;

        ret = SQLExecDirect(hStmt, reinterpret_cast<SQLCHAR*>(const_cast<char*>(sqlQuery.c_str())), SQL_NTS);
        if (!SQL_SUCCEEDED(ret)) {
            if (ret != SQL_NO_DATA) {
                CheckOdbcError(ret, SQL_HANDLE_STMT, hStmt, "SQLExecDirect(SELECT)");
            }
            SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
            return;
        }
        
        // Get column count
        SQLSMALLINT colCount = 0;
        ret = SQLNumResultCols(hStmt, &colCount);
        if (!SQL_SUCCEEDED(ret)) {
            CheckOdbcError(ret, SQL_HANDLE_STMT, hStmt, "SQLNumResultCols");
            SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
            return;
        }
        
        std::cout << "Column count = " << colCount << std::endl;
        
        // If no columns, return directly
        if (colCount <= 0) {
            SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
            return;
        }
        
        // Get column names and type information
        std::vector<std::string> columnNames;
        std::vector<SQLSMALLINT> columnTypes(colCount);
        std::vector<SQLULEN> columnSizes(colCount);
        std::vector<SQLSMALLINT> decimalDigits(colCount);
        std::vector<SQLSMALLINT> nullable(colCount);
        
        // Get basic column information
        for (SQLSMALLINT i = 1; i <= colCount; i++) {
            SQLSMALLINT nameLength = 0;
            ret = SQLDescribeCol(hStmt, i, NULL, 0, &nameLength, NULL, NULL, NULL, NULL);
            if (!SQL_SUCCEEDED(ret)) {
                CheckOdbcError(ret, SQL_HANDLE_STMT, hStmt, "SQLDescribeCol (get length)");
                SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
                return;
            }
            
            std::vector<SQLCHAR> colNameBuffer(nameLength + 1);
            SQLSMALLINT actualNameLength = 0;
            
            ret = SQLDescribeCol(hStmt, i, colNameBuffer.data(), nameLength + 1, 
                                &actualNameLength, NULL, NULL, NULL, NULL);
            if (!SQL_SUCCEEDED(ret)) {
                CheckOdbcError(ret, SQL_HANDLE_STMT, hStmt, "SQLDescribeCol (get name)");
                SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
                return;
            }
            
            std::string fullName(reinterpret_cast<char*>(colNameBuffer.data()));
            
            size_t pos = fullName.find_last_of('.');
            if (pos != std::string::npos) {
                columnNames.push_back(fullName.substr(pos + 1));
            } else {
                columnNames.push_back(fullName);
            }
            
            ret = SQLDescribeCol(hStmt, i, NULL, 0, NULL, &columnTypes[i-1], 
                                &columnSizes[i-1], &decimalDigits[i-1], &nullable[i-1]);
            if (!SQL_SUCCEEDED(ret)) {
                CheckOdbcError(ret, SQL_HANDLE_STMT, hStmt, "SQLDescribeCol (get type info)");
                SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
                return;
            }
        }
        
        std::vector<std::vector<std::string>> tableRows;
        
        int rowCount = 0;
        // Fetch data for every row
        while (true) {
            ret = SQLFetch(hStmt);
            if (ret == SQL_NO_DATA) {
                break;  
            }

            if (!SQL_SUCCEEDED(ret)) {
                CheckOdbcError(ret, SQL_HANDLE_STMT, hStmt, "SQLFetch");
                break;
            }
            
            std::vector<std::string> row;
            
            for (SQLSMALLINT i = 1; i <= colCount; i++) {
                SQLLEN indicator = 0;
                std::string valueStr;
                
                SQLSMALLINT cType;
                size_t bufferSize;
                bool isCharacterType = false;
                const int maxBufferSize = 32768;
                
                switch (columnTypes[i-1]) {
                    case SQL_CHAR:
                    case SQL_VARCHAR:
                    case SQL_LONGVARCHAR:
                    case SQL_WCHAR:
                    case SQL_WVARCHAR:
                    case SQL_WLONGVARCHAR:
                        cType = SQL_C_CHAR;
                        if (columnSizes[i - 1] > 0) {
                            bufferSize = min(maxBufferSize, static_cast<size_t>(columnSizes[i-1]) * 4 + 1);
                        } else {
                            bufferSize = maxBufferSize;
                        }
                        isCharacterType = true;
                        break;
                        
                    case SQL_DECIMAL:
                    case SQL_NUMERIC:
                        cType = SQL_C_CHAR;
                        if (columnSizes[i - 1] > 0) {
                            bufferSize = min(maxBufferSize, static_cast<size_t>(columnSizes[i-1]) * 4 + 1);
                        } else {
                            bufferSize = maxBufferSize;
                        }
                        isCharacterType = true;
                        break;
                        
                    case SQL_INTEGER:
                    case SQL_SMALLINT:
                    case SQL_TINYINT:
                    case SQL_BIGINT:
                        cType = SQL_C_SBIGINT;  
                        bufferSize = sizeof(SQLBIGINT);
                        break;
                        
                    case SQL_REAL:
                    case SQL_FLOAT:
                    case SQL_DOUBLE:
                        cType = SQL_C_DOUBLE;
                        bufferSize = sizeof(double);
                        break;
                        
                    case SQL_BIT:
                        cType = SQL_C_BIT;
                        bufferSize = sizeof(SQLCHAR);
                        break;
                        
                    case SQL_DATE:
                    case SQL_TYPE_DATE:
                        cType = SQL_C_DATE;
                        bufferSize = sizeof(SQL_DATE_STRUCT);
                        break;
                        
                    case SQL_TIME:
                    case SQL_TYPE_TIME:
                        cType = SQL_C_TIME;
                        bufferSize = sizeof(SQL_TIME_STRUCT);
                        break;
                        
                    case SQL_TIMESTAMP:
                    case SQL_TYPE_TIMESTAMP:
                        cType = SQL_C_TIMESTAMP;
                        bufferSize = sizeof(SQL_TIMESTAMP_STRUCT);
                        break;
                        
                    default:
                        cType = SQL_C_CHAR;
                        bufferSize = 256; 
                        isCharacterType = true;
                        break;
                }
                
                std::vector<BYTE> buffer(bufferSize);

                ret = SQLGetData(hStmt, i, cType, buffer.data(), bufferSize, &indicator);

                if (indicator == SQL_NULL_DATA) {
                    valueStr = "NULL";
                } 
                else if (ret != SQL_SUCCESS) {
                    valueStr = "ERR_CONV";
                }
                else {
                    if (cType == SQL_C_CHAR) {
                        valueStr = reinterpret_cast<char*>(buffer.data());
                    }
                    else if (cType == SQL_C_SBIGINT) {
                        SQLBIGINT intVal = *reinterpret_cast<SQLBIGINT*>(buffer.data());
                        valueStr = std::to_string(intVal);
                    }
                    else if (cType == SQL_C_DOUBLE) {
                        double doubleVal = *reinterpret_cast<double*>(buffer.data());
                        valueStr = std::to_string(doubleVal);
                    }
                    else if (cType == SQL_C_BIT) {
                        valueStr = (*buffer.data() != 0) ? "TRUE" : "FALSE";
                    }
                    else if (cType == SQL_C_DATE) {
                        SQL_DATE_STRUCT* date = reinterpret_cast<SQL_DATE_STRUCT*>(buffer.data());
                        char dateStr[20];
                        snprintf(dateStr, sizeof(dateStr), "%04d-%02d-%02d", 
                                date->year, date->month, date->day);
                        valueStr = dateStr;
                    }
                    else if (cType == SQL_C_TIME) {
                        SQL_TIME_STRUCT* time = reinterpret_cast<SQL_TIME_STRUCT*>(buffer.data());
                        char timeStr[15];
                        snprintf(timeStr, sizeof(timeStr), "%02d:%02d:%02d", 
                                time->hour, time->minute, time->second);
                        valueStr = timeStr;
                    }
                    else if (cType == SQL_C_TIMESTAMP) {
                        SQL_TIMESTAMP_STRUCT* ts = reinterpret_cast<SQL_TIMESTAMP_STRUCT*>(buffer.data());
                        char tsStr[30];
                        snprintf(tsStr, sizeof(tsStr), "%04d-%02d-%02d %02d:%02d:%02d.%06d", 
                                ts->year, ts->month, ts->day,
                                ts->hour, ts->minute, ts->second,
                                ts->fraction / 1000); 
                        valueStr = tsStr;
                    }
                    else {
                        valueStr = "UNKNOWN_TYPE";
                    }
                    
                    if (isCharacterType && ret == SQL_SUCCESS_WITH_INFO) {
                        SQLLEN actualSize = 0;
                        SQLGetDiagField(SQL_HANDLE_STMT, hStmt, 0, SQL_DIAG_COLUMN_SIZE, 
                                       &actualSize, SQL_IS_INTEGER, NULL);
                        
                        if (indicator > 0 && static_cast<size_t>(indicator) > bufferSize - 1) {
                            valueStr += "...";  
                        }
                    }
                    
                }
                
                row.push_back(valueStr);
            }
            
            tableRows.push_back(row);
        }
        
        if (!tableRows.empty()) {
            PrintSimpleTable(columnNames, tableRows);
        }
        
        SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
    }
    catch (const std::exception& ex) {
        std::cerr << "Exception: " << ex.what() << std::endl;
        if (hStmt != SQL_NULL_HSTMT) {
            SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
        }
        throw;
    }
    catch (...) {
        std::cerr << "Unknown exception occurred" << std::endl;
        if (hStmt != SQL_NULL_HSTMT) {
            SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
        }
        throw;
    }
}

/// Executes non-query SQL statements (such as CREATE DATABASE, CREATE TABLE, INSERT, etc.)
void Execute(SQLHDBC hDbc, const std::string& command) {
    SQLHSTMT hStmt = SQL_NULL_HSTMT;
    SQLRETURN ret;
    
    try {
        // Allocate statement handle
        ret = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt);
        CheckOdbcError(ret, SQL_HANDLE_DBC, hDbc, "SQLAllocHandle(SQL_HANDLE_STMT)");
        
        // Execute command
        ret = SQLExecDirect(hStmt, (SQLCHAR*)command.c_str(), SQL_NTS);
        if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO) {
            CheckOdbcError(ret, SQL_HANDLE_STMT, hStmt, "SQLExecDirect");
        }
        
        // Free statement handle
        SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
    }
    catch (...) {
        if (hStmt != SQL_NULL_HSTMT) {
            SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
        }
        throw;
    }
}

int main() {
    SQLHENV hEnv = SQL_NULL_HENV;
    SQLHDBC hDbc = SQL_NULL_HDBC;
    SQLRETURN ret;
    
    try {
        std::cout << "Start" << std::endl;
        
        // 1. Initialize ODBC environment
        ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);
        CheckOdbcError(ret, SQL_HANDLE_ENV, hEnv, "SQLAllocHandle(SQL_HANDLE_ENV)");
        
        ret = SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
        CheckOdbcError(ret, SQL_HANDLE_ENV, hEnv, "SQLSetEnvAttr");
        
        // 2. Establish connection
        ret = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc);
        CheckOdbcError(ret, SQL_HANDLE_ENV, hEnv, "SQLAllocHandle(SQL_HANDLE_DBC)");
        
        // Connection string
        std::string dsn = "Apache IoTDB DSN";
        std::string user = "root";
        std::string password = "root";
        std::string server = "127.0.0.1";
        std::string database = "test";
        
        std::string connectionString = "DSN=" + dsn + ";Server=" + server + 
                                     ";UID=" + user + ";PWD=" + password + 
                                     ";Database=" + database + ";loglevel=4";
        std::cout << "Using connection string: " << connectionString << std::endl;
        
        SQLCHAR outConnStr[1024];
        SQLSMALLINT outConnStrLen;
        
        ret = SQLDriverConnect(hDbc, NULL, 
                              (SQLCHAR*)connectionString.c_str(), SQL_NTS,
                              outConnStr, sizeof(outConnStr),
                              &outConnStrLen, SQL_DRIVER_COMPLETE);
        
        if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO) {
            std::cerr << "Login failed" << std::endl;
            CheckOdbcError(ret, SQL_HANDLE_DBC, hDbc, "SQLDriverConnect");
            return 1;
        }
        
        // Get driver name
        SQLCHAR driverName[256];
        SQLSMALLINT nameLength;
        ret = SQLGetInfo(hDbc, SQL_DRIVER_NAME, driverName, sizeof(driverName), &nameLength);
        CheckOdbcError(ret, SQL_HANDLE_DBC, hDbc, "SQLGetInfo");
        
        std::cout << "Successfully opened connection. database name = " << driverName << std::endl;
        
        // 3. Execute operations
        Execute(hDbc, "CREATE DATABASE IF NOT EXISTS test");
        Execute(hDbc, "use test");
        std::cout << "use test Execute complete. Begin to setup fulltable." << std::endl;

        // Create fulltable table and insert test data
        Execute(hDbc, "CREATE TABLE IF NOT EXISTS fullTable (time TIMESTAMP TIME, bool_col BOOLEAN FIELD, int32_col INT32 FIELD, int64_col INT64 FIELD, float_col FLOAT FIELD, double_col DOUBLE FIELD, text_col TEXT FIELD, string_col STRING FIELD, blob_col BLOB FIELD, timestamp_col TIMESTAMP FIELD, date_col DATE FIELD) WITH (TTL=315360000000)");
        const char* insertStatements[] = {
            "INSERT INTO fulltable VALUES (1735689600000, true, 100, 10000000000, 36.5, 128.689, 'Device operating normally', 'DeviceA-Room1', '0x506C616E7444617461', 1735689600000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735689660000, false, 101, 10000000001, 36.6, 128.789, 'Device operating normally', 'DeviceA-Room1', '0x506C616E7444617461', 1735689660000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735689720000, true, 102, 10000000002, 36.7, 128.889, 'Device operating normally', 'DeviceA-Room1', '0x506C616E7444617461', 1735689720000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735689780000, false, 103, 10000000003, 36.8, 128.989, 'Device temperature high alarm', 'DeviceA-Room1', '0x506C616E7444617462', 1735689780000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735689840000, true, 104, 10000000004, 36.9, 129.089, 'Device status returned to normal', 'DeviceA-Room1', '0x506C616E7444617461', 1735689840000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735689900000, false, 105, 10000000005, 37.0, 129.189, 'Device operating normally', 'DeviceB-Room2', '0x506C616E7444617463', 1735689900000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735689960000, true, 106, 10000000006, 37.1, 129.289, 'Device operating normally', 'DeviceB-Room2', '0x506C616E7444617463', 1735689960000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735690020000, false, 107, 10000000007, 37.2, 129.389, 'Device humidity low alarm', 'DeviceB-Room2', '0x506C616E7444617464', 1735690020000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735690080000, true, 108, 10000000008, 37.3, 129.489, 'Device status returned to normal', 'DeviceB-Room2', '0x506C616E7444617463', 1735690080000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735690140000, false, 109, 10000000009, 37.4, 129.589, 'Device operating normally', 'DeviceC-Room3', '0x506C616E7444617465', 1735690140000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735690200000, true, 110, 10000000010, 37.5, 129.689, 'Device operating normally', 'DeviceC-Room3', '0x506C616E7444617465', 1735690200000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735690260000, false, 111, 10000000011, 37.6, 129.789, 'Device voltage unstable alarm', 'DeviceC-Room3', '0x506C616E7444617466', 1735690260000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735690320000, true, 112, 10000000012, 37.7, 129.889, 'Device status returned to normal', 'DeviceC-Room3', '0x506C616E7444617465', 1735690320000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735690380000, false, 113, 10000000013, 37.8, 129.989, 'Device operating normally', 'DeviceD-Room4', '0x506C616E7444617467', 1735690380000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735690440000, true, 114, 10000000014, 37.9, 130.089, 'Device operating normally', 'DeviceD-Room4', '0x506C616E7444617467', 1735690440000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735690500000, false, 115, 10000000015, 38.0, 130.189, 'Device operating normally', 'DeviceD-Room4', '0x506C616E7444617467', 1735690500000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735690560000, true, 116, 10000000016, 38.1, 130.289, 'Device signal interrupted alarm', 'DeviceD-Room4', '0x506C616E7444617468', 1735690560000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735690620000, false, 117, 10000000017, 38.2, 130.389, 'Device operating normally', 'DeviceE-Room5', '0x506C616E7444617469', 1735690620000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735690680000, true, 118, 10000000018, 38.3, 130.489, 'Device operating normally', 'DeviceE-Room5', '0x506C616E7444617469', 1735690680000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735690740000, false, 119, 10000000019, 38.4, 130.589, 'Device operating normally', 'DeviceE-Room5', '0x506C616E7444617469', 1735690740000, '2026-01-04')",
            "INSERT INTO fulltable VALUES (1735690790000, false, 119, 10000000019, 38.4, 130.589, 'Device operating normally', 'DeviceE-Room5', '0x506C616E7444617469', 1735690740000, '2026-01-04')"
        };
        for (const char* sql : insertStatements) {
            Execute(hDbc, sql);
        }
        std::cout << "fulltable setup complete. Begin to query." << std::endl;
        Query(hDbc);
        std::cout << "Query ok" << std::endl;

        // 4. Clean up resources
        SQLDisconnect(hDbc);
        SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
        SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
        
        return 0;
    }
    catch (...) {
        // Exception cleanup
        if (hDbc != SQL_NULL_HDBC) {
            SQLDisconnect(hDbc);
            SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
        }
        if (hEnv != SQL_NULL_HENV) {
            SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
        }
        
        std::cerr << "Unexpected error!" << std::endl;
        return 1;
    }
}

4.4 PowerBI Example

  1. Open PowerBI Desktop and create a new project.
  2. Click "Home" → "Get Data" → "More..." → "ODBC" → Click the "Connect" button.
  3. Data Source Selection: In the pop-up window, select "Data Source Name (DSN)" and choose Apache IoTDB DSN from the dropdown.
  4. Advanced Configuration:
    • Click "Advanced options" and fill in the configuration in the "Connection string" input box (example):
      server=127.0.0.1;port=6667;database=test;isTableModel=true;loglevel=4
    • Notes:
      • The dsn item is optional; filling it in or not does not affect the connection.
      • loglevel ranges from 0-4: Level 0 (ERROR) has the least logs, Level 4 (TRACE) has the most detailed logs; set as needed.
      • server/database/dsn/loglevel are case-insensitive (e.g., can be written as Server/DATABASE).
      • If relevant information is configured in the DSN, you do not need to fill in any configuration information; the Driver Manager will automatically use the configuration filled in the DSN.
  5. Authentication: Enter the username (default root) and password (default root), then click "Connect".
  6. Data Loading: Select the table to be called in the interface (e.g., fulltable/table1) , and then click "Load" to view the data.

4.5 Excel Example

  1. Open Excel and create a blank workbook.
  2. Click the "Data" tab → "From Other Sources" → "From Data Connection Wizard".
  3. Data Source Selection: Select "ODBC DSN" → Next → Select Apache IoTDB DSN → Next.
  4. Connection Configuration:
    • The input process for connection string, username, and password is exactly the same as in PowerBI. Reference format for connection string:
      server=127.0.0.1;port=6667;database=test;isTableModel=true;loglevel=4
    • If relevant information is configured in the DSN, you do not need to fill in any configuration information; the Driver Manager will automatically use the configuration filled in the DSN.
  5. Table Selection: Choose the database and table you wish to access (e.g., fulltable), then click Next.
  6. Save Connection: Customize settings for the data connection file name, connection description, etc., then click "Finish".
  7. Import Data: Select the location to import the data into the worksheet (e.g., cell A1 of "Existing Worksheet"), click "OK" to complete data loading.
Last Updated:

Copyright © 2026 Timecho Limited All rights reserved.
Apache IoTDB and the Apache IoTDB project logo are trademarks of The Apache Software Foundation.