Problem inserting in SQLITE

Fransebas

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
}

dheerendra

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.

Fransebas

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.

koahnig

@Fransebas

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

ambershark

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

SGaist

Hi,

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

ambershark

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

Fransebas

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

Fransebas

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

mrjj

@Fransebas

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

michalos

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.

ambershark

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

karti gesar

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();

SGaist

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