Using bound values in insert gives "parameter count mismatch" error in SQLite

[Jeffb]

Hi all

Any help is appreciated.

This is the error output:
ROLLBACK Parameter count mismatch

This is the create statement for the SQLite database table entered through the command line:
create table Contacts(contactID integer primary key, name text, URL text, contactType text);

This is the relevant code:
db.transaction();
QSqlQuery* aQuery = new QSqlQuery();
aQuery->prepare("insert into Contacts (contactID, name, URL, contactType) "
"values (?, ?, ?, ?)");
aQuery->addBindValue(5);
aQuery->addBindValue("New One");
aQuery->addBindValue("www.newone.com");
aQuery->addBindValue("web");
aQuery->exec();
if (!aQuery->isActive())
{
db.rollback();
cout << "ROLLBACK" << qPrintable(aQuery->lastError().text()) << endl;
}
else
{
db.commit();
cout << "COMMIT" << endl;
}

Cheers
Jeff

[JD2000]

Your record names have spaces in them!

[Jeffb]

Hi JD2000

What do you mean?

Also I just changed the query to a simple:
aQuery = new QSqlQuery();
aQuery->exec("insert into contacts (contactID, name, URL, contactType) values (4, 'New One', 'www.newone.com', 'web')");

and got this error:
"no such table: contacts Unable to execute statement"

So have I actually connected to the database properly? This is the code:
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setHostName("localhost");
db.setDatabaseName("test.db");
if (!db.open())
{
cout << "DB Didn't Open: " << qPrintable(db.lastError().text()) << endl;
}

Since I'm not getting the error code for not opening, I must be getting a connection somewhere.
Just to check, QT comes with SQLite as part of <QtSQL> so I don't have to add anything else do I? For instance somewhere in the Qt documentation it talks about linking in the driver to the Qt Library.

[JD2000]

In your create table statement, I believe that field names with spaces need to be quoted.

[Lesiok]

In firts post You are using name of table "Contacts", now you are using "contacts". And this is why You have an error : "no such table: contacts Unable to execute statement".

[JD2000]

In your create table statement, I believe that field names with spaces need to be quoted.

Apologies, I misread the 'text' data types as being part of the respective field names, this is not what caused the mismatch.

You could try explicity stating which DB is being queried:

QSqlQuery* aQuery = new QSqlQuery(db);
This sometimes helps

Failing that
You can also compare the number of parameters QT thinks it has

const QVector<QVariant> values = boundValues();
int paramCount = values.count();

against how many sqlite thinks it has

int paramCount = sqlite3_bind_parameter_count(aQuery);

[Jeffb]

@Lesiok
I tried it with both uppercase and lowercase c for contacts and it still doesn't work.

@JD2000
I'll keep those in mind but think I'll try and get the "no such table Contacts" error sorted out first.
I tried specifying the database as you suggested but it didn't help.

I tried a simple select statement and still get the "DIDN'T WORK: no such table: Contacts Unable to execute statement" error:

aQuery->exec("select name, URL, contactType from Contacts where contactID = 2");
if (!aQuery->isActive())
{
cout << "DIDN'T WORK: " << qPrintable(aQuery->lastError().text()) << endl;
}
else
{
while (aQuery->next()) {
QString aName = aQuery->value(1).toString();
QString aUrl = aQuery->value(2).toString();
QString aContactID = aQuery->value(3).toString();
cout << "Name is " << qPrintable(aName) << ", URL is " << qPrintable(aUrl) << ", ContactID is " <<
qPrintable(aContactID) << endl;
}
cout << "COMMIT" << endl;
}

[Lesiok]

First please use tag CODE for examples. It will be more readable.
Second show us TRUE code - full method or procedure in which You try SQL.

[Jeffb]

I got the select and insert worked out.
Insert now works with bound values.

But now I can't get the update working - I get the same error "parameter count mismatch".
Relevant code is below. Note that the QSqlQuery is returned as a pointer from the Contacts Class and executed in main.

Contacts class


