database is locked [复制链接]

////////////////////////////////////choicemanager.h
#ifndef CHOICEMANAGER_H
#define CHOICEMANAGER_H

#include <QDialog>

class QSqlDatabase;
class QSqlQuery;
namespace Ui
{
    class manager;
}
class choiceManager : public QDialog
{
    Q_OBJECT
public:
    choiceManager(QString id=QString("0"));
    ~choiceManager();
    QStringList getField();
    QStringList getSpecial(QString selectedField);
    QStringList getHandman();
    int getRowCount();
    bool queryRowCount();
    void deleteRowCount();
    void updateRowCount(int rowCount);
    void createRowCount();
private:
    void connection();
    void insertField(QString fieldName);
    void insertSpecial(QString fieldName,QString specialName);
    void insertHandman(QString handmanName);
    void deleteField(QString fieldName);
    void deleteSpecial(QString fieldName,QString specialName);
    void deleteHandman(QString handmanName);

private slots:
    void updateField();
    void updateSpecial();
    void updateHandman();
    void handleInsertField();
    void handleInsertSpecial();
    void handleInsertHandman();
    void handleDeleteField();
    void handleDeleteSpecial();
    void handleDeleteHandman();
private:
    Ui::manager *dialog;
    QSqlDatabase *db;
    QSqlQuery *query;
    QString userId;
};

#endif // CHOICEMANAGER_H

/////////////////////choicemanager.cpp
#include "choiceManager.h"
#include"ui_choiceManager.h"
#include<QSqlDatabase>
#include<QSqlError>
#include<QtDebug>
#include<QMessageBox>
#include<QSqlQuery>
#include<QInputDialog>
#include<QStringList>
//#include"qsql_sqlite.h"
//#include
choiceManager::choiceManager(QString id)
        :QDialog(),dialog(new Ui::manager),userId(id)
{
    this->setModal(true);
    dialog->setupUi(this);
    this->show();

    connect(dialog->addGeneral,SIGNAL(clicked()),this,SLOT(handleInsertField()));
    connect(dialog->addSpecial,SIGNAL(clicked()),this,SLOT(handleInsertSpecial()));
    connect(dialog->addPerson,SIGNAL(clicked()),this,SLOT(handleInsertHandman()));
    connect(dialog->deleteGeneral,SIGNAL(clicked()),this,SLOT(handleDeleteField()));
    connect(dialog->deleteSpecial,SIGNAL(clicked()),this,SLOT(handleDeleteSpecial()));
    connect(dialog->deletePerson,SIGNAL(clicked()),this,SLOT(handleDeleteHandman()));

    connect(dialog->listWidget,SIGNAL(currentItemChanged(QListWidgetItem*,QListWidgetItem*)),this,SLOT(updateSpecial()));

    connection();
    updateField();
    updateSpecial();
    updateHandman();

    dialog->listWidget->setCurrentRow(0);
}

