Introduction

Download the ODBC driver.

Define an ODBC error-handling function to retrieve error information for the environment handle, connection handle, and statement handle when errors occur.

void sqlerr(SQLSMALLINT HandleType, SQLHANDLE handle)
{
    SQLCHAR state[32];
    SQLINTEGER native;
    SQLCHAR message[255];
    SQLGetDiagRec(HandleType, handle, 1, state, &native, message, 255, NULL);
    printf("state = %s, message = %s\n", state, message);
}

Allocate environment and connection handles, and establish a database connection.

SQLHANDLE hEnv;
SQLHANDLE hDbc;
SQLHANDLE hStmt;
int ret;

// 1. Allocate an ODBC environment handle
ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);
if (!SQL_SUCCEEDED(ret)){
    printf("Failed to allocate environment handle\n");
    return -1;
}

// 2. Set up the environment
ret = SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
if (!SQL_SUCCEEDED(ret)){
    printf("Failed to set up the environment\n");
    sqlerr(SQL_HANDLE_ENV, hEnv);
    return -1;
}

// 3. Allocate an ODBC connection handle
ret = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc);
if (!SQL_SUCCEEDED(ret)){
    printf("Failed to allocate connection handle\n");
    sqlerr(SQL_HANDLE_ENV, hEnv);
    return -1;
}

bool flag = true;
while (flag) {
    cout << "Enter your DSN string: \n  For example: dsn=dolphindb \n";
    string dsn;
    getline(cin, dsn);
    if (dsn == "exit" || dsn == "quit") {
        flag = false;
        break;
    }

    // 4. Connect to the database
    ret = SQLDriverConnect(hDbc,
            NULL,
            (SQLCHAR*)(dsn.c_str()),
            SQL_NTS,
            NULL,
            0,
            NULL,
            SQL_DRIVER_COMPLETE);
    if (!SQL_SUCCEEDED(ret)){
        printf("Failed to connect to the database\n");
        sqlerr(SQL_HANDLE_DBC, hDbc);
        continue;
    }
    break;
}

Allocate a statement handle and then execute the SQL query.

// 5. Allocate the statement handle
ret = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt);
if (!SQL_SUCCEEDED(ret)) {
    printf("Failed to allocate statement handle\n");
    sqlerr(SQL_HANDLE_DBC, hDbc);
    return -1;
}

// 6. Execute the specified SQL statement
string stat;
getline(cin, stat);
if (stat == "exit" || stat == "quit") {
    break;
}
ret = SQLExecDirect(hStmt, (SQLCHAR*)(stat.c_str()), SQL_NTS);

if (!SQL_SUCCEEDED(ret)) {
    printf("Execution error\n");
    sqlerr(SQL_HANDLE_STMT, hStmt);
    continue;
}

Get the number of columns, the number of rows, and column names from the result set.

// 7. Retrieve the number of columns in the result set
SQLSMALLINT sNumResults;
ret = SQLNumResultCols(hStmt, &sNumResults);
if (!SQL_SUCCEEDED(ret)) {
    printf("Error getting result columns\n");
    sqlerr(SQL_HANDLE_STMT, hStmt);
    continue;
}

// 8. Retrieve the number of rows in the result set
SQLLEN cRowCount;
ret = SQLRowCount(hStmt, &cRowCount);
if (!SQL_SUCCEEDED(ret)) {
    printf("Error getting row count\n");
    sqlerr(SQL_HANDLE_STMT, hStmt);
    continue;
}

// 9. Retrieve column names
for (int i = 0; i < sNumResults; i++) {
    SQLCHAR colName[64];
    ret = SQLDescribeCol(hStmt, i + 1, colName, 64, NULL, NULL, NULL, NULL, NULL);
    if (!SQL_SUCCEEDED(ret)) {
        printf("Error getting column name\n");
        sqlerr(SQL_HANDLE_STMT, hStmt);
        continue;
    }
    printf("%s\t", colName);
}

Bind data buffers.

// 10. Bind the data buffers to the driver
vector<DataBinding> columnData;
columnData.resize(sNumResults);
int bufferSize = 1024;
vector<vector<char>> buffer;
buffer.resize(sNumResults);

for (int i = 0; i < sNumResults; i++) {
    columnData[i].TargetType = SQL_C_CHAR;
    columnData[i].BufferLength = bufferSize;
    buffer[i].resize(bufferSize);
    columnData[i].TargetValuePtr = buffer[i].data();
}

for (int i = 0; i < sNumResults; i++) {
    ret = SQLBindCol(hStmt, (SQLUSMALLINT)i + 1, columnData[i].TargetType,
                     columnData[i].TargetValuePtr, columnData[i].BufferLength, &(columnData[i].StrLen_or_Ind));
    if (!SQL_SUCCEEDED(ret)) {
        printf("Error binding column\n");
        sqlerr(SQL_HANDLE_STMT, hStmt);
        continue;
    }
}

Fetch data row by row and print results.

// 11. Call SQLFetch to retrieve data until SQL_NO_DATA_FOUND is returned. The driver writes the fetched data into the bound buffers, which the application can access.
for (ret = SQLFetch(hStmt); ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO; ret = SQLFetch(hStmt)) {
    for (int j = 0; j < sNumResults; j++) {
        printf("%s\t", (char*)columnData[j].TargetValuePtr);
    }
    printf("\n");
}

Before exiting, release the statement handle, connection handle, and environment handle to ensure resources are properly freed.

SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
SQLDisconnect(hDbc);
SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
SQLFreeHandle(SQL_HANDLE_ENV, hEnv);

The default type conversion mappings when writing data are as follows:

Supported DolphinDB types for queries include: BOOL, CHAR, SHORT, INT, LONG, DATE, MONTH, TIME, MINUTE, SECOND, DATETIME, TIMESTAMP, NANOTIME, NANOTIMESTAMP, DATEHOUR, FLOAT, DOUBLE, SYMBOL, STRING, BLOB, INT128, UUID, IPADDR.

DolphinDB numeric types can be mapped to any numeric type in ODBC, and its temporal types can be mapped to any ODBC temporal type.

The default type conversion mappings when retrieving data are as follows: