QSqlTableModel::insertRecord(row, record) always appends the new record - never uses the row parameter

[jdent]

I have a QSqlRelationalTableModel and am calling

bool ok = model->insertRecord(2,record);

The record is appended to the list of the model always and ignores the row number I pass (2 in this case)...

Why is this happening?

[JonB]

@jdent said in QSqlTableModel::insertRecord(row, record) always appends the new record - never uses the row parameter:

where it is inserted so yes if I insert ar row 0 then I expected the new row to be at row 0

I don't know what you mean. SQL tables don't have "a row 0", or any row numbering. It's not a column and isn't stored anywhere and is "meaningless" for a relational database.

Qt's in-memory models do have an ordering, which can be indexed by an integer, like an array/list. This does not get passed on to the database back-end. If you want an ordering in SQL you need a column with a value in it which you can sort by. For example, an incrementing, unique ID number column.

[Christian Ehrlicher]

Please provide a minimal, compilable example of your problem.

[jdent]

@Christian-Ehrlicher

void add(QSqlRecord record)
{
auto model = new QSqlRelationalTableModel{};
model->setTable("password");
model->setEditStrategy(QSqlTableModel::EditStrategy::OnManualSubmit);
model->select();

bool ok = model->insertRecord(2, record);
	ok = model->submitAll();
	auto id = model->query().lastInsertId().toInt();
	bool found = false;
	for (int row = 0; row < model->rowCount(); ++row)
	{
		if (model->record(row).value(0).toInt() == id)
		{
			found = true;
			REQUIRE(row == model->rowCount() - 1);
			break;
		}
	}
}

See?

[Christian Ehrlicher]

No I don't see since I don't know what you're trying to achieve. id and row in the model are two different things.

Again: please provide a minimal, compilable exmaple of your problem.

[jdent]

@Christian-Ehrlicher I have a model on table password, I insert a record in row 2 - at least that's how it looks like - but the record is appended to the model and not inserted at row 2!!

What I am unclear about?

[Christian Ehrlicher]

@jdent said in QSqlTableModel::insertRecord(row, record) always appends the new record - never uses the row parameter:

auto model = new QSqlRelationalTableModel{};

You create a new model over and over again - are you sure it has at least one entry.

I will not look anything further until there is a compilable code base here. Using an in-memory sqlite db and provide the few lines around to create and fil the table shouldn't be that hard if you want help.

[jdent]

@Christian-Ehrlicher Ok here is my code:

inline void showError(const QSqlError& err)
{
	QMessageBox::critical(nullptr, "Unable to initialize Database",
		"Error initializing database: " + err.text());
}

struct Password
{
	bool valid = false;
	int id;
	QString password;
	QDateTime beginDate;
	int fkLocation;
	Password(QSqlRecord* rec)
	{
		valid = rec != nullptr;
		id = rec->value(0).toInt();
		password = rec->value(1).toString();
		beginDate = rec->value(2).toDateTime();
		fkLocation = rec->value(3).toInt();
	}
};


class PasswordFields
{
	QSqlField f1;
	QSqlField f2;
	QSqlField f3;
	QSqlField f4;
	QSqlRecord rec;
public:
	PasswordFields(int id, const QString& password, const QDateTime& beginDate,int fkLocation ) : f1{"id", QMetaType(QMetaType::Int)}, f2{"password", QMetaType(QMetaType::QString)},
	f3{"beginDate", QMetaType(QMetaType::QDateTime)},	f4("fkLocation", QMetaType{ QMetaType::Int })
	{
		if (id > -1)
		{
			f1.setValue(id);
			rec.append(f1);
		}
		f2.setValue(password);
		f3.setValue(beginDate);
		f4.setValue(fkLocation);
		
		rec.append(f2);
		rec.append(f3);
		rec.append(f4);
	}
	void setId(int pk)
	{
		f1.setValue(pk);
		rec.append(f1);
		auto id = rec.value(0).toInt();
		auto pass = rec.value(1).toString();
	}
	Password get() { return Password{ &rec }; }
	QSqlRecord record() const { return rec; }
};

