MQL5参考使用数据库DatabaseTransactionBegin 

DatabaseTransactionBegin

开始事务执行。

bool  DatabaseTransactionBegin(
   int  database      // 在DatabaseOpen中接收的数据库句柄
   );

参数

database

[in]  在DatabaseOpen()中接收的数据库句柄。

如果成功返回true,否则返回false。要获得错误代码,请使用GetLastError(),可能回应:

  • ERR_INTERNAL_ERROR (4001)                   –  重要运行时错误;
  • ERR_INVALID_PARAMETER (4003)              –  sql参数包含空字符串;
  • ERR_NOT_ENOUGH_MEMORY (4004)          –  内存不足;
  • ERR_WRONG_STRING_PARAMETER (5040)  – 将请求转换为UTF-8字符串时出现错误;
  • ERR_DATABASE_INTERNAL (5120)              – 内部数据库错误;
  • ERR_DATABASE_INVALID_HANDLE (5121)   – 无效数据库句柄;
  • ERR_DATABASE_EXECUTE (5124)               –  请求执行错误。

注意

DatabaseTransactionBegin()函数应该在事务执行之前调用。任何事务都应该以调用DatabaseTransactionBegin()开始,以调用DatabaseTransactionCommit()结束。

Example:

//+------------------------------------------------------------------+
//| Script program start function                                    |
//+------------------------------------------------------------------+
void OnStart()
  {
//--- create the file name
   string filename=AccountInfoString(ACCOUNT_SERVER) +"_"+IntegerToString(AccountInfoInteger(ACCOUNT_LOGIN))+".sqlite";
//--- open/create the database in the common terminal folder
   int db=DatabaseOpen(filenameDATABASE_OPEN_READWRITE | DATABASE_OPEN_CREATE | DATABASE_OPEN_COMMON);
   if(db==INVALID_HANDLE)
     {
      Print("DB: "filename" open failed with code "GetLastError());
      return;
     }
//--- if the DEALS table already exists, delete it
   if(!DeleteTable(db"DEALS"))
     {
      DatabaseClose(db);
      return;
     }
//--- create the DEALS table
   if(!CreateTableDeals(db))
     {
      DatabaseClose(db);
      return;
     }
//---  request the entire trading history
   datetime from_date=0;
   datetime to_date=TimeCurrent();
//--- request the history of deals in the specified interval
   HistorySelect(from_dateto_date);
   int deals_total=HistoryDealsTotal();
   PrintFormat("Deals in the trading history: %d "deals_total);
 
//--- measure the transaction execution speed using DatabaseTransactionBegin/DatabaseTransactionCommit
   ulong start=GetMicrosecondCount();
   bool fast_transactions=true;
   InsertDeals(dbfast_transactions);
   double fast_transactions_time=double(GetMicrosecondCount()-start)/1000;
   PrintFormat("Transations WITH    DatabaseTransactionBegin/DatabaseTransactionCommit: time=%.1f milliseconds"fast_transactions_time);
 
//--- delete the DEALS table, and then create it again
   if(!DeleteTable(db"DEALS"))
     {
      DatabaseClose(db);
      return;
     }
//--- create a new DEALS table
   if(!CreateTableDeals(db))
     {
      DatabaseClose(db);
      return;
     }
 
//--- test again, this time without using DatabaseTransactionBegin/DatabaseTransactionCommit
   fast_transactions=false;
   start=GetMicrosecondCount();
   InsertDeals(dbfast_transactions);
   double slow_transactions_time=double(GetMicrosecondCount()-start)/1000;
   PrintFormat("Transations WITHOUT DatabaseTransactionBegin/DatabaseTransactionCommit: time=%.1f milliseconds"slow_transactions_time);
//--- report gain in time
   PrintFormat("Use of DatabaseTransactionBegin/DatabaseTransactionCommit provided acceleration by %.1f times"double(slow_transactions_time)/fast_transactions_time);
//--- close the database
   DatabaseClose(db);
  }
/*
Results:
   Deals in the trading history2737
   Transations WITH    DatabaseTransactionBegin/DatabaseTransactionCommittime=48.5 milliseconds
   Transations WITHOUT DatabaseTransactionBegin/DatabaseTransactionCommittime=25818.9 milliseconds
   Use of DatabaseTransactionBegin/DatabaseTransactionCommit provided acceleration by 532.8 times
*/
//+------------------------------------------------------------------+
//| Deletes a table with the specified name from the database        |
//+------------------------------------------------------------------+
bool DeleteTable(int databasestring table_name)
  {
   if(!DatabaseExecute(database"DROP TABLE IF EXISTS "+table_name))
     {
      Print("Failed to drop table with code "GetLastError());
      return(false);
     }
//--- the table has been successfully deleted
   return(true);
  }
