View Full Version : Using bound values in insert gives "parameter count mismatch" error in SQLite
Jeffb
27th April 2010, 14:11
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
27th April 2010, 14:30
Your record names have spaces in them!
Jeffb
27th April 2010, 14:42
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
27th April 2010, 14:59
In your create table statement, I believe that field names with spaces need to be quoted.
Lesiok
27th April 2010, 16:57
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
27th April 2010, 19:08
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
27th April 2010, 23:47
@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
28th April 2010, 07:39
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
29th April 2010, 00:31
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
29th April 2010, 06:59
Fixed it.
It was in getUpdateSQLQueryPtr() lines 8 and 15. I was surrounding the string with quotation marks.
Cheers
Jeff
JD2000
29th April 2010, 10:27
Glad you solved it,
FYI 'sqlite3_bind_parameter_count' is included in "sqlite3.h"
Thanks JD
Thats good to know for future use.
Cheers
Jeff
remy_david
4th February 2011, 09:34
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
13th May 2017, 06:24
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
16th May 2017, 03:51
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();
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.