- Driver Layer
- SQL API Layer
- User Interface Layer
In order to connect to a database from Qt application, related database driver needs to be configured.
After setting connection to a specific database from Qt application, SQL API helps to achieve common database operations such as open-close connection, query tables, etc.
Configuration required to run this application:
1-) PostgreSQL 9.4.4
2-) Qt Version 5.4.0
3-) Qt Creator 3.2.2
Create sql script for Person table: You can use pgAdmin tools in order to create tables on postgreSQL database.
-- Table: person -- DROP TABLE person; CREATE TABLE person ( id integer NOT NULL, name text NOT NULL, age integer NOT NULL, address character(50), CONSTRAINT person_pkey PRIMARY KEY (id) ) WITH ( OIDS=FALSE ); ALTER TABLE person OWNER TO test;
Following sample project created by qt creator and contains following files :
1- QSqlConnection.pro
2- QSQLDbHelper.h
3- QSQLDbHelper.cpp
4- main.cpp
QSqlConnection.pro file contains project configuration :
TEMPLATE = app CONFIG += console CONFIG -= app_bundle QT += core sql QT -= gui SOURCES += main.cpp \ QSQLDbHelper.cpp HEADERS += \ QSQLDbHelper.h
In this example qt sql project QSQLDbHelper.h class header file contains database related functions.
#ifndef QSQLDBHELPER_H #define QSQLDBHELPER_H #include <QtSql> #include <QString> #include <QDebug> class QSQLDbHelper { public: QSQLDbHelper(const char* driver); ~QSQLDbHelper(); QSqlDatabase* connect( const QString& server, const QString& databaseName, const QString& userName, const QString& password ); void disConnect(); int selectRowCountResult(QSqlQuery* query); bool executeInsert(QSqlQuery* query); bool executeUpdate(QSqlQuery* query); bool executeDelete(QSqlQuery* query); private: QSqlDatabase* db; }; #endif // QSQLDBHELPER_H
QSQLDbHelper.cpp file contains implementation details for the following functions :
1- Class constructor/desctructor
2- Db connect
3- Db disconnect
4- selectRowCount
5- insert operation
6- update operation
7- delete operation
#include "QSQLDbHelper.h" QSQLDbHelper::QSQLDbHelper(const char* driver) { db = new QSqlDatabase( QSqlDatabase::addDatabase(driver) ); } QSQLDbHelper::~QSQLDbHelper() { qDebug() << "Called Destructor!"; delete db; } QSqlDatabase* QSQLDbHelper::connect( const QString& server, const QString& databaseName, const QString& userName, const QString& password ) { db->setConnectOptions(); db->setHostName(server); db->setDatabaseName(databaseName); db->setUserName(userName); db->setPassword(password); if(db->open()) { return db; } else { return NULL; } } int QSQLDbHelper::selectRowCountResult(QSqlQuery* query) { bool queryRes = query->exec(); if (query->lastError().type() != QSqlError::NoError || !queryRes) { qDebug() << query->lastError().text(); return -1; } int recordCount = 0; while (query->next()) { qDebug() << "Field 1 : " << query->value(0).toString() << "Field 2 : " << query->value(1).toString(); ++recordCount; } return recordCount; } bool QSQLDbHelper::executeInsert(QSqlQuery* query) { db->transaction(); bool queryRes = query->exec(); if (query->lastError().type() != QSqlError::NoError || !queryRes) { qDebug() << query->lastError().text(); db->rollback(); return false; } db->commit(); return true; } bool QSQLDbHelper::executeUpdate(QSqlQuery* query) { db->transaction(); bool queryRes = query->exec(); if (query->lastError().type() != QSqlError::NoError || !queryRes) { qDebug() << query->lastError().text(); db->rollback(); return false; } db->commit(); return true; } bool QSQLDbHelper::executeDelete(QSqlQuery* query) { db->transaction(); bool queryRes = query->exec(); if (query->lastError().type() != QSqlError::NoError || !queryRes) { qDebug() << query->lastError().text(); db->rollback(); return false; } db->commit(); return true; } void QSQLDbHelper::disConnect() { qDebug() << "Disconnected From Database!"; db->close(); }
In order to make database related operations from a qt application it is required to include "QtSql" header file into the related source file.
QSqlDatabase class represents a database in your application. A transaction is started and concluded by using rollback() and commit() functions of QSqlDatabase.
QSqlQuery class executes passed sql queries on postgresql database.
Main method in the main.cpp file calls implemented connect, disconnect and CRUD operation methods from QSQLDbHelper class.
#include <iostream> #include <QDebug> #include "QSQLDbHelper.h" int main() { qDebug() << "Compiled with Qt Version = " << QT_VERSION_STR; const char* driverName = "QPSQL"; QSQLDbHelper* qSQLDbHelper = new QSQLDbHelper(driverName); QSqlDatabase* db = qSQLDbHelper->connect("localhost", "testdb", "test", "test"); if(db->open()) { QSqlQuery* query = new QSqlQuery(*db); query->setForwardOnly(true); // Select empty person table QString name = "Paul"; if( !query->prepare(QString("SELECT id, name from person where name = ? ")) ) { qDebug() <<"Error = " << db->lastError().text(); return -1; } else query->addBindValue(name); int queryResultRowCount = qSQLDbHelper->selectRowCountResult(query); qDebug() << "Initial Row Count = " << queryResultRowCount << "\n"; // insert into empty person table QString id = "1"; QString age = "34"; QString address = "istanbul"; if( !query->prepare( QString("INSERT INTO person( id, name, age, address) VALUES ( ?, ?, ?, ?)") )) { qDebug() <<"Error = " << db->lastError().text(); return -1; } else { query->addBindValue(id); query->addBindValue(name); query->addBindValue(age); query->addBindValue(address); } bool result = qSQLDbHelper->executeInsert(query); if( result ) qDebug() << "Successful insert"; else qDebug() << "Insert failed"; // Select person table with 1 matching record if( !query->prepare( QString("SELECT id, name from person where name = ? "))) { qDebug() <<"Error = " << db->lastError().text(); return -1; } else query->addBindValue(name); queryResultRowCount = qSQLDbHelper->selectRowCountResult(query); qDebug() << "After Insert Row Count = " << queryResultRowCount << "\n"; // Update person table name = "Paul2"; if( !query->prepare(QString("UPDATE person set name=? where id =? ")) ) { qDebug() <<"Error = " << db->lastError().text(); return -1; } else { query->addBindValue(name); query->addBindValue(id); } result = qSQLDbHelper->executeUpdate(query); if( result ) qDebug() << "Successful update"; else qDebug() << "Update failed"; // Select person table with 0 no matching record if( !query->prepare( QString("SELECT id, name from person where name = ?")) ) { qDebug() <<"Error = " << query->lastError().text(); return -1; } else { query->addBindValue(name); } queryResultRowCount = qSQLDbHelper->selectRowCountResult(query); qDebug() << "After Update Row Count = " << queryResultRowCount << "\n"; // Delete from person table whose name is Paul2 // name = "Paul2"; if( !query->prepare(QString("Delete from person where name =? ")) ) { qDebug() << "Error = " << db->lastError().text(); return -1; } else { query->addBindValue(name); } result = qSQLDbHelper->executeDelete(query); if( result ) qDebug() << "Successful delete"; else qDebug() << "Delete failed"; // Select person table with 0 no matching record if( !query->prepare( QString("SELECT id, name from person where name = ? ")) ) { qDebug() << "Error = " << db->lastError().text(); return -1; } else { query->addBindValue(name); } queryResultRowCount = qSQLDbHelper->selectRowCountResult(query); qDebug() << "After Delete Row Count = " << queryResultRowCount << "\n"; delete query; } else { qDebug() << "Something went Wrong:" << db->lastError().text(); } qSQLDbHelper->disConnect(); delete qSQLDbHelper; return 0; }
In main function database connection is established and related database related operations are being tested. Parametric, dynamic sql queries created by using addBindValue method of QSqlQuery.
Output of the execution of this program shows database operation results from Qt application in terminal.
Works on Mac, but on windows it says Driver not loaded. Why?
ReplyDeletehttp://doc.qt.io/qt-5/sql-driver.html#qpsql
DeleteYou should add postgre's 'bin' and 'lib' path to your environment variables
ReplyDeletecan you provide sample .pro for defining postgres environment variables. i'm only a beginner.thank you.
DeleteThis comment has been removed by the author.
ReplyDeleteI fond how to set the application name the easy way (i think it is not possible to pass the option at connect time trough setOptions() but it is not sure.
ReplyDeleteThe way i find is to execute a direct query from QSqlDatabase directly with:
QString str_query = QString("set application_name = %1 ;").arg(appName);
if (db.exec(str_query).isActive()) qDebug() "it should not failed: " << db.lastError().text();
tell me if there is a way to pass application_name through setOptions() method, please.
if (db.isOpen())
/home/ice/sqlqt/qsqldbhelper.cpp:3: error: multiple definition of `QSQLDbHelper::QSQLDbHelper(char const*)'
ReplyDeleteICE take a look at your QSqlConnection.pro
ReplyDelete