Need Help with QT and SQLite

[chetu1984]

Hi,
In my application I want to use a SQLite Database which I have it in the File . I created the connection as shown in the examples and I want display only one value from the the data base in a label on the main window.

this is the code i have

QSqlQuery qwery;
    qwery.exec("Select pwd from pwdtbl where Role = 1");
    QSqlRecord record = qwery.record();
    QString result = qwery.value(record.indexOf("pwd")).toString();
    ui->lblMWinput->text()= result;

QSqlQuery qwery;
    qwery.exec("Select pwd from pwdtbl where Role = 1");
    QSqlRecord record = qwery.record();
    QString result = qwery.value(record.indexOf("pwd")).toString();
    ui->lblMWinput->text()= result;

To copy to clipboard, switch view to plain text mode 

if this is not the right way can please someone direct me in the right way ..

Thanks,
Regards.

[ChrisW67]

From the QSqlQuery::exec () docs:

After the query is executed, the query is positioned on an invalid record and must be navigated to a valid record before data values can be retrieved (for example, using next()).

You should also check that the exec() call succeeded rather than discard its return value.

[chetu1984]

First of all thanks ChrisW67 for the Quick response

Coming to the Question now i am trying it this way ..

My code is
Connection. h

#ifndef CONNECTION_H
#define CONNECTION_H
 
 
#include <QMessageBox>
#include <QtSql/QSQLiteDriver>
#include <QtSql/QSqlError>
#include <QtSql/QSqlQuery>
#include <QDebug>
 
 
 
 
static bool createConnection()
{
       QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
 
       db.setDatabaseName("./RMD0_2.db");
 
       db.open();
 
       qDebug()<< db <<db.isOpen();
 
       if (!db.open()) {
           QMessageBox::critical(0, qApp->tr("Cannot open database"),
               qApp->tr("Unable to establish a database connection.\n"
                        "This example needs SQLite support. Please read "
                        "the Qt SQL driver documentation for information how "
                        "to build it.\n\n"
                        "Click Cancel to exit."), QMessageBox::Cancel);
           return false;
       }
 
           return true;
 
 
 
}
 
 
#endif // CONNECTION_H

#ifndef CONNECTION_H
#define CONNECTION_H


#include <QMessageBox>
#include <QtSql/QSQLiteDriver>
#include <QtSql/QSqlError>
#include <QtSql/QSqlQuery>
#include <QDebug>




static bool createConnection()
{
       QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");

       db.setDatabaseName("./RMD0_2.db");

       db.open();

       qDebug()<< db <<db.isOpen();

       if (!db.open()) {
           QMessageBox::critical(0, qApp->tr("Cannot open database"),
               qApp->tr("Unable to establish a database connection.\n"
                        "This example needs SQLite support. Please read "
                        "the Qt SQL driver documentation for information how "
                        "to build it.\n\n"
                        "Click Cancel to exit."), QMessageBox::Cancel);
           return false;
       }

           return true;



}


#endif // CONNECTION_H

To copy to clipboard, switch view to plain text mode 

And the main.cpp

#include <QtGui/QApplication>
#include "mainwindow.h"
#include <connection.h>
#include <QMessageBox>
 
int main(int argc, char *argv[])
{
    QApplication a(argc, argv);
 
    if (!createConnection()){
 
      return 1;
    }
 
    MainWindow w;
    w.show();
 
    return a.exec();
}

#include <QtGui/QApplication>
#include "mainwindow.h"
#include <connection.h>
#include <QMessageBox>

int main(int argc, char *argv[])
{
    QApplication a(argc, argv);

    if (!createConnection()){

      return 1;
    }

    MainWindow w;
    w.show();

    return a.exec();
}

To copy to clipboard, switch view to plain text mode 

The mainwindow.cpp

void MainWindow::menuAction()
{
 
   QSqlQuery q ;
   QString qexe= "SELECT STAFF_ID FROM EVENT_STAFF WHERE EVENT_STAFF_ID = 2";
   q.prepare(qexe);
   if(!q.exec())
       qDebug()<< q.lastError();
   else
      mm.show();
 
}

void MainWindow::menuAction()
{

   QSqlQuery q ;
   QString qexe= "SELECT STAFF_ID FROM EVENT_STAFF WHERE EVENT_STAFF_ID = 2";
   q.prepare(qexe);
   if(!q.exec())
       qDebug()<< q.lastError();
   else
      mm.show();

}

To copy to clipboard, switch view to plain text mode 

my out put is :
QSqlDatabase(driver=""QSQLITE"", database=""./RMD0_2.db"", host="""", port=-1, user="""", open=true) true

and when i click on button menuAction in the mainwindow.cpp
QSqlError(-1, "Unable to fetch row", "No query")

what can be the problem.?

[waynew]

Chetu, for a test, try changing your code like this:

QSqlQuery q ;
q.exec( "SELECT STAFF_ID FROM EVENT_STAFF WHERE EVENT_STAFF_ID = 2");
q.last();
qDebug() << "query error is " << q.lastError(); // just to check
QString staff_id = q.value(0).toString(); // get the value from select field 0
qDebug() << "staff_id is now " << staff_id; // check the value

QSqlQuery q ;
q.exec( "SELECT STAFF_ID FROM EVENT_STAFF WHERE EVENT_STAFF_ID = 2");
q.last();
qDebug() << "query error is " << q.lastError(); // just to check
QString staff_id = q.value(0).toString(); // get the value from select field 0
qDebug() << "staff_id is now " << staff_id; // check the value

To copy to clipboard, switch view to plain text mode 

I think you will find this works.

You only need to do a query.prepare then a query .exec if you have parameters you need to substitute like event_staff_id using query.addBindValue(<value>);

[chetu1984]

Thanks for the help waynew

when i tried to execute the code as you said the output is as follows

Starting C:\Qt\2010.05\qt\TestRM-build-desktop\debug\TestRM.exe...
QSqlDatabase(driver=""QSQLITE"", database=""./RMD0_2.db"", host="""", port=-1, user="""", open=true) true
query error is QSqlError(1, "Unable to execute statement", "no such table: EVENT_STAFF")
QSqlQuery::value: not positioned on a valid record
staff_id is now ""

and the port = -1 what does that signify..

[chetu1984]

I am sorry .. the problem was with the database path ..

now everything works fine

[waynew]

Glad you got it working ok!