DatabasePrepare

Creates a handle of a request, which can then be executed using DatabaseRead().

int  DatabasePrepare(
   int     database,      // database handle received in DatabaseOpen
   string  sql,           // SQL request
           ...            // request parameters
   );

Parameters

database

[in]  Database handle received in DatabaseOpen().

sql

[in]  SQL request that may contain automatically substituted parameters named ?1,?2,...

...

[in]  Automatically substituted request parameters.

Return Value

If successful, the function returns a handle for the SQL request. Otherwise, it returns INVALID_HANDLE. To get the error code, use GetLastError(), the possible responses are:

  • ERR_INVALID_PARAMETER (4003)                  – path to the database file contains an empty string, or an incompatible combination of flags is set;
  • ERR_NOT_ENOUGH_MEMORY (4004)              - insufficient memory;
  • ERR_WRONG_STRING_PARAMETER (5040)      – error converting a request into a UTF-8 string;
  • ERR_DATABASE_INVALID_HANDLE (5121)       - invalid database handle;
  • ERR_DATABASE_TOO_MANY_OBJECTS (5122) - exceeded the maximum acceptable number of Database objects;
  • ERR_DATABASE_PREPARE (5125)                   - request generation error.        

Note

The DatabasePrepare() function does not perform a request to a database. Its purpose is to verify the request parameters and return the handle for executing the SQL request based on the verification results. The request itself is set during the DatabaseRead() first call.

Example:

//--- Structure to store the deal
struct Deal
  {
   ulong             ticket;           // DEAL_TICKET
   long              order_ticket;     // DEAL_ORDER
   long              position_ticket;  // DEAL_POSITION_ID
   datetime          time;             // DEAL_TIME
   char              type;             // DEAL_TYPE
   char              entry;            // DEAL_ENTRY
   string            symbol;           // DEAL_SYMBOL
   double            volume;           // DEAL_VOLUME
   double            price;            // DEAL_PRICE
   double            profit;           // DEAL_PROFIT
   double            swap;             // DEAL_SWAP
   double            commission;       // DEAL_COMMISSION
   long              magic;            // DEAL_MAGIC
   char              reason;           // DEAL_REASON
  };
//--- Structure to store the trade: the order of members corresponds to the position in the terminal
struct Trade
  {
   datetime          time_in;          // entry time
   ulong             ticket;           // position ID
   char              type;             // buy or sell
   double            volume;           // volume
   string            symbol;           // symbol
   double            price_in;         // entry price
   datetime          time_out;         // exit time
   double            price_out;        // exit price
   double            commission;       // entry and exit commission
   double            swap;             // swap
   double            profit;           // profit or loss
  };
//+------------------------------------------------------------------+
//| Script program start function                                    |
//+------------------------------------------------------------------+
void OnStart()
  {
//--- create the file name
   string filename=IntegerToString(AccountInfoInteger(ACCOUNT_LOGIN))+"_trades.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;
     }
//--- 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);
//--- add deals to the table
   if(!InsertDeals(db))
      return;
//--- show the first 10 deals
   Deal deals[], deal;
   ArrayResize(deals10);
   int request=DatabasePrepare(db"SELECT * FROM DEALS");
   if(request==INVALID_HANDLE)
     {
      Print("DB: "filename" request failed with code "GetLastError());
      DatabaseClose(db);
      return;
     }
   int i;
   for(i=0DatabaseReadBind(requestdeal); i++)
     {
      if(i>=10)
         break;
      deals[i].ticket=deal.ticket;
      deals[i].order_ticket=deal.order_ticket;
      deals[i].position_ticket=deal.position_ticket;
      deals[i].time=deal.time;
      deals[i].type=deal.type;
      deals[i].entry=deal.entry;
      deals[i].symbol=deal.symbol;
      deals[i].volume=deal.volume;
      deals[i].price=deal.price;
      deals[i].profit=deal.profit;
      deals[i].swap=deal.swap;
      deals[i].commission=deal.commission;
      deals[i].magic=deal.magic;
      deals[i].reason=deal.reason;
     }
//--- print the deals
   if(i>0)
     {
      ArrayResize(dealsi);
      PrintFormat("The first %d deals:"i);
      ArrayPrint(deals);
     }
 
//--- delete request after use
   DatabaseFinalize(request);
 
//--- make sure that hedging system for open position management is used on the account
   if((ENUM_ACCOUNT_MARGIN_MODE)AccountInfoInteger(ACCOUNT_MARGIN_MODE)!=ACCOUNT_MARGIN_MODE_RETAIL_HEDGING)
     {
      //--- deals cannot be transformed to trades using a simple method through transactions, therefore complete operation
      DatabaseClose(db);
      return;
     }
 
//--- now create the TRADES table based on the DEALS table
   if(!CreateTableTrades(db))
     {
      DatabaseClose(db);
      return;
     }
