MQL5 ReferenceWorking with databasesDatabaseTransactionBegin 

DatabaseTransactionBegin

Starts transaction execution.

bool  DatabaseTransactionBegin(
   int  database      // database handle received in DatabaseOpen
   );

Parameters

database

[in]  Database handle received in DatabaseOpen().

Return true if successful, otherwise false. To get the error code, use GetLastError(), the possible responses are:

  • ERR_INTERNAL_ERROR (4001)                   –  critical runtime error;
  • ERR_INVALID_PARAMETER (4003)              –  sql parameter contains an empty string;
  • ERR_NOT_ENOUGH_MEMORY (4004)          –  insufficient memory;
  • ERR_WRONG_STRING_PARAMETER (5040)  – error converting a request into a UTF-8 string;
  • ERR_DATABASE_INTERNAL (5120)              – internal database error;
  • ERR_DATABASE_INVALID_HANDLE (5121)   – invalid database handle;
  • ERR_DATABASE_EXECUTE (5124)               –  request execution error.

Note

The DatabaseTransactionBegin() function should be called before a transaction execution. Any transaction should start with calling DatabaseTransactionBegin() and end with calling 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);
  }
//+------------------------------------------------------------------+

See also

DatabaseExecute, DatabasePrepare, DatabaseTransactionCommit, DatabaseTransactionRollback