DatabasePrepare
创建可使用DatabaseRead()执行的请求句柄。
int DatabasePrepare(
int database, // 在DatabaseOpen中接收的数据库句柄
string sql, // SQL 请求
... // 请求参数
);参数
database
[in] 在DatabaseOpen()中接收的数据库句柄。
sql
[in] SQL请求，可能包含自动替换参数，其名为?1,?2,...
...
[in] 自动替换请求参数。
返回值
如果成功，函数返回SQL请求句柄。否则，返回INVALID_HANDLE。要获得错误代码，请使用GetLastError()，可能回应：
- ERR_INVALID_PARAMETER (4003) – 数据库文件的路径包含空字符串，或设置不兼容的标识组合；
- ERR_NOT_ENOUGH_MEMORY (4004) - 内存不足；
- ERR_WRONG_STRING_PARAMETER (5040) – 将请求转换为UTF-8字符串时出现错误；
- ERR_DATABASE_INVALID_HANDLE (5121) - 无效数据库句柄；
- ERR_DATABASE_TOO_MANY_OBJECTS (5122) - 超过可接受的数据库对象的最大数目；
- ERR_DATABASE_PREPARE (5125) - 请求生成错误。
注意
DatabasePrepare()函数不执行对数据库的请求。其目的是验证请求参数，并根据验证结果返回执行SQL请求的句柄。请求本身在第一次调用DatabaseRead()期间进行设置。
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(filename, DATABASE_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_date, to_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(deals, 10);
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=0; DatabaseReadBind(request, deal); 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(deals, i);
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(trades, 10);
request=DatabasePrepare(db, "SELECT * FROM TRADES");
if(request==INVALID_HANDLE)
{
Print("DB: ", filename, " request failed with code ", GetLastError());
DatabaseClose(db);
return;
}
for(i=0; DatabaseReadBind(request, trade); 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(trades, i);
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 history: 2741
The first 10 deals:
[ticket] [order_ticket] [position_ticket] [time] [type] [entry] [symbol] [volume] [price] [profit] [swap] [commission] [magic] [reason]
[0] 34429573 0 0 2019.09.05 22:39:59 2 0 "" 0.00000 0.00000 2000.00000 0.0000 0.00000 0 0
[1] 34432127 51447238 51447238 2019.09.06 06:00:03 0 0 "USDCAD" 0.10000 1.32320 0.00000 0.0000 -0.16000 500 3
[2] 34432128 51447239 51447239 2019.09.06 06:00:03 1 0 "USDCHF" 0.10000 0.98697 0.00000 0.0000 -0.16000 500 3
[3] 34432450 51447565 51447565 2019.09.06 07:00:00 0 0 "EURUSD" 0.10000 1.10348 0.00000 0.0000 -0.18000 400 3
[4] 34432456 51447571 51447571 2019.09.06 07:00:00 1 0 "AUDUSD" 0.10000 0.68203 0.00000 0.0000 -0.11000 400 3
[5] 34432879 51448053 51448053 2019.09.06 08:00:00 1 0 "USDCHF" 0.10000 0.98701 0.00000 0.0000 -0.16000 600 3
[6] 34432888 51448064 51448064 2019.09.06 08:00:00 0 0 "USDJPY" 0.10000 106.96200 0.00000 0.0000 -0.16000 600 3
[7] 34435147 51450470 51450470 2019.09.06 10:30:00 1 0 "EURUSD" 0.10000 1.10399 0.00000 0.0000 -0.18000 100 3
[8] 34435152 51450476 51450476 2019.09.06 10:30:00 0 0 "GBPUSD" 0.10000 1.23038 0.00000 0.0000 -0.20000 100 3
[9] 34435154 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]
[0] 2019.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
[1] 2019.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
[2] 2019.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
[3] 2019.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
[4] 2019.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
[5] 2019.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
[6] 2019.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
[7] 2019.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
[8] 2019.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
[9] 2019.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 database, string 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=0; i<deals; i++)
{
deal_ticket= HistoryDealGetTicket(i);
order_ticket= HistoryDealGetInteger(deal_ticket, DEAL_ORDER);
position_ticket=HistoryDealGetInteger(deal_ticket, DEAL_POSITION_ID);
time= (datetime)HistoryDealGetInteger(deal_ticket, DEAL_TIME);
type= HistoryDealGetInteger(deal_ticket, DEAL_TYPE);
entry= HistoryDealGetInteger(deal_ticket, DEAL_ENTRY);
symbol= HistoryDealGetString(deal_ticket, DEAL_SYMBOL);
volume= HistoryDealGetDouble(deal_ticket, DEAL_VOLUME);
price= HistoryDealGetDouble(deal_ticket, DEAL_PRICE);
profit= HistoryDealGetDouble(deal_ticket, DEAL_PROFIT);
swap= HistoryDealGetDouble(deal_ticket, DEAL_SWAP);
commission= HistoryDealGetDouble(deal_ticket, DEAL_COMMISSION);
magic= HistoryDealGetInteger(deal_ticket, DEAL_MAGIC);
reason= HistoryDealGetInteger(deal_ticket, DEAL_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_ticket, order_ticket, position_ticket, time, type, entry, symbol, volume, price, profit, swap, commission, magic, reason);
if(!DatabaseExecute(database, request_text))
{
PrintFormat("%s: failed to insert deal #%d with code %d", __FUNCTION__, deal_ticket, GetLastError());
PrintFormat("i=%d: deal #%d %s", i, deal_ticket, symbol);
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);
}
//+------------------------------------------------------------------+