DB Design & Dev

Database Design & Development

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

Use this link local_db.sqlite (173 downloads) 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.

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.zip (223 downloads) 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.zip (223 downloads) 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.zip (223 downloads)

C++ Standards Support in GCC

GNU has announced the stable release of GNU Compiler Collection 7 (GCC 7), which has been marked with the release of GCC 7.1. This month, the 30-year anniversary of GCC 1.0 will also be celebrated.

C++ Standards Support in GCC
This page is a brief summary of some of the huge number of improvements in GCC 7. For more information, see the Porting to GCC 7 page and the full GCC documentation.

Changes
gcc_7.1.0.pdf
gcc.gnu.org/gcc-7
Onlinedocs
Porting to GCC 7
List of GNU GCC Releases

Qt Creator plugins

Just reIf you just started to familiar yourself with Qt’s IDE, this is my advice. After a long time of daily usage became my favorite developing IDE, as it is covering almost every part of the work I do on a daily basis. However there are three (3rd party) plugins am always adding to my Qt Creator IDE wherever I work on-site or in the office they are very useful. Especially CppCheck but gtest and the DB viewing tool can coming handy.
Qt Creator CppCheck integration plugin
Qt Creator Google C++ Testing Framework integration plugin
Qt Creator SQL database viewer plugin

All of the plugins mentioned above developed by Gres.

Bottom line: a good and comfortable development enviromet setup if playing a key role of utilizing your almost always limited time and result an efficient way of your daily developing work.
Valgrind (adding memory and function profiler feature to Qt Creator), sometimes preferring GDB instead of LLDB especially project on OSX.
I also use Doxygen for generating documentation from annotated C++ sources.
You can enable/disable plugins, I strongly recommend to enable this very convenient plugging, it is much more helpful compare to my previously used tool. It recognizes notes: TODO, BUG, FIXME, NOTE, HACK by default.

Very simply to use. Here is one of my projects TODO list just started.

Click image to zoom inClick image to zoom in

Sort My List

Sort My List an easy to use very handy online tool where You can sort or clean your list in a variety of ways. This simple online tool could help you remove, add, separate data without using other other softwares sorting features. There are limitations in this online tool, and I won’t recommend on large datasets, however in many cases could be very handy, removing unwanted whitespaces or any characters.
Find out more: sortmylist.com

Cheerp

Cheerp is a C++ compiler for the Web, based and integrated into the LLVM/clang infrastructure.

With Cheerp you can write Web applications in pure C++ code, or port existing C++ code to the Web. Your C++ code is fully optimized by the LLVM system and it’s compiled into JavaScript code that runs on the browser.

htop and glances interactive process viewers for Linux Servers.

This is htop, an interactive process viewer for Unix systems. It is a text-mode application (for console or X terminals) and requires ncurses.
I am using this brilliant app for monitoring my servers and Raspberry Pi. Download

I recommend use PyPi:

Glances – “An Eye on your system”
Glances is a cross-platform system monitoring tool. Written in Python, Glances will run on almost any platform : GNU/Linux, FreeBSD, OS X and Windows.
Install:

You can enable many useful plugins using the following command.

or with python3

Configuration file options:


Ncdu is also a brilliant command line disk usage analyzer with an ncurses interface.
dev.yorhel.nl/ncdu

 

[PDF] Jenkins The Definitive Guide – Continuous Integration

Continuous Integration is a key element of modern software development best practices. You can learn how to automate your build, integration, release and deployment process with Jenkins from this free ebook.

jenkins-the-definitive-guide
jenkins-the-definitive-guide

Download pdf eBook
You can get more information about CI on Eclipse Hudson website where you can read The Hudson Book for free in online, PDF, EPUB formats.

*Request(Please report broken links) Dear Reader, if any of the external links in this post are broken please inform me using the contact us form. Thank you.

Reset mysql root password on unix without needing old password. Raw

 

Differences between c++11 vs c++14 standards

