Makers Blog

How to store data in SQLite in a PLCnext C++ project

nilshettig 24 June 2022 min. read
427 views 2 comments

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'.

Image1.png

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;
Image2.png

Finally, we have to connect the ports:

Image3.png

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):

Image4.png

Further information

SQlite pragma statements

Note:

The Makers Blog shows applications and user stories of community members that are not tested or reviewed by Phoenix Contact. Use them at your own risk.

Discussion

Please login/register to comment
Login/Register
Newsletter
Never miss a new article
Sign up for the newsletter
Never miss news about PLCnext Technology
Get interesting content via newsletter four times a year
Receive exclusive information before all other users