//--- fill in the TRADES table using an SQL query based on DEALS table data
   ulong start=GetMicrosecondCount();
   if(DatabaseTableExists(db"DEALS"))
      //--- populate the TRADES table
      if(!DatabaseExecute(db"INSERT INTO TRADES(TIME_IN,TICKET,TYPE,VOLUME,SYMBOL,PRICE_IN,TIME_OUT,PRICE_OUT,COMMISSION,SWAP,PROFIT) "
                          "SELECT "
                          "   d1.time as time_in,"
                          "   d1.position_id as ticket,"
                          "   d1.type as type,"
                          "   d1.volume as volume,"
                          "   d1.symbol as symbol,"
                          "   d1.price as price_in,"
                          "   d2.time as time_out,"
                          "   d2.price as price_out,"
                          "   d1.commission+d2.commission as commission,"
                          "   d2.swap as swap,"
                          "   d2.profit as profit "
                          "FROM DEALS d1 "
                          "INNER JOIN DEALS d2 ON d1.position_id=d2.position_id "
                          "WHERE d1.entry=0 AND d2.entry=1     "))
        {
         Print("DB: fillng the TRADES table failed with code "GetLastError());
         return;
        }
   ulong transaction_time=GetMicrosecondCount()-start;
   
//--- show the first 10 deals
   Trade trades[], trade;
   ArrayResize(trades10);
   request=DatabasePrepare(db"SELECT * FROM TRADES");
   if(request==INVALID_HANDLE)
     {
      Print("DB: "filename" request failed with code "GetLastError());
      DatabaseClose(db);
      return;
     }
   for(i=0DatabaseReadBind(requesttrade); i++)
     {
      if(i>=10)
         break;
      trades[i].time_in=trade.time_in;
      trades[i].ticket=trade.ticket;
      trades[i].type=trade.type;
      trades[i].volume=trade.volume;
      trades[i].symbol=trade.symbol;
      trades[i].price_in=trade.price_in;
      trades[i].time_out=trade.time_out;
      trades[i].price_out=trade.price_out;
      trades[i].commission=trade.commission;
      trades[i].swap=trade.swap;
      trades[i].profit=trade.profit;
     }
//--- print trades
   if(i>0)
     {
      ArrayResize(tradesi);
      PrintFormat("\r\nThe first %d trades:"i);
      ArrayPrint(trades);
      PrintFormat("Filling the TRADES table took %.2f milliseconds",double(transaction_time)/1000);
     }
//--- delete request after use
   DatabaseFinalize(request);
 
//--- close the database
   DatabaseClose(db);
  }
/*
Results:
   Deals in the trading history2741 
   The first 10 deals:
       [ticket] [order_ticket] [position_ticket]              [time] [type] [entry] [symbol] [volume]   [price]   [profit] [swap] [commission] [magic] [reason]
   [034429573              0                 0 2019.09.05 22:39:59      2       0 ""        0.00000   0.00000 2000.00000 0.0000      0.00000       0        0
   [134432127       51447238          51447238 2019.09.06 06:00:03      0       0 "USDCAD"  0.10000   1.32320    0.00000 0.0000     -0.16000     500        3
   [234432128       51447239          51447239 2019.09.06 06:00:03      1       0 "USDCHF"  0.10000   0.98697    0.00000 0.0000     -0.16000     500        3
   [334432450       51447565          51447565 2019.09.06 07:00:00      0       0 "EURUSD"  0.10000   1.10348    0.00000 0.0000     -0.18000     400        3
   [434432456       51447571          51447571 2019.09.06 07:00:00      1       0 "AUDUSD"  0.10000   0.68203    0.00000 0.0000     -0.11000     400        3
   [534432879       51448053          51448053 2019.09.06 08:00:00      1       0 "USDCHF"  0.10000   0.98701    0.00000 0.0000     -0.16000     600        3
   [634432888       51448064          51448064 2019.09.06 08:00:00      0       0 "USDJPY"  0.10000 106.96200    0.00000 0.0000     -0.16000     600        3
   [734435147       51450470          51450470 2019.09.06 10:30:00      1       0 "EURUSD"  0.10000   1.10399    0.00000 0.0000     -0.18000     100        3
   [834435152       51450476          51450476 2019.09.06 10:30:00      0       0 "GBPUSD"  0.10000   1.23038    0.00000 0.0000     -0.20000     100        3
   [934435154       51450479          51450479 2019.09.06 10:30:00      1       0 "EURJPY"  0.10000 118.12000    0.00000 0.0000     -0.18000     200        3
 
   The first 10 trades:
                 [time_in] [ticket] [type] [volume] [symbol] [price_in]          [time_out] [price_out] [commission]   [swap]  [profit]
   [02019.09.06 06:00:03 51447238      0  0.10000 "USDCAD"    1.32320 2019.09.06 18:00:00     1.31761     -0.32000  0.00000 -42.43000
   [12019.09.06 06:00:03 51447239      1  0.10000 "USDCHF"    0.98697 2019.09.06 18:00:00     0.98641     -0.32000  0.00000   5.68000
   [22019.09.06 07:00:00 51447565      0  0.10000 "EURUSD"    1.10348 2019.09.09 03:30:00     1.10217     -0.36000 -1.31000 -13.10000
   [32019.09.06 07:00:00 51447571      1  0.10000 "AUDUSD"    0.68203 2019.09.09 03:30:00     0.68419     -0.22000  0.03000 -21.60000
   [42019.09.06 08:00:00 51448053      1  0.10000 "USDCHF"    0.98701 2019.09.06 18:00:01     0.98640     -0.32000  0.00000   6.18000
   [52019.09.06 08:00:00 51448064      0  0.10000 "USDJPY"  106.96200 2019.09.06 18:00:01   106.77000     -0.32000  0.00000 -17.98000
   [62019.09.06 10:30:00 51450470      1  0.10000 "EURUSD"    1.10399 2019.09.06 14:30:00     1.10242     -0.36000  0.00000  15.70000
   [72019.09.06 10:30:00 51450476      0  0.10000 "GBPUSD"    1.23038 2019.09.06 14:30:00     1.23040     -0.40000  0.00000   0.20000
   [82019.09.06 10:30:00 51450479      1  0.10000 "EURJPY"  118.12000 2019.09.06 14:30:00   117.94100     -0.36000  0.00000  16.73000
   [92019.09.06 10:30:00 51450480      0  0.10000 "GBPJPY"  131.65300 2019.09.06 14:30:01   131.62500     -0.40000  0.00000  -2.62000
   Filling the TRADES table took 12.51 milliseconds
*/  
//+------------------------------------------------------------------+
//| Creates the DEALS table                                          |
//+------------------------------------------------------------------+
bool CreateTableDeals(int database)
  {
//--- if the DEALS table already exists, delete it
   if(!DeleteTable(database"DEALS"))
     {
      return(false);
     }
//--- 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 DEALS table failed with code "GetLastError());
         return(false);
        }