class PasswordRecord
{
	QSqlField f1;
	QSqlField f2;
	QSqlField f3;
	QSqlField f4;
	QSqlRecord rec;
public:
	PasswordRecord(const QSqlRecord& rec) : f1{"id", QMetaType(QMetaType::Int)}, f2{"password", QMetaType(QMetaType::QString)}, f3 {"beginDate", QMetaType(QMetaType::QDateTime)},
							f4("fkLocation", QMetaType{QMetaType::Int}), rec{rec}
	{}
	Password get() { return Password{ &rec }; }
};


class PasswordGateway
{
	struct SQL
	{
		inline static const auto CreatePasswordTable = QLatin1String(R"(
			create table password(id integer primary key, password varchar, beginDate date, fkLocation integer)
		)");
		inline static const auto DestroyPasswordTable = QLatin1String(R"(
			drop table password
		)");
		inline static const auto InsertPassword = QLatin1String(R"(
			insert into password(password, beginDate, fkLocation)
                      values(?, ?, ?)
		)");
		inline static const auto UpdatePassword = QLatin1String(R"(
			update password SET password = ?, beginDate = ?, fkLocation = ?
			where id = ?)");
		inline static const auto DeletePassword = QLatin1String(R"(
			delete from password where id = ?)");

		inline static const auto FindPassword = QLatin1String(R"(
			select * from password where id = ?)");

		inline static const auto FindAllPassword = QLatin1String(R"(
			select * from password)");

	};
public:
	std::vector<Password> findAll()
	{
		std::vector<Password> vec;
		QSqlQuery q;
		if(! q.exec(SQL::FindAllPassword))
		{
			showError(q.lastError());
			return vec;
		}
		while(q.next())
		{
			PasswordRecord rec(q.record());
			auto r = rec.get();
			vec.push_back(r);
		}
		return vec;
	}
	Password find(int pk)
	{
		QSqlQuery q;
		if( !q.prepare(SQL::FindPassword))
		{
			showError(q.lastError());
			return Password(nullptr);
		}
		q.addBindValue(pk);
		q.exec();

		QSqlRecord record;

		QSqlField f1("id", QMetaType(QMetaType::Int));
		QSqlField f2("password", QMetaType(QMetaType::QString));
		QSqlField f3("beginDate", QMetaType(QMetaType::QDateTime));
		QSqlField f4("fkLocation", QMetaType(QMetaType::Int));

		if(q.next())
		{
			f1.setValue(q.value(0));
			f2.setValue(q.value(1));
			f3.setValue(q.value(2));
			f4.setValue(q.value(3));
		}
		record.append(f1);
		record.append(f2);
		record.append(f3);
		record.append(f4);

		return Password(&record);
	}
	int insert(const QString& password, QDateTime beginDate, QVariant fkLocation)
	{
		QSqlQuery q;
		if (!q.prepare(SQL::InsertPassword))
		{
			showError(q.lastError());
			return -1;
		}
		q.addBindValue(password);
		q.addBindValue(beginDate);
		q.addBindValue(fkLocation);
		bool ok = q.exec();
		return q.lastInsertId().toInt();
	}
	bool update(int pk, const QString& password, QDateTime beginDate, QVariant fkLocation)
	{
		QSqlQuery q;
		if (!q.prepare(SQL::UpdatePassword))
		{
			showError(q.lastError());
			return false;
		}
		q.addBindValue(password);
		q.addBindValue(beginDate);
		q.addBindValue(fkLocation);
		q.addBindValue(pk);
		bool ok = q.exec();
		return ok;
	}
	bool remove(int pk)
	{
		QSqlQuery q;
		if(! q.prepare(SQL::DeletePassword))
		{
			showError(q.lastError());
			return false;
		}
		q.addBindValue(pk);
		bool ok = q.exec();
		return ok;
	}
	bool createTable()
	{
		QSqlQuery q;
		bool ok = q.exec(SQL::CreatePasswordTable);
		if(!ok)
		{
			showError(q.lastError());
		}
		return ok;
	}
	bool destroyTable()
	{
		QSqlQuery q;
		bool ok = q.exec(SQL::DestroyPasswordTable);
		if(!ok)
		{
			showError(q.lastError());
		}
		return ok;
	}
};
void Sample()
{
	QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
	QString dbName = ":memory:";
	db.setDatabaseName(dbName);

	db.open();

	PasswordGateway gateway;
	gateway.createTable();
	gateway.insert("MyPassword", QDateTime::currentDateTime(), 1);
	gateway.insert("Otherpassword", QDateTime::currentDateTime(), 1);


	QSqlRelationalTableModel* model = new QSqlRelationalTableModel{};
	
	model->setTable("password");
	// model->setRelation(3, QSqlRelation("location", "id", "name"));
	model->setEditStrategy(QSqlTableModel::EditStrategy::OnManualSubmit);
	model->select();

	QDateTime dt = QDateTime::currentDateTime();
	PasswordFields pwd(-1, "D54555555F", dt, 1);

	bool ok = model->insertRecord(0, pwd.record());
	ok = model->submitAll();
	auto id = model->query().lastInsertId().toInt();

	//  new row should be at row==0
	auto password = model->record(0).value(1);
	assert(password != "D54555555F");
}

