在PLCnext C++项目中用SQLite存储过程数据

本文描述PLCnext控制器预装的SQLite数据库引擎如何存储通过全局数据空间（GDS）提供的过程数据。数据库以标准化方式存储过程数据，可通过SFTP导出到其他系统。

请确保plcncli工具版本与控制器固件版本匹配。

创建Eclipse C++项目

在Eclipse中按PLCnext Info Center说明创建新C++项目，属性如下：

项目名：CppDB
组件名：DBComponent
程序名：DBProgram
项目命名空间：CppDB

其他名称也可，但统一名称简化教程。

在项目中创建新文件夹（与src同级），命名'cmake'。文件夹内创建文件'FindSqlite.cmake'并插入以下内容：

FindSqlite.cmake

# Copyright (c) 2018 PHOENIX CONTACT GmbH &amp; Co. KG # Created by Björn sauer # # - Find Sqlite # Find the Sqlite headers and libraries. # # Defined Variables: # Sqlite_INCLUDE_DIRS - Where to find sqlite3.h. # Sqlite_LIBRARIES - The sqlite library. # Sqlite_FOUND - True if sqlite found. # # Defined Targets: # Sqlite::Sqlite find_path(Sqlite_INCLUDE_DIR NAMES sqlite3.h) find_library(Sqlite_LIBRARY NAMES sqlite3) include(FindPackageHandleStandardArgs) find_package_handle_standard_args(Sqlite DEFAULT_MSG Sqlite_LIBRARY Sqlite_INCLUDE_DIR) if(Sqlite_FOUND) set(Sqlite_INCLUDE_DIRS "${Sqlite_INCLUDE_DIR}") set(Sqlite_LIBRARIES "${Sqlite_LIBRARY}") mark_as_advanced(Sqlite_INCLUDE_DIRS Sqlite_LIBRARIES) if(NOT TARGET Sqlite::Sqlite) add_library(Sqlite::Sqlite UNKNOWN IMPORTED) set_target_properties(Sqlite::Sqlite PROPERTIES IMPORTED_LOCATION "${Sqlite_LIBRARY}" INTERFACE_INCLUDE_DIRECTORIES "${Sqlite_INCLUDE_DIRS}") endif() endif()

替换DBComponent.cpp和DBComponent.hpp内容为以下：

DBComponent.hpp

#pragma once #include "Arp/System/Core/Arp.h" #include "Arp/System/Acf/ComponentBase.hpp" #include "Arp/System/Acf/IApplication.hpp" #include "Arp/Plc/Commons/Esm/ProgramComponentBase.hpp" #include "DBComponentProgramProvider.hpp" #include "Arp/Plc/Commons/Meta/MetaLibraryBase.hpp" #include "Arp/System/Commons/Logging.h" #include "CppDBLibrary.hpp" #include "Arp/System/Acf/IControllerComponent.hpp" #include "Arp/System/Commons/Threading/WorkerThread.hpp" #include  namespace CppDB { using namespace Arp; using namespace Arp::System::Acf; using namespace Arp::Plc::Commons::Esm; using namespace Arp::Plc::Commons::Meta; //#component class DBComponent : public ComponentBase, public IControllerComponent, public ProgramComponentBase, private Loggable { public: // typedefs public: // construction/destruction DBComponent(IApplication& application, const String& name); virtual ~DBComponent() = default; public: // IComponent operations void Initialize() override; void LoadConfig() override; void SetupConfig() override; void ResetConfig() override; void PowerDown() override; public: // IControllerComponent operations void Start(void) override; void Stop(void) override; public: // ProgramComponentBase operations void RegisterComponentPorts() override; void WriteToDB(); private: // methods DBComponent(const DBComponent& arg) = delete; DBComponent& operator= (const DBComponent& arg) = delete; public: // static factory operations static IComponent::Ptr Create(Arp::System::Acf::IApplication& application, const String& name); private: // fields DBComponentProgramProvider programProvider; WorkerThread workerThread; private: // static fields static const int workerThreadIdleTimeWrite = 10; // 10 ms public: // Ports //#port //#attributes(Input) int16 control = 0; //#port //#attributes(Input) int16 intArray[10] {}; // INT in PLCnext Engineer //#port //#attributes(Input) float32 floatArray[10] {}; // REAL in PLCnext Engineer //#port //#attributes(Output) int16 status = 0; }; // inline methods of class DBComponent inline DBComponent::DBComponent(IApplication& application, const String& name) : ComponentBase(application, ::CppDB::CppDBLibrary::GetInstance(), name, ComponentCategory::Custom) , programProvider(*this) , workerThread(make_delegate(this, &DBComponent::WriteToDB), workerThreadIdleTimeWrite, "CppDB.WriteToDatabase") // WorkerThread , ProgramComponentBase(::CppDB::CppDBLibrary::GetInstance().GetNamespace(), programProvider) { } inline IComponent::Ptr DBComponent::Create(Arp::System::Acf::IApplication& application, const String& name) { return IComponent::Ptr(new DBComponent(application, name)); } } // end of namespace CppDB

