open62541通过odbc实现OpcUA历史数据存取

最新推荐文章于 2024-07-31 19:38:21 发布
lyDOS
最新推荐文章于 2024-07-31 19:38:21 发布
阅读量232
点赞数 5
文章标签：数据库 oracle
本文链接：https://blog.csdn.net/lyDOS/article/details/140679589
版权
open62541开源库内置历史数据功能，但是基于内存存取的，重启程序历史数据就没了，以下这篇文章给出了基于sqlite3数据库存取方案：学习open62541 --- [58] 使用数据库存储历史数据_open62541历史事件-CSDN博客
关于open62541历史数据的使用可以参考这篇文章：学习open62541 --- [13] 历史数据-CSDN博客
历史数据边界问题参考下这篇文章：学习open62541 --- [48] 历史数据的边界问题_open62541源码分析-CSDN博客
我参考这些文章以及open62541历史数据插件源码，实现了通过odbc存取历史数据，并支持多变量、数组功能，应该可以在实际中使用。注意本文不支持返回边界，所以必须把客户端相关returnBounds 参数设置为 false。
文中使用了unixODBC、cJSON、CString、CMutex类库，使用方法可以在网上查找。其实也可以不用CString类库，稍微修改下即可。CMutex 是互斥类，可以采用自己的方式实现，这里就不列出具体实现代码。
本文要区分数据库类型，主要还是因为sqlite3和其它的数据库在SQL语句上有点不同，sqlite3默认数据库名称为main，其它均可自己定数据库名称。除了sqlite3可以不指定数据库名称，其它几乎均需要指定数据库名称。
文中实现了6种数据类型：double、float、int32、int16、byte、boolean。
说明：UA_DataValue->value->arrayLength 的值非0表示为数组，需要使用UA_Variant_setArray函数，只有0才使用UA_Variant_setScalar函数。
#ifndef BACKEND_H
#define BACKEND_H

#include <time.h>
#include <stdlib.h>
#include "open62541.h"
#include "cstring.h"

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


/* 检测返回代码是否为成功标志，当为成功标志返回 TRUE，否则返回 FALSE */
#define RC_SUCCESSFUL(rc) ((rc) == SQL_SUCCESS || (rc) == SQL_SUCCESS_WITH_INFO)
/* 检测返回代码是否为失败标志，当为失败标志返回 TRUE，否则返回 FALSE */
#define RC_NOTSUCCESSFUL(rc) (!(RC_SUCCESSFUL(rc)))

static const size_t END_OF_DATA = SIZE_MAX;
static const size_t QUERY_BUFFER_SIZE = 500;

CMutex opcLock;

UA_Int64 convertTimestampStringToUnixSeconds(const char* timestampString)
{
    UA_DateTimeStruct dts;
    memset(&dts, 0, sizeof(dts));

    sscanf(timestampString, "%hu-%hu-%hu %hu:%hu:%hu",
        &dts.year, &dts.month, &dts.day, &dts.hour, &dts.min, &dts.sec);

    UA_DateTime dt = UA_DateTime_fromStruct(dts);

    UA_Int64 t = UA_DateTime_toUnixTime(dt-UA_DateTime_localTimeUtcOffset());

    return t;
}

const char* convertUnixSecondsToTimestampString(UA_Int64 unixSeconds)
{
    static char buffer[20];


    UA_DateTime dt = UA_DateTime_fromUnixTime(unixSeconds);
    UA_DateTimeStruct dts = UA_DateTime_toStruct(dt+UA_DateTime_localTimeUtcOffset());

    struct tm tm;
    memset(&tm, 0, sizeof(tm));
    tm.tm_year = dts.year - 1900;
    tm.tm_mon  = dts.month - 1;
    tm.tm_mday = dts.day;
    tm.tm_hour = dts.hour;
    tm.tm_min  = dts.min;
    tm.tm_sec  = dts.sec;

    memset(buffer, 0, 20);

    strftime(buffer, 20, "%Y-%m-%d %H:%M:%S", &tm);

    return buffer;
}

struct ODBC_DATA
{
    int type;
    int arrayLength;
    int dataLength;
    uint8_t data[5120];
};

//Context that is needed for the SQLite callback for copying data.
struct context_copyDataValues {
    size_t maxValues;
    size_t counter;
    UA_DataValue *values;
};