//+------------------------------------------------------------------+
//| Creates the DEALS table                                          |
//+------------------------------------------------------------------+
bool CreateTableDeals(int database)
  {
//--- check if the table exists
   if(!DatabaseTableExists(database"DEALS"))
      //--- create the table
      if(!DatabaseExecute(database"CREATE TABLE DEALS("
                          "ID          INT KEY NOT NULL,"
                          "ORDER_ID    INT     NOT NULL,"
                          "POSITION_ID INT     NOT NULL,"
                          "TIME        INT     NOT NULL,"
                          "TYPE        INT     NOT NULL,"
                          "ENTRY       INT     NOT NULL,"
                          "SYMBOL      CHAR(10),"
                          "VOLUME      REAL,"
                          "PRICE       REAL,"
                          "PROFIT      REAL,"
                          "SWAP        REAL,"
                          "COMMISSION  REAL,"
                          "MAGIC       INT,"
                          "REASON      INT );"))
        {
         Print("DB: create the table DEALS failed with code "GetLastError());
         return(false);
        }
//--- the table has been successfully created
   return(true);
  }
//+------------------------------------------------------------------+
//| Adds deals to the database table                                 |
//+------------------------------------------------------------------+
bool InsertDeals(int databasebool begintransaction=true)
  {
//--- Auxiliary variables
   ulong    deal_ticket;         // deal ticket
   long     order_ticket;        // the ticket of the order by which the deal was executed
   long     position_ticket;     // ID of the position to which the deal belongs
   datetime time;                // deal execution time
   long     type ;               // deal type
   long     entry ;              // deal direction
   string   symbol;              // the symbol fro which the deal was executed
   double   volume;              // operation volume
   double   price;               // price
   double   profit;              // financial result
   double   swap;                // swap
   double   commission;          // commission
   long     magic;               // Magic number
   long     reason;              // deal execution reason or source
//--- go through all deals and add to the database
   bool failed=false;
   int deals=HistoryDealsTotal();
//--- if fast transaction performance method is used
   if(begintransaction)
     {
      // --- lock the database before executing transactions
      DatabaseTransactionBegin(database);
     }
   for(int i=0i<dealsi++)
     {
      deal_ticket=    HistoryDealGetTicket(i);
      order_ticket=   HistoryDealGetInteger(deal_ticketDEAL_ORDER);
      position_ticket=HistoryDealGetInteger(deal_ticketDEAL_POSITION_ID);
      time= (datetime)HistoryDealGetInteger(deal_ticketDEAL_TIME);
      type=           HistoryDealGetInteger(deal_ticketDEAL_TYPE);
      entry=          HistoryDealGetInteger(deal_ticketDEAL_ENTRY);
      symbol=         HistoryDealGetString(deal_ticketDEAL_SYMBOL);
      volume=         HistoryDealGetDouble(deal_ticketDEAL_VOLUME);
      price=          HistoryDealGetDouble(deal_ticketDEAL_PRICE);
      profit=         HistoryDealGetDouble(deal_ticketDEAL_PROFIT);
      swap=           HistoryDealGetDouble(deal_ticketDEAL_SWAP);
      commission=     HistoryDealGetDouble(deal_ticketDEAL_COMMISSION);
      magic=          HistoryDealGetInteger(deal_ticketDEAL_MAGIC);
      reason=         HistoryDealGetInteger(deal_ticketDEAL_REASON);
      //--- add each deal using the following request
      string request_text=StringFormat("INSERT INTO DEALS (ID,ORDER_ID,POSITION_ID,TIME,TYPE,ENTRY,SYMBOL,VOLUME,PRICE,PROFIT,SWAP,COMMISSION,MAGIC,REASON)"
                                       "VALUES (%d, %d, %d, %d, %d, %d, '%s', %G, %G, %G, %G, %G, %d, %d)",
                                       deal_ticket, order_ticket, position_tickettimetypeentrysymbolvolumepriceprofitswapcommissionmagicreason);
      if(!DatabaseExecute(databaserequest_text))
        {
         PrintFormat("%s: failed to insert deal #%dwith code %d"__FUNCTION__deal_ticketGetLastError());
         PrintFormat("i=%d: deal #%d  %s"ideal_ticket, symbol);
         failed=true;
         break;
        }
     }
//--- check for transaction execution errors
   if(failed)
     {
      //--- if fast transaction performance method is used
      if(begintransaction)
        {
         //--- roll back all transactions and unlock the database
         DatabaseTransactionRollback(database);
        }
      Print("%s: DatabaseExecute() failed with code "__FUNCTION__GetLastError());
      return(false);
     }
//--- if fast transaction performance method is used
   if(begintransaction)
     {
      //--- all transactions have been performed successfully - record changes and unlock the database
      DatabaseTransactionCommit(database);
     }
//--- successful completion
   return(true);
  }
//+------------------------------------------------------------------+

另见

DatabaseExecute DatabasePrepareDatabaseTransactionCommitDatabaseTransactionRollback