DBComponent.cpp

#include "DBComponent.hpp" #include "Arp/Plc/Commons/Esm/ProgramComponentBase.hpp" namespace CppDB { sqlite3 *db = nullptr; // pointer to the database sqlite3_stmt * stmt = nullptr; // needed to prepare std::string sql = ""; // sqlite statement int rc = 0; // for error codes of the database void DBComponent::Initialize() { // never remove next line ProgramComponentBase::Initialize(); // subscribe events from the event system (Nm) here } void DBComponent::LoadConfig() { // load project config here } void DBComponent::SetupConfig() { // never remove next line ProgramComponentBase::SetupConfig(); // setup project config here } void DBComponent::ResetConfig() { // never remove next line ProgramComponentBase::ResetConfig(); // implement this inverse to SetupConfig() and LoadConfig() } #pragma region IControllerComponent operations void DBComponent::Start() { // start your threads here accessing any Arp components or services // open the database connection // the database path (/opt/plcnext/) and name (database) could be modified rc = sqlite3_open("/opt/plcnext/database.db", &db); if( rc ) { Log::Error("DB - 1 - {}", sqlite3_errmsg(db)); status = 1; return; } else{ // modify the database behaviour with pragma statements sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, NULL); sqlite3_exec(db, "PRAGMA journal_mode = MEMORY", NULL, NULL, NULL); sqlite3_exec(db, "PRAGMA temp_store = MEMORY", NULL, NULL, NULL); // create tables sql = "CREATE TABLE IF NOT EXISTS tb0 (" "_id INTEGER PRIMARY KEY, " "value1 INTEGER DEFAULT 0, " "value2 REAL DEFAULT 0.0 );"; // execute the sql-statement rc = sqlite3_exec(db, sql.c_str(), 0, 0, 0); if(rc) { Log::Error("DB - 3 - {}", sqlite3_errmsg(db)); status = 3; } } // prepare sql-statement sql = "INSERT INTO tb0 (value1, value2) VALUES (?,?)"; rc = sqlite3_prepare_v2(db, sql.c_str(), strlen(sql.c_str()), &stmt, nullptr); if(rc) { Log::Error("DB - 4 - {}", sqlite3_errmsg(db)); status = 4; } // start the WorkerThread this->workerThread.Start(); } void DBComponent::Stop() { // stop your threads here accessing any Arp components or services // delete the prepared sqlite statements rc = sqlite3_finalize(stmt); { Log::Error("DB - 1 - {}", sqlite3_errmsg(db)); status = 1; } // close the database connection rc = sqlite3_close(db); { Log::Error("DB - 1 - {}", sqlite3_errmsg(db)); status = 1; } // stop the WorkerThread this->workerThread.Stop(); } #pragma endregion void DBComponent::PowerDown() { // implement this only if data must be retained even on power down event // Available with 2021.6 FW } void DBComponent::WriteToDB() { // store data in the database if(control == 1) { // start transaction rc = sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, NULL); if(rc) { Log::Error("DB - 5 - {}", sqlite3_errmsg(db)); status = 5; } // iterate over the arrays for(int i = 0; i < 10; i++) { // bind values to the prepared statement rc = sqlite3_bind_int(stmt, 1, intArray[i]); if(rc) { Log::Error("DB - 6 - {}", sqlite3_errmsg(db)); status = 6; } rc = sqlite3_bind_double(stmt, 2, floatArray[i]); if(rc) { Log::Error("DB - 6 - {}", sqlite3_errmsg(db)); status = 6; } // execute the sqlite statement and reset the prepared statement rc = sqlite3_step(stmt); rc = sqlite3_clear_bindings(stmt); if(rc) { Log::Error("DB - 6 - {}", sqlite3_errmsg(db)); status = 6; } rc = sqlite3_reset(stmt); if(rc) { Log::Error("DB - 6 - {}", sqlite3_errmsg(db)); status = 6; } } // end transaction rc = sqlite3_exec(db, "END TRANSACTION", NULL, NULL, NULL); if(rc) { Log::Error("DB - 5 - {}", sqlite3_errmsg(db)); status = 5; } } // delete the database entries if(control == 2) { // begin transaction rc = sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, NULL); if(rc) { Log::Error("DB - 5 - {}", sqlite3_errmsg(db)); status = 5; } rc = sqlite3_exec(db, "DELETE FROM tb0", 0, 0, 0); if(rc) { Log::Error("DB - 7 - {}", sqlite3_errmsg(db)); status = 7; } // end transaction sqlite3_exec(db, "END TRANSACTION", NULL, NULL, NULL); if(rc) { Log::Error("DB - 5 - {}", sqlite3_errmsg(db)); status = 5; } // release the used memory rc = sqlite3_exec(db, "VACUUM", 0, 0, 0); if(rc) { Log::Error("DB - 8 - {}", sqlite3_errmsg(db)); status = 8; } } } } // end of namespace CppDB