typedef  struct context_copyDataValues  context_copyDataValues;


struct context_odbc {

    HENV henv;/* 环境句柄 */
    HDBC hdbc;/* 连接句柄 */
    HSTMT hstmt;/* 语句句柄 */
    int DBtype; //数据库型号：0-Sqlite3  1-达梦  2-MYSQL
};

int GetDBtype(HENV env,char *dsn); //获取数据库型号：0-Sqlite3  1-达梦  2-MYSQL
#define SQLITE3 0
#define DM8 1
#define MYSQL 2

int GetDBtype(HENV env,char *dsn) //获取数据库型号：0-Sqlite3  1-达梦  2-MYSQL
{
    char l_dsn[100],l_desc[100];
    short int  l_len1,l_len2,l_next;
    l_next=SQL_FETCH_FIRST;
    CString sType,sDSN(dsn);
    int id;
    sDSN.MakeUpper();
    while( SQLDataSources(env,l_next,l_dsn, sizeof(l_dsn),&l_len1, l_desc, sizeof(l_desc), &l_len2) == SQL_SUCCESS)
    {
        CString sdsn = CString(l_dsn);
        sdsn.MakeUpper();
        if(sDSN==sdsn)
        {
            sType.Format("%s",l_desc);
            sType.MakeUpper();

            id=sType.Find("SQLITE3");
            if(id>=0) return SQLITE3 ;
            id=sType.Find("DM8");
            if(id>=0) return DM8;
            id=sType.Find("MYSQL");
            if(id>=0) return MYSQL;
        }

        l_next=SQL_FETCH_NEXT;
    }
    return DM8;
}


static struct context_odbc * generateContext_odbc()
{
    HENV henv;/* 环境句柄 */
    HDBC hdbc;/* 连接句柄 */
    HSTMT hstmt;/* 语句句柄 */
    SQLRETURN sret; /* 返回代码 */

    const char *dsn="commdata";
    //const char  *DBName[]={"Sqlite3","DM8","MySQL"};

    /* 申请句柄 */
    SQLAllocHandle(SQL_HANDLE_ENV, NULL, &henv);
    SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER);
    SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);

    //数据库类型名称
    int iDBid = GetDBtype(henv,dsn);    //=0 Sqlite3 , 1=DM8
    //printf("Database: %s\n",DBName[iDBid]);

    //连接数据库
    sret = SQLConnect(hdbc, (SQLCHAR *)dsn, SQL_NTS, (SQLCHAR *)"", SQL_NTS, (SQLCHAR *)"", SQL_NTS);
    if (RC_NOTSUCCESSFUL(sret))
    {
        printf("odbc: fail to connect to server,dsn=%s!\n",dsn);
        SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
        SQLFreeHandle(SQL_HANDLE_ENV, henv);
        //exit(0);
    }
    else
        printf("odbc: connect to server success!\n");

    /* 申请一个语句句柄 */
    SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

    struct context_odbc* ret = (struct context_odbc*)UA_calloc(1, sizeof(struct context_odbc));
    if (ret == NULL)
    {
        return NULL;
    }

    char sql[500];
    if(iDBid==SQLITE3)
        sprintf(sql,"CREATE TABLE OPCUA_HIST_DATA(NAME_EN varchar(100), VALUE TEXT, TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (NAME_EN, TIMESTAMP));");
    else
        sprintf(sql,"CREATE TABLE COMMDATA.OPCUA_HIST_DATA(NAME_EN varchar(100), VALUE TEXT, TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (NAME_EN, TIMESTAMP));");

    SQLExecDirect(hstmt, (SQLCHAR *) sql, SQL_NTS);

    ret->henv = henv;
    ret->hdbc = hdbc;
    ret->hstmt = hstmt;
    ret->DBtype = iDBid;

    return ret;
}

#include <stdio.h>
#include "cJSON.h"

