Configuration required to run this application:
1-) PostgreSQL 9.4.4
2-) Qt Version 5.4.0
3-) Qt Creator 3.2.2
PostgreSQL has got its own database programming language named with PL/pgSQL and following functions implemented in PL/pgSQL.
-- 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;
person table is filled with the following sample records/rows in order to see the execution result.
INSERT INTO person(
id, name, age, address)
VALUES (1, 'paul', 12, 'adana');
INSERT INTO person(
id, name, age, address)
VALUES (2, 'tufan', 15, 'ankara');
INSERT INTO person(
id, name, age, address)
VALUES (3, 'colin', 18, 'antep');
First PostgreSQL PL/pgSQL function returns the max age from the person table.
CREATE OR REPLACE FUNCTION maxAge() RETURNS integer AS $$ declare max_age integer; BEGIN SELECT max(age) into max_age FROM person; RETURN max_age; END; $$ LANGUAGE plpgsql;
maxAge() plpgsql function called with a select statement from pgAdmin III Query Tool.
SELECT maxage();
Output of the call of the maxage() function from PostgreSQL database is "18".
Second PostgreSQL PL/pgSQL function returns the age of the person whose name is sent as a parameter from the person table.
CREATE OR REPLACE FUNCTION getAgeOfPersonByName( nameOfPerson text ) RETURNS integer AS $$ declare person_age integer; BEGIN SELECT age into person_age FROM person where name=nameOfPerson; RETURN person_age; END; $$ LANGUAGE plpgsql;
getAgeOfPersonByName( nameOfPerson text ) plpgsql function called with a select statement from pgAdmin III Query Tool.
SELECT getAgeOfPersonByName('paul');
Output of the call of the getAgeOfPersonByName('paul') function from PostgreSQL database is "12".After creating the required tables and functions in PostgreSQL, now it is time to write our Qt application that calls these PL/pgSQL functions from PostgreSQL with Qt SQL.
Following sample project created by qt creator and contains following files :
1- QtSQLCallFunctionFromPostgreSQL.pro
2- main.cpp
QtSQLCallFunctionFromPostgreSQL.pro file contains project configuration :
TEMPLATE = app CONFIG += console CONFIG -= app_bundle QT += core sql QT -= gui TARGET = QtSQLCallFunctionFromPostgreSQL SOURCES += main.cppSample project only contains main.cpp file as source file. maxAge and getAgeOfPersonByName PL/pgSQL functions from PostgreSQL are called and return values are displayed in the terminal.
#include <QCoreApplication>
#include <QtSql>
#include <QDebug>
int main()
{
const char* driverName = "QPSQL";
QSqlDatabase db( QSqlDatabase::addDatabase(driverName) );
db.setConnectOptions();
db.setHostName("localhost");
db.setDatabaseName("testdb");
db.setUserName("test");
db.setPassword("test");
db.open();
QString functionNameToCall = "maxAge";
QSqlQuery* query = new QSqlQuery(db);
query->prepare(QString("SELECT %1()").arg(functionNameToCall));
query->exec();
QString maxAgeOfPersonTable;
while (query->next())
{
maxAgeOfPersonTable = query->value(0).toString();
}
qDebug() << maxAgeOfPersonTable;
functionNameToCall = "getageofpersonbyname";
QString name = "paul";
query->prepare(QString("SELECT %1(?)").arg(functionNameToCall));
query->addBindValue(name);
query->exec();
QString personAge;
while (query->next())
{
personAge = query->value(0).toString();
}
qDebug() << personAge;
delete query;
db.close();
return 0;
}
getageofpersonbyname PL/pgSQL function takes a parameter and the parameter is binded by addBindValue function of QSqlQuery. When the Qt application runs in the terminal the following output is displayed.
