Unable to update QSqlite database - database locked unable to fetch row

Question

I am writing a contact book application. The contacts are stored in a qsqlite database and displayed using QSqlQueryModel.

The QueryModel by default runs a query that displays all the contacts. The contacts can be edited and removed.

When the user enters a name in the search field, a SearchQuery runs and only those contacts that match the search terms are displayed. The problem is that I can not edit or remove these contacts. I get an error that says database is locked, unable to fetch row.

I don't understand why this is happening. If I can edit/remove the results of the default query than I should be able to edit/remove the results of the search query.

Below is the code for:

• Constructor

• Searching Contact

• Removing Contact

Constructor (relevant portions)

 OpenDatabase();

    DefaultModelQuery.append("SELECT Id,FirstName||' '||LastName AS FullName,");
    DefaultModelQuery.append("FirstName,LastName,Email,HomeNumber,WorkNumber,MobileNumber,Address,City,Country,Birthdate FROM Contacts ");
    DefaultModelQuery.append("ORDER BY FirstName;");

    QSqlQuery Query(DefaultModelQuery);

    ActiveQuery = Query;
    Model = new QSqlQueryModel(this);
    Model->setQuery(ActiveQuery);

    ui->listView->setModel(Model);
    ui->listView->setModelColumn(1);

Searching Contact

void Contacts::SearchContact()
{
    /*1. Read Search Term
      2. Decide Type
      3. Generate Query
      4. Execute Query.
      5. If No results, return
      6. If resutls, update Model, show first result.
      */

    //STEP 1: Read Search Term.
    QString SearchTerm = ui->SearchLineEdit->text();

    if(SearchTerm.isEmpty())
        return;

    /*STEP 2. Decide Type
      Type n : Name
      Type e : Email
      Type p : Number
      */
    char QueryType;

    if(SearchTerm.contains(QRegExp("[A-za-z-]+")))
    {
        if(SearchTerm.contains("@"))
            QueryType = 'e';
        else
            QueryType = 'n';
    }else if(SearchTerm.contains(QRegExp("[0-9]+")))
        QueryType = 'p';
    else
        QueryType = 'n';


    //STEP 3: Generate Query
    QSqlQuery SearchQuery;

    switch(QueryType)
    {
    case 'n':
    {
        QStringList Names = SearchTerm.split(" ");
        if(Names.size()>=2)
        {
            qDebug()<<QString("Searching for %1 %2").arg(Names.first(),Names.last());
            SearchQuery.prepare("SELECT Id,FirstName||' '||LastName AS FullName,FirstName,LastName,Email,HomeNumber,MobileNumber,WorkNumber,Address,City,Country,Birthdate FROM CONTACTS WHERE FirstName=:f AND LastName=:l ORDER BY FirstName");
            SearchQuery.bindValue(":f",Names.first());
            SearchQuery.bindValue(":l",Names.last());
        }else
        {
            qDebug()<<QString("Searching for %1").arg(Names.first());
            SearchQuery.prepare("SELECT Id,FirstName||' '||LastName AS FullName,FirstName,LastName,Email,HomeNumber,MobileNumber,WorkNumber,Address,City,Country,Birthdate FROM CONTACTS WHERE FirstName=:f OR LastName=:l ORDER BY FirstName");
            SearchQuery.bindValue(":f",Names.first());
            SearchQuery.bindValue(":l",Names.first());
        }
        break;
    }

    case 'e':
    {
        QString Email = SearchTerm.trimmed();
        SearchQuery.prepare("SELECT Id,FirstName||' '||LastName AS FullName,FirstName,LastName,Email,HomeNumber,MobileNumber,WorkNumber,Address,City,Country,Birthdate FROM CONTACTS WHERE Email=:e ORDER BY FirstName");
        SearchQuery.bindValue(":e",Email);
        break;
    }

    case 'p':
    {
        QString Number = SearchTerm.trimmed();
        SearchQuery.prepare("SELECT Id,FirstName||' '||LastName AS FullName,FirstName,LastName,Email,HomeNumber,MobileNumber,WorkNumber,Address,City,Country,Birthdate FROM CONTACTS WHERE HomeNumber=:h OR WorkNumber=:w OR MobileNumber=:m ORDER BY FirstName");
        SearchQuery.bindValue(":h",Number);
        SearchQuery.bindValue(":m",Number);
        SearchQuery.bindValue(":w",Number);
        break;
    }
    }

    //STEP 4: Execute Query
    if(!SearchQuery.exec())
    {
        qDebug()<<QueryType<<": "<<SearchQuery.lastError().text();
        QMessageBox::information(this,
                                 tr("Search Error"),
                                 tr("The following error occured while trying to search contacts:\nError: %1").arg(SearchQuery.lastError().text()));
        return;
    }

    //STEP 5: If no results, return;

    /*Note:
    I used the QSqlQuery::first() method here to check if there are any result or not.
    I doubt this is efficient, because the function probably invovles unnecessary steps & resources.
    So the following code could be made more efficient.

    I can't use QSqlQuery::size() because it always returns -1.
      */

    if(!SearchQuery.first())
    {
        QMessageBox::information(this,
                                 tr("No Results Found"),
                                 tr("No results were found for \"%1\"").arg(SearchTerm));
        return;
    }

    //STEP 6: If results, update model, show first result.
    Model->setQuery(SearchQuery);

    QModelIndex index = Model->index(0,1);
    DisplayContact(index);
    ui->listView->scrollTo(index);

    SetView(Contacts::View_DisplaySearchResultsView);
}

Removing Contact

void Contacts::RemoveContact()
{
    if(SelectedRecordId!=-1)
    {
        if(QMessageBox::information(this,
                                 "Confirm Removal",
                                 "Are you sure you want to remove this contact from your list?",
                                 QMessageBox::Yes|QMessageBox::No)==QMessageBox::No)
            return;

        QSqlQuery DeleteQuery;
        DeleteQuery.prepare("DELETE FROM Contacts WHERE Id = :i;");
        DeleteQuery.bindValue(":i",SelectedRecordId);


        if(!DeleteQuery.exec())
        {
            qDebug()<<DeleteQuery.lastError().text();
            QMessageBox::warning(this,
                                 tr("Error Removing Contact"),
                                 tr("An error occured while trying to remove this contact."));
        }else
        {
            SelectedRecordId =-1;

            clear();
            SetView(Contacts::View_AddContactView);
            Model->setQuery(ActiveQuery);
            UpdateContactCount();
        }
    }else
        QMessageBox::warning(this,
                              tr("No Record Selected"),
                              tr("Unexpected Error: Remove Contact Operation is being attempted while no contact is selected."));
}

I would really appreciate any help with this. Thanks :)

Answer 1

Oddly enough, the code works fine after eliminating this line of code:

 ui->listView->scrollTo(index);

Answer 2

The only thing that comes to mind is the need to ensure that all of the results of the query have been fetched. An open query probably keeps the database lock. Maybe.

Beginning of STEP6 would thus look like the following:

//STEP 6: If results, update model, show first result.
Model->setQuery(SearchQuery);
while (Model->canFetchMore()) {
   Model->fetchMore();
}

Doesn't hurt to try that, I guess.