static char * SetJSON(UA_DataValue *data)
{
    int arrayLength;
    cJSON* cjson_root = NULL;
    cJSON* cjson_data = NULL;
    char* str = NULL;
    arrayLength = data->value.arrayLength;

    /* 创建一个JSON数据对象(链表头结点) */
    cjson_root = cJSON_CreateObject();

    /* 添加一条整数类型的JSON数据(添加一个链表节点) */
    cJSON_AddNumberToObject(cjson_root, "arrayLength", arrayLength);

    /* 添加一条字符串类型的JSON数据(添加一个链表节点) */
    if (data->value.type == &UA_TYPES[UA_TYPES_DOUBLE])
    {
        cJSON_AddStringToObject(cjson_root, "type", "double");
        /* 添加一个数组类型的JSON数据(添加一个链表节点) */
        cjson_data = cJSON_CreateDoubleArray((const double *)data->value.data,arrayLength==0?1:arrayLength);
        cJSON_AddItemToObject(cjson_root, "data", cjson_data);
    }
    else if (data->value.type == &UA_TYPES[UA_TYPES_FLOAT])
    {
        cJSON_AddStringToObject(cjson_root, "type", "float");
        /* 添加一个数组类型的JSON数据(添加一个链表节点) */
        cjson_data = cJSON_CreateFloatArray((const float *)data->value.data,arrayLength==0?1:arrayLength);
        cJSON_AddItemToObject(cjson_root, "data", cjson_data);
    }
    else if (data->value.type == &UA_TYPES[UA_TYPES_INT32])
    {
        cJSON_AddStringToObject(cjson_root, "type", "int32");
        /* 添加一个数组类型的JSON数据(添加一个链表节点) */
        cjson_data = cJSON_CreateIntArray((const int *)data->value.data,arrayLength==0?1:arrayLength);
        cJSON_AddItemToObject(cjson_root, "data", cjson_data);
    }
    else if (data->value.type == &UA_TYPES[UA_TYPES_INT16])
    {
        cJSON_AddStringToObject(cjson_root, "type", "int16");

        cjson_data = cJSON_CreateArray();
        UA_Int16 * pVal = (UA_Int16*)data->value.data;
        for(int i=0;i<arrayLength==0?1:arrayLength;i++)
        {
            char sVal[32]={0};
            sprintf(sVal,"%d",pVal[i]);
            cJSON_AddItemToArray(cjson_data, cJSON_CreateString( sVal ));
        }
        cJSON_AddItemToObject(cjson_root, "data", cjson_data);
    }
    else if (data->value.type == &UA_TYPES[UA_TYPES_BYTE])
    {
        cJSON_AddStringToObject(cjson_root, "type", "byte");

        cjson_data = cJSON_CreateArray();
        UA_Byte * pVal = (UA_Byte*)data->value.data;
        for(int i=0;i<arrayLength==0?1:arrayLength;i++)
        {
            char sVal[32]={0};
            sprintf(sVal,"%d",pVal[i]);
            cJSON_AddItemToArray(cjson_data, cJSON_CreateString( sVal ));
        }
        cJSON_AddItemToObject(cjson_root, "data", cjson_data);
    }
    else if (data->value.type == &UA_TYPES[UA_TYPES_BOOLEAN])
    {
        cJSON_AddStringToObject(cjson_root, "type", "boolean");

        cjson_data = cJSON_CreateArray();
        UA_Boolean * pVal = (UA_Boolean*)data->value.data;
        for(int i=0;i<arrayLength==0?1:arrayLength;i++)
        {
            char sVal[32]={0};
            sprintf(sVal,"%d",pVal[i]);
            cJSON_AddItemToArray(cjson_data, cJSON_CreateString( sVal ));
        }
        cJSON_AddItemToObject(cjson_root, "data", cjson_data);
    }

    /* 打印JSON对象(整条链表)的所有数据 */
    str = cJSON_Print(cjson_root);
    //printf("%s\n", str);
    // 释放内存
    cJSON_Delete(cjson_root);
    return str;
}

