Problems with Quey

[SirBabyface]

Hi,

I've a problem in updating data using a Model/View approach.

If I try to do this:

bool PhonoLabSentenceSqlModel::setCode(int sentenceId, const QString &code)
{
	QSqlQuery query;
	query.prepare("UPDATE sentence SET code='?' WHERE id=?");
	query.addBindValue(code);
	query.addBindValue(sentenceId);
	if (!query.exec()) {
		return false;
	}
	return true;
}

bool PhonoLabSentenceSqlModel::setCode(int sentenceId, const QString &code)
{
	QSqlQuery query;
	query.prepare("UPDATE sentence SET code='?' WHERE id=?");
	query.addBindValue(code);
	query.addBindValue(sentenceId);
	if (!query.exec()) {
		return false;
	}
	return true;
}

To copy to clipboard, switch view to plain text mode 

But if change to this, it works fine.

bool PhonoLabSentenceSqlModel::setCode(int sentenceId, const QString &code)
{
	QSqlQuery query;
	QString s = "UPDATE sentence SET code='" + code + "' WHERE id=" + QString::number(sentenceId);
	query.prepare(s);
	if (!query.exec()) {
		return false;
	}
	return true;
}

bool PhonoLabSentenceSqlModel::setCode(int sentenceId, const QString &code)
{
	QSqlQuery query;
	QString s = "UPDATE sentence SET code='" + code + "' WHERE id=" + QString::number(sentenceId);
	query.prepare(s);
	if (!query.exec()) {
		return false;
	}
	return true;
}

To copy to clipboard, switch view to plain text mode 

Does anyone know what I'm doing wrong

[marcel]

What if you drop the single quotes for the code value? Maybe there's a parsing problem.

Regards

[SirBabyface]

I've also tried that. It is really strange.

I've also tested with double qoute ".

I'm using SQLITE as database engine.

[marcel]

Well, the "?" placeholder syntax is supported only by ODBC.
Do you connect using ODBC, or something else?

Regards

[SirBabyface]

I'm using Qt SQLITE plugin. But I've another method that I use the ? place holder, and it works fine.

bool PhonoLabSentenceSqlModel::insertRows (int row, int count, const QModelIndex & parent)
{
	beginInsertRows(parent, row, row);
 
	QSqlQuery query;
	QSqlDatabase db = QSqlDatabase::database();
	db.transaction();
 
	int id = generateId("sentence");
 
	query.prepare("INSERT INTO sentence(id, actionid, objectid) VALUES(?, 0, 0)");
	query.addBindValue(id);
	if (!query.exec()) {
		db.rollback();
		return false;
	}
 
	QStringList languages;
	languages << "pt" << "en" << "es";
	QString lang;
	foreach (lang, languages) {
		query.prepare("INSERT INTO sentence_translation(sentenceid, language, sentencetext) VALUES(?, ?, '')");
		query.addBindValue(id);
		query.addBindValue(lang);
		if (!query.exec()) {
			db.rollback();
			return false;
		}
	}
 
	db.commit();
	endInsertRows();
	return true;
}

bool PhonoLabSentenceSqlModel::insertRows (int row, int count, const QModelIndex & parent)
{
	beginInsertRows(parent, row, row);

	QSqlQuery query;
	QSqlDatabase db = QSqlDatabase::database();
	db.transaction();

	int id = generateId("sentence");

	query.prepare("INSERT INTO sentence(id, actionid, objectid) VALUES(?, 0, 0)");
	query.addBindValue(id);
	if (!query.exec()) {
		db.rollback();
		return false;
	}

	QStringList languages;
	languages << "pt" << "en" << "es";
	QString lang;
	foreach (lang, languages) {
		query.prepare("INSERT INTO sentence_translation(sentenceid, language, sentencetext) VALUES(?, ?, '')");
		query.addBindValue(id);
		query.addBindValue(lang);
		if (!query.exec()) {
			db.rollback();
			return false;
		}
	}
	
	db.commit();
	endInsertRows();
	return true;
}

To copy to clipboard, switch view to plain text mode 

[marcel]

Well, I think those are the only constructs in which the "?" placeholder works.
Also are the examples are like:

INSERT INTO sentence(id, actionid, objectid) VALUES(?, 0, 0)

.

I think you can stick to your solution.

Regards

[SirBabyface]

So, if I understand, only when I use INSERT, I can use the placeholder ?.

In UPDATE, for example, I can't use it.