This article describes how the SQLite database engine already installed on PLCnext Controllers could be used to store data provided via the Global Data Space (GDS). The database enables the storage of process data in a standardized way and could be exported to other systems with SFTP.
Please make sure, that the plcncli tool version matches the firmware version of your controller.
Create an Eclipse C++ Project
Create a new C++ project in Eclipse, following the instructions from the PLCnext Info Center with the following properties:
- project name: CppDB
- component name: DBComponent
- program name: DBProgram
- project namespace: CppDB
Other names would also be fine, however, a common name simplifies the tutorial.
Create a new folder (same hirarchy as the src folder) in the project and name it 'cmake'. Inside the folder create a file and name it 'FindSqlite.cmake' and insert the follwing content into it.
FindSqlite.cmake
# Copyright (c) 2018 PHOENIX CONTACT GmbH & 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()
Replace the content of the files DBComponent.cpp and DBComponent.hpp with the following:
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 <sqlite3.h>
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<DBComponent>
{
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
Thereafter, build the project. The created PLCnext Library can be found in the project directory (C:\Users\eclipse-workspace\CppDB\bin).
Explanation
In this approach a WorkerThread is used to handle the writing operation. This is a thread with low priority repeating the execution of the threaded code until Stop() is called. In the thread we will check if new data for the database is available and store the data. After the execution the WorkerThreads waits for a specified time (here: 10 ms).
With the help of the 'control' port, we can trigger different database operations. The data that should be stored is provided with the ports 'intArray' and 'floatArray'.
Lets create a simple IEC program:
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;
Finally, we have to connect the ports:
Now, we can compile the project and send it to a connected PLC. In the 'live-mode', we can interact with the database, by assigning different values to the 'iControl' variable.
A database 'database.db' is created in the controllers directory /opt/plcnext. We can access it by tools like WinSCP. We can check the database content with the tool DB Browser (SQLite):
Leave a Reply
You must be logged in to post a comment.