I thought I repost two worth reading summarizing article about c++11 and c++14 in my blog.
There is a nice article about the top 10 c++11 features written by Marius Bancila.
I actually quite agree with this article, so if you are interested you can take a look (codeproject.com/Articles/570638/Ten-Cplusplus-Features-Every-Cplusplus-Developer).
There is also a good article about some of the c++14 standards written by Mark Nelson, if you are interested you can take also look (drdobbs.com/cpp/the-c14-standard-what-you-need-to-know).
Finally, there is a c++14 Language Features list on the C++1y/C++14 Support in GCC link.

Bjarne Stroustrup announces C++ Core Guidelines

This morning in his opening keynote at CppCon, Bjarne Stroustrup announced the C++ Core Guidelines (github.com/isocpp/CppCoreGuidelines), the start of a new open source project on GitHub to build modern authoritative guidelines for writing C++ code.

The guidelines are designed to be modern, machine-enforceable wherever possible, and open to contributions and forking so that organizations can easily incorporate them into their own corporate coding guidelines.

Read more on the source site: [https://isocpp.org]

In addition you might find very useful to read Bjarne Stroustrup’s C++ Style and Technique FAQ [Updated Modified February 13, 2016]

[C++] Store different types in a single vector

 

Number Factorizer in C++

 

C++ program to generate Fibonacci series

Here is my C++ program to generate Fibonacci series.
The Fibonacci sequence is a sequence Fn of natural numbers defined recursively.

 

How to install java 1.6.31 JRE on Ubuntu (64bit)

lnrsoft@lnrsoft:$cd /opt
lnrsoft@lnrsoft:/opt$sudo mkdir java
lnrsoft@lnrsoft:/opt$cd java
lnrsoft@lnrsoft:/opt/java$sudo mkdir 64
lnrsoft@lnrsoft:/opt/java$sudo mv ~/Desktop/jre-6u31-linux-x64.bin /opt/java/64
lnrsoft@lnrsoft:/opt/java$sudo chmod 755 /opt/java/64/jre-6u31-linux-x64.bin
lnrsoft@lnrsoft/opt/java:$cd /opt/java/64
lnrsoft@lnrsoft:/opt/java/64$sudo ./jre-6u31-linux-x64.bin

Agree the license agreement

lnrsoft@lnrsoft:/opt/java/64$sudo update-alternatives –install “/usr/bin/java” “java” “/opt/java/64/jre1.6.0_31/bin/java” 1
lnrsoft@lnrsoft:/opt/java/64$sudo update-alternatives –set java /opt/java/64/jre1.6.0_31/bin/java

Install the Firefox plugin

lnrsoft@lnrsoft:/opt/java/64$mkdir ~/.mozilla/plugins
lnrsoft@lnrsoft:/opt/java/64$sudo apt-get remove icedtea6-plugin
lnrsoft@lnrsoft:/opt/java/64$rm ~/.mozilla/plugins/libjavaplugin_oji.so
lnrsoft@lnrsoft:/opt/java/64$rm ~/.mozilla/plugins/libnpjp2.so
lnrsoft@lnrsoft:/opt/java/64$ln -s /opt/java/64/jre1.6.0_31/lib/amd64/libnpjp2.so ~/.mozilla/plugins/

Start up and shut down manually Oracle 11g on ubuntu

Start up

lnrsoft@lnrsoft:$cd /home/LnRSoft/Oracle/app/lnr/product/11.1.0/db_1/bin
lnrsoft@lnrsoft:~/Oracle/app/lnr/product/11.1.0/db_1/bin$ ./lsnrctl start
Services Summary…
Service “lnr” has 1 instance(s).
Instance “lnr”, status.
The command completed successfully

lnrsoft@lnrsoft:~/Oracle/app/lnr/product/11.1.0/db_1/bin$./sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 – Production on Wed Oct 20 21:10:07 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to an idle instance.
SQL>
SQL>startup;
ORACLE instance started.
Total System Global Area 841162752 bytes
Fixed Size 1303160 bytes
Variable Size 444599688 bytes
Database Buffers 390070272 bytes
Redo Buffers 5189632 bytes
Database mounted.
Database opened.
SQL> exit;

lnrsoft@lnrsoft:~/Oracle/app/lnr/product/11.1.0/db_1/bin$ ./emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.1.0.6.0
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
https://lnr-server:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ………… started.

Shut down

lnrsoft@lnrsoft:~/Oracle/app/lnr/product/11.1.0/db_1/bin$ ./emctl stop dbconsole
Stopping Oracle Enterprise Manager 11g Database Control … Stopped.
lnrsoft@lnrsoft:~/Oracle/app/lnr/product/11.1.0/db_1/bin$./sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 – Production on Wed Oct 20 21:58:12 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to an idle instance.
SQL>
SQL>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit;

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
lnrsoft@lnrsoft:~/Oracle/app/lnr/product/11.1.0/db_1/bin$ ./lsnrctl stop

The command completed successfully

RemoteOperationException: ERROR: Invalid username and/or password

For Host Credentials Setup – NOT connected to a Domain:
username = user_name
password = pass_word

For Host Credentials Setup – connected to a Domain:
username = domainuser_name
password = pass_word

For Database Credentials Setup:
username = database_user_name
password = database_user_password

For Enterprise Manager logon Screen prompt
username = sysman
password = sysman_password

For Database logon Screen prompt
username = sys (or any database_user_name)
password = sys_password (or any database_user_password)

Set the logon as batch job from local security policy
1. Go to Control Panel, Administrative Tools.
2. Click on Local Security Policy.
3. Local Policies, User Rights Assignment.
4. Double click, ‘Log on as a batch job’

How to install java 1.6.22 JRE on ubuntu

$ cd /opt
$ sudo mkdir java
$ cd java
$ sudo mkdir 32
$ sudo mv ~/Desktop/jre-6u22-linux-i586.bin /opt/java/32
$ sudo chmod 755 /opt/java/32/jre-6u22-linux-i586.bin
$ cd /opt/java/32
$ sudo ./jre-6u22-linux-i586.bin

Agree the license agreement

$ sudo update-alternatives --install "/usr/bin/java" "java" "/opt/java/32/jre1.6.0_22/bin/java" 1
$ sudo update-alternatives --set java /opt/java/32/jre1.6.0_22/bin/java

Install the Firefox plugin

$ mkdir ~/.mozilla/plugins
$ sudo apt-get remove icedtea6-plugin
$ rm ~/.mozilla/plugins/libjavaplugin_oji.so
$ rm ~/.mozilla/plugins/libnpjp2.so
$ ln -s /opt/java/32/jre1.6.0_22/lib/i386/libnpjp2.so ~/.mozilla/plugins/

[SOLVED] Could not install Google Earth on Linux

$ ./GoogleEarthLinux.bin
Verifying archive integrity… All good.
Uncompressing Google Earth for GNU/Linux 5.2.1.1588……………………………………………………..
setup.data/setup.xml:1: parser error : Document is empty
^
setup.data/setup.xml:1: parser error : Start tag expected, ‘< ' not found ^ Couldn't load 'setup.data/setup.xml' Solution: $ ./GoogleEarthLinux.bin --target /tmp/ge $ cd /tmp/ge/setup.data/bin/Linux/x86/ $ mv setup.gtk setup.gtk2 $ cd /tmp/ge $ ./setup.sh

How to set up ORACLE_SID and ORACLE_HOME on ubuntu

1. step Open the .profile hidden file which is located in /yourname/home/.profile

2. step add the following lines to end of the .profile file

3. step save the changes , restart your computer

4. step you can check your environment variable

Path : Oracle/app/yourname/product/11.1.0/db_1/bin

UnsatisfiedLinkError exception loading native library: njni11

Installing Oracle 11g on Linux
[Oracle Net Services configuration failed.]

$./netca
Error message:
UnsatisfiedLinkError exception loading native library: njni11

java.lang.UnsatisfiedLinkError: jniGetOracleHome
at oracle.net.common.NetGetEnv.jniGetOracleHome(Native Method)
at oracle.net.common.NetGetEnv.getOracleHome(Unknown Source)
at oracle.net.ca.NetCA.main(Unknown Source)
Oracle Net Services configuration failed. The exit code is -1

Solution:
sudo apt-get install build-essential libaio1 gawk ksh