SetQuery INSERT

gabor53

I'm using an sqlite database with the following INSERT:
model->setQuery ("INSERT INTO People (ID,FirstName,LastName)""VALUES('4','John','Smith");
I'd like to use variables as values such as ID for for, Fname for John, Lname for Smith.
What should I change to make the query work?
I tried
setQuery ("INSERT INTO People (ID,FirstName,LastName)""VALUES(ID,Fname,Lname");
but it doesn't work.
Thank you for your help.

kshegunov

@gabor53
Hello,
QSqlQueryModel (assuming that you're using that class) is for displaying data. I don't understand what the model is supposed to display if you give it an insert query ... Maybe elaborate a bit on what you're trying to achieve? For example, if you're interested in just inserting data into the SQL database, you could use the QSqlQuery class to do just that.
As a side note, you seem to have some quotes doubled in the middle of the queries and the strings actually don't seem quite right, is this a typo?

Kind regards.

mjsurette

@kshegunov

As @gabor53 said, you're not looking for a model to insert data. What you need is QSqlQuery. In particular the prepare and execute methods.

gabor53

I tried to fix the problem like this:

#include <QCoreApplication>
#include <QDebug>
#include <QtGui>
#include <QtCore>
#include <QtSql>
#include <QtDebug>
#include <QSqlQueryModel>

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

    QSqlDatabase db;
    QSqlQuery query;

    db = QSqlDatabase::addDatabase ("QSQLITE");
    db.setDatabaseName ("C:/Programming/Qtsamples/Variabletodb/database.db");
    db.open ();

    if(!db.open ())
    {
        qDebug() << "The database is not open!";
    }
	else
    {
        qDebug() << "The database is open!";
    }

    QString ID = "213";
    QString Fname = "John";
    QString Lname = "Smith";

    query.prepare ("INSERT INTO People (ID, FirstName, LastName)"
                   "VALUES(:ID, :FirstName, :LastName)");
    query.bindValue (0,ID);
    query.bindValue (1,Fname);
    query.bindValue (2,Lname);
    query.exec ();
    db.close ();


    return a.exec();
}

I got the following messages:
The database is open!
QSqlQuery::prepare: database not open

Of course nothing was written into the database. Please tell me what I did wrong at this time. Thank you for your help.

kshegunov

@gabor53
Hello,
You should use named bindings, like this:

query.bindValue(":ID", ID);
query.bindValue(":FirstName", Fname);
// ... and so on

Additionally, how does

query.prepare ("INSERT INTO People (ID, FirstName, LastName)"
                   "VALUES(:ID, :FirstName, :LastName)");

even compile? you have two strings on two lines, and the function expects a single string ...

jsulm

@kshegunov This is valid syntax: compiler combines both string literals into one string.

jsulm

@gabor53 I think you should pass the database to the query:

QSqlQuery query(db);

kshegunov

@jsulm

This is valid syntax: compiler combines both string literals into one string.

It is possible, I have never seen it before though.

mjsurette

@gabor53
@jsulm is right, you need to pass the database to the query. I always do this after the database is open. This assumes of course that the database is already created and the table defined.

@kshegunov C++ and I believe C as well concatenates two or more string literals separated only by whitespace. This is quite useful for maintaining readability in long SQL queries.

SGaist

Hi,

@jsulm and @mjsurette Passing the database to QSqlQuery's constructor is only needed if you don't use the default connection which is not the case.

However, query is created before the database is added thus using an invalid connection . That's way there's a complaint about a closed database.

@kshegunov Indeed, the two strings are concatenated but here there's a space that will be missing in the current case.

What should also be done is check the return value of query.exec() and print the related error if it fails. Otherwise there's no way to have a real clue of what is going on.

kshegunov

@mjsurette @SGaist
Thanks for the clearing that up. I never knew this to be possible and I always have used the \ + newline approach (the way macros are broken down to multiple lines).

gabor53

Thanks for all your help.
I added

 qDebug()<< query.lastError ();

and I got the following message:
QSqlError("","Driver not loaded","Driver not loaded").

 qDebug()<< query.lastError ();

returns false.
Any idea what's wrong?
Thank you.

gabor53

The following code worked:

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

    QSqlDatabase db;


    db = QSqlDatabase::addDatabase ("QSQLITE");
    db.setDatabaseName ("C:/Programming/Qtsamples/Variabletodb/database.db");
    db.open ();
	QSqlQuery query(db);

    if(!db.open ())
    {
        qDebug() << "The database is not open!";
    }
	else
    {
        qDebug() << "The database is open!";
    }

    QString ID = "213";
    QString Fname = "John";
    QString Lname = "Smith";

    query.prepare ("INSERT INTO People (ID, FirstName, LastName)"
                   "VALUES(:ID, :FirstName, :LastName)");
    query.bindValue (":ID",ID);
    query.bindValue (":FirstName",Fname);
    query.bindValue (":LastName",Lname);
    query.exec ();



//    qDebug() <<  query.exec();

//    qDebug()<< query.lastError ();

    db.close ();


    return a.exec();
}

Thank you for your help!