Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
I'm writing a program in QT and have a problem with writing an SQL query Select.
I have a simple table which contains columns like: ID, name_or_nickname, surname, occupation. I have 3 variables I want to use in the query:
QString name = "Peter";
QString surname = "Smith";
QString occupation = "New York";
I want to run this query using those variables:
QSqlDatabase sql;
QSqlQuery query(sql);
QString execute = "SELECT COUNT(name) FROM table1 WHERE name_or_nickname='?' AND surname='?' AND occupation='?';";
query.prepare(execute);
query.bindValue(0, name);
query.bindValue(1, _surname);
query.bindValue(2, _occupation);
query.exec();
if (query.next())
rows= query.value(0).toInt();
return true;
qDebug() << query.lastError();
return false;
However it doesn't work and isActive() function returns false, so there's something wrong with the query - probably with the brackets. Could you show me an example how should I deal with it? Thank you in advance!
===========================================================================
I post here the necessary code:
MainWindow.h:
#ifndef MAINWINDOW_H
#define MAINWINDOW_H
#include <person.h>
#include <QMainWindow>
#include <QString>
#include <QtDebug>
#include <QtSql>
namespace Ui {
class MainWindow;
class MainWindow : public QMainWindow
Q_OBJECT
public:
explicit MainWindow(QWidget *parent = nullptr);
~MainWindow();
private slots:
void on_pushButton_1_clicked();
private:
Ui::MainWindow *ui;
QSqlDatabase sql;
bool open_or_not;
#endif // MAINWINDOW_H
MainWindow.cpp
#include "mainwindow.h"
#include "ui_mainwindow.h"
MainWindow::MainWindow(QWidget *parent) :
QMainWindow(parent),
ui(new Ui::MainWindow)
ui->setupUi(this);
sql = QSqlDatabase::addDatabase("QSQLITE", "db");
sql.setDatabaseName("E:\\folder\\database.sqlite3");
sql.close(); // this was called beacuse of the problem with first query
sql.open();
QSqlQuery query(sql);
QString execute = "CREATE TABLE IF NOT EXISTS table1 (id INTEGER UNIQUE PRIMARY KEY, name_or_nickname TEXT, surname TEXT, occupation TEXT);";
query.exec(execute);
qDebug() << "isActive: CREATING TABLE" << query.isActive();
query.clear();
open_or_not = sql.open();
MainWindow::~MainWindow()
delete ui;
void MainWindow::on_pushButton_1_clicked()
if(open_or_not)
Person person1(_sql, Johnny, Walker, California, this);
bool result = false;
result = person1.search_in_database();
qDebug() << result;
Person.h:
#ifndef PERSON_H
#define PERSON_H
#include <QNetworkRequest>
#include <QNetworkReply>
#include <QtDebug>
#include <QDebug>
#include <QJsonDocument>
#include <QtSql>
namespace Ui {
class Person;
class Person : public QDialog
Q_OBJECT
public:
explicit Person(QSqlDatabase & sql, QString name_or_nickname, QString surname, QString occupation, QWidget *parent);
~Person();
bool search_in_table();
private:
Ui::Person *ui;
QSqlDatabase sql;
QString name_or_nickname;
QString surname;
QString occupation;
#endif // PERSON_H
Person.cpp:
#include "person.h"
#include "ui_person.h"
Person::Person(QSqlDatabase & sql, QString name_or_nickname, QString surname, QString occupation, QWidget *parent) :
QDialog(parent),
ui(new Ui::Person)
ui->setupUi(this);
this->sql = sql;
this->name_or_nickname = name_or_nickname;
this->surname = surname;
this->occupation = occupation;
Person::~Person()
delete ui;
bool Person::search_in_table()
QSqlQuery query(sql);
int rows = 0;
QString name = this->name_or_nickname;
QString _surname = this->surname;
QString _occupation = this->occupation;
QString execute = "SELECT COUNT(name) FROM table1 WHERE name_or_nickname='?' AND surname='?' AND occupation='?';";
query.prepare(execute);
query.bindValue(0, name);
query.bindValue(1, _surname);
query.bindValue(2, _occupation);
query.exec();
if (query.next())
rows= query.value(0).toInt();
return true;
qDebug() << query.lastError();
return false;
Main.cpp:
#include "mainwindow.h"
#include <QApplication>
#include <QPushButton>
int main(int argc, char *argv[])
QApplication a(argc, argv);
MainWindow w;
w.show();
return a.exec();
–
–
–
–
–
Better use query with parameters to prevent different type convertation issues in SQL
QString execute = "SELECT COUNT(name) FROM table1 WHERE surname=? AND occupation=?;";
QSqlQuery query;
query.prepare(execute);
query.bindValue(0, surname);
query.bindValue(1, occupation);
query.exec();
if (query.next()) {
rows= query.value(0).toInt();
} else {
qDebug() << query.lastError(); //check your error here
–
–
QSqlDatabase _sql;
QSqlQuery _query(_sql);
QString execute_stat = "SELECT COUNT(name) FROM table1 WHERE surname='%1' AND
occupation='%2';";
_query.prepare(execute_stat.arg(surname, occupation));
q.exec();
int rows= 0;
if (q.next()) {
rows= q.value(0).toInt();
–
Yours :
QString execute = "SELECT COUNT(name) FROM table1 WHERE surname='%1' AND occupation='%2';
Should Be :
QString execute = "SELECT COUNT(name) FROM table1 WHERE surname='%1' AND occupation='%2';"
–
To be honest, I don't know why neither of the suggested methods above did the job.
However, the simpliest version worked for me:
QString execute = "SELECT COUNT(*) FROM table1 WHERE (name_or_nickname='" + name + "') AND (surname='" + surname + "') AND (occupation='" + occupation + "');";
Thank you for all your help.
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
