SetQuery INSERT



  • 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.


  • Qt Champions 2016

    @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.



  • @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.



  • 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.


  • Qt Champions 2016

    @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 ...


  • Moderators

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


  • Moderators

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

    QSqlQuery query(db);
    

  • Qt Champions 2016

    @jsulm

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

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



  • @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.


  • Lifetime Qt Champion

    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.


  • Qt Champions 2016

    @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).



  • 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.



  • 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!


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.