choiceManager::~choiceManager()
{
    delete dialog;
}
void choiceManager::connection()
{
    db=new QSqlDatabase();
    *db=QSqlDatabase::addDatabase("QSQLITE");
    db->setDatabaseName( "manager.db" );
    if( !db->open() )
    {
        qDebug() << db->lastError()<<db->drivers();
        QMessageBox::information(this,"Fail","Fail to connect database");
    }

    query=new QSqlQuery(*db);

    QStringList tablesEnsure=db->tables();
    if(!tablesEnsure.contains(QString("field")))
    {
       if(!query->exec("CREATE TABLE field(x INTEGER PRIMARY KEY ASC,id,field,special)"))
        {
           QMessageBox::information(this,"Fail","Fail to creat table field");
           qDebug()<<query->lastError();
       }
   }
    if(!tablesEnsure.contains(QString("handman")))
    {
       if(!query->exec("CREATE TABLE handman(x INTEGER PRIMARY KEY ASC,id,handman)"))
        {
           QMessageBox::information(this,"Fail","Fail to creat table handman");
           qDebug()<<query->lastError();
       }
   }
     if(!tablesEnsure.contains(QString("rowCount")))
    {
       if(!query->exec("CREATE TABLE rowCount(x INTEGER PRIMARY KEY ASC,id,rowCount INTEGER)"))
        {
           QMessageBox::information(this,"Fail","Fail to creat table rowCount");
           qDebug()<<query->lastError();
       }
   }
    db->close();
   //
}
void choiceManager::updateField()
{
    db=new QSqlDatabase();
    *db=QSqlDatabase::addDatabase("QSQLITE");
    db->setDatabaseName( "manager.db" );
    if( !db->open() )
    {
        qDebug() << db->lastError()<<db->drivers();
        QMessageBox::information(this,"Fail","Fail to connect database");
    }

    query=new QSqlQuery(*db);
    query->prepare("SELECT DISTINCT field FROM field WHERE id=:userId");
    query->bindValue(":userId",userId);
    if(!query->exec())
        {
           QMessageBox::information(this,"Fail","Fail to get field");
           qDebug()<<query->lastError();
           db->close();
           return;
       }
    dialog->listWidget->clear();
    while(query->next())
    {
        dialog->listWidget->addItem(query->value(0).toString());
    }
    if(dialog->listWidget->item(0))
        dialog->listWidget->setCurrentItem(dialog->listWidget->item(0));
    db->close();
}
void choiceManager::updateSpecial()
{
    db=new QSqlDatabase();
    *db=QSqlDatabase::addDatabase("QSQLITE");
    db->setDatabaseName( "manager.db" );
    if( !db->open() )
    {
        qDebug() << db->lastError()<<db->drivers();
        QMessageBox::information(this,"Fail","Fail to connect database");
    }

    query=new QSqlQuery(*db);
    QListWidgetItem *temp;
    if((temp=dialog->listWidget->currentItem())==NULL)
        return;
    query->prepare("SELECT DISTINCT special FROM field WHERE field=:selectedField AND id=:userId");
    query->bindValue(":selectedField",temp->text());

    query->bindValue(":userId",userId);

    if(!query->exec())
        {
           QMessageBox::information(this,"Fail","Fail to get special");
           qDebug()<<query->lastError();
           return;
       }
    dialog->listWidget_1->clear();
    while(query->next())
    {
        dialog->listWidget_1->addItem(query->value(0).toString());
    }
    db->close();
}
void choiceManager::updateHandman()
{
    db=new QSqlDatabase();
    *db=QSqlDatabase::addDatabase("QSQLITE");
    db->setDatabaseName( "manager.db" );
    if( !db->open() )
    {
        qDebug() << db->lastError()<<db->drivers();
        QMessageBox::information(this,"Fail","Fail to connect database");
    }

    query=new QSqlQuery(*db);
    query->prepare("SELECT DISTINCT handman FROM handman WHERE id=:userId");
    query->bindValue(":userId",userId);

    if(!query->exec())
        {
           QMessageBox::information(this,"Fail","Fail to get handman");
           qDebug()<<query->lastError();
           return;
       }
    dialog->listWidget_2->clear();
    while(query->next())
    {
        dialog->listWidget_2->addItem(query->value(0).toString());
    }
    db->close();
}

