使用sqlite3驱动程序在QSqlQuery中使用多个sql语句

2 回答

• 1

  正如QSqlQuery::prepare()和QSqlQuery::exec()的Qt文档中明确指出的那样，

  对于SQLite，查询字符串一次只能包含一个语句 . 如果给出多个语句，则该函数返回false .

  正如您已经猜到的，这个限制的唯一已知解决方法是将所有sql语句用一些字符串分隔，拆分语句并在循环中执行每个语句 .

  请参阅以下示例代码（使用“;”作为分隔符，并假设在查询中未使用相同的字符 . 这缺乏一般性，因为您可能在/ insert / update语句中的字符串文字中具有给定字符）：

  QSqlDatabase database;
  QSqlQuery query(database);
  QFile scriptFile("/path/to/your/script.sql");
  if (scriptFile.open(QIODevice::ReadOnly))
  {
      // The SQLite driver executes only a single (the first) query in the QSqlQuery
      //  if the script contains more queries, it needs to be splitted.
      QStringList scriptQueries = QTextStream(&scriptFile).readAll().split(';');
  
      foreach (QString queryTxt, scriptQueries)
      {
          if (queryTxt.trimmed().isEmpty()) {
              continue;
          }
          if (!query.exec(queryTxt))
          {
              qFatal(QString("One of the query failed to execute."
                          " Error detail: " + query.lastError().text()).toLocal8Bit());
          }
          query.finish();
      }
  }
  

  回复于 2024-04-20T14:56:57+08:00
• 9

  我写了一个简单的函数来从一个文件读取SQL并一次执行一个语句 .

  /**
  * @brief executeQueriesFromFile Read each line from a .sql QFile
  * (assumed to not have been opened before this function), and when ; is reached, execute
  * the SQL gathered until then on the query object. Then do this until a COMMIT SQL
  * statement is found. In other words, this function assumes each file is a single
  * SQL transaction, ending with a COMMIT line.
  */
  
  void executeQueriesFromFile(QFile *file, QSqlQuery *query)
  {
      while (!file->atEnd()){
          QByteArray readLine="";
          QString cleanedLine;
          QString line="";
          bool finished=false;
          while(!finished){
              readLine = file->readLine();
              cleanedLine=readLine.trimmed();
              // remove comments at end of line
              QStringList strings=cleanedLine.split("--");
              cleanedLine=strings.at(0);
  
              // remove lines with only comment, and DROP lines
              if(!cleanedLine.startsWith("--")
                      && !cleanedLine.startsWith("DROP")
                      && !cleanedLine.isEmpty()){
                  line+=cleanedLine;
              }
              if(cleanedLine.endsWith(";")){
                  break;
              }
              if(cleanedLine.startsWith("COMMIT")){
                  finished=true;
              }
          }
  
          if(!line.isEmpty()){
              query->exec(line);
          }
          if(!query->isActive()){
              qDebug() << QSqlDatabase::drivers();
              qDebug() <<  query->lastError();
              qDebug() << "test executed query:"<< query->executedQuery();
              qDebug() << "test last query:"<< query->lastQuery();
          }
      }
  }
  

  • http://www.fluxitek.fi/2013/10/reading-sql-text-file-sqlite-database-qt/

  • https://gist.github.com/savolai/6852986

  回复于 2024-04-20T14:56:57+08:00