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

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

To copy to clipboard, switch view to plain text mode 

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

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

To copy to clipboard, switch view to plain text mode 

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

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

To copy to clipboard, switch view to plain text mode 

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...sert_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);

QSqlQuery query(this->db);

To copy to clipboard, switch view to plain text mode 

To:

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

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

To copy to clipboard, switch view to plain text mode 

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

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

To copy to clipboard, switch view to plain text mode 

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

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

To copy to clipboard, switch view to plain text mode 

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

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

To copy to clipboard, switch view to plain text mode 

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

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

To copy to clipboard, switch view to plain text mode 

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

#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;
}

To copy to clipboard, switch view to plain text mode 

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

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

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

To copy to clipboard, switch view to plain text mode 

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

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

To copy to clipboard, switch view to plain text mode 

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