此后构建项目。生成的PLCnext库在项目目录（C:\Users\eclipse-workspace\CppDB\bin）。

解释

此法用WorkerThread处理写操作。这是低优先级线程，重复执行线程代码直到调用Stop()。线程中检查是否有新数据需存入数据库并存储。执行后WorkerThread等待指定时间（此处：10 ms）。

借助'control'端口触发不同数据库操作。待存数据通过'intArray'和'floatArray'端口提供。

创建简单IEC程序：

IF iControl = 1 THEN iControl := 0; END_IF; IF xWrite THEN arrInt[0] := 0; arrInt[1] := 1; arrInt[2] := 2; arrInt[3] := 3; arrInt[4] := 4; arrInt[5] := 5; arrInt[6] := 6; arrInt[7] := 7; arrInt[8] := 8; arrInt[9] := 9; arrReal[0] := 9.0; arrReal[1] := 8.0; arrReal[2] := 7.0; arrReal[3] := 6.0; arrReal[4] := 5.0; arrReal[5] := 4.0; arrReal[6] := 3.0; arrReal[7] := 2.0; arrReal[8] := 1.0; arrReal[9] := 0.0; iControl := 1; xWrite := FALSE; END_IF;

最后连接端口：

现在可编译项目并发送到连接的PLC。在'live-mode'中，通过给'iControl'变量赋不同值与数据库交互。

数据库'database.db'在控制器目录/opt/plcnext创建。可用WinSCP等工具访问。用DB Browser (SQLite)工具检查数据库内容：

在PLCnext C++项目中用SQLite存储过程数据

创建Eclipse C++项目

解释

更多信息