[Qt][SQLite] Two problems with SQLite.

Hello,

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

First problem:

Firstly I give some code.

Code:

---

#include <QtGui>
#include <QtSql>
 
#include "listresourcesdialog.h"
#include "ui_listresourcesdialog.h"
 
void listResourcesDialog::initializeModel(QSqlQueryModel *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:

Code:

---

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:

Code:

---

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?

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

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?

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:

Code:

---

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:

Code:

---

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

---

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

Quote:

---

Originally Posted by toutarrive

Code:

---

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:

Code:

---

SELECT COUNT(name) FROM employee WHERE year = 1964

---

If your results only some rows you can consider iterating over the result set in Qt instead of a new database query.

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