Problem inserting in SQLITE



  • Hi, I'm trying to make a code to inset into a sqlite3 database but it promps:

    "INSERT INTO CLIENTS(NAME, LAT, LNG, STATUS, DUEDATE, DEBT) VALUES (?, ?, ?, ?, ?, ?)"
    addPerson error:   QSqlError("", "Parameter count mismatch", "")
    

    I don't believe the binding is the error because I also use manual binding and prompt other error so I believe is a error with the DB connection:

    "INSERT INTO CLIENTS(NAME, LAT, LNG, STATUS, DUEDATE, DEBT) VALUES ('cliente1' ,0,0 , 'good' ,'1990:11:02' ,123413)"
    addPerson error:   QSqlError("", "Unable to fetch row", "No query")
    

    The code is this:

    #include "dbinterface.h"
    #include <QDebug>
    #include <QSqlQuery>
    #include <QSqlError>
    
    DBInterface::DBInterface(QString dbSrc)
    {
        this->db = QSqlDatabase::addDatabase("QSQLITE");
        this->db.setHostName(dbSrc);
    
        if (!db.open())
        {
            qDebug() << "Error: connection with database fail";
        }
        else
        {
            qDebug() << "Database: connection ok";
        }
        this->db.close();
    }
    DBInterface::DBInterface(){}
    
    int DBInterface::addClient(const Client  & cl)
    {
        int exec = 0;
        this->db.open();
    
        if (this->db.isOpen()){
    
            QSqlQuery query;
    
            // This is string is for debugging, spoiler alert it didn't work either
            QString bindValues = "\'" + cl.name + "\' ," + QString::number(cl.lat) + "," + QString::number(cl.lng) + " , "
                                + "\'"  + cl.status + "\' ," + "\'"  + cl.duedate.toString("yyyy:MM:dd") + "\' ," + QString::number((double)cl.debt);
    
            query.prepare("INSERT INTO CLIENTS(NAME, LAT, LNG, STATUS, DUEDATE, DEBT) VALUES (:name, :lat, :lng, :status, :duedate, :debt)");
            query.bindValue(":name", cl.name);
            query.bindValue(":lat", cl.lat);
            query.bindValue(":lng", cl.lng);
            query.bindValue(":status", cl.status);
            query.bindValue(":duedate", cl.duedate);
            query.bindValue(":debt", cl.debt);
            qDebug() << query.executedQuery();
    
            if(query.exec())
            {
                exec = 0;
            }
            else
            {
                qDebug() << "addPerson error:  "
                         << query.lastError();
                exec = -1;
            }
        } else
        {
            qDebug() << "Connection is closed";
        }
    
        this->db.close();
        return exec; // 0 if succes, else otherwise
    }
    
    


  • It definitely issue with you query. Not sure what is it. Can you check the column names exactly matching ? DB connection successful ? Are you able to insert into DB from command line utility of SQLite ? Try your query on command line and then try in program. If it is successful in command line, it will be surely successful in program as well.



  • Yes it worked on the terminal (mac user), How do I make sure of the DB connection is successful?

    sqlite> INSERT INTO CLIENTS(NAME, LAT, LNG, STATUS, DUEDATE, DEBT) VALUES ('cliente1' ,0,0 , 'good' ,'1990:11:02' ,123413);
    sqlite> INSERT INTO CLIENTS(NAME, LAT, LNG, STATUS, DUEDATE, DEBT) VALUES ('cliente1' ,0,0 , 'good' ,'1990:11:02' ,123413);
    sqlite> SELECT * FROM CLIENTS;
    1|hi|0.0|9.0|good|4:4:4|3245.0 // previous test
    2|cliente1|0.0|0.0|good|1990:11:02|123413.0
    3|cliente1|0.0|0.0|good|1990:11:02|123413.0
    sqlite>
    

    Another funny think is that the binding return "?" instead of the value, because that suppose to happen before the connection right?

    Maybe is the string of the path of the DB, because I do not add it to the resources file because I've heard that everything in the src file get compiled and I don't want to add the DB.

    PS: Thanks.


  • Moderators

    @Fransebas

    Stupid question did you check with your debugger that the values in cl are matching to what you want to write?


  • Moderators

    @Fransebas The code and syntax look right to me so there has to be an issue with the bound values (not being exactly what you think they are), or the database itself.

    I looked over that code again and again expecting to see a bad value somewhere and I just don't see it.

    Only thing I noticed that is weird is you open the database in the constructor but then close it at the end. That is a total waste. You should just leave the connection open and not reopen it in your addPerson function. Save yourself the overhead of opening and closing the db all the time. Unless this application shares the database and you don't want to use up the limited number of connections, or some other good reason to close it after every use. :)

    Maybe try moving addPerson to just doing a simple SELECT NAME, LAT, LNG, STATUS, DUEDATE, DEBT FROM CLIENTS and see if that query works correctly. Kind of a test to make sure the db works without doing an insert.

    Then what I would do if you can't find the bad value is start with a single value, i.e. INSERT INTO CLIENTS (NAME) VALUES(:name) and continue adding each item until it breaks. Then you'll know what value is causing it and it will give you and us a better understanding of why.


  • Lifetime Qt Champion

    Hi,

    AFAIK, currently, the SQLite plugin doesn't support named placeholder. You need to switch to positional placeholders.


  • Moderators

    @SGaist said in Problem inserting in SQLITE:

    Hi,

    AFAIK, currently, the SQLite plugin doesn't support named placeholder. You need to switch to positional placeholders.

    Well that would definitely do it, lol.



  • @SGaist Tested already whit positional and manually, but I'll do the check one by one as @ambershark suggested



  • @ambershark I just tested the SELECT and give me another error

    "SELECT * FROM CLIENTS"
    addPerson error:   QSqlError("", "Unable to fetch row", "No query")
    

    This is the code:

    QMap<int, Client> * DBInterface::fetchClient()
    {
        QMap<int, Client> * clMap = new QMap<int, Client>();
    
        if (this->db.isOpen()){
    
            QSqlQuery query;
    
            // This is string is for debugging, spoiler alert it didn't work either
    
            query.prepare("SELECT * FROM CLIENTS");
            qDebug() << query.executedQuery();
            int ID;
            QString name, status;
            float lat, lng, debt;
            QDate duedate;
    
            if(query.exec())
            {
                while (query.next())
                {
                    ID = query.value("ID").toInt();
                    name = query.value("NAME").toString();
                    status = query.value("STATUS").toString();
                    lat = query.value("LAT").toFloat();
                    lng = query.value("LNG").toFloat();
                    debt = query.value("DEBT").toFloat();
                    duedate = query.value("DEBT").toDate();
                    (*clMap)[ID] = Client(ID, name, lat, lng, status, debt, duedate);
                }
            }
            else
            {
                qDebug() << "addPerson error:  "
                         << query.lastError();
            }
        } else
        {
            qDebug() << "Connection is closed";
        }
    
        return clMap;
    }
    

    I believe my probelm is the path to the DB: my DB is in the same folder as the files and not in src file and called "test.db", how I have to write the path?.


  • Qt Champions 2016

    @Fransebas

    Hi if you mean the database is next to the .exe file then you can use
    qDebug() << "App path : " << qApp->applicationDirPath();



  • I'm no expert, but in Your first post there is

        this->db = QSqlDatabase::addDatabase("QSQLITE");
        this->db.setHostName(dbSrc);
    

    shouldn't it be something like:

    if(QSqlDatabase::contains("DBName"))
        {
            db = new QSqlDatabase::database("DBName"));
            qDebug() << "Database already exists, connecting to DB";
    
        }   else {
            db = new QSqlDatabase(QSqlDatabase::addDatabase("QSQLITE", "DBName"));
            db.setDatabaseName("memory.db");
    
            if(!_messengerDB->open()) {
                qDebug()<<"Cannot open database"
                    "Unable to establish a database connection.\n";
    
            }
         qDebug() << "Connected to database DBName.";
    
        }
    

    then You would know if the connetion is established correctly.


  • Moderators

    @michalos I wrote a quick test.. works fine for me. Here is my code that connected and worked fine. My db file was named test in the same directory as my binary:

    void DB::doWork()
    {
        // setup driver
        qDebug() << "Adding sqlite driver";
        QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
        qDebug() << "driver is valid? " << db.isValid();
    
        // connect to db
        db.setDatabaseName("./test");
    
        auto ret = db.open();
        qDebug() << "db is open? " << ret;
    
        // run quick select
        QSqlQuery q;
        qDebug() << "SELECT * FROM clients...";
        q.prepare("SELECT * FROM clients");
        q.exec();
        int idName = q.record().indexOf("name");
        while (q.next())
        {
            qDebug() << "name: " << q.value(idName).toString();
        }
    
        // quit
        QCoreApplication::quit();
    }
    

    Results:

    Adding sqlite driver
    driver is valid?  true
    db is open?  true
    name:  "mike"
    

    And the db file:

    [shockwave] ~/tmp/sqlite > sqlite3 test
    SQLite version 3.13.0 2016-05-18 10:57:30
    Enter ".help" for usage hints.
    sqlite> select * from clients;
    mike|100
    sqlite>
    


  • Try this....
    QSqlDatabase mdb=QSqlDatabase::addDatabase("QSQLITE");
    mdb.setDatabaseName("C:/sqlite/test.db");
    if(mdb.open())
    qDebug()<<"open";
    else
    qDebug()<<"not open";
    //Inserting the values in the database

    QSqlQuery query;
       query.prepare("INSERT INTO department (id, dept, emp_id) "
                     "VALUES (:id, :dept, :emp_id)");
       query.bindValue(":id", 1);
       query.bindValue(":dept", "XXXX");
       query.bindValue(":emp_id", 12000);
       query.exec();

  • Lifetime Qt Champion

    @karti-gesar Like I already wrote, the SQLite driver currently doesn't support named placeholders.


Log in to reply
 

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