[Qt][SQLite] Two problems with SQLite.

[Xandareva]

Hello,

I have got two problems and I don't know how to repair to run correctly.

First problem:

Firstly I give some code.


#include <QtGui>
#include <QtSql>

#include "listresourcesdialog.h"
#include "ui_listresourcesdialog.h"

void listResourcesDialog::initializeModel(QSqlQueryMode l *model)
{
model->setQuery("SELECT * FROM resources");
model->setHeaderData(0, Qt::Horizontal, QObject::tr("ID"));
model->setHeaderData(1, Qt::Horizontal, QObject::tr("Name"));
}


listResourcesDialog::listResourcesDialog(QWidget *parent) : QWidget(parent), ui(new Ui::listResourcesDialog)
{
ui->setupUi(this);
QSqlQueryModel model;
initializeModel(&model);
ui->tableView->setModel(&model);
}

listResourcesDialog::~listResourcesDialog()
{
delete ui;
}


void listResourcesDialog::changeEvent(QEvent *e)
{
QWidget::changeEvent(e);
switch (e->type()) {
case QEvent::LanguageChange:
ui->retranslateUi(this);
break;
default:
break;
}
}


All is ok but when I called constructor of this class I saw table but there is nothing there. But :) if I give in:



listResourcesDialog::listResourcesDialog(QWidget *parent) : QWidget(parent), ui(new Ui::listResourcesDialog)
{
ui->setupUi(this);
QSqlQueryModel model;
initializeModel(&model);
ui->tableView->setModel(&model);
QMessageBox::information(this, tr("blabla"), tr("blalbla"));
}


So I saw table with data from database, but if I accept QMessageBox (I click "OK") then data from table disappear. I don't know what is wrong and what can I do to run correctly.

Second problem:



QSqlQuery query;
query.prepare("SELECT * FROM profiles WHERE name = :name");
query.bindValue(":name", ui->lineEdit_2->text());



All is ok, when this query has been executed I have in object results of this query, but I don't know how can I check how records are in objects (like numRows()) I have to know how many records are in this object.

Has somebody any solutions?

[Lykurg]

to 1) create the model on the heap using new! In your code it gets deleted after the ctor!
to 2) use QSqlQuery::size()

[Xandareva]

ok, thanks :) First problem has been fixed and run correctly but second problem isn't fixed. QSqlQuery::size() return always "-1" in SQLite. Any ideas?

[toutarrive]

You can get the number of items from a query using SQlite syntax.
Simple example taken from one of my app. I give you some details for a better understanding of the relevant code snippet.
Database creation:


QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE","db"); //connection name specified
db.setDatabaseName(":memory:");
if (!db.open()) {
QMessageBox::critical(0, QT_TR_NOOP("Cannot open database"),
QT_TR_NOOP("Unable to establish a database connection.\n"
"Click Cancel to exit."), QMessageBox::Cancel);
return false;
}

QSqlQuery dbquery(db);
dbquery.exec("CREATE TABLE employee("
"id INTEGER PRIMARY KEY, "
"name VARCHAR(50) , "
"year INTEGER,"
"title VARCHAR(100)");


Getting the number of items regarding a select statement:


QSqlDatabase db= QSqlDatabase::database("db"); //getting acces to db database.
int first, second;
QSqlQuery queryDB(db);
queryDB.exec("SELECT COUNT(name) FROM (SELECT name FROM employee WHERE year= 1964 )" );
if ( queryDB.next() ) first= queryDB.value(0).toInt();


queryDB.exec("SELECT COUNT(title) FROM (SELECT title FROM employee WHERE year= 2005) " );
if ( queryDB.next() ) second= queryDB.value(2).toInt();
// and so on

[Xandareva]

good idea ;-)
Thanks for all.
Please topic closed.

[Lykurg]

queryDB.exec("SELECT COUNT(name) FROM (SELECT name FROM employee WHERE year= 1964 )" );
The problem with that solution is that you have to make two querries, one for the values and one for the count. That's a disadvantage of SQLite. (I thougt a have read a solution for only ony query but I can't remember right now...)
Anyway, I would avoid the subquery and simply use:SELECT COUNT(name) FROM employee WHERE year = 1964If your results only some rows you can consider iterating over the result set in Qt instead of a new database query.

[waynew]

SELECT count(*), column1, column2 from table_name
group by column1, column2;