SQLite misbehaving

[Scorp1us]

I have upgraded to SQLite 3.3.13. I don't know if that is a problem or not, because Qt came with 3.2.7.

But my problem is I run a simple query:

QString sql;
	QSqlQuery query1(db);
 
	sql="CREATE TABLE IF NOT EXISTS patients ("
		"dbID integer PRIMARY KEY AUTOINCREMENT,"
		"identificationNumber text,"
		"lastname text,"
		"firstname text,"
		"mi text);";
 
	if (!query1.exec(sql)) {		
		QMessageBox::critical(0, "", "Patients:" + query1.lastError().text());
		//return false;
	}

QString sql;
	QSqlQuery query1(db);

	sql="CREATE TABLE IF NOT EXISTS patients ("
		"dbID integer PRIMARY KEY AUTOINCREMENT,"
		"identificationNumber text,"
		"lastname text,"
		"firstname text,"
		"mi text);";
	
	if (!query1.exec(sql)) {		
		QMessageBox::critical(0, "", "Patients:" + query1.lastError().text());
		//return false;
	}

To copy to clipboard, switch view to plain text mode 

at application start up, then at user election, I do

QSqlQuery query(db);
	query.prepare("INSERT INTO patients (lastname, firstname, mi, identificationNumber) "
		"VALUES (:lastname, :firstname, :mi, :identificationnumber);");
	query.bindValue(":lastname", pr.lastName);
	query.bindValue(":firstname", pr.firstName);
	query.bindValue(":mi", pr.MI);
	query.bindValue(":identificationnumber", pr.identificationNumber);
	if (!query.exec()) {
		QMessageBox::critical(0, "", "Patient add failed! " + query.lastError().text());
		return;
	}

QSqlQuery query(db);
	query.prepare("INSERT INTO patients (lastname, firstname, mi, identificationNumber) "
		"VALUES (:lastname, :firstname, :mi, :identificationnumber);");
	query.bindValue(":lastname", pr.lastName);
	query.bindValue(":firstname", pr.firstName);
	query.bindValue(":mi", pr.MI);
	query.bindValue(":identificationnumber", pr.identificationNumber);
	if (!query.exec()) {
		QMessageBox::critical(0, "", "Patient add failed! " + query.lastError().text());
		return;
	}

To copy to clipboard, switch view to plain text mode 

And it tells me that database is locked.

I also noticed that the application SEEMS to hold a lock on the database, so the application has to be exited before the tables are created... I've done ODBC with Qt with no problems. Anyone have an idea on SQLite? is ti a driver setting?

EDIT: Added db.transaction() and db.commit(), still no joy.

[Scorp1us]

It seems that:

QSqlQuery q(db);

QSqlQuery q(db);

To copy to clipboard, switch view to plain text mode 

is not right. It is better to leave off the

(db)

-- that's what got my program working this far.

Ok, I've done some experimenting, I have everything working except one feature: the database does not save... Here's what I mean. I can run my program it seems to create the schemas and I can insert and query data. But when I exit, it all gets lost. When I run it again it is as if it was never written to in the first place...

Any ideas?
My database file DOES get created, and there is even a journal. The commandline sqlite3 that I have works just fine.

I've upgraded to 4.2.2, and tried that sqlite verion (3.3.6) and its all the same.

[camel]

When I had problem with SQLite telling me that it thought the database/table was locked, it was mostly always a problem with queries that overstayed their welcome.

My tip is, either let the query run out of scope when you do not need it anymore, or insert this little line after every SELECT (where you do not loop through ".next()" anyways)

while (query.next()) {}

while (query.next()) {}

To copy to clipboard, switch view to plain text mode 

The reason is that the QtSql/Sqlite combination frees its internal result block only after the last next() has been reached.


But could you please post the complete error message?


Now for the not saving:
Are you sure you do not have a transaction started somewhere that never is commited? I must say I never had that kind of problem...

[kiker99]

Probably this isn't the cause of your problem, but.. why are you using your own id field? SQLite provides rowid which does autoincrement automatically. I read a page from the SQLite developers, where they discourage use of AUTO_INCREMENT.

[kroenecker]

Are you sure that it is lost? Using the command line and browsing the table shows no entries?