QT5 and SQLLite: Parameter count mismatch upon Insert

[skruffynerherder]

I am having issues making my program insert data into an SQLLite db file. I am a bit lost due to the ambigious error message aswell, as I am passing the correct parameters to the insert command(?)

Here is the SQL Lite Table structure:


CREATE TABLE inventory (
id INT PRIMARY KEY NOT NULL,
name CHAR(120) NOT NULL,
description TEXT,
category INT,
employee INT,
location INT
);

And here is my QT/C++ Code:



QSqlQuery query(this->db);

if (!this->db.open()) {
qDebug() <<this->db.lastError().text();
}

query.prepare
("INSERT INTO inventory (name, description, category) VALUES (?,?,?)");
query.addBindValue(name);
query.addBindValue(description);
query.addBindValue(category);



if (!query.exec()) {
qDebug() <<query.lastError().text();
}


For the record, the QSqlDatabase object is initiated in the constructor, and I am able to run SELECT queries from the database. So the connection works ....




QSettings settings;
this->db = QSqlDatabase::addDatabase("QSQLITE", "NETTO_CONNECTION");
this->db.setDatabaseName(settings.value("db").toString());


Any ideas to what I am doing wrong here?

[ChrisW67]

I don't know about the specific error message, but since "id" is declared not null you need to provide a value on insert.

BTW. The id column is not an alias for the Sqlite rowid because its data type is not quite right

[skruffynerherder]

I don't know about the specific error message, but since "id" is declared not null you need to provide a value on insert.

BTW. The id column is not an alias for the Sqlite rowid because its data type is not quite right

Hmm, I thought that auto incremented as I read in the SQLLITE docs that the primary key is aliased to rowid?

Anyway, I did try to set the "ID" column manually, but still no dice ....

[ChrisW67]

The column type must be "INTEGER" in full to get he magic meaning.
http://www.sqlite.org/faq.html#q1

Is the object "query" constructed using the connection "NETTO_CONNECTION" or is it trying to use the default connection?

[skruffynerherder]

The column type must be "INTEGER" in full to get he magic meaning.
http://www.sqlite.org/faq.html#q1


Alright, I need to stop blindly trusting google and third party sites then, I was following documentation from this site:

http://www.tutorialspoint.com/sqlite/sqlite_insert_query.htm


Is the object "query" constructed using the connection "NETTO_CONNECTION" or is it trying to use the default connection?

Changed:


QSqlQuery query(this->db);

To:


QSqlQuery query(this->db.database("NETTO_CONNECTION"));

And the table structure now reads:


CREATE TABLE inventory ( id INTEGER PRIMARY KEY NOT NULL UNIQUE, name CHAR(120) NOT NULL, description TEXT, category INTEGER, employee INTEGER, location INTEGER );

Still no dice, the same output from QSqlQuery via qDebug(): "Parameter count mismatch"

(I did tell QSqlQuery to use NETTO_CONNECTION on an earlier attempt, but as I got increasingly more frustrated with the problem I reshuffeled the code blindly in a desperate attempt to locate the problem ... but the INTEGER value was new to me, thanks for the heads up on that one!)

[skruffynerherder]

Allright, revisiting this thread as I have come up with a solution to my problem, and not contributing to a closure when you have found a workaround is bad form. Basically the solution was be more competent. (even though I have no idea why my initial approach didn’t work in the first place, but I digress). I scrapped the notion of running direct queries on the Sqlite database, and switched my entire codebase over to QT’s model view architecture. So where my code previously looked like this:


QSqlQuery query(this->db);

if (!this->db.open()) {
qDebug() <<this->db.lastError().text();
}

query.prepare
("INSERT INTO inventory (name, description, category) VALUES (?,?,?)");
query.addBindValue(name);
query.addBindValue(description);
query.addBindValue(category);



if (!query.exec()) {
qDebug() <<query.lastError().text();
}

It now looks like this:


QHash<QString, QVariant> tableInsert;

tableInsert["name"] = ui->inputName->text();
tableInsert["description"] = ui->inputDescription->toPlainText();
tableInsert["category"] = ui->inputCategory->currentData().toInt();

if (!this->inventory->insert(tableInsert)){
qDebug() << this->inventory->getModel().lastError().text();
} else {
ui->inputName->clear();
ui->inputDescription->clear();

ui->inputName->setFocus();
}

The Inventory object has a QSqlTableModel attached to it, it’s “insert” method reads like this:


bool Dataset::insert(QHash<QString, QVariant> values) {

QHashIterator<QString, QVariant> i(values);
QSqlRecord record = this->model->record();

while (i.hasNext()){
i.next();
record.setValue(i.key(), i.value());
}

this->model->insertRecord(-1, record);
return this->model->submitAll();
}


(where the model object is offcourse an instance of QSqlTableModel). This way I not only get more readable code, but I manage to abstract the code into a more logical workflow. Let me know if you have any comments or suggestions for improvement

[ChrisW67]

This test program mirrors your starting point with the data type corrected:


#include <QtCore>
#include <QtSql>

int main(int argc, char **argv)
{
QCoreApplication app(argc, argv);

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName(":memory:");
if (db.open()) {
QSqlQuery query;

bool ok = query.exec(
" CREATE TABLE inventory ( "
" id INTEGER PRIMARY KEY NOT NULL, "
" name CHAR(120) NOT NULL, "
" description TEXT, "
" category INT, "
" employee INT, "
" location INT ); ");
qDebug() << "Create" << ok;

ok = query.prepare("INSERT INTO inventory (name, description, category) VALUES (?,?,?)");
qDebug() << "Prepare" << ok;

query.addBindValue("name");
query.addBindValue("description");
query.addBindValue(1);

ok = query.exec();
qDebug() << "Insert" << ok << query.lastError();

if (query.exec("select id from inventory")) {
query.next();
qDebug() << "Id" << query.value(0).toLongLong();
}
}
return 0;
}

and it runs perfectly well when built with Qt 5.3 on Linux:


Create true
Prepare true
Insert true QSqlError("", "", "")
Id 1


Assuming your database is persistent I'd check that the pre-existing inventory table is correctly defined.

[skruffynerherder]

Assuming your database is persistent I'd check that the pre-existing inventory table is correctly defined.

I haven't changed the table definition after I switched the method for inserting the data, so I really doubt there is anything wrong with the inventory table. According to SQL browser it is now defined as such:


CREATE TABLE `inventory` (
`id` INTEGER NOT NULL UNIQUE,
`name` CHAR(120) NOT NULL,
`description` TEXT,
`category` INTEGER,
`employee` INTEGER,
`location` INTEGER,
PRIMARY KEY(id)
);

[ChrisW67]

Your definition of the id colum is neither an alias for the automatic rowid nor will it accept a NULL value. Consequently your insert must provide a value for the id column, and it does not.

[skruffynerherder]

Your definition of the id colum is neither an alias for the automatic rowid nor will it accept a NULL value. Consequently your insert must provide a value for the id column, and it does not.

"With one exception noted below, if a rowid table has a primary key that consists of a single column and the declared type of that column is "INTEGER" in any mixture of upper and lower case, then the column becomes an alias for the rowid."

https://www.sqlite.org/lang_createtable.html

The 'id' column doesn't accept NULL but it certainly auto increments upon insertion of new row with an automatic value without explicitly providing this...