PLCnext on Instagram  PLCnext on YouTube Github PLCnext CommunityStore PLCnext Community

 

 How to create a Blog Entry

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

This article describes how the SQLite database engine allready 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 exportet to other systems with SFTP. 

Prerequisites

Modify the Component.hpp file

Include the IControllerComponent and complete the class declaration:

#include "Arp/System/Acf/IControllerComponent.hpp" 
#include <sqlite3.h>

class MYComponent : public ComponentBase, public IControllerComponent, public ProgramComponentBase, private Loggable<MyComponent>

Declaration of the IControlerComponents operations and the method used to write to the database :

public: // IControllerComponent operations
    void Start(void) override;
    void Stop(void) override;

public: // ProgramComponentBase operations
    void RegisterComponentPorts() override;
    void WriteToDB();

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 specifed time (here: 10 ms): 

private: // fields
    MyComponentProgramProvider  programProvider;
    WorkerThread                workerThread;

private: // static fields
    static const int workerThreadIdleTime = 10; // 10 ms

Furthermore some ports are needed to get the data from the GDS:

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;

The last modification in the header file relates to the inline methods:

///////////////////////////////////////////////////////////////////////////////
// inline methods of class MyComponent
inline MyComponent::DBComponent(IApplication& application, const String& name)
: ComponentBase(application, ::CppDB::CppDBLibrary::GetInstance(), name, ComponentCategory::Custom)
, programProvider(*this)
, workerThread(make_delegate(this, &MyComponent::WriteToDB), workerThreadIdleTime, "MyProject.WriteToDatabase") // WorkerThread
, ProgramComponentBase(::CppDB::CppDBLibrary::GetInstance().GetNamespace(), programProvider)
{}

Modify the Component.cpp file

First of all some variables are declared for the database connection and the prepared SQL-statements. The latter will improve the writing speed:

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

Implement the Start() method (cold, warm or hot restart of the PLC application):

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();
}

Implement the Stop() method (PLC application is stopped):

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();
}

Implement the WriteToDB() method:

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(stmt1, 1, intArray[i]);
                if(rc)
                {
                  Log::Error("DB - 6 - {}", sqlite3_errmsg(db));
                  status = 6;
                }

                rc = sqlite3_bind_double(stmt1, 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(stmt1);

                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;
        }
    }
}

Further information

SQlite pragma statements