void choiceManager::insertField(QString fieldName)
{
    db=new QSqlDatabase();
    *db=QSqlDatabase::addDatabase("QSQLITE");
    db->setDatabaseName( "manager.db" );
    db->setConnectOptions("QSQLITE_BUSY_TIMEOUT=1");
    if( !db->open() )
    {
        qDebug() << db->lastError()<<db->drivers();
        QMessageBox::information(this,"Fail","Fail to connect database");
    }

    query=new QSqlQuery(*db);
    query->prepare("INSERT INTO field(id,field) VALUES(:userId,:insertedField) ");
    query->bindValue(":userId",userId);
    query->bindValue(":insertedField",fieldName);

    if(!query->exec())
        {
           QMessageBox::information(this,"Fail","Fail to insert field");
           qDebug()<<query->lastError();
       }
    db->close();
}
void choiceManager::insertSpecial(QString fieldName,QString specialName)
{
    db=new QSqlDatabase();
    *db=QSqlDatabase::addDatabase("QSQLITE");
    db->setDatabaseName( "manager.db" );
    db->setConnectOptions("QSQLITE_BUSY_TIMEOUT=1");
    if( !db->open() )
    {
        qDebug() << db->lastError()<<db->drivers();
        QMessageBox::information(this,"Fail","Fail to connect database");
    }

    query=new QSqlQuery(*db);
    query->prepare("INSERT INTO field(id,field,special) VALUES(:userId,:insertedField,:insertedSpecial)");
    query->bindValue(":userId",userId);
    query->bindValue(":insertedField",fieldName);
    query->bindValue(":insertedSpecial",specialName);

    if(!query->exec())
        {
           QMessageBox::information(this,"Fail","Fail to insert special");
           qDebug()<<query->lastError();
       }
    db->close();
}
void choiceManager::insertHandman(QString handmanName)
{
    db=new QSqlDatabase();
    *db=QSqlDatabase::addDatabase("QSQLITE");
    db->setDatabaseName( "manager.db" );
    db->setConnectOptions("QSQLITE_BUSY_TIMEOUT=1");
    if( !db->open() )
    {
        qDebug() << db->lastError()<<db->drivers();
        QMessageBox::information(this,"Fail","Fail to connect database");
    }

    query=new QSqlQuery(*db);
    query->prepare("INSERT INTO handman(id,handman) VALUES(:id,:insertedHandman)");
    query->bindValue(":userId",userId);
    query->bindValue(":insertedHandman",handmanName);

    if(!query->exec())
        {
           QMessageBox::information(this,"Fail","Fail to get handman");
           qDebug()<<query->lastError();
       }
    db->close();
}
void choiceManager::deleteField(QString fieldName)
{
    db=new QSqlDatabase();
    *db=QSqlDatabase::addDatabase("QSQLITE");
    db->setDatabaseName( "manager.db" );
    if( !db->open() )
    {
        qDebug() << db->lastError()<<db->drivers();
        QMessageBox::information(this,"Fail","Fail to connect database");
    }

    query=new QSqlQuery(*db);
    query->prepare("DELETE FROM field WHERE id=:userId AND field=:deleteField");
    query->bindValue(":userId",userId);
    query->bindValue(":deleteField",fieldName);

    if(!query->exec())
        {
           QMessageBox::information(this,"Fail","Fail to delete field");
           qDebug()<<query->lastError();
       }
    db->close();
}
void choiceManager::deleteSpecial(QString fieldName,QString specialName)
{
    db=new QSqlDatabase();
    *db=QSqlDatabase::addDatabase("QSQLITE");
    db->setDatabaseName( "manager.db" );
    if( !db->open() )
    {
        qDebug() << db->lastError()<<db->drivers();
        QMessageBox::information(this,"Fail","Fail to connect database");
    }

    query=new QSqlQuery(*db);
    query->prepare("DELETE FROM field WHERE id=:userId AND field=:deletedField AND special=:deletedSpecial");
    query->bindValue(":userId",userId);
    query->bindValue("deletedField",fieldName);
    query->bindValue("deletedSpecial",specialName);

    if(!query->exec())
        {
           QMessageBox::information(this,"Fail","Fail to delete special");
           qDebug()<<query->lastError();
       }
    db->close();
}
void choiceManager::deleteHandman(QString handmanName)