static int GetJSON(char *message,ODBC_DATA * odbcVal)
{
    cJSON* cjson_root = NULL;
    cJSON* cjson_type = NULL;
    cJSON* cjson_arrayLength = NULL;
    cJSON* cjson_data = NULL;
    int    data_array_size = 0, i = 0;
    cJSON* cjson_data_item = NULL;

    /* 解析整段JSO数据 */
    cjson_root = cJSON_Parse(message);
    if(cjson_root == NULL)
    {
        //printf("parse fail.\n");
        return -1;
    }

    /* 依次根据名称提取JSON数据（键值对） */
    cjson_type = cJSON_GetObjectItem(cjson_root, "type");
    cjson_arrayLength = cJSON_GetObjectItem(cjson_root, "arrayLength");

    //printf("type: %s\n", cjson_type->valuestring);
    //printf("arrayLength:%d\n", cjson_arrayLength->valueint);

    odbcVal->arrayLength = cjson_arrayLength->valueint;
    if(strcmp(cjson_type->valuestring,"double")==0)
    {
        odbcVal->type = UA_TYPES_DOUBLE;
    }
    else if(strcmp(cjson_type->valuestring,"float")==0)
    {
        odbcVal->type = UA_TYPES_FLOAT;
    }
    else if(strcmp(cjson_type->valuestring,"int32")==0)
    {
        odbcVal->type = UA_TYPES_INT32;
    }
    else if(strcmp(cjson_type->valuestring,"int16")==0)
    {
        odbcVal->type = UA_TYPES_INT16;
    }
    else if(strcmp(cjson_type->valuestring,"byte")==0)
    {
        odbcVal->type = UA_TYPES_BYTE;
    }
    else if(strcmp(cjson_type->valuestring,"boolean")==0)
    {
        odbcVal->type = UA_TYPES_BOOLEAN;
    }

    /* 解析数组 */
    cjson_data = cJSON_GetObjectItem(cjson_root, "data");
    data_array_size = cJSON_GetArraySize(cjson_data);
    //printf("data:[");
    for(i = 0; i < data_array_size; i++)
    {
        cjson_data_item = cJSON_GetArrayItem(cjson_data, i);
        /*if((strcmp(cjson_type->valuestring,"double")==0) || (strcmp(cjson_type->valuestring,"float")==0))
            printf("%f,", cjson_data_item->valuedouble);
        else
            printf("%d,", cjson_data_item->valueint);*/
        if(odbcVal->type = UA_TYPES_DOUBLE)
        {
            UA_Double *pVal = (UA_Double*)odbcVal->data;
            pVal[i] = cjson_data_item->valuedouble;
        }
        else if(odbcVal->type = UA_TYPES_FLOAT)
        {
            UA_Float *pVal = (UA_Float*)odbcVal->data;
            pVal[i] = cjson_data_item->valuedouble;
        }
        else if(odbcVal->type = UA_TYPES_INT32)
        {
            UA_Int32 *pVal = (UA_Int32*)odbcVal->data;
            pVal[i] = cjson_data_item->valueint;
        }
        else if(odbcVal->type = UA_TYPES_INT16)
        {
            UA_Int16 *pVal = (UA_Int16*)odbcVal->data;
            pVal[i] = cjson_data_item->valueint;
        }
        else if(odbcVal->type = UA_TYPES_BYTE)
        {
            UA_Byte *pVal = (UA_Byte*)odbcVal->data;
            pVal[i] = cjson_data_item->valueint;
        }
        else if(odbcVal->type = UA_TYPES_BOOLEAN)
        {
            UA_Boolean *pVal = (UA_Boolean*)odbcVal->data;
            pVal[i] = cjson_data_item->valueint;
        }

    }
    //printf("\b]\n");
    cJSON_Delete(cjson_root);

    return 0;
}


static UA_StatusCode
serverSetHistoryData_odbcHDB(UA_Server *server,
        void *hdbContext,
        const UA_NodeId *sessionId,
        void *sessionContext,
        const UA_NodeId *nodeId,
        UA_Boolean historizing,
        const UA_DataValue *data)
{
    struct context_odbc* context = (struct context_odbc*)hdbContext;

    char *value=SetJSON(data);
    char sql1[10000]={0};

    time_t tNow =time(NULL);
    struct tm ptm = { 0 };
    localtime_r(&tNow, &ptm);
    struct tm *tt=&ptm;
    char timestamp[32]={0};
    sprintf(timestamp,"%04d-%02d-%02d %02d:%02d:%02d",1900+tt->tm_year,1+tt->tm_mon,tt->tm_mday,tt->tm_hour,tt->tm_min,tt->tm_sec);

    if(context->DBtype==SQLITE3)
        sprintf(sql1,"insert into OPCUA_HIST_DATA values('%s','%s','%s')",(char*)nodeId->identifier.string.data,value,timestamp);
    else
        sprintf(sql1,"insert into COMMDATA.OPCUA_HIST_DATA values('%s','%s','%s')",(char*)nodeId->identifier.string.data,value,timestamp);
    opcLock.Lock();
    SQLExecDirect(context->hstmt, (SQLCHAR *) sql1, SQL_NTS);
    opcLock.Unlock();
    free(value);

    return UA_STATUSCODE_GOOD;
}


