Baisc SQLite insert - does not work

[johnnyturbo3]

Hi,

The code below should insert new data into the table. But it doesn't. Can anybody see anything wrong?

Thanks

#include <QApplication>
#include <QTSql>
#include <QMessageBox>
#include <QDebug>
#include <QString>
#include <fstream>
#include <String>
#include <QSqlQuery>
using namespace std;
 
 
bool createConnection(){
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("ManagerDB.db");
    if(!db.open()){
        QMessageBox::critical(0, qApp->tr("Cannot open database"),
                qApp->tr("Unable to establish a database connection.\n"
                "This program 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;
}
 
 
 
void insertData(){
 
      QSqlQuery query("INSERT INTO table1 (field1) "
                "VALUES (""\"203""\")");
      query.exec();
}
 
 
 
int main(int argc, char *argv[]){
        QApplication app(argc, argv);
        if (!createConnection()){
                return false;
        }
        insertData();
        return app.exec();
}

#include <QApplication>
#include <QTSql>
#include <QMessageBox>
#include <QDebug>
#include <QString>
#include <fstream>
#include <String>
#include <QSqlQuery>
using namespace std;


bool createConnection(){
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("ManagerDB.db");
    if(!db.open()){
        QMessageBox::critical(0, qApp->tr("Cannot open database"),
                qApp->tr("Unable to establish a database connection.\n"
                "This program 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;
}



void insertData(){

      QSqlQuery query("INSERT INTO table1 (field1) "
                "VALUES (""\"203""\")");
      query.exec();
}



int main(int argc, char *argv[]){
        QApplication app(argc, argv);
        if (!createConnection()){
                return false;
        }
        insertData();
        return app.exec();
}

To copy to clipboard, switch view to plain text mode 

[saa7_go]

Does table "table1" exist?

[Lykurg]

your " are really a mess. See QSqlQuery::prepare().

[waynew]

Your query is not hooked to a database connection. You have to do this before the query.prepare statement.

If you look at this: http://doc.trolltech.com/4.5/qtsql.html#details

You can see how to do it.
as Lykurg said,

query.prepare(...
query.addBindValue(....
query.exec();

[johnnyturbo3]

Thanks for the replies.

The database and 'table1' do exist.

The code now looks like this has been changed to use 'prepare', but unfortunately, it still doesn't work. Looking at examples for the web and official docs I can't see what is wrong with what is written. I am using NetBeans, so I have checked the SQL check box in the project properties in order to use the SQL libraries.

I use SQLite Admin http://sqliteadmin.orbmu2k.de/ to check to see if the data has been inserted. I've tried running the program with SQLite Admin both running and off - makes no difference.

#include <QApplication>
#include <QTSql>
#include <QMessageBox>
#include <QString>
#include <String>
#include <QSqlQuery>
using namespace std;
 
 
 
bool createConnection(){
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("C:\\NetBeans Projects\\SQLiteExample\\ManagerDB.db");
    if(!db.open()){
        QMessageBox::critical(0, qApp->tr("Cannot open database"),
            qApp->tr("Something bad happened"), QMessageBox::Cancel);
        return false;
    }
 
    QSqlQuery query;
    query.prepare("INSERT INTO table1 (field1) VALUES (:field1)");
    query.bindValue(":field1", "work damn it");
    query.exec();
    db.close();
    return true;
}
 
 
 
int main(int argc, char *argv[]){
    QApplication app(argc, argv);
    if(!createConnection()){
        return false;
    }
    return app.exec();
}

#include <QApplication>
#include <QTSql>
#include <QMessageBox>
#include <QString>
#include <String>
#include <QSqlQuery>
using namespace std;



bool createConnection(){
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("C:\\NetBeans Projects\\SQLiteExample\\ManagerDB.db");
    if(!db.open()){
        QMessageBox::critical(0, qApp->tr("Cannot open database"),
            qApp->tr("Something bad happened"), QMessageBox::Cancel);
        return false;
    }

    QSqlQuery query;
    query.prepare("INSERT INTO table1 (field1) VALUES (:field1)");
    query.bindValue(":field1", "work damn it");
    query.exec();
    db.close();
    return true;
}



int main(int argc, char *argv[]){
    QApplication app(argc, argv);
    if(!createConnection()){
        return false;
    }
    return app.exec();
}

To copy to clipboard, switch view to plain text mode 

[johnnyturbo3]

Do I need to build a SQLite driver like MySQL? I've read that SQLite is comes included in the Qt installation.

[waynew]

You are getting close, and frustrated as I can understand.
Unfortunately, the doc is a little fragmented and doesn't really give you a good complete example. We have all been through that before.

The problem is, as I stated earlier, that your query is not 'hooked' to your database connection.
Here is an example that might help:

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "myConnection");
   db.setDatabaseName("myDatabase.sqlite");
   db.open();
QSqlQuery query(db);
   query.prepare("SELECT  max(id) from myTable where call = ? ");
      query.addBindValue(searchCall);
      query.exec();
      query.last();
// now you can use debug to see the result

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "myConnection");
   db.setDatabaseName("myDatabase.sqlite");
   db.open();
QSqlQuery query(db);
   query.prepare("SELECT  max(id) from myTable where call = ? ");
      query.addBindValue(searchCall);
      query.exec();
      query.last();
// now you can use debug to see the result

To copy to clipboard, switch view to plain text mode 

So what you need to change in your code is:
1. the database file name extension to .sqlite
2. QSqlQuery query; to QSqlQuery query(db); // This is the 'hook'

Good luck and post again if you get stuck.

[johnnyturbo3]

Thanks for your help. Unfortunately, it still doesn't add data to my database. I created a new database with the .sqlite file extension, and with a table called table1 that has a field called field1.

I managed to get the same program to work with MySQL, so, I have no idea what is wrong.

bool createConnectionSQLite(){
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE","myConnection");
    db.setDatabaseName("C:\\NetBeans Projects\\SQLiteExample\\myDataBase.sqlite");
    db.open();
    if (!db.open()) {
        QMessageBox::critical(0, QObject::tr("Database Error"),
                              db.lastError().text());
        return false;
    }
    QSqlQuery query(db);
    query.prepare("INSERT INTO table1 (field1) VALUES (:field1)");
    query.bindValue(":field1", "work damn it");
    query.exec();
    db.close();
    return true;
}
 
 
int main(int argc, char *argv[]){
    QApplication app(argc, argv);
    if(!createConnectionSQLite()){
        return false;
    }
    return app.exec();
}

bool createConnectionSQLite(){
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE","myConnection");
    db.setDatabaseName("C:\\NetBeans Projects\\SQLiteExample\\myDataBase.sqlite");
    db.open();
    if (!db.open()) {
        QMessageBox::critical(0, QObject::tr("Database Error"),
                              db.lastError().text());
        return false;
    }
    QSqlQuery query(db);
    query.prepare("INSERT INTO table1 (field1) VALUES (:field1)");
    query.bindValue(":field1", "work damn it");
    query.exec();
    db.close();
    return true;
}


int main(int argc, char *argv[]){
    QApplication app(argc, argv);
    if(!createConnectionSQLite()){
        return false;
    }
    return app.exec();
}

To copy to clipboard, switch view to plain text mode 

[saa7_go]

How do you create the database and tables? If you are using other application, try to create your database and tables by code.

[pervlad]

I seems to me that trouble might be in this line

db.setDatabaseName("C:\\NetBeans Projects\\SQLiteExample\\myDataBase.sqlite");

db.setDatabaseName("C:\\NetBeans Projects\\SQLiteExample\\myDataBase.sqlite");

To copy to clipboard, switch view to plain text mode 

Use "/" instead of "\\". I believe that in Qt file path separator is always "/" regardless of OS on which application is developed or compiled. See QFile Detailed Description section.

Connect to database can be done without giving it name e.g. "myConnection", and in that case connection becomes default connection for particular application, and in that case it is not necessary to pass db as argument to a query constructor.

I do not get what is result of query.exec() when you debug? Check QSqlQuery::lastError() and QSqlQuery::numRowsAffected () functions;

Wild guess try opening transaction and committing it.

If you run this app, if it fails it will return 0 which means that it exited ok. If something is wrong app should exit with int value different then 0. In main.cpp file there is line return false; (int)false = 0 !

[johnnyturbo3]

I've tried all sorts of combinations. Defining absolute file path and omitting it. My first version of the code did not use a connection name, and had no query constructor.

Is there a way to test whether the SQLite plug-ins are installed/where they should be? If the syntax is okay, then there must be something wrong with my installation.

[pervlad]

Ok, if you tried replacing "\\" with "/".
There is a way to check is driver loaded: QSqlDatabase::isDriverAvailable(), QSqlDatabase::drivers().
I the beck of my mind I remember that somewhere in Qt documentation there are articles on how to install other sql drivers.
Did you try to build Qt sql lite example Cached Table which works with db stored in memory?

[pervlad]

I have built Qt example "Cached Table" which works with SqlLite in-memory database and it works i.e. connects to database. I changed one line in static bool createConnection() function
db.setDatabaseName(":memory:");
in to:
db.setDatabaseName("F:/Temp/test.sdb");//(":memory:");
Application created file "F:/Temp/test.sdb" and changes I have committed become permanent. I have not intall nor Qt SqlLite drivers nor SqlLite db engine it self, thus SqlLite db drvers are distributed and installed together with Qt distribution.

[tsp]

it still doesn't add data to my database

What is the return value from QSqlQuery::exec and in case it is false, what is the output from QSqlQuery::lastError? You can use qDebug to print the values to output window.

[johnnyturbo3]

query.exec() is returning 'false'
error: 'Parameter count mismatch'

[tsp]

Can you please copy/paste the piece of code that you used to create the SQL table?

[johnnyturbo3]

Some success now.
Previously, I created the database and table using other means i.e. not code. However, I included a CREATE TABLE statement in the code and it now seems to work.
Ultimately, I would like the program to read from an existing database and table created by another program.

QSqlQuery query(db);
    query.prepare("CREATE TABLE table1 (field1 varchar(50))");
    query.exec();
    query.prepare("INSERT INTO table1 (field1) VALUES (:field1)");
    query.bindValue(":field1", "work damn it");
    if(query.exec() == true){
        QMessageBox::critical(0, QObject::tr("Database Success"),
                              db.lastError().text());
    }else{
        QMessageBox::critical(0, QObject::tr("Database Error"),
                              query.lastError().text());
    }
    db.close();

QSqlQuery query(db);
    query.prepare("CREATE TABLE table1 (field1 varchar(50))");
    query.exec();
    query.prepare("INSERT INTO table1 (field1) VALUES (:field1)");
    query.bindValue(":field1", "work damn it");
    if(query.exec() == true){
        QMessageBox::critical(0, QObject::tr("Database Success"),
                              db.lastError().text());
    }else{
        QMessageBox::critical(0, QObject::tr("Database Error"),
                              query.lastError().text());
    }
    db.close();

To copy to clipboard, switch view to plain text mode 

Thanks

[saa7_go]

Quoting from Qt Doc.

QSQLITE File Format Compatibility

SQLite minor releases sometimes break file format forward compatibility. For example, SQLite 3.3 can read database files created with SQLite 3.2, but databases created with SQLite 3.3 cannot be read by SQLite 3.2. Please refer to the SQLite documentation and change logs for information about file format compatibility between versions.

Qt minor releases usually follow the SQLite minor releases, while Qt patch releases follow SQLite patch releases. Patch releases are therefore both backward and forward compatible.

Maybe, this explains why you fail inserting record into your table. I guess, SQLite Administrator using newer version of SQLite than SQLite provided by Nokia.

I rebuild qsqlite plugin with the newest sqlite sources(version 3.7.0.1), then create database using SQLite Administrator. I successfully insert/update/delete record and etc.

[johnnyturbo3]

That must be it.
I've switched from SQLite Admin to the FireFox plugin SQLite Manager to create my test DB's and everything seems to be working.......for now.

Thanks everyone for your help!