{
    db=new QSqlDatabase();
    *db=QSqlDatabase::addDatabase("QSQLITE");
    db->setDatabaseName( "manager.db" );
    if( !db->open() )
    {
        qDebug() << db->lastError()<<db->drivers();
        QMessageBox::information(this,"Fail","Fail to connect database");
    }

    query=new QSqlQuery(*db);
    query->prepare("DELETE FROM handman WHERE id=:userId AND handman=:deletedHandman");
    query->bindValue(":userId",userId);
    query->bindValue(":deletedHandman",handmanName);

    if(!query->exec())
        {
           QMessageBox::information(this,"Fail","Fail to delete handman");
           qDebug()<<query->lastError();
       }
    db->close();

}
void choiceManager::handleInsertField()
{
    QString temp;
    if((temp=QInputDialog::getText(this,"get string","field")).isEmpty())
    {
        QMessageBox::information(this,"Wrong string","No string is strived");
        return;
    }
    insertField(temp);
    updateField();
    updateSpecial();

}
void choiceManager::handleInsertSpecial()
{
    QString temp;
    if((temp=QInputDialog::getText(this,"get string","special")).isEmpty())
    {
        QMessageBox::information(this,"Wrong string","No string is strived");
        return;
    }
    insertSpecial(dialog->listWidget->currentItem()->text(),temp);
    updateSpecial();
}
void choiceManager::handleInsertHandman()
{
    QString temp;
    if((temp=QInputDialog::getText(this,"get string","handman")).isEmpty())
    {
        QMessageBox::information(this,"Wrong string","No string is strived");
        return;
    }
    insertHandman(temp);
    updateHandman();
}
void choiceManager::handleDeleteField()
{
    deleteField(dialog->listWidget->currentItem()->text());
    updateField();
    updateSpecial();
}
void choiceManager::handleDeleteSpecial()
{
    deleteSpecial(dialog->listWidget->currentItem()->text(),dialog->listWidget_1->currentItem()->text());
    updateSpecial();
}
void choiceManager::handleDeleteHandman()
{
    deleteHandman(dialog->listWidget_2->currentItem()->text());
    updateHandman();
}
QStringList choiceManager::getField()
{
    db=new QSqlDatabase();
    *db=QSqlDatabase::addDatabase("QSQLITE");
    db->setDatabaseName( "manager.db" );
    if( !db->open() )
    {
        qDebug() << db->lastError()<<db->drivers();
        QMessageBox::information(this,"Fail","Fail to connect database");
    }

    query=new QSqlQuery(*db);
    query->prepare("SELECT DISTINCT field FROM field WHERE id=:userId");
    query->bindValue(":userId",userId);
    if(!query->exec())
        {
           QMessageBox::information(this,"Fail","Fail to get field");
           qDebug()<<query->lastError();
           return QStringList();
       }
    QStringList temp;
    while(query->next())
    {
        temp.append(query->value(0).toString());
    }
    db->close();
    return temp;
}
QStringList choiceManager::getSpecial(QString selectedField)
{
    db=new QSqlDatabase();
    *db=QSqlDatabase::addDatabase("QSQLITE");
    db->setDatabaseName( "manager.db" );
    if( !db->open() )
    {
        qDebug() << db->lastError()<<db->drivers();
        QMessageBox::information(this,"Fail","Fail to connect database");
    }

    query=new QSqlQuery(*db);
    if(selectedField.isNull())
        return QStringList();
    query->prepare("SELECT DISTINCT special FROM field WHERE field=:selectedField AND id=:userId;");
    query->bindValue(":selectedField",selectedField);
    query->bindValue(":userId",userId);
    if(!query->exec())
        {
           QMessageBox::information(this,"Fail","Fail to get special");
           qDebug()<<query->lastError();
           return QStringList();
       }
    QStringList temp;
    while(query->next())
    {
        temp.append(query->value(0).toString());
    }
    db->close();
    delete db;
    return temp;
}
QStringList choiceManager::getHandman()
{
    db=new QSqlDatabase();
    *db=QSqlDatabase::addDatabase("QSQLITE");
    db->setDatabaseName( "manager.db" );
    if( !db->open() )
    {
        qDebug() << db->lastError()<<db->drivers();
        QMessageBox::information(this,"Fail","Fail to connect database");
    }

    query=new QSqlQuery(*db);
    query->prepare("SELECT DISTINCT handman FROM handman WHERE id=:userId;");
    query->bindValue(":userId",userId);

    if(!query->exec())
        {
           QMessageBox::information(this,"Fail","Fail to get handman");
           qDebug()<<query->lastError();
           return QStringList();
       }
    QStringList temp;
    while(query->next())
    {
        temp.append(query->value(0).toString());
    }
    db->close();
    delete db;
    return temp;
}
bool choiceManager::queryRowCount()
{
    db=new QSqlDatabase();
    *db=QSqlDatabase::addDatabase("QSQLITE");
    db->setDatabaseName( "manager.db" );
    if( !db->open() )
    {
        qDebug() << db->lastError()<<db->drivers();
        QMessageBox::information(this,"Fail","Fail to connect database");
        return false;
    }

    query=new QSqlQuery(*db);
    query->prepare("SELECT * FROM rowCount WHERE id=:userId;");                  //check whether there user has set this
    query->bindValue(":userId",userId);
    if(!query->exec())
    {
        QMessageBox::information(0,"tellme","select rowCount failed");
        qDebug()<<query->lastError();
        db->close();
        return false;
    }
    if(!query->next())
    {
         return false;
    }
    db->close();
    delete db;
    return true;
}
void choiceManager::deleteRowCount()
{
    db=new QSqlDatabase();
    *db=QSqlDatabase::addDatabase("QSQLITE");
    db->setDatabaseName( "manager.db" );
    if( !db->open() )
    {
        qDebug() << db->lastError()<<db->drivers();
        QMessageBox::information(this,"Fail","Fail to connect database");
    }

    query=new QSqlQuery(*db);
    query->prepare("DELETE FROM rowCount WHERE id=:userId;");
    query->bindValue(":userId",userId);

    if(!query->exec())
        {
           QMessageBox::information(this,"Fail","Fail to delete rowCount");
           qDebug()<<query->lastError();
       }
    db->close();
    delete db;
}
int choiceManager::getRowCount()
{
    db=new QSqlDatabase();
    *db=QSqlDatabase::addDatabase("QSQLITE");
    db->setDatabaseName( "manager.db" );
    if( !db->open() )
    {
        qDebug() << db->lastError()<<db->drivers();
        QMessageBox::information(this,"Fail","Fail to connect database");
    }

    query=new QSqlQuery(*db);
    query->prepare("SELECT rowCount FROM rowCount WHERE id=:userId;");
    query->bindValue(":userId",userId);

    if(!query->exec())
        {
           QMessageBox::information(this,"Fail","Fail to get row count");
           qDebug()<<query->lastError();
           db->close();
           delete db;
           return 31;
       }
    bool ok=false;
    int result=31;
    if(query->next())
    {
        result=query->value(0).toInt(&ok);
    }
    if(ok)
    {
        db->close();
        delete db;
        return result;
    }
    else
    {
        db->close();
        delete db;
        QMessageBox::information(0,"query is successfully","but result can't be returned");
        return 31;
    }
}
void choiceManager::updateRowCount(int rowCount)
{
    db=new QSqlDatabase();
    *db=QSqlDatabase::addDatabase("QSQLITE");
    db->setDatabaseName( "manager.db" );
    db->setConnectOptions("QSQLITE_BUSY_TIMEOUT=1");
    if( !db->open() )
    {
        qDebug() << db->lastError()<<db->drivers();
        QMessageBox::information(this,"Fail","Fail to connect database");
    }

    query=new QSqlQuery(*db);
    QString temp=QString("UPDATE rowCount SET rowCount=%1 WHERE id='%2';").arg(rowCount).arg(userId);

    if(!query->exec(temp))
        {
           QMessageBox::information(this,"Fail","Fail to update row count");
           qDebug()<<query->lastError()<<db->connectionName()<<db->connectOptions();
       }
        QString temp1;
    QMessageBox::information(0,"updateRowCount",temp1.setNum(rowCount));
    db->close();
    delete db;
}
void choiceManager::createRowCount()
{
    db=new QSqlDatabase();
    *db=QSqlDatabase::addDatabase("QSQLITE");
    db->setDatabaseName( "manager.db" );
    if( !db->open() )
    {
        qDebug() << db->lastError()<<db->drivers();
        QMessageBox::information(this,"Fail","Fail to connect database");
    }

    query=new QSqlQuery(*db);
    QString temp=QString("INSERT INTO rowCount(id,rowCount) VALUES(%1,31);").arg(userId);

        if(!query->exec(temp))
        {
           QMessageBox::information(this,"Fail","Fail to create rowCount");
           qDebug()<<query->lastError();
           return;
        }
         db->close();
    delete db;
}

