How to use SQLite in a PLCnext C++ project

PLCnext Technology controllers come with the SQLite database engine already installed. This database can be used by applications that require a “small, fast, self-contained, high-reliability, full-featured, SQL database engine” on the PLC.

This guide shows how to link a PLCnext Control C++ project to SQLite, and gives a simple example of how to call a SQLite function from a C++ component.

Prerequisites

• AXC F 2152 FW 2019.0 LTS or higher
• PLCnext Engineer 2019.0 LTS or higher
• PLCnext Command Line Interface (CLI) 2019.0 LTS or higher.
• A suitable PLCnext Software Development Kit (SDK), installed using the CLI.
• Eclipse IDE for C/C++ developers, with the PLCnext Technology add-in.

Procedure

1. In Eclipse, create a new PLCnext C++ project

• Use the procedure described in this video tutorial.

2. Include SQLite in the CMake build process

In order to use the SQLite header and library files that come with the PLCnext SDK, the location of these files must be made known during the project build process. This can be achieved using CMake, as follows:

• Create a new directory under the Eclipse project’s root directory. Call the directory cmake.
• You should now see this folder in the Eclipse Project Explorer, alongside the src and bin directories.
• In this new folder, create a file named FindSqlite.cmake.
• Open this file in Eclipse, and add the following text:# Copyright (c) 2018 PHOENIX CONTACT GmbH & Co. KG # Created by Björn sauer <bjoern.sauer@phoenixcontact.de> # # - 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()
• In the Eclipse project, open the CMakeLists.txt file in the project root directory. Add references to SQLite, as follows:The ‘include arp cmake module path’ section of this file should look like this:################# include arp cmake module path ####################################### list(INSERT CMAKE_MODULE_PATH 0 "${ARP_TOOLCHAIN_CMAKE_MODULE_PATH}") list(APPEND CMAKE_MODULE_PATH "${PROJECT_SOURCE_DIR}/cmake") #######################################################################################… and the ‘add link targets’ section should look like this (be sure to replace MyProject below with the name of your C++ project):################# add link targets #################################################### find_package(ArpDevice REQUIRED) find_package(ArpProgramming REQUIRED) find_package(Sqlite REQUIRED) target_link_libraries(MyProject PRIVATE ArpDevice ArpProgramming Sqlite::Sqlite) #######################################################################################

3. Write your C++ code

Write your C++ code to use the SQLite database.

The following example simply opens a test database – creating it if necessary – and immediately closes it.

Example:

• In the Component.hpp file, add the following directive:#include <sqlite3.h>
• In the Component.cpp file, add the following code to the SetupConfig() method: // setup project config here sqlite3 *db; int rc; rc = sqlite3_open("/opt/plcnext/test.db", &db); if( rc ) { Log::Error("Can't open database: {}", sqlite3_errmsg(db)); return; } else { Log::Info("Opened database successfully."); } sqlite3_close(db);

4. Build, deploy and run your project

• Build, deploy and run your project in the normal way, using the procedure described in this video tutorial.

5. Check that the program executed successfully

• On the PLC, examine the log file /opt/plcnext/logs/Output.log. You should see an entry similar to the following:15.04.19 06:59:35.090 Arp.Plc.Plm.Internal.PlmComponentManager INFO - Component 'MyComponent1' from library 'MyProject' created. 15.04.19 06:59:35.095 root INFO - Opened database successfully.
• List the contents of the directory /opt/plcnext. You should see that the SQLite database file test.db has been created.You can now develop a C++ program to write and read records to your own SQLite databases on the PLC.

Further information

• An introduction to using SQLite with C++ can be found in this tutorial from TutorialsPoint.
• To examine the contents of SQLite database files, either use the sqlite3 command line tool directly on the PLC, or copy the database files from the PLC and open them using something like this DB Browser for SQLite.