static size_t
getEnd_odbcHDB(UA_Server *server,
        void *hdbContext,
        const UA_NodeId *sessionId,
        void *sessionContext,
        const UA_NodeId *nodeId)
{
    return END_OF_DATA;
}


static size_t
lastIndex_odbcHDB(UA_Server *server,
    void *hdbContext,
    const UA_NodeId *sessionId,
    void *sessionContext,
    const UA_NodeId *nodeId)
{
    struct context_odbc* context = (struct context_odbc*)hdbContext;

    SQLCHAR dbResult[20][208]= {{0},{0} };
    SQLLEN  dbResult_ind[20] ={ 0};

    size_t result;
    opcLock.Lock();

    char query[QUERY_BUFFER_SIZE]={0};
    if(context->DBtype==SQLITE3)
        sprintf(query,"SELECT TIMESTAMP FROM OPCUA_HIST_DATA WHERE NAME_EN='%s' ORDER BY TIMESTAMP DESC LIMIT 1",(char*)nodeId->identifier.string.data);
    else
        sprintf(query,"SELECT TIMESTAMP FROM COMMDATA.OPCUA_HIST_DATA WHERE NAME_EN='%s' ORDER BY TIMESTAMP DESC LIMIT 1",(char*)nodeId->identifier.string.data);

    SQLExecDirect(context->hstmt, (SQLCHAR *) query, SQL_NTS);
    for(int i=0;i<1;i++)
    {
        SQLBindCol(context->hstmt, 1+i, SQL_C_CHAR, &dbResult[i], sizeof(dbResult[i]), &dbResult_ind[i]);
    }
    while(SQLFetch(context->hstmt) != SQL_NO_DATA)
    {
        result = convertTimestampStringToUnixSeconds(dbResult[0]);
    }
    SQLFreeStmt(context->hstmt, SQL_CLOSE);
    opcLock.Unlock();
    return result;
}

static size_t
firstIndex_odbcHDB(UA_Server *server,
    void *hdbContext,
    const UA_NodeId *sessionId,
    void *sessionContext,
    const UA_NodeId *nodeId)
{
    struct context_odbc* context = (struct context_odbc*)hdbContext;

    SQLCHAR dbResult[20][208]= {{0},{0} };
    SQLLEN  dbResult_ind[20] ={ 0};

    size_t result;
    opcLock.Lock();

    char query[QUERY_BUFFER_SIZE]={0};
    if(context->DBtype==SQLITE3)
        sprintf(query,"SELECT TIMESTAMP FROM OPCUA_HIST_DATA WHERE NAME_EN='%s' ORDER BY TIMESTAMP LIMIT 1",(char*)nodeId->identifier.string.data);
    else
        sprintf(query,"SELECT TIMESTAMP FROM COMMDATA.OPCUA_HIST_DATA WHERE NAME_EN='%s' ORDER BY TIMESTAMP LIMIT 1",(char*)nodeId->identifier.string.data);

    SQLExecDirect(context->hstmt, (SQLCHAR *) query, SQL_NTS);
    for(int i=0;i<1;i++)
    {
        SQLBindCol(context->hstmt, 1+i, SQL_C_CHAR, &dbResult[i], sizeof(dbResult[i]), &dbResult_ind[i]);
    }
    while(SQLFetch(context->hstmt) != SQL_NO_DATA)
    {
        result = convertTimestampStringToUnixSeconds(dbResult[0]);
    }
    SQLFreeStmt(context->hstmt, SQL_CLOSE);
    opcLock.Unlock();
    return result;
}