//--- the table has been successfully created
   return(true);
  }
//+------------------------------------------------------------------+
//| 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 the DEALS table  with code "GetLastError());
      return(false);
     }
//--- the table has been successfully deleted
   return(true);
  }
//+------------------------------------------------------------------+
//| Adds deals to the database table                                 |
//+------------------------------------------------------------------+
bool InsertDeals(int database)
  {
//--- 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 (Expert Advisor ID)
   long     reason;              // deal execution reason or source
//--- go through all deals and add them to the database
   bool failed=false;
   int deals=HistoryDealsTotal();
// --- 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 to the table 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_ticketorder_ticketposition_tickettimetypeentrysymbolvolumepriceprofitswapcommissionmagicreason);
      if(!DatabaseExecute(databaserequest_text))
        {
         PrintFormat("%s: failed to insert deal #%d with code %d"__FUNCTION__deal_ticketGetLastError());
         PrintFormat("i=%d: deal #%d  %s"ideal_ticketsymbol);
         failed=true;
         break;
        }
     }
//--- check for transaction execution errors
   if(failed)
     {
      //--- roll back all transactions and unlock the database
      DatabaseTransactionRollback(database);
      PrintFormat("%s: DatabaseExecute() failed with code %d"__FUNCTION__GetLastError());
      return(false);
     }
//--- all transactions have been performed successfully - record changes and unlock the database
   DatabaseTransactionCommit(database);
   return(true);
  }
//+------------------------------------------------------------------+
//| Creates the TRADES table                                          |
//+------------------------------------------------------------------+
bool CreateTableTrades(int database)
  {
//--- if the TRADES table already exists, delete it
   if(!DeleteTable(database"TRADES"))
      return(false);
//--- check if the table exists
   if(!DatabaseTableExists(database"TRADES"))
      //--- create the table
      if(!DatabaseExecute(database"CREATE TABLE TRADES("
                          "TIME_IN     INT     NOT NULL,"
                          "TICKET      INT     NOT NULL,"
                          "TYPE        INT     NOT NULL,"
                          "VOLUME      REAL,"
                          "SYMBOL      CHAR(10),"
                          "PRICE_IN    REAL,"
                          "TIME_OUT    INT     NOT NULL,"
                          "PRICE_OUT   REAL,"
                          "COMMISSION  REAL,"
                          "SWAP        REAL,"
                          "PROFIT      REAL);"))
        {
         Print("DB: create the TRADES table failed with code "GetLastError());
         return(false);
        }
//--- the table has been successfully created
   return(true);
  }
//+------------------------------------------------------------------+

See also

DatabaseExecute, DatabaseFinalize