sqlite - 无法更新 Sqlite 数据库 - 数据库锁定无法获取行

Question

我正在编写通讯录应用程序。联系人存储在 qsqlite 数据库中并使用 QSqlQueryModel 显示。

默认情况下，QueryModel 运行显示所有联系人的查询。可以编辑和删除联系人。

当用户在搜索字段中输入姓名时，SearchQuery 会运行，并且仅显示与搜索词匹配的联系人。问题是我无法编辑或删除这些联系人。我收到一条错误消息，指出数据库已锁定，无法获取行。

我不明白为什么会这样。如果我可以编辑/删除默认查询的结果，那么我应该能够编辑/删除搜索查询的结果。

下面是代码:

构造函数

搜索联系人

删除联系人

构造函数(相关部分)

 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);

搜索联系人

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);
}

删除联系人

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."));
}

我真的很感激这方面的任何帮助。谢谢:)

Answer 1

唯一想到的是需要确保已获取查询的所有结果。一个打开的查询可能会保持数据库锁定。也许。

因此，STEP6 的开头如下所示:

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

我想，尝试这样做不会有什么坏处。