static UA_Boolean
search_odbc(struct context_odbc* context,const UA_NodeId *nodeId,
        UA_Int64 unixSeconds, MatchStrategy strategy,
        size_t *index)
{
    SQLCHAR dbResult[20][208]= {{0},{0} };
    SQLLEN  dbResult_ind[20] ={ 0};

    *index = END_OF_DATA; // TODO
    opcLock.Lock();

    char query[QUERY_BUFFER_SIZE]={0};
    if(context->DBtype==SQLITE3)
        sprintf(query,"SELECT TIMESTAMP FROM OPCUA_HIST_DATA WHERE NAME_EN='%s' AND ",(char*)nodeId->identifier.string.data);
    else
        sprintf(query,"SELECT TIMESTAMP FROM COMMDATA.OPCUA_HIST_DATA WHERE NAME_EN='%s' AND ",(char*)nodeId->identifier.string.data);

    switch (strategy)
    {
    case MATCH_EQUAL_OR_AFTER:
        strncat(query, "TIMESTAMP>='", QUERY_BUFFER_SIZE);
        strncat(query, convertUnixSecondsToTimestampString(unixSeconds), QUERY_BUFFER_SIZE);
        strncat(query, "' ORDER BY TIMESTAMP LIMIT 1", QUERY_BUFFER_SIZE);
        break;
    case MATCH_AFTER:
        strncat(query, "TIMESTAMP>'", QUERY_BUFFER_SIZE);
        strncat(query, convertUnixSecondsToTimestampString(unixSeconds), QUERY_BUFFER_SIZE);
        strncat(query, "' ORDER BY TIMESTAMP LIMIT 1", QUERY_BUFFER_SIZE);
        break;
    case MATCH_EQUAL_OR_BEFORE:
        strncat(query, "TIMESTAMP<='", QUERY_BUFFER_SIZE);
        strncat(query, convertUnixSecondsToTimestampString(unixSeconds), QUERY_BUFFER_SIZE);
        strncat(query, "' ORDER BY TIMESTAMP DESC LIMIT 1", QUERY_BUFFER_SIZE);
        break;
    case MATCH_BEFORE:
        strncat(query, "TIMESTAMP<'", QUERY_BUFFER_SIZE);
        strncat(query, convertUnixSecondsToTimestampString(unixSeconds), QUERY_BUFFER_SIZE);
        strncat(query, "' ORDER BY TIMESTAMP DESC LIMIT 1", QUERY_BUFFER_SIZE);
        break;
    default:
        return false;
    }
    SQLExecDirect(context->hstmt, (SQLCHAR *) query, SQL_NTS);
    for(int i=0;i<1;i++)
    {
        SQLBindCol(context->hstmt, 1+i, SQL_C_CHAR, &dbResult[i], sizeof(dbResult[i]), &dbResult_ind[i]);
    }
    while(SQLFetch(context->hstmt) != SQL_NO_DATA)
    {
        *index = convertTimestampStringToUnixSeconds(dbResult[0]);
    }
    SQLFreeStmt(context->hstmt, SQL_CLOSE);
    opcLock.Unlock();
    //printf("%s\n",query);
    return true;
}

static size_t
getDateTimeMatch_odbcHDB(UA_Server *server,
    void *hdbContext,
    const UA_NodeId *sessionId,
    void *sessionContext,
    const UA_NodeId *nodeId,
    const UA_DateTime timestamp,
    const MatchStrategy strategy)
{
    struct context_odbc* context = (struct context_odbc*)hdbContext;

    UA_Int64 ts = UA_DateTime_toUnixTime(timestamp);

    size_t result = END_OF_DATA;

    UA_Boolean res = search_odbc(context, nodeId,ts, strategy, &result);

    return result;
}