Can you look at my problem now?

Thanks!

[jdent]

@jdent is this example clear enough?

[JonB]

@jdent
Apart from the fact that it is a lot of code for a minimal example (could be reduced drastically)....

It seems to me you keep asking the same question, with the same answer (unless I am mistaken)? You insert at row #0 (model->insertRecord(0, pwd.record())). The model row number does not relate to anything at the SQL database side. You then read from row #0 (model->record(0).value(1)) after refilling the model. Your SQL statemebnt seems to be select * from password. You show no sorting. Do you expect the record you last inserted at row #0 to come back at row #0? If so, why would you expect that?

You also keep raising new questions all the time, when you have the same or sufficiently similar question on-going in another question, without even a cross reference so people are aware. For example your new https://forum.qt.io/topic/155733/how-can-i-set-the-id-field-of-an-inserted-qsqlrecord. So of someone answers in one place (like here) someone else is supposed to also answer elsewhere. Please think about the duplicated effort someone wanting to answer has to go through for this. I'm not going to answer there as well as here.....

[jdent]

@JonB said in QSqlTableModel::insertRecord(row, record) always appends the new record - never uses the row parameter:

It seems to me you keep asking the same question, with the same answer (unless I am mistaken)? You insert at row #0 (model->insertRecord(0, pwd.record())). The model row number does not relate to anything at the SQL database side. You then read from row #0 (model->record(0).value(1)) after refilling the model. Your SQL statemebnt seems to be select * from password. You show no sorting. Do you expect the record you last inserted at row #0 to come back at row #0? If so, why would you expect that?

Yes I understood the row number passed to insertRecord to be the row number where it is inserted so yes if I insert ar row 0 then I expected the new row to be at row 0. But you are saying that's not how it works... Ok so can you please tell me what the row parameter means? row with respect to what??

Regarding my other question, its a totally different issue I don't see why it should be a problem to ask 2 questions at the same time.

[JonB]

@jdent said in QSqlTableModel::insertRecord(row, record) always appends the new record - never uses the row parameter:

where it is inserted so yes if I insert ar row 0 then I expected the new row to be at row 0

I don't know what you mean. SQL tables don't have "a row 0", or any row numbering. It's not a column and isn't stored anywhere and is "meaningless" for a relational database.

Qt's in-memory models do have an ordering, which can be indexed by an integer, like an array/list. This does not get passed on to the database back-end. If you want an ordering in SQL you need a column with a value in it which you can sort by. For example, an incrementing, unique ID number column.

[Christian Ehrlicher]

Please read the docs of submitAll():
"In OnManualSubmit, on success the model will be repopulated. Any views presenting it will lose their selections."