QSqlQuery* Contacts::getUpdateSQLQueryPtr()
{
QSqlQuery *aQuery = 0;

if (contactObjectInUse)
{
aQuery = new QSqlQuery();
QString updateStatement = "\"update Contacts set ";
if (nameInUse)
updateStatement += "name = :name, ";
if (urlInUse)
updateStatement += "url = :url, ";
if (contactTypeInUse)
updateStatement += "contactType = :contactType, ";
updateStatement += "where contactID = :contactID\"";
int i = updateStatement.indexOf(", where");
// strip the final comma out
if (i >= 0)
updateStatement.remove(i, 1);

aQuery->prepare(updateStatement);
cout << "Contacts update statement: " << qPrintable(updateStatement) << endl;

if (nameInUse)
aQuery->bindValue(":name", name);
if (urlInUse)
aQuery->bindValue(":url", url);
if (contactTypeInUse)
aQuery->bindValue(":contactType", contactType);
aQuery->bindValue(":contactID", contactID);
}
return aQuery;


Note: name, url and contactType are all QStrings. contactID is an int.

main


QPointer<Contacts> aContact = new Contacts();
aQueryPtr = new QSqlQuery();
aQueryPtr->exec("select url, contactType from contacts where contactID = 4");
aQueryPtr->next();
// set aContact to selected values;
aContact->setContactID(4);
aContact->setUrl(aQueryPtr->value(0).toString());
aContact->setContactType(aQueryPtr->value(1).toString());

// set aContact variables to new values;
aContact->setUrl("www.a_really_cool_url");


aQueryPtr = aContact->getUpdateSQLQueryPtr();
// check bound values
QMapIterator<QString, QVariant> i(aQueryPtr->boundValues());
while (i.hasNext())
{
i.next();
cout << qPrintable(i.key()) << ": "
<< qPrintable(i.value().toString()) << endl;
}
int numBoundValues = aQueryPtr->boundValues().size();
cout << "Number of bound values = " << qPrintable(QString::number(numBoundValues)) << endl;
//int paramCount = sqlite3_bind_parameter_count(aQueryPtr);
//cout << "SQLite number of bound values = " << qPrintable(QString::number(paramCount)) << endl;

aQueryPtr->exec();
cout << " QUERY IS: " << qPrintable(aQueryPtr->lastQuery()) << endl;
delete aContact;


Also I tried using sqlite3_bind_parameter_count to see how many bound values SQLite thought it had but I got an error:
'sqlite3_bind_parameter_count was not declared in this scope'.
How do I fix that?

The output is below

:contactID: 4
:contactType: web
:url: www.a_really_cool_url
Number of bound values = 3
QUERY IS: "update Contacts set url = :url, contactType = :contactType where contactID = :contactID"
ROLLBACK - Parameter count mismatch

Thanks for all the help
Jeff

[Jeffb]

Fixed it.

It was in getUpdateSQLQueryPtr() lines 8 and 15. I was surrounding the string with quotation marks.

Cheers
Jeff

[JD2000]

Glad you solved it,

FYI 'sqlite3_bind_parameter_count' is included in "sqlite3.h"

[Jeffb]

Thanks JD

Thats good to know for future use.

Cheers
Jeff

[remy_david]

I had a similar problem leading to "parameter count mismatch" error.
I was trying to bind the table name like:


QSqlQuery q;
q.prepare("SELECT * FROM :table");
q.bind(":table", "myTable");
q.exec();


This is not possible, one can only bind filed values.
Could be useful for future readers.

[curtwagner1984]

I had a similar problem leading to "parameter count mismatch" error.
I was trying to bind the table name like:


QSqlQuery q;
q.prepare("SELECT * FROM :table");
q.bind(":table", "myTable");
q.exec();


This is not possible, one can only bind filed values.
Could be useful for future readers.

Your post helped my figure out what I was doing wrong with the same problem.
It's should be pointed out that it's also impossible to bind column names like so:


query.prepare("INSERT INTO Mytable (?,?) VALUES (?,?))"
query.addBindValue("columnOne");
query.addBindValue("columnTwo");
query.addBindValue("firstValue");
query.addBindValue("secondValue");

[d_stranz]

It should be pointed out that it's also impossible to bind column names like so:

That's why the method is named addBindValue(). It isn't a general purpose "substitute anything for anything" method, it is specifically for binding variable values to VALUES() placeholders in a query.

If you need to specify things like table and column names at run time, then construct the prepare() statement as a QString:



QString myTableName = "MyTable";
QString column1Name = "columnOne";
QString column2Name = "columnTwo";
QString queryStr = QString( "INSERT INTO %1 (%2,%3) VALUES (?,?);" ).arg( myTableName ).arg( column1Name ).arg( column2Name );

QSqlQuery query;
query.prepare( queryStr );
query.addBindValue( "firstValue" );
query.addBindValue( "secondValue" );
query.exec();