Makers Blog

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

nilshettig 24 June 2022 min. read
1,026 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

Leave a Reply

Janne Vesen 05.05.2022

Hi Nils, Thank you for the helpfull blog post on how to expand on "How to use SQLite in a PLCnext C++ project". I have problems compiling the Eclipse project: WorkerThread (Type 'WorkerThread' could not be resolved) and inline commands (Member declaration not found). Is it possible that the code is a little out of date and thats why there is compiler errors? PLC Firmware: 2021.9.0 PLCnext: 2022.0.1 CLI/SDK: PLCnCLI_SDK_2021.6.0_LTS_Windows_AXC_F_3152 Eclipse IDE: 2020-12 (4.18.0) Thank you.

Login / Register to reply
Nils Hettig 05.05.2022

Hallo Janne, I wrote the article back in 2019 and since then a lot has happened concerning the firmware and SDKs of the devices. Therefore, it is a good guess, that something has changed in the meantime. Unfortunately, I am out of office this week. I’ll have a look at it next week. Hope that is fast enough. Best Regards Nils

Janne Vesen 05.05.2022

It would be great if you can look into it next week, thank you!

Nils Hettig 11.05.2022

Hallo Janne, let me thank you once more for your feedback. It seemed, that I forgot to mention the include statement for the WorkerThread in the article. Therefore, I decided to update the whole article to avoid ambiguity (it should go online soon). Now every step is described in detail. I hope that the new version helps you. Don't hesitate to contact me, if you have further questions. Furthermore, I noticed that your plcncli tool version does not match your controllers firmware. While they could work together, it is not guaranteed that they will. Therefore, I recommend to use matching versions. Best Regards Nils

Janne Vesen 13.05.2022

Thank you for the update and for pointing out firmware mismatch. In the end i got my system working with a different approach. I use BalenaEngine runtime on PLC's linux side and then ran MariaDB image on balena. There is an DBFL_SQL library in the PLCnext store that includes working example on how to use database (create/delete tables, write and read)

Janne Vesen 02.06.2022

Hi, i gave this a quick try and i'm getting a lot of errors. Maybe missing libraries? What i did was to create new PLCnext project with same names as in this example with Eclipse -> added cmake -> copied everything from this examples DBComponent.hpp and DBComponent.cpp to Eclipse (deleted whatever there was in those .hpp and .cpp files in Eclipse). PS. I still have different versions on controller firmware and plcncli tool.

Login / Register to reply
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