sqlite read lock.

[gilgm]

When I execute the following code:

QString username = "gilgm";
QSqlQuery * userQuery = new QSqlQuery("DELETE FROM users WHERE username=?");
userQuery->addBindValue(username);
int ret = userQuery->exec();
if( !ret ) qDebug() << userQuery->lastError();   
return ret

QString username = "gilgm";
QSqlQuery * userQuery = new QSqlQuery("DELETE FROM users WHERE username=?");
userQuery->addBindValue(username);
int ret = userQuery->exec();
if( !ret ) qDebug() << userQuery->lastError();   
return ret

To copy to clipboard, switch view to plain text mode 

It reports no error, and returns without problems. The copy of my database on disk doesn't get updated, and a "sqlite-journal" file is created.

The same thing often happens when I execute an UPDATE or INSERT statement.

Any ideas?

-Matt

[tjm]

maybe you need a

userQuery.prepare("DELETE FROM users WHERE username=?");

userQuery.prepare("DELETE FROM users WHERE username=?");

To copy to clipboard, switch view to plain text mode 

between lines 2 and 3.

[gilgm]

Thanks for the suggestion, I tried passing in the query string to object via its "prepare" function (rather than in the constructor as above), and I have the same result. A sqlite-journal file is created, and the program returns without warning or error.

One thing worth noting is that, when my QSqlQueryModel repopulates the view after the query, I get a duplicate of the last entry when recreating all the table rows. For example, if I have a user table that reads:

John
Fred
Mark

And I execute delete on Mark, My table repopulates like this:

John
Fred
Fred

QSqlQueryModel * getUsers()
{
  QSqlQueryModel * users = new QSqlQueryModel();
  QString userQuery = "SELECT * FROM users"; 
  users->setQuery(userQuery, db);
  return users;
}
 
void TbxUsers::populateUserList( void )
{
  // Remove out all rows in the table.
  for( int i = 0; i < m_ui->tableWidget->rowCount(); i++)
    m_ui->tableWidget->removeRow(i);
 
  // Get a model of users contained in the table.
  QSqlQueryModel * users = getUsers();
 
  // Have to use QModelIndex to get the record count.
  QModelIndex index;
  int numUsers = users->rowCount(index);
 
  for(int i = 0; i < numUsers; i++)
  {
    QTableWidgetItem *username;
    username = new QTableWidgetItem(users->record(i).value("username").toString());
    m_ui->tableWidget->insertRow(i);
    m_ui->tableWidget->setItem(i,0,username);
  }
}

QSqlQueryModel * getUsers()
{
  QSqlQueryModel * users = new QSqlQueryModel();
  QString userQuery = "SELECT * FROM users"; 
  users->setQuery(userQuery, db);
  return users;
}

void TbxUsers::populateUserList( void )
{
  // Remove out all rows in the table.
  for( int i = 0; i < m_ui->tableWidget->rowCount(); i++)
    m_ui->tableWidget->removeRow(i);

  // Get a model of users contained in the table.
  QSqlQueryModel * users = getUsers();

  // Have to use QModelIndex to get the record count.
  QModelIndex index;
  int numUsers = users->rowCount(index);

  for(int i = 0; i < numUsers; i++)
  {
    QTableWidgetItem *username;
    username = new QTableWidgetItem(users->record(i).value("username").toString());
    m_ui->tableWidget->insertRow(i);
    m_ui->tableWidget->setItem(i,0,username);
  }
}

To copy to clipboard, switch view to plain text mode 

Something really strange is going on here. I considered the possibility of a corrupt database, but I've been using the firefox plugin to connect and manage my database outside of Qt, and things always seem to work well with it.

PS: Adding transaction code in getUsers() didn't seem to help the matter either, though I didn't add transaction logic to every place in the software I use the QSqlQueryModel.

Hmmm....

[Lykurg]

Hi, to your original problem, I don't the problem right now, but for deleting all rows, you can also use QTableWidget::clear() or QTableWidget::clearContents() and then you really should have a look at the docs regarding Qt Model/View. Because using a model with a QTableWidget is nonsens. Use a QTableView instead...

[gilgm]

Lykurg:

Thanks for the quick reply. Unfortunately QTableWidget::clear() removes the row headers. QTableWidget::clearContents() is what I should be using, but it didn't seem to clear the table entirely; or so it seemed.

I think the real problem is with the QSqlQueryModel object. I believe that the TableWidget is doing its job, and is being populated with what the QSqlQueryModel object believes its current model is. The funny part is, that when I execute the DELETE statement, the item goes away, but the QSqlQueryModel record count stays the same.

As far as a QTableView goes, I understand the Qt Model/View objects and what they provide, and I can tell you that there is certainly a method to my madness. QTableView really isn't a good fit for my particular application, in fact, QSqlQueryModel isn't really a good fit either.

I am most concerned with the fact that the QSqlQueryModel object seems to believe *incorrectly* that the record has been removed from the database(until program restart). Possibly a sqlite driver issue? Is there is a command I need to perform to "close" a QSqlQueryModel query? (Otherwise it creates the *.sqlite-journal and locks the database until the program exits) Can't find any info in the Qt docs...

[Banjo]

Hello Gilgm

I too am having problems with SQLite3 and QSqlRelationalTableModels. I have found that model.query().finish() before commit() works until the model has relations and then the queries for these need to be finished as well.

I have an app with about 12 QSqlRelationalTableModels and this doesn't seem like a good solution to me.

Cheers

[creativemidhun]

THE REAL PROBLEM IS IN SOME PART OF THE PROGRAM SOME QUERY LL BE STILL EXECUTING CAUSING AN EXCLUSIVE LOCK TO THE DB ..
THIS PREVENTS ANY FURTHER MODIFICATION OF THE DB..
Try to commit the db ..
Just run..

COMMIT or END
Or if that didnt work try to close the connection u r using ..
and get the error report ..
u can see that the close wont work.. and some query still active kind of error ll b raised ..

hop this helps thank u..