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.
-
@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. -
Hi,
AFAIK, currently, the SQLite plugin doesn't support named placeholder. You need to switch to positional placeholders.
-
@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.
-
@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?.
-
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.
-
@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 databaseQSqlQuery 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();
-
@karti-gesar Like I already wrote, the SQLite driver currently doesn't support named placeholders.