问题是：
我这个类中所有的update函数的执行都会失败，终端输出
QSqlError(5, "Unable to fetch row", "database is locked") "qt_sql_default_connection" "QSQLITE_BUSY_TIMEOUT=1"
不知道是什么原因？
如果提供的信息有什么不足的地方，欢迎大侠提出，我将尽量提供。这个问题困扰我很多天了，真的是没什么办法了。

1.select ,insert 能执行吗？
2.QSqlError(5, "Unable to fetch row", "database is locked") "qt_sql_default_connection" "QSQLITE_BUSY_TIMEOUT=1"  这个错误时你自己定义的吗？
3.执行失败 lastError输出的是什么？

引用第4楼yb824于2009-12-10 09:27发表的  :
1.select ,insert 能执行吗？
2.QSqlError(5, "Unable to fetch row", "database is locked") "qt_sql_default_connection" "QSQLITE_BUSY_TIMEOUT=1"  这个错误时你自己定义的吗？
3.执行失败 lastError输出的是什么？

1、select能执行，insert不能执行
2、qDebug()<<query->lastError()<<db->connectionName()<<db->connectOptions();对应的错误是这个语句输出的。
3、lastError输出的是：QSqlError(5, "Unable to fetch row", "database is locked")

大家不要被代码长度吓住了，里面很多都是重复相似的，求解答。

数据库只连接一次就够了，你为啥连接了那么多次？并且都不释放。

我开始的时候是只链接一次，比如我在构造函数中链接数据库，并初始化QSqlQuery，然后执行一次查询操作。
但是我发现一旦离开构造函数，在其他的成员函数中再QSqlQuery的exec(QString)时，程序就崩溃了。好像构造函数中构造的QSqlDatabase和QSqlQuery都失效了，无奈之下，我使用了现在这种方法。

解决了，每次qsqlquery使用后都要加query->finish()