static size_t
resultSize_odbcHDB(UA_Server *server,
    void *hdbContext,
    const UA_NodeId *sessionId,
    void *sessionContext,
    const UA_NodeId *nodeId,
    size_t startIndex,
    size_t endIndex)
{
    struct context_odbc* context = (struct context_odbc*)hdbContext;

    SQLCHAR dbResult[20][208]= {{0},{0} };
    SQLLEN  dbResult_ind[20] ={ 0};

    size_t result = 0;
    opcLock.Lock();

    char query[QUERY_BUFFER_SIZE]={0};
    if(context->DBtype==SQLITE3)
        sprintf(query,"SELECT COUNT(*) FROM OPCUA_HIST_DATA WHERE (NAME_EN='%s') AND ",(char*)nodeId->identifier.string.data);
    else
        sprintf(query,"SELECT COUNT(*) FROM COMMDATA.OPCUA_HIST_DATA WHERE (NAME_EN='%s') AND ",(char*)nodeId->identifier.string.data);

    strncat(query, "(TIMESTAMP>='", QUERY_BUFFER_SIZE);
    strncat(query, convertUnixSecondsToTimestampString(startIndex), QUERY_BUFFER_SIZE);
    strncat(query, "') AND (TIMESTAMP<='", QUERY_BUFFER_SIZE);
    strncat(query, convertUnixSecondsToTimestampString(endIndex), QUERY_BUFFER_SIZE);
    strncat(query, "') ", QUERY_BUFFER_SIZE);

    SQLExecDirect(context->hstmt, (SQLCHAR *) query, SQL_NTS);
    for(int i=0;i<1;i++)
    {
        SQLBindCol(context->hstmt, 1+i, SQL_C_CHAR, &dbResult[i], sizeof(dbResult[i]), &dbResult_ind[i]);
    }
    while(SQLFetch(context->hstmt) != SQL_NO_DATA)
    {
        result = atoi(dbResult[0]);
    }
    SQLFreeStmt(context->hstmt, SQL_CLOSE);
    opcLock.Unlock();
    //printf("COUNT(*)=%d\n",result);

    return result;
}


static int copyDataValues_callback(void* result, char *sVal, char *data)
{
    UA_DataValue dv;
    UA_DataValue_init(&dv);

    dv.status = UA_STATUSCODE_GOOD;
    dv.hasStatus = true;

    dv.sourceTimestamp = UA_DateTime_fromUnixTime(convertTimestampStringToUnixSeconds(data));
    dv.hasSourceTimestamp = true;

    dv.serverTimestamp = dv.sourceTimestamp;
    dv.hasServerTimestamp = true;

    ODBC_DATA odbcdata;
    ODBC_DATA * odbcval = &odbcdata;

    GetJSON(sVal,odbcval);

    if(odbcval->arrayLength)
        UA_Variant_setArray(&dv.value, odbcval->data,odbcval->arrayLength, &UA_TYPES[odbcval->type]);
    else
        UA_Variant_setScalar(&dv.value, odbcval->data, &UA_TYPES[odbcval->type]);

    dv.hasValue = true;

    context_copyDataValues* ctx = (context_copyDataValues*)result;

    UA_DataValue_copy(&dv, &ctx->values[ctx->counter]);

    ctx->counter++;

    if (ctx->counter == ctx->maxValues)
    {
        return 1;
    }
    else
    {
        return 0;
    }
}

