C++ Libraries

Cross-platform C++ libraries ACE,Boost,POCO,Qt

SQLite – How to use fundamental functions in C++

SQLite operations in C++

This tutorial aimed to demonstrate how to use SQLite database operations such as CREATE, INSERT, UPDATE, SELECT and DELETE in C++. Since the appearance first smartphone and other mobile devices SQLite become most widely deployed and used database engine in the world. Precise numbers are difficult to obtain but SQLite is likely used more than all other database engines combined. We can find SQLite in every Android device, iPhone and iOS device, Mac, Windows10, Firefox, Chrome, Safari, Skype, Dropbox, TurboTax, QuickBooks, PHP, Python, television sets, set-top cable boxes and many automotive multimedia systems.

It’s an open-source SQL database engine. You can download the source code or precompiled binaries depending your requirements. You might also need to add [-lsqlite3] and [-std=c++17] to successfully compile, create an executable file. Each of the following source code available on my git repository.

github.com/lnrsoft/sqlite3_cpp_basics_updated or lnrrepo.ddns.net/git/lnrsoft/sqlite3_cpp_basics_updated

Use this link local_db.sqlite if you would like to download the local_db.sqlite file that I used in this tutorial.

Step 1 – The first thing we need to do is to create the actual database file that we will use in this article. I called sqltdemo.db in our case.

Compile and run our source code to create the local_db.sqlite with the following command. Here we link our code with the sqlite3 library that will create our db file.

Verify our result:

Step 2 – CREATE TABLE

Compile and run.

Step 3 – INSERT INTO our CANDIDATE TABLE

Compile and run.

Step 4 – Fetch and display records from the company CANDIDATE TABLE. In this scenario we use a callback function that obtains results from SELECT statement.

Compile and run.

Step 5 – Using UPDATE statement to update any records in our CANDIDATE table.

Compile and run.

Step 6 – DELETE statement can delete any records from the CANDIDATE table.

Compile and run.

I recommend you to check the official SQLite documentation at sqlite.org for more advance SQLite features and options and support. For further info about using sql with c++ you might found it useful to read me previous post of  a simple Qt tool to test QSqlDatabase access connection to a MySQL database. I created this simple Qt command line tool to test connection between Qt client applications and a remote Mysql Server. The code includes Secure Sockets Layer (SSL) support to test secure connection. The QSqlDatabase class provides an interface for accessing a database through a connection where an instance of QSqlDatabase represents the connection. The connection provides access to the database via one of the supported database drivers, which are derived from QSqlDriver.

Alternatively, we can subclass your own database driver from QSqlDriver. My next post will be an article how to write your own database driver in Qt for Unix, OSX and Windows platforms.

SPECIFY ADDITIONAL COMPILER OPTIONS IN QTCREATOR OR SIMPLE ADD THEM TO QMAKE

List of build-specific variants to specify your Qt project.

QSqlDatabase connection testing tool

QSqlDatabase connection access

A simple command line tool to test connection between Qt client applications and a remote Mysql Server. The code includes Secure Sockets Layer (SSL) support to test secure connection. The QSqlDatabase class provides an interface for accessing a database through a connection. An instance of QSqlDatabase represents the connection. The connection provides access to the database via one of the supported database drivers (MySQL in our case), which are derived from QSqlDriver. Alternatively, we can subclass your own database driver from QSqlDriver. I’ll post an another article about how to write your own database driver in Qt for Unix, OSX and Windows platforms. It’s recommend to write your own database driver especially when you planning to deploy your application in different platforms. It’s simply because there could be surprises especially on client deploying your application that using one of the supported database drivers due to missing dependencies or environmental variables.

Software prerequisites:
Remote MySQL server version: 5.7 or above
Qt version: Qt_5_7_1 or above
OpenSSL 1.0.x or above the for SSL feature

How to use it:
1. I simply run the mysql_conn_test_database.sql SQL script file to create a test database on the remote server.
2. Set the hostname or IP, port number, username and password. You migth need to create a new database user for this purpose.
[You will need to create SSL certificates for the database server and client in order to test SSL Feature on the MySQL Server.]
3. If everthing is set just simple build and run the project in your QtCreator.

If everything goes well you will recieve the following feedback from your terminal:


QSqlDatabase connection test tool was created and written by Roland Ihasz and it is licensed under the GNU General Public License (GPL) Version 2 or above.

Check out on GitHub. https://github.com/lnrsoft/QSqlDatabase-connection-test-tool

 mysql_db_connection_test.pro

 main.cpp

 mysql_conn_test_database.sql

Alternatively, use the following shorter source code to check the connection without any sql query.
 QSqlDatabase connection test tool without query

[SOLVED] QSQLDATABASE: QMYSQL DRIVER NOT LOADED ON OSX AND LINUX

After spending a quite a bit of time to find the solution of this often-occurring
QMYSQL driver not loaded problem mainly after installing Qt installation in a new software environment, So I thought I am going to share my best practice to fix this issue.
I wasn’t able to establish database connection even if I received the list of available drivers:

QSqlDatabase: available drivers: QSQLITE QMYSQL QMYSQL3 QODBC QPSQL
QSqlDatabase: QMYSQL driver not loaded

The following simple method helps to fix the problem.
On OSX system just simple install or re-install mysql using the flowing command. I usually prefer Homebrew as it fulfils most my requirements and using MacPort when I really no other options available.

then place the following libmysqlclient.18.dylib file to the path below:

On different Linux distributions I usually apt install or yum install the MySQL package basically the only thing I need to make sure that place the libmysqlclient.18.dylib file to the following path:

This method solves the QMYSQL driver not loaded issue and allows me to establish database connection, it might work on Windows system as well, however I had not opportunity to try before.
Please, let me know if this method also working for you by dropping a line.

Download libmysqlclient.18.dylib