Introduction

The CDP2SQL generic database interface aims to insulate the programmer from most of the underlying SQL implementation as possible. It supports different SQL database systems implementing the CDP2SQL::IDatabaseConnection interface. As such, it is comprised of a small collection of holder classes that the developer uses to access and manipulate the SQL database. Using this API requires knowledge of the SQL language. Support for SQLite and MariaDB/MySQL is included with CDP.

The most important classes of CDP2SQL are:

• CDP2SQL::Database
• CDP2SQL::Query
• CDP2SQL::Statement
• CDP2SQL::Transaction
• CDP2SQL::SQLException

Getting Started

First create a library as described in How to Create a Library manual.

Adding the CDP2SQL Dependency

• In Code mode, right click on your project and select Add Library....
• In the wizard select CDP Library and click Next.
• Select CDP2SQL. Then click Next and Finish.

SQLite is included by default with CDP2SQL. To connect to MariaDB or MySQL, repeat the previous step and also add mariadb dependency.

Threading

Code accessing database must run in a background thread. Otherwise it will interfere with the CDP framework real-time code.

An easy way to execute code in a background thread is to use the Threaded Component Model wizard:

• Right click on the library.
• Select Add new... option in the context menu.
• Select CDP from the left column.
• Select Threaded Component Model from the middle column.
• Click Choose...
• Name the component SQLComponent. Then click Next and Finish.

Next, open SQLComponent.cpp file in Code mode. Make sure to add any database related code to void SQLComponent::Main() - this is executed in a background thread.

Examples

Here is some example code showing how to connect to a database and execute simple queries and statements.

SQLite

SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file.

#include <CDP2SQL/CDP2SQL.h>
#include <CppSQLite3/SQLite3Factory.h>

using namespace CDP2SQL;

...

try
{
    Database db(SQLite3Factory().Create(), "directory-path/database-file.db");

    Statement st(db, "insert into employee values (?, ?, ?);");
    st.Bind(1, 123);
    st.Bind(2, "John Doe");
    st.Bind(3, 64000.99);
    st.Execute();

    Query q(db, "select id, fullname, salary from employee;");
    while (!q.IsEof())
    {
        int id = q.FieldValueInt(0);
        std::string name = q.FieldValueStr(1);
        double pay = q.FieldValueDouble(2);
        q.NextRow();
    }
}
catch (const SQLException& e)
{
    printf("Error occurred: %s\n", e.Message());
}

See also CDP2SQL::SQLite3Factory documentation.

MariaDB and MySQL

MariaDB Server is one of the most popular database servers in the world. It turns data into structured information in a wide array of applications, ranging from banking to websites.

MariaDB's client protocol is binary compatible with MySQL's client protocol.

To connect to a MariaDB or MySQL database, see the previous example but replace the Database creation with following:

#include <MariaDB/MariaDBFactory.h>
...
Database db(MariaDB::MariaDBFactory().Create("127.0.0.1:3306", "user", "password"), "database-name");

See also MariaDB::MariaDBFactory documentation.

Troubleshooting

• Access from outside of localhost

  Edit the my.cnf and change

  bind-address            = 127.0.0.1

  to

  bind-address            = 0.0.0.0

  It may be needed to also connect with the local command line client and execute the following statement:

  GRANT ALL PRIVILEGES ON *.* TO 'youruser'@'%';

Credits

The CDP2SQL interface is loosely based on CppSQLite3:

• CppSQLite3 - A C++ wrapper around the SQLite3 embedded database library
• Copyright (c) 2004 Rob Groves. All Rights Reserved. rob.groves@btinternet.com