static UA_StatusCode
copyDataValues_odbcHDB(UA_Server *server,
    void *hdbContext,
    const UA_NodeId *sessionId,
    void *sessionContext,
    const UA_NodeId *nodeId,
    size_t startIndex,
    size_t endIndex,
    UA_Boolean reverse,
    size_t maxValues,
    UA_NumericRange range,
    UA_Boolean releaseContinuationPoints,
    const UA_ByteString *continuationPoint,
    UA_ByteString *outContinuationPoint,
    size_t *providedValues,
    UA_DataValue *values)
{
    size_t skip = 0;
    if (continuationPoint->length > 0) {
        if (continuationPoint->length == sizeof(size_t)) {
            skip = *((size_t*)(continuationPoint->data));
        } else {
            return UA_STATUSCODE_BADCONTINUATIONPOINTINVALID;
        }
    }

    struct context_odbc* context = (struct context_odbc*)hdbContext;
    SQLCHAR dbResult[2][10000]= {{0},{0} };
    SQLLEN  dbResult_ind[2] ={ 0};

    char query[QUERY_BUFFER_SIZE];

    opcLock.Lock();

    if(context->DBtype==SQLITE3)
        sprintf(query,"SELECT TIMESTAMP,VALUE FROM OPCUA_HIST_DATA WHERE (NAME_EN='%s') AND ",(char*)nodeId->identifier.string.data);
    else
        sprintf(query,"SELECT TIMESTAMP,VALUE FROM COMMDATA.OPCUA_HIST_DATA WHERE (NAME_EN='%s') AND ",(char*)nodeId->identifier.string.data);

    strncat(query, "(TIMESTAMP>='", QUERY_BUFFER_SIZE);
    strncat(query, convertUnixSecondsToTimestampString(startIndex), QUERY_BUFFER_SIZE);
    strncat(query, "') AND (TIMESTAMP<='", QUERY_BUFFER_SIZE);
    strncat(query, convertUnixSecondsToTimestampString(endIndex), QUERY_BUFFER_SIZE);
    strncat(query, "') ", QUERY_BUFFER_SIZE);

    //printf("%s\n",query);
    SQLExecDirect(context->hstmt, (SQLCHAR *) query, SQL_NTS);
    for(int i=0;i<2;i++)
    {
        SQLBindCol(context->hstmt, 1+i, SQL_C_CHAR, &dbResult[i], sizeof(dbResult[i]), &dbResult_ind[i]);
    }
    context_copyDataValues ctx;
    ctx.maxValues = maxValues;
    ctx.counter = 0;
    ctx.values = values;
    //printf("maxSizeValues=%d reverse=%d startIndex=%d endIndex=%d\n",maxValues,reverse,startIndex,endIndex);

    size_t skipedValues = 0;

    while(SQLFetch(context->hstmt) != SQL_NO_DATA)
    {
        if (skipedValues++ >= skip) {
            if(copyDataValues_callback(&ctx,(char*)dbResult[1],(char*)dbResult[0]))
            {
                if(providedValues) *providedValues = ctx.counter;
                break;
            }
        }
    }
    SQLFreeStmt(context->hstmt, SQL_CLOSE);
    opcLock.Unlock();

    size_t size=resultSize_odbcHDB(server,hdbContext,sessionId,sessionContext,nodeId,startIndex, endIndex);
    if ((!reverse && (size-skip) > ctx.counter)) {
        outContinuationPoint->length = sizeof(size_t);
        size_t t = sizeof(size_t);
        outContinuationPoint->data = (UA_Byte*)UA_malloc(t);
        *((size_t*)(outContinuationPoint->data)) = skip + ctx.counter;
    }


    return UA_STATUSCODE_GOOD;
}

static const UA_DataValue*
getDataValue_odbcHDB(UA_Server *server,
    void *hdbContext,
    const UA_NodeId *sessionId,
    void *sessionContext,
    const UA_NodeId *nodeId,
        size_t index)
{
    struct context_odbc* context = (struct context_odbc*)hdbContext;

    return NULL;
}


static UA_Boolean
boundSupported_odbcHDB(UA_Server *server,
    void *hdbContext,
    const UA_NodeId *sessionId,
    void *sessionContext,
    const UA_NodeId *nodeId)
{
        return false; // We don't support returning bounds in this demo
}


static UA_Boolean
timestampsToReturnSupported_odbcHDB(UA_Server *server,
    void *hdbContext,
    const UA_NodeId *sessionId,
    void *sessionContext,
    const UA_NodeId *nodeId,
    const UA_TimestampsToReturn timestampsToReturn)
{
    return true;
}


UA_HistoryDataBackend
UA_HistoryDataBackend_odbc()
{
    UA_HistoryDataBackend result;
    memset(&result, 0, sizeof(UA_HistoryDataBackend));
    result.serverSetHistoryData = &serverSetHistoryData_odbcHDB;
    result.resultSize = &resultSize_odbcHDB;
    result.getEnd = &getEnd_odbcHDB;
    result.lastIndex = &lastIndex_odbcHDB;
    result.firstIndex = &firstIndex_odbcHDB;
    result.getDateTimeMatch = &getDateTimeMatch_odbcHDB;
    result.copyDataValues = &copyDataValues_odbcHDB;
    result.getDataValue = &getDataValue_odbcHDB;
    result.boundSupported = &boundSupported_odbcHDB;
    result.timestampsToReturnSupported = &timestampsToReturnSupported_odbcHDB;
    result.deleteMembers = NULL; // We don't support deleting in this demo
    result.getHistoryData = NULL; // We don't support the high level API in this demo
    result.context = generateContext_odbc();
    return result;
}

#endif
使用方法可以参考：学习open62541 --- [58] 使用数据库存储历史数据_open62541历史事件-CSDN博客