第三十章 使用数据库
这些数据库函数使用了流行且易于使用的SQLite引擎。该引擎的便利之处在于，整个数据库都位于用户PC硬盘上的单个文件中。 这些函数可以方便地创建表格、向表中添加数据、执行更改和使用简单的SQL请求进行采样：
接收任何格式的交易历史和报价，
保存优化和测试结果，
通过其他分析组合准备和交换数据，
存储MQL5应用程序设置和状态。
数据库函数可以使您通过SQL请求替换重复率最高的大数据组处理操作，因此常常可以使用DatabaseExecute/DatabasePrepare调用，而不是编写复杂的循环和比较。使用DatabaseReadBind函数，方便地获取现有架构中的查询结果。该函数运行在单个调用中一次性读取所有记录字段。
若要加快读取、编写和更改速度，可以使用DATABASE_OPEN_MEMORY标识在RAM中打开/创建数据库，虽然这样的数据库只适用于特定的应用程序，不可共享。当处理位于硬盘上的数据库时，应该使用DatabaseTransactionBegin/DatabaseTransactionCommit/DatabaseTransactionRollback将批量数据插入/更改封装在交易事务中。这可以使整个过程加快数百倍。
若要开始使用函数，请参阅文章SQLite：本地操作MQL5中的SQL数据库。
| 函数 | 功能 |
|---|---|
| DatabaseOpen | 在指定文件中打开或创建数据库 |
| DatabaseClose | 关闭数据库 |
| DatabaseImport | 从文件导入数据到表格中 |
| DatabaseExport | 将表格或SQL请求执行结果到处到CSV文件 |
| DatabasePrint | 在专家日志中打印表格或SQL执行结果 |
| DatabaseTableExists | 检查数据库中是否存在表格 |
| DatabaseExecute | 执行对指定数据库的请求 |
| DatabasePrepare | 创建可使用DatabaseRead()执行的请求句柄 |
| DatabaseReset | 重置请求，比如调用DatabasePrepare()之后 |
| DatabaseBind | 在请求中设置一个参数值 |
| DatabaseBindArray | 将数组设置为参数值 |
| DatabaseRead | 作为请求结果，移到下一个条目 |
| DatabaseFinalize | 移除在DatabasePrepare()中创建的请求 |
| DatabaseTransactionBegin | 开始事务执行 |
| DatabaseTransactionCommit | 完成事务执行 |
| DatabaseTransactionRollback | 回滚事务 |
| DatabaseColumnsCount | 获取请求中的字段数 |
| DatabaseColumnName | 按索引获取字段名 |
| DatabaseColumnType | 按索引获取字段类型 |
| DatabaseColumnSize | 获取字段大小（以字节为单位） |
| DatabaseColumnText | 从当前记录中获取作为字符串的字段值 |
| DatabaseColumnInteger | 从当前记录中获取int类型的值 |
| DatabaseColumnLong | 从当前记录中获取long类型的值 |
| DatabaseColumnDouble | 从当前记录中获取double类型的值 |
| DatabaseColumnBlob | 从当前记录中获取作为数组的字段值 |
# 30.1 DatabaseOpen
在指定文件中打开或创建数据库。
int DatabaseOpen(
string filename, // 文件名
uint flags // 标识组合
);
1
2
3
4
2
3
4
参数
filename
[in] 相对于"MQL5\Files"文件夹的文件名称。
flags
[in] ENUM_DATABASE_OPEN_FLAGS枚举中的标识组合。
返回值
如果执行成功，函数返回用于访问数据库的数据库句柄。否则，返回INVALID_HANDLE。要获得错误代码，请使用GetLastError()，可能回应：
ERR_INTERNAL_ERROR (4001) – 重要运行时错误；
ERR_WRONG_INTERNAL_PARAMETER (4002) - 访问"MQL5\Files"文件夹时出现的内部错误；
ERR_INVALID_PARAMETER (4003) –
数据库文件的路径包含空字符串，或设置不兼容的标识组合；
ERR_NOT_ENOUGH_MEMORY (4004) - 内存不足；
ERR_WRONG_FILENAME (5002) - 错误的数据库文件名称；
ERR_TOO_LONG_FILENAME (5003) - 数据库文件的绝对路径超过最大长度；
ERR_DATABASE_TOO_MANY_OBJECTS (5122) - 超过可接受的数据库对象的最大数目；
ERR_DATABASE_CONNECT (5123) - 数据库连接错误；
ERR_DATABASE_MISUSE (5621) - 错误使用SQLite库。
注意
如果filename参数为NULL或是空字符串""，则在磁盘上创建一个临时文件。并会在关闭数据库连接后自动删除。
如果filename参数为":memory:"，则数据库在内存中创建，并在与其连接关闭后自动删除。
如果flags参数没有DATABASE_OPEN_READONLY或DATABASE_OPEN_READWRITE标识，则使用DATABASE_OPEN_READWRITE标识。
如果没有指定文件扩展名，请使用".sqlite"。
ENUM_DATABASE_OPEN_FLAGS
| ID | 描述 |
|---|---|
| DATABASE_OPEN_READONLY | 只读 |
| DATABASE_OPEN_READWRITE | 为读写开放 |
| DATABASE_OPEN_CREATE | 必要时，在磁盘上创建文件 |
| DATABASE_OPEN_MEMORY | 在RAM中创建数据库 |
| DATABASE_OPEN_COMMON | 该文件位于所有程序端的通用文件夹 |
# 30.2 DatabaseClose
关闭数据库。
void DatabaseClose(
int database // 在DatabaseOpen中接收的数据库句柄
);
1
2
3
2
3
参数
database
[in] 在DatabaseOpen()中接收的数据库句柄。
返回值
无。
注意
调用DatabaseClose之后，对数据库的所有请求句柄都将被自动删除并变为无效。
如果句柄无效，函数设置ERR_DATABASE_INVALID_HANDLE错误。您可以使用GetLastError()检查错误。
# 30.3 DatabaseImport
从文件导入数据到表格中。
long DatabaseImport(
int database, // 在DatabaseOpen中接收的数据库句柄
const string table, // 要插入数据的表格名称
const string filename, // 要导入数据的文件名
uint flags, // 标识组合
const string separator, // 数据分隔符
ulong skip_rows, // 要跳过多少初始字符串
const string skip_comments // 定义注释的字符串
);
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
参数
database
[in] 在DatabaseOpen()中接收的数据库句柄。
table
[in] 将要添加文件数据的表格名称。
filename
[in] 用于读取数据的CSV文件或ZIP存档文件。该名称可能包含子目录，并且是相对于MQL5\Files文件夹设置。
标识
[in] 标识组合。
separator
[in] CSV文件中的数据分隔符。
skip_rows
[in] 当从文件读取数据时，要跳过的初始字符串数。
skip_comments
[in]
用于将字符串指定为注释的字符串。如果在字符串的开头检测到skip_comments中的任何字符，那么该字符串将被视为注释，并不被导入。
返回值
返回导入字符串的数量，如果出现错误则返回-1。要获得错误代码，请使用GetLastError()，可能回应：
ERR_INVALID_PARAMETER (4003) – 没有指定表格名称（空字符串或NULL）；
ERR_DATABASE_INTERNAL (5120) – 内部数据库错误；
ERR_DATABASE_INVALID_HANDLE (5121) - 无效数据库句柄。
注意
如果没有名为 table 的表格，则自动生成该表。根据文件数据自动定义创建表格中的名称和字段类型。
另见
DatabaseOpen、DatabasePrint
# 30.4 DatabaseExport
将表格或SQL请求执行结果到处到CSV文件。该文件使用UTF-8编码创建。
long DatabaseExport(
int database, //在DatabaseOpen中接收的数据库句柄
const string table_or_sql, // 表格名称或SQL请求
const string filename, // 用于数据导出的CSV文件名称
uint flags, // 标识组合
const string separator // CSV文件中的数据分隔符
);
1
2
3
4
5
6
7
2
3
4
5
6
7
参数
database
[in] 在DatabaseOpen()中接收的数据库句柄。
table_or_sql
[in] 结果将被导出到指定文件的表格名称或SQL请求文本。
filename
[in] 用于数据导出的文件名。设置相对于MQL5\Files文件夹的路径。
标识
[in] 定义文件输出的标识组合。标识定义如下：
DATABASE_EXPORT_HEADER – 显示包含字段名的字符串
DATABASE_EXPORT_INDEX – 显示字符串索引
DATABASE_EXPORT_NO_BOM – 没有在文件开始插入BOM标记（默认插入BOM）
DATABASE_EXPORT_CRLF – 使用CRLF换行（默认为LF）
DATABASE_EXPORT_APPEND –
将数据添加到现有文件的末尾（默认情况下，覆盖该文件）。如果文件不存在，则将被创建。
DATABASE_EXPORT_QUOTED_STRINGS – 在双引号中显示字符串值。
DATABASE_EXPORT_COMMON_FOLDER - 在所有客户端的常规文件夹中创建CSV文件\Terminal\Common\File。
separator
[in] 数据分隔符。如果指定NULL，则将'\t'制表符用作分隔符。 空字符串""被认为是有效分隔符，但不能将获取的CSV文件读取为表格 – 它被认为是一组字符串。
返回值
返回导出条目的数量，或者在错误情况下返回负值。要获得错误代码，请使用GetLastError()，可能回应：
ERR_INTERNAL_ERROR (4001) – 重要运行时错误；
ERR_INVALID_PARAMETER (4003) –
数据库文件的路径包含空字符串，或设置不兼容的标识组合；
ERR_NOT_ENOUGH_MEMORY (4004) - 内存不足；
ERR_FUNCTION_NOT_ALLOWED(4014) – 不允许指定渠道；
ERR_PROGRAM_STOPPED(4022) – 操作取消（MQL程序停止）；
ERR_WRONG_FILENAME (5002) - 无效文件名；
ERR_TOO_LONG_FILENAME (5003) - 文件的绝对路径超过最大长度；
ERR_CANNOT_OPEN_FILE(5004) – 无法打开文件进行编写；
ERR_FILE_WRITEERROR(5026) – 无法写入文件；
ERR_DATABASE_INTERNAL (5120) – 内部数据库错误；
ERR_DATABASE_INVALID_HANDLE (5121) - 无效数据库句柄；
ERR_DATABASE_QUERY_PREPARE(5125) – 请求生成错误；
ERR_DATABASE_QUERY_NOT_READONLY – 允许只读请求。
注意
如果请求结果被导出，则SQL请求应该以"SELECT" 或 "select"开始。换句话说，SQL请求不能改变数据库状态，否则DatabaseExport()失败并显示错误。
数据库字符串值可能包含转换字符('\r'或'\r\n' )，以及分隔符参数中设置的值分隔符。在这种情况下，请确保在'flags'参数中使用DATABASE_EXPORT_QUOTED_STRINGS标识。如果该标识已存在，则所有显示的字符串用双引号表示。如果一个字符串包含双引号，则用双层双引号代替。
另见
DatabasePrint、DatabaseImport
# 30.5 DatabasePrint
在专家日志中打印表格或SQL执行结果。
long DatabasePrint(
int database, // 在DatabaseOpen中接收的数据库句柄
const string table_or_sql, // 表格或SQL请求
uint flags // 标识组合
);
1
2
3
4
5
2
3
4
5
参数
database
[in] 在DatabaseOpen()中接收的数据库句柄。
table_or_sql
[in] 结果显示在专家日志中的表格名称或SQL请求文本。
标识
[in] 轻易输出格式的标识组合。标识定义如下：
DATABASE_PRINT_NO_HEADER – 不显示表格的列名（字段名）
DATABASE_PRINT_NO_INDEX – 不显示字符串索引
DATABASE_PRINT_NO_FRAME – 不显示分隔标题和数据的框架
DATABASE_PRINT_STRINGS_RIGHT – 将字符串向右对齐。
如果flags=0，则显示列和字符串，标题和数据用框架分隔，而字符串向左对齐。
返回值
返回导出字符串的数量，如果出现错误则返回-1。要获得错误代码，请使用GetLastError()，可能回应：
ERR_INTERNAL_ERROR (4001) – 重要运行时错误；
ERR_NOT_ENOUGH_MEMORY (4004) - 内存不足；
ERR_DATABASE_INTERNAL (5120) – 内部数据库错误；
ERR_DATABASE_INVALID_HANDLE (5121) - 无效数据库句柄；
注意
如果日志显示请求结果，则SQL请求应该以"SELECT" 或 "select"开始。换句话说，SQL请求不能改变数据库状态，否则DatabasePrint()失败并显示错误。
例如：
//+------------------------------------------------------------------+
//| 脚本程序起始函数 |
//+------------------------------------------------------------------+
void OnStart()
{
string filename="departments.sqlite";
//--- 在常规程序端文件夹中创建或打开数据库
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;
}
//--- 创建COMPANY表格
if(!CreataTableCompany(db))
{
DatabaseClose(db);
return;
}
//--- 创建DEPARTMENT表格
if(!CreataTableDepartment(db))
{
DatabaseClose(db);
return;
}
//--- 显示COMPANY和DEPARTMENT表格中所有字段的列表
PrintFormat("Try to print request \"PRAGMA TABLE_INFO(COMPANY);PRAGMA TABLE_INFO(DEPARTMENT)\"");
if(DatabasePrint(db, "PRAGMA TABLE_INFO(COMPANY);PRAGMA TABLE_INFO(DEPARTMENT)", 0)<0)
{
PrintFormat("DatabasePrint(\"PRAGMA TABLE_INFO()\") failed, error code=%d", GetLastError());
DatabaseClose(db);
return;
}
//--- 在日志中显示COMPANY表格
PrintFormat("Try to print request \"SELECT * from COMPANY\"");
if(DatabasePrint(db, "SELECT * from COMPANY", 0)<0)
{
Print("DatabasePrint failed with code ", GetLastError());
DatabaseClose(db);
return;
}
//--- 请求合并COMPANY和DEPARTMENT表格的文本
string request="SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT "
"ON COMPANY.ID = DEPARTMENT.EMP_ID";
//--- 显示表格合并结果
PrintFormat("Try to print request \"SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT\"");
if(DatabasePrint(db, request, 0)<0)
{
Print("DatabasePrint failed with code ", GetLastError());
DatabaseClose(db);
return;
}
//--- 关闭数据库
DatabaseClose(db);
}
/*
结论：
Try to print request "PRAGMA TABLE_INFO(COMPANY);PRAGMA TABLE_INFO(DEPARTMENT)"
#| cid name type notnull dflt_value pk
-+-------------------------------------------
1| 0 ID INT 1 1
2| 1 NAME TEXT 1 0
3| 2 AGE INT 1 0
4| 3 ADDRESS CHAR(50) 0 0
5| 4 SALARY REAL 0 0
#| cid name type notnull dflt_value pk
-+------------------------------------------
1| 0 ID INT 1 1
2| 1 DEPT CHAR(50) 1 0
3| 2 EMP_ID INT 1 0
Try to print request "SELECT * from COMPANY"
#| ID NAME AGE ADDRESS SALARY
-+--------------------------------
1| 1 Paul 32 California 25000.0
2| 2 Allen 25 Texas 15000.0
3| 3 Teddy 23 Norway 20000.0
4| 4 Mark 25 Rich-Mond 65000.0
5| 5 David 27 Texas 85000.0
6| 6 Kim 22 South-Hall 45000.0
7| 7 James 24 Houston 10000.0
Try to print request "SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT"
#| EMP_ID NAME DEPT
-+-------------------------
1| 1 Paul IT Billing
2| 2 Allen Engineering
3| Teddy
4| Mark
5| David
6| Kim
7| 7 James Finance
*/
//+------------------------------------------------------------------+
//| 创建COMPANY表格 |
//+------------------------------------------------------------------+
bool CreateTableCompany(int database)
{
//--- 如果COMPANY表格已存在，请将其删除
if(DatabaseTableExists(database, "COMPANY"))
{
//--- 删除表格
if(!DatabaseExecute(database, "DROP TABLE COMPANY"))
{
Print("Failed to drop table COMPANY with code ", GetLastError());
return(false);
}
}
//--- 创建COMPANY表格
if(!DatabaseExecute(database, "CREATE TABLE COMPANY("
"ID INT PRIMARY KEY NOT NULL,"
"NAME TEXT NOT NULL,"
"AGE INT NOT NULL,"
"ADDRESS CHAR(50),"
"SALARY REAL );"))
{
Print("DB: create table COMPANY failed with code ", GetLastError());
return(false);
}
//--- 将数据输入到COMPANY表格
if(!DatabaseExecute(database, "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 25000.00); "
"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Allen', 25, 'Texas', 15000.00); "
"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00); "
"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Mark', 25, 'Rich-Mond', 65000.00); "
"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, 'David', 27, 'Texas', 85000.0); "
"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, 'Kim', 22, 'South-Hall', 45000.0); "
"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (7, 'James', 24, 'Houston', 10000.00); "))
{
Print("COMPANY insert failed with code ", GetLastError());
return(false);
}
//--- 成功
return(true);
}
//+------------------------------------------------------------------+
//| 创建DEPARTMENT表格 |
//+------------------------------------------------------------------+
bool CreateTableDepartment(int database)
{
//--- 如果DEPARTMENT表格已存在，请将其删除
if(DatabaseTableExists(database, "DEPARTMENT"))
{
//--- 删除表格
if(!DatabaseExecute(database, "DROP TABLE DEPARTMENT"))
{
Print("Failed to drop table DEPARTMENT with code ", GetLastError());
return(false);
}
}
//--- 创建DEPARTMENT表格
if(!DatabaseExecute(database, "CREATE TABLE DEPARTMENT ("
"ID INT PRIMARY KEY NOT NULL,"
"DEPT CHAR(50) NOT NULL,"
"EMP_ID INT NOT NULL);"))
{
Print("DB: create table DEPARTMENT failed with code ", GetLastError());
return(false);
}
//--- 将数据输入到DEPARTMENT表格
if(!DatabaseExecute(database, "INSERT INTO DEPARTMENT (ID,DEPT,EMP_ID) VALUES (1, 'IT Billing', 1); "
"INSERT INTO DEPARTMENT (ID,DEPT,EMP_ID) VALUES (2, 'Engineering', 2); "
"INSERT INTO DEPARTMENT (ID,DEPT,EMP_ID) VALUES (3, 'Finance', 7);"))
{
Print("DEPARTMENT insert failed with code ", GetLastError());
return(false);
}
//--- 成功
return(true);
}
//+-------------------------------------------------------------------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
另见
DatabaseExport、DatabaseImport
# 30.6 DatabaseTableExists
检查数据库中是否存在表格。
bool DatabaseTableExists(
int database, // 在DatabaseOpen中接收的数据库句柄
string table // 表格名称
);
1
2
3
4
2
3
4
参数
database
[in] 在DatabaseOpen()中接收的数据库句柄。
table
[in] 表格名称。
返回值
如果成功返回true，否则返回false。要获得错误代码，请使用GetLastError()，可能回应：
ERR_INVALID_PARAMETER (4003) – 没有指定表格名称（空字符串或NULL）；
ERR_WRONG_STRING_PARAMETER (5040) – 将请求转换为UTF-8字符串时出现错误；
ERR_DATABASE_INTERNAL (5120) – 内部数据库错误；
ERR_DATABASE_INVALID_HANDLE (5121) - 无效数据库句柄；
ERR_DATABASE_EXECUTE (5124) - 请求执行错误；
ERR_DATABASE_NO_MORE_DATA (5126) - 不存在表格（没有错误，正常完成）。
# 30.7 DatabaseExecute
执行对指定数据库的请求。
bool DatabaseExecute(
int database, // 在DatabaseOpen中接收的数据库句柄
string sql // SQL请求
);
1
2
3
4
2
3
4
参数
database
[in] 在DatabaseOpen()中接收的数据库句柄。
sql
[in] SQL request.
返回值
如果成功返回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) – 请求执行错误。
例:
//--- symbol statistics
struct Symbol_Stats
{
string name; // symbol name
int trades; // number of trades for the symbol
double gross_profit; // total profit for the symbol
double gross_loss; // total loss for the symbol
double total_commission; // total commission for the symbol
double total_swap; // total swaps for the symbol
double total_profit; // total profit excluding swaps and commissions
double net_profit; // net profit taking into account swaps and commissions
int win_trades; // number of profitable trades
int loss_trades; // number of losing trades
double expected_payoff; // expected payoff for the trade excluding swaps and commissions
double win_percent; // percentage of winning trades
double loss_percent; // percentage of losing trades
double average_profit; // average profit
double average_loss; // average loss
double profit_factor; // profit factor
};
//--- Magic Number statistics
struct Magic_Stats
{
long magic; // EA's Magic Number
int trades; // number of trades for the symbol
double gross_profit; // total profit for the symbol
double gross_loss; // total loss for the symbol
double total_commission; // total commission for the symbol
double total_swap; // total swaps for the symbol
double total_profit; // total profit excluding swaps and commissions
double net_profit; // net profit taking into account swaps and commissions
int win_trades; // number of profitable trades
int loss_trades; // number of losing trades
double expected_payoff; // expected payoff for the trade excluding swaps and commissions
double win_percent; // percentage of winning trades
double loss_percent; // percentage of losing trades
double average_profit; // average profit
double average_loss; // average loss
double profit_factor; // profit factor
};
//--- entry hour statistics
struct Hour_Stats
{
char hour_in; // market entry hour
int trades; // number of trades in this entry hour
double volume; // volume of trades in this entry hour
double gross_profit; // total profit in this entry hour
double gross_loss; // total loss in this entry hour
double net_profit; // net profit taking into account swaps and commissions
int win_trades; // number of profitable trades
int loss_trades; // number of losing trades
double expected_payoff; // expected payoff for the trade excluding swaps and commissions
double win_percent; // percentage of winning trades
double loss_percent; // percentage of losing trades
double average_profit; // average profit
double average_loss; // average loss
double profit_factor; // profit factor
};
int ExtDealsTotal=0;;
//+------------------------------------------------------------------+
//| Script program start function |
//+------------------------------------------------------------------+
void OnStart()
{
//--- create the file name
string filename=IntegerToString(AccountInfoInteger(ACCOUNT_LOGIN))+"_stats.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;
}
PrintFormat("Deals in the trading history: %d ", ExtDealsTotal);
//--- get trading statistics per symbols
int request=DatabasePrepare(db, "SELECT r.*,"
" (case when r.trades != 0 then (r.gross_profit+r.gross_loss)/r.trades else 0 end) as expected_payoff,"
" (case when r.trades != 0 then r.win_trades*100.0/r.trades else 0 end) as win_percent,"
" (case when r.trades != 0 then r.loss_trades*100.0/r.trades else 0 end) as loss_percent,"
" r.gross_profit/r.win_trades as average_profit,"
" r.gross_loss/r.loss_trades as average_loss,"
" (case when r.gross_loss!=0.0 then r.gross_profit/(-r.gross_loss) else 0 end) as profit_factor "
"FROM "
" ("
" SELECT SYMBOL,"
" sum(case when entry =1 then 1 else 0 end) as trades,"
" sum(case when profit > 0 then profit else 0 end) as gross_profit,"
" sum(case when profit < 0 then profit else 0 end) as gross_loss,"
" sum(swap) as total_swap,"
" sum(commission) as total_commission,"
" sum(profit) as total_profit,"
" sum(profit+swap+commission) as net_profit,"
" sum(case when profit > 0 then 1 else 0 end) as win_trades,"
" sum(case when profit < 0 then 1 else 0 end) as loss_trades "
" FROM DEALS "
" WHERE SYMBOL <> '' and SYMBOL is not NULL "
" GROUP BY SYMBOL"
" ) as r");
if(request==INVALID_HANDLE)
{
Print("DB: ", filename, " request failed with code ", GetLastError());
DatabaseClose(db);
return;
}
Symbol_Stats stats[], symbol_stats;
ArrayResize(stats, ExtDealsTotal);
int i=0;
//--- get records from request results
for(; DatabaseReadBind(request, symbol_stats) ; i++)
{
stats[i].name=symbol_stats.name;
stats[i].trades=symbol_stats.trades;
stats[i].gross_profit=symbol_stats.gross_profit;
stats[i].gross_loss=symbol_stats.gross_loss;
stats[i].total_commission=symbol_stats.total_commission;
stats[i].total_swap=symbol_stats.total_swap;
stats[i].total_profit=symbol_stats.total_profit;
stats[i].net_profit=symbol_stats.net_profit;
stats[i].win_trades=symbol_stats.win_trades;
stats[i].loss_trades=symbol_stats.loss_trades;
stats[i].expected_payoff=symbol_stats.expected_payoff;
stats[i].win_percent=symbol_stats.win_percent;
stats[i].loss_percent=symbol_stats.loss_percent;
stats[i].average_profit=symbol_stats.average_profit;
stats[i].average_loss=symbol_stats.average_loss;
stats[i].profit_factor=symbol_stats.profit_factor;
}
ArrayResize(stats, i);
Print("Trade statistics by Symbol");
ArrayPrint(stats);
Print("");
//--- delete the request
DatabaseFinalize(request);
//--- get trading statistics for Expert Advisors by Magic Numbers
request=DatabasePrepare(db, "SELECT r.*,"
" (case when r.trades != 0 then (r.gross_profit+r.gross_loss)/r.trades else 0 end) as expected_payoff,"
" (case when r.trades != 0 then r.win_trades*100.0/r.trades else 0 end) as win_percent,"
" (case when r.trades != 0 then r.loss_trades*100.0/r.trades else 0 end) as loss_percent,"
" r.gross_profit/r.win_trades as average_profit,"
" r.gross_loss/r.loss_trades as average_loss,"
" (case when r.gross_loss!=0.0 then r.gross_profit/(-r.gross_loss) else 0 end) as profit_factor "
"FROM "
" ("
" SELECT MAGIC,"
" sum(case when entry =1 then 1 else 0 end) as trades,"
" sum(case when profit > 0 then profit else 0 end) as gross_profit,"
" sum(case when profit < 0 then profit else 0 end) as gross_loss,"
" sum(swap) as total_swap,"
" sum(commission) as total_commission,"
" sum(profit) as total_profit,"
" sum(profit+swap+commission) as net_profit,"
" sum(case when profit > 0 then 1 else 0 end) as win_trades,"
" sum(case when profit < 0 then 1 else 0 end) as loss_trades "
" FROM DEALS "
" WHERE SYMBOL <> '' and SYMBOL is not NULL "
" GROUP BY MAGIC"
" ) as r");
if(request==INVALID_HANDLE)
{
Print("DB: ", filename, " request failed with code ", GetLastError());
DatabaseClose(db);
return;
}
Magic_Stats EA_stats[], magic_stats;
ArrayResize(EA_stats, ExtDealsTotal);
i=0;
//--- print
for(; DatabaseReadBind(request, magic_stats) ; i++)
{
EA_stats[i].magic=magic_stats.magic;
EA_stats[i].trades=magic_stats.trades;
EA_stats[i].gross_profit=magic_stats.gross_profit;
EA_stats[i].gross_loss=magic_stats.gross_loss;
EA_stats[i].total_commission=magic_stats.total_commission;
EA_stats[i].total_swap=magic_stats.total_swap;
EA_stats[i].total_profit=magic_stats.total_profit;
EA_stats[i].net_profit=magic_stats.net_profit;
EA_stats[i].win_trades=magic_stats.win_trades;
EA_stats[i].loss_trades=magic_stats.loss_trades;
EA_stats[i].expected_payoff=magic_stats.expected_payoff;
EA_stats[i].win_percent=magic_stats.win_percent;
EA_stats[i].loss_percent=magic_stats.loss_percent;
EA_stats[i].average_profit=magic_stats.average_profit;
EA_stats[i].average_loss=magic_stats.average_loss;
EA_stats[i].profit_factor=magic_stats.profit_factor;
}
ArrayResize(EA_stats, i);
Print("Trade statistics by Magic Number");
ArrayPrint(EA_stats);
Print("");
//--- delete the request
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
if(DatabaseTableExists(db, "DEALS"))
//--- populate the TRADES table
if(!DatabaseExecute(db, "INSERT INTO TRADES(TIME_IN,HOUR_IN,TICKET,TYPE,VOLUME,SYMBOL,PRICE_IN,TIME_OUT,PRICE_OUT,COMMISSION,SWAP,PROFIT) "
"SELECT "
" d1.time as time_in,"
" d1.hour as hour_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 table TRADES failed with code ", GetLastError());
return;
}
//--- get trading statistics by market entry hours
request=DatabasePrepare(db, "SELECT r.*,"
" (case when r.trades != 0 then (r.gross_profit+r.gross_loss)/r.trades else 0 end) as expected_payoff,"
" (case when r.trades != 0 then r.win_trades*100.0/r.trades else 0 end) as win_percent,"
" (case when r.trades != 0 then r.loss_trades*100.0/r.trades else 0 end) as loss_percent,"
" r.gross_profit/r.win_trades as average_profit,"
" r.gross_loss/r.loss_trades as average_loss,"
" (case when r.gross_loss!=0.0 then r.gross_profit/(-r.gross_loss) else 0 end) as profit_factor "
"FROM "
" ("
" SELECT HOUR_IN,"
" count() as trades,"
" sum(volume) as volume,"
" sum(case when profit > 0 then profit else 0 end) as gross_profit,"
" sum(case when profit < 0 then profit else 0 end) as gross_loss,"
" sum(profit) as net_profit,"
" sum(case when profit > 0 then 1 else 0 end) as win_trades,"
" sum(case when profit < 0 then 1 else 0 end) as loss_trades "
" FROM TRADES "
" WHERE SYMBOL <> '' and SYMBOL is not NULL "
" GROUP BY HOUR_IN"
" ) as r");
if(request==INVALID_HANDLE)
{
Print("DB: ", filename, " request failed with code ", GetLastError());
DatabaseClose(db);
return;
}
Hour_Stats hours_stats[], h_stats;
ArrayResize(hours_stats, ExtDealsTotal);
i=0;
//--- print
for(; DatabaseReadBind(request, h_stats) ; i++)
{
hours_stats[i].hour_in=h_stats.hour_in;
hours_stats[i].trades=h_stats.trades;
hours_stats[i].volume=h_stats.volume;
hours_stats[i].gross_profit=h_stats.gross_profit;
hours_stats[i].gross_loss=h_stats.gross_loss;
hours_stats[i].net_profit=h_stats.net_profit;
hours_stats[i].win_trades=h_stats.win_trades;
hours_stats[i].loss_trades=h_stats.loss_trades;
hours_stats[i].expected_payoff=h_stats.expected_payoff;
hours_stats[i].win_percent=h_stats.win_percent;
hours_stats[i].loss_percent=h_stats.loss_percent;
hours_stats[i].average_profit=h_stats.average_profit;
hours_stats[i].average_loss=h_stats.average_loss;
hours_stats[i].profit_factor=h_stats.profit_factor;
}
ArrayResize(hours_stats, i);
Print("Trade statistics by entry hour");
ArrayPrint(hours_stats);
Print("");
//--- delete the request
DatabaseFinalize(request);
//--- close database
DatabaseClose(db);
return;
}
/*
Deals in the trading history: 2771
Trade statistics by Symbol
[name] [trades] [gross_profit] [gross_loss] [total_commission] [total_swap] [total_profit] [net_profit] [win_trades] [loss_trades] [expected_payoff] [win_percent] [loss_percent] [average_profit] [average_loss] [profit_factor]
[0] "AUDUSD" 112 503.20000 -568.00000 -8.83000 -24.64000 -64.80000 -98.27000 70 42 -0.57857 62.50000 37.50000 7.18857 -13.52381 0.88592
[1] "EURCHF" 125 607.71000 -956.85000 -11.77000 -45.02000 -349.14000 -405.93000 54 71 -2.79312 43.20000 56.80000 11.25389 -13.47676 0.63512
[2] "EURJPY" 127 1078.49000 -1057.83000 -10.61000 -45.76000 20.66000 -35.71000 64 63 0.16268 50.39370 49.60630 16.85141 -16.79095 1.01953
[3] "EURUSD" 233 1685.60000 -1386.80000 -41.00000 -83.76000 298.80000 174.04000 127 106 1.28240 54.50644 45.49356 13.27244 -13.08302 1.21546
[4] "GBPCHF" 125 1881.37000 -1424.72000 -22.60000 -51.56000 456.65000 382.49000 80 45 3.65320 64.00000 36.00000 23.51712 -31.66044 1.32052
[5] "GBPJPY" 127 1943.43000 -1776.67000 -18.84000 -52.46000 166.76000 95.46000 76 51 1.31307 59.84252 40.15748 25.57145 -34.83667 1.09386
[6] "GBPUSD" 121 1668.50000 -1438.20000 -7.96000 -49.93000 230.30000 172.41000 77 44 1.90331 63.63636 36.36364 21.66883 -32.68636 1.16013
[7] "USDCAD" 99 405.28000 -475.47000 -8.68000 -31.68000 -70.19000 -110.55000 51 48 -0.70899 51.51515 48.48485 7.94667 -9.90563 0.85238
[8] "USDCHF" 206 1588.32000 -1241.83000 -17.98000 -65.92000 346.49000 262.59000 131 75 1.68199 63.59223 36.40777 12.12458 -16.55773 1.27902
[9] "USDJPY" 107 464.73000 -730.64000 -35.12000 -34.24000 -265.91000 -335.27000 50 57 -2.48514 46.72897 53.27103 9.29460 -12.81825 0.63606
Trade statistics by Magic Number
[magic] [trades] [gross_profit] [gross_loss] [total_commission] [total_swap] [total_profit] [net_profit] [win_trades] [loss_trades] [expected_payoff] [win_percent] [loss_percent] [average_profit] [average_loss] [profit_factor]
[0] 100 242 2584.80000 -2110.00000 -33.36000 -93.53000 474.80000 347.91000 143 99 1.96198 59.09091 40.90909 18.07552 -21.31313 1.22502
[1] 200 254 3021.92000 -2834.50000 -29.45000 -98.22000 187.42000 59.75000 140 114 0.73787 55.11811 44.88189 21.58514 -24.86404 1.06612
[2] 300 250 2489.08000 -2381.57000 -34.37000 -96.58000 107.51000 -23.44000 134 116 0.43004 53.60000 46.40000 18.57522 -20.53078 1.04514
[3] 400 224 1272.50000 -1283.00000 -24.43000 -64.80000 -10.50000 -99.73000 131 93 -0.04687 58.48214 41.51786 9.71374 -13.79570 0.99182
[4] 500 198 1141.23000 -1051.91000 -27.66000 -63.36000 89.32000 -1.70000 116 82 0.45111 58.58586 41.41414 9.83819 -12.82817 1.08491
[5] 600 214 1317.10000 -1396.03000 -34.12000 -68.48000 -78.93000 -181.53000 116 98 -0.36883 54.20561 45.79439 11.35431 -14.24520 0.94346
Trade statistics by entry hour
[hour_in] [trades] [volume] [gross_profit] [gross_loss] [net_profit] [win_trades] [loss_trades] [expected_payoff] [win_percent] [loss_percent] [average_profit] [average_loss] [profit_factor]
[ 0] 0 50 5.00000 336.51000 -747.47000 -410.96000 21 29 -8.21920 42.00000 58.00000 16.02429 -25.77483 0.45020
[ 1] 1 20 2.00000 102.56000 -57.20000 45.36000 12 8 2.26800 60.00000 40.00000 8.54667 -7.15000 1.79301
[ 2] 2 6 0.60000 38.55000 -14.60000 23.95000 5 1 3.99167 83.33333 16.66667 7.71000 -14.60000 2.64041
[ 3] 3 38 3.80000 173.84000 -200.15000 -26.31000 22 16 -0.69237 57.89474 42.10526 7.90182 -12.50938 0.86855
[ 4] 4 60 6.00000 361.44000 -389.40000 -27.96000 27 33 -0.46600 45.00000 55.00000 13.38667 -11.80000 0.92820
[ 5] 5 32 3.20000 157.43000 -179.89000 -22.46000 20 12 -0.70187 62.50000 37.50000 7.87150 -14.99083 0.87515
[ 6] 6 18 1.80000 95.59000 -162.33000 -66.74000 11 7 -3.70778 61.11111 38.88889 8.69000 -23.19000 0.58886
[ 7] 7 14 1.40000 38.48000 -134.30000 -95.82000 9 5 -6.84429 64.28571 35.71429 4.27556 -26.86000 0.28652
[ 8] 8 42 4.20000 368.48000 -322.30000 46.18000 24 18 1.09952 57.14286 42.85714 15.35333 -17.90556 1.14328
[ 9] 9 118 11.80000 1121.62000 -875.21000 246.41000 72 46 2.08822 61.01695 38.98305 15.57806 -19.02630 1.28154
[10] 10 206 20.60000 2280.59000 -2021.80000 258.79000 115 91 1.25626 55.82524 44.17476 19.83122 -22.21758 1.12800
[11] 11 138 13.80000 1377.02000 -994.18000 382.84000 84 54 2.77420 60.86957 39.13043 16.39310 -18.41074 1.38508
[12] 12 152 15.20000 1247.56000 -1463.80000 -216.24000 84 68 -1.42263 55.26316 44.73684 14.85190 -21.52647 0.85227
[13] 13 64 6.40000 778.27000 -516.22000 262.05000 36 28 4.09453 56.25000 43.75000 21.61861 -18.43643 1.50763
[14] 14 62 6.20000 536.93000 -427.47000 109.46000 38 24 1.76548 61.29032 38.70968 14.12974 -17.81125 1.25606
[15] 15 50 5.00000 699.92000 -413.00000 286.92000 28 22 5.73840 56.00000 44.00000 24.99714 -18.77273 1.69472
[16] 16 88 8.80000 778.55000 -514.00000 264.55000 51 37 3.00625 57.95455 42.04545 15.26569 -13.89189 1.51469
[17] 17 76 7.60000 533.92000 -1019.46000 -485.54000 44 32 -6.38868 57.89474 42.10526 12.13455 -31.85813 0.52373
[18] 18 52 5.20000 237.17000 -246.78000 -9.61000 24 28 -0.18481 46.15385 53.84615 9.88208 -8.81357 0.96106
[19] 19 52 5.20000 407.67000 -150.36000 257.31000 30 22 4.94827 57.69231 42.30769 13.58900 -6.83455 2.71129
[20] 20 18 1.80000 65.92000 -89.09000 -23.17000 9 9 -1.28722 50.00000 50.00000 7.32444 -9.89889 0.73993
[21] 21 10 1.00000 41.86000 -32.38000 9.48000 7 3 0.94800 70.00000 30.00000 5.98000 -10.79333 1.29277
[22] 22 14 1.40000 45.55000 -83.72000 -38.17000 6 8 -2.72643 42.85714 57.14286 7.59167 -10.46500 0.54408
[23] 23 2 0.20000 1.20000 -1.90000 -0.70000 1 1 -0.35000 50.00000 50.00000 1.20000 -1.90000 0.63158
*/
//+------------------------------------------------------------------+
//| 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,"
"HOUR INT,"
"REASON INT);"))
{
Print("DB: create the DEALS table failed with code ", GetLastError());
return(false);
}
//--- 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);
ExtDealsTotal=HistoryDealsTotal();
//--- add deals to the table
if(!InsertDeals(database))
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
char hour; // deal execution hour
MqlDateTime time_strusture;
//--- 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);
TimeToStruct(time, time_strusture);
hour= (char)time_strusture.hour;
//--- 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,HOUR)"
"VALUES (%d, %d, %d, %d, %d, %d, '%s', %G, %G, %G, %G, %G, %d, %d,%d)",
deal_ticket, order_ticket, position_ticket, time, type, entry, symbol, volume, price, profit, swap, commission, magic, reason, hour);
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 ", __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,"
"HOUR_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);
}
//+------------------------------------------------------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
另见
DatabasePrepare、DatabaseFinalize
# 30.8 DatabasePrepare
创建可使用DatabaseRead()执行的请求句柄。
int DatabasePrepare(
int database, // 在DatabaseOpen中接收的数据库句柄
string sql, // SQL 请求
... // 请求参数
);
1
2
3
4
5
2
3
4
5
参数
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()期间进行设置。
例:
//--- 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);
}
//+------------------------------------------------------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
另见
DatabaseExecute、DatabaseFinalize
# 30.9 DatabaseReset
重置请求，比如调用DatabasePrepare()之后。
int DatabaseReset(
int request //在DatabasePrepare中接收的请求句柄
);
1
2
3
2
3
参数
request
[in] DatabasePrepare()中获得的请求句柄。
返回值
如果成功返回true，否则返回false。要获得错误代码，请使用GetLastError()，可能回应：
ERR_DATABASE_INVALID_HANDLE (5121) - 无效数据库句柄；
以ERR_DATABASE_ERROR(5601)开始的SQLite错误代码。
注意
DatabaseReset()函数用于通过不同的参数值多次执行请求。例如，当使用INSERT命令将数据批量添加到表格中时，应该为每个条目形成一组自定义的字段值。
与DatabasePrepare()不同，DatabaseReset() 调用不会通过SQL命令将字符串编译到新请求中，因此DatabaseReset()的执行速度要比DatabasePrepare()快得多。
如果在执行DatabaseRead()之后应该更改请求参数值，则DatabaseReset()与DatabaseBind()函数和/或DatabaseBindArray()一起使用。这意味着在设置新请求参数值之前（阻止DatabaseBind/DatabaseBindArray调用之前），应该调用DatabaseReset()进行重置。参数化请求本身应该使用DatabasePrepare()来创建。
与atabasePrepare()一样，DatabaseReset()不会发出数据库请求。当调用DatabaseRead()或DatabaseReadBind()时，进行直接请求执行。
如果通过调用DatabaseBind()/DatabaseBindArray()来设置参数值，则DatabaseReset()调用不会导致重置请求中的参数值，即参数保留其值。因此，只能更改单个参数值。调用DatabaseReset()后不需要重新设置所有请求参数。
使用DatabaseFinalize()移除的请求句柄不能传递给DatabaseReset()。这将导致错误。
例如：
//+------------------------------------------------------------------+
//| 脚本程序起始函数 |
//+------------------------------------------------------------------+
void OnStart()
{
//--- 创建或打开数据库
string filename="symbols.sqlite";
int db=DatabaseOpen(filename, DATABASE_OPEN_READWRITE | DATABASE_OPEN_CREATE);
if(db==INVALID_HANDLE)
{
Print("DB: ", filename, " open failed with code ", GetLastError());
return;
}
else
Print("Database: ", filename, " opened successfully");
//--- 如果SYMBOLS表格已存在，请将其删除
if(DatabaseTableExists(db, "SYMBOLS"))
{
//--- 删除表格
if(!DatabaseExecute(db, "DROP TABLE SYMBOLS"))
{
Print("Failed to drop table SYMBOLS with code ", GetLastError());
DatabaseClose(db);
return;
}
}
//--- 创建SYMBOLS表格
if(!DatabaseExecute(db, "CREATE TABLE SYMBOLS("
"NAME TEXT NOT NULL,"
"DESCRIPTION TEXT ,"
"PATH TEXT ,"
"SPREAD INT ,"
"POINT REAL NOT NULL,"
"DIGITS INT NOT NULL,"
"JSON BLOB );"))
{
Print("DB: ", filename, " create table failed with code ", GetLastError());
DatabaseClose(db);
return;
}
//--- 显示SYMBOLS表格中所有字段列表
if(DatabasePrint(db, "PRAGMA TABLE_INFO(SYMBOLS)", 0)<0)
{
PrintFormat("DatabasePrint(\"PRAGMA TABLE_INFO(SYMBOLS)\") failed, error code=%d at line %d", GetLastError(), __LINE__);
DatabaseClose(db);
return;
}
//--- 创建参数化请求，将交易品种添加到SYMBOLS表格中
string sql="INSERT INTO SYMBOLS (NAME,DESCRIPTION,PATH,SPREAD,POINT,DIGITS,JSON)"
" VALUES (?1,?2,?3,?4,?5,?6,?7);"; // 请求参数
int request=DatabasePrepare(db, sql);
if(request==INVALID_HANDLE)
{
PrintFormat("DatabasePrepare() failed with code=%d", GetLastError());
Print("SQL request: ", sql);
DatabaseClose(db);
return;
}
//--- 检查所有交易品种并将其添加到SYMBOLS表格中
int symbols=SymbolsTotal(false);
bool request_error=false;
DatabaseTransactionBegin(db);
for(int i=0; i<symbols; i++)
{
//--- 在添加交易品种之前设置参数值
ResetLastError();
string symbol=SymbolName(i, false);
if(!DatabaseBind(request, 0, symbol))
{
PrintFormat("DatabaseBind() failed at line %d with code=%d", __LINE__, GetLastError());
request_error=true;
break;
}
//--- 如果之前的DatabaseBind()调用成功，则设置下一个参数
if(!DatabaseBind(request, 1, SymbolInfoString(symbol, SYMBOL_DESCRIPTION)))
{
PrintFormat("DatabaseBind() failed at line %d with code=%d", __LINE__, GetLastError());
request_error=true;
break;
}
if(!DatabaseBind(request, 2, SymbolInfoString(symbol, SYMBOL_PATH)))
{
PrintFormat("DatabaseBind() failed at line %d with code=%d", __LINE__, GetLastError());
request_error=true;
break;
}
if(!DatabaseBind(request, 3, SymbolInfoInteger(symbol, SYMBOL_SPREAD)))
{
PrintFormat("DatabaseBind() failed at line %d with code=%d", __LINE__, GetLastError());
request_error=true;
break;
}
if(!DatabaseBind(request, 4, SymbolInfoDouble(symbol, SYMBOL_POINT)))
{
PrintFormat("DatabaseBind() failed at line %d with code=%d", __LINE__, GetLastError());
request_error=true;
break;
}
if(!DatabaseBind(request, 5, SymbolInfoInteger(symbol, SYMBOL_DIGITS)))
{
PrintFormat("DatabaseBind() failed at line %d with code=%d", __LINE__, GetLastError());
request_error=true;
break;
}
if(!DatabaseBind(request, 6, GetSymBolAsJson(symbol)))
{
PrintFormat("DatabaseBind() failed at line %d with code=%d", __LINE__, GetLastError());
request_error=true;
break;
}
//--- 执行插入条目的请求并检查错误
if(!DatabaseRead(request)&&(GetLastError()!=ERR_DATABASE_NO_MORE_DATA))
{
PrintFormat("DatabaseRead() failed with code=%d", GetLastError());
DatabaseFinalize(request);
request_error=true;
break;
}
else
PrintFormat("%d: added %s", i+1, symbol);
//--- 在下一次参数更新之前重置请求
if(!DatabaseReset(request))
{
PrintFormat("DatabaseReset() failed with code=%d", GetLastError());
DatabaseFinalize(request);
request_error=true;
break;
}
} //--- 完成所有交易品种的检查
//--- 交易事务状态
if(request_error)
{
PrintFormat("Table SYMBOLS: failed to add %d symbols", symbols);
DatabaseTransactionRollback(db);
DatabaseClose(db);
return;
}
else
{
DatabaseTransactionCommit(db);
PrintFormat("Table SYMBOLS: added %d symbols",symbols);
}
//--- 将SYMBOLS表格保存为CSV文件。
string csv_filename="symbols.csv";
if(DatabaseExport(db, "SELECT * FROM SYMBOLS", csv_filename,
DATABASE_EXPORT_HEADER|DATABASE_EXPORT_INDEX|DATABASE_EXPORT_QUOTED_STRINGS, ";"))
Print("Database: table SYMBOLS saved in ", csv_filename);
else
Print("Database: DatabaseExport(\"SELECT * FROM SYMBOLS\") failed with code", GetLastError());
//--- 关闭数据库文件并告知
DatabaseClose(db);
PrintFormat("Database: %s created and closed", filename);
}
//+------------------------------------------------------------------+
//| 交易品种规格返回为JSON |
//+------------------------------------------------------------------+
string GetSymBolAsJson(string symbol)
{
//--- 缩进
string indent1=Indent(1);
string indent2=Indent(2);
string indent3=Indent(3);
//---
int digits=(int)SymbolInfoInteger(symbol, SYMBOL_DIGITS);
string json="{"+
"\n"+indent1+"\"ConfigSymbols\":["+
"\n"+indent2+"{"+
"\n"+indent3+"\"Symbol\":\""+symbol+"\","+
"\n"+indent3+"\"Path\":\""+SymbolInfoString(symbol, SYMBOL_PATH)+"\","+
"\n"+indent3+"\"CurrencyBase\":\""+SymbolInfoString(symbol, SYMBOL_CURRENCY_BASE)+"\","+
"\n"+indent3+"\"CurrencyProfit\":\""+SymbolInfoString(symbol, SYMBOL_CURRENCY_PROFIT)+"\","+
"\n"+indent3+"\"CurrencyMargin\":\""+SymbolInfoString(symbol, SYMBOL_CURRENCY_MARGIN)+"\","+
"\n"+indent3+"\"ColorBackground\":\""+ColorToString((color)SymbolInfoInteger(symbol, SYMBOL_BACKGROUND_COLOR))+"\","+
"\n"+indent3+"\"Digits\":\""+IntegerToString(SymbolInfoInteger(symbol, SYMBOL_DIGITS))+"\","+
"\n"+indent3+"\"Point\":\""+DoubleToString(SymbolInfoDouble(symbol, SYMBOL_POINT), digits)+"\","+
"\n"+indent3+"\"TickBookDepth\":\""+IntegerToString(SymbolInfoInteger(symbol, SYMBOL_TICKS_BOOKDEPTH))+"\","+
"\n"+indent3+"\"ChartMode\":\""+IntegerToString(SymbolInfoInteger(symbol, SYMBOL_CHART_MODE))+"\","+
"\n"+indent3+"\"TradeMode\":\""+IntegerToString(SymbolInfoInteger(symbol, SYMBOL_TRADE_EXEMODE))+"\","+
"\n"+indent3+"\"TradeCalcMode\":\""+IntegerToString(SymbolInfoInteger(symbol, SYMBOL_TRADE_CALC_MODE))+"\","+
"\n"+indent3+"\"OrderMode\":\""+IntegerToString(SymbolInfoInteger(symbol, SYMBOL_ORDER_MODE))+"\","+
"\n"+indent3+"\"CalculationMode\":\""+IntegerToString(SymbolInfoInteger(symbol, SYMBOL_TRADE_CALC_MODE))+"\","+
"\n"+indent3+"\"ExecutionMode\":\""+IntegerToString(SymbolInfoInteger(symbol, SYMBOL_TRADE_EXEMODE))+"\","+
"\n"+indent3+"\"ExpirationMode\":\""+IntegerToString(SymbolInfoInteger(symbol, SYMBOL_EXPIRATION_MODE))+"\","+
"\n"+indent3+"\"FillFlags\":\""+IntegerToString(SymbolInfoInteger(symbol, SYMBOL_FILLING_MODE))+"\","+
"\n"+indent3+"\"ExpirFlags\":\""+IntegerToString(SymbolInfoInteger(symbol, SYMBOL_EXPIRATION_MODE))+"\","+
"\n"+indent3+"\"Spread\":\""+IntegerToString(SymbolInfoInteger(symbol, SYMBOL_SPREAD))+"\","+
"\n"+indent3+"\"TickValue\":\""+StringFormat("%G", (SymbolInfoDouble(symbol, SYMBOL_TRADE_TICK_VALUE)))+"\","+
"\n"+indent3+"\"TickSize\":\""+StringFormat("%G", (SymbolInfoDouble(symbol, SYMBOL_TRADE_TICK_SIZE)))+"\","+
"\n"+indent3+"\"ContractSize\":\""+StringFormat("%G",(SymbolInfoDouble(symbol, SYMBOL_TRADE_CONTRACT_SIZE)))+"\","+
"\n"+indent3+"\"StopsLevel\":\""+IntegerToString(SymbolInfoInteger(symbol, SYMBOL_TRADE_STOPS_LEVEL))+"\","+
"\n"+indent3+"\"VolumeMin\":\""+StringFormat("%G",(SymbolInfoDouble(symbol, SYMBOL_VOLUME_MIN)))+"\","+
"\n"+indent3+"\"VolumeMax\":\""+StringFormat("%G",(SymbolInfoDouble(symbol, SYMBOL_VOLUME_MAX)))+"\","+
"\n"+indent3+"\"VolumeStep\":\""+StringFormat("%G",(SymbolInfoDouble(symbol, SYMBOL_VOLUME_STEP)))+"\","+
"\n"+indent3+"\"VolumeLimit\":\""+StringFormat("%G",(SymbolInfoDouble(symbol, SYMBOL_VOLUME_STEP)))+"\","+
"\n"+indent3+"\"SwapMode\":\""+IntegerToString(SymbolInfoInteger(symbol, SYMBOL_SWAP_MODE))+"\","+
"\n"+indent3+"\"SwapLong\":\""+StringFormat("%G",(SymbolInfoDouble(symbol, SYMBOL_SWAP_LONG)))+"\","+
"\n"+indent3+"\"SwapShort\":\""+StringFormat("%G",(SymbolInfoDouble(symbol, SYMBOL_SWAP_SHORT)))+"\","+
"\n"+indent3+"\"Swap3Day\":\""+IntegerToString(SymbolInfoInteger(symbol, SYMBOL_SWAP_ROLLOVER3DAYS))+"\""+
"\n"+indent2+"}"+
"\n"+indent1+"]"+
"\n}";
return(json);
}
//+------------------------------------------------------------------+
//| 形成空格缩进 |
//+------------------------------------------------------------------+
string Indent(const int number, const int characters=3)
{
int length=number*characters;
string indent=NULL;
StringInit(indent, length, ' ');
return indent;
}
/*
结果：
Database: symbols.sqlite opened successfully
#| cid name type notnull dflt_value pk
-+-------------------------------------------
1| 0 NAME TEXT 1 0
2| 1 DESCRIPTION TEXT 0 0
3| 2 PATH TEXT 0 0
4| 3 SPREAD INT 0 0
5| 4 POINT REAL 1 0
6| 5 DIGITS INT 1 0
7| 6 JSON BLOB 0 0
1: added EURUSD
2: added GBPUSD
3: added USDCHF
...
82: added USDCOP
83: added USDARS
84: added USDCLP
Table SYMBOLS: added 84 symbols
Database: table SYMBOLS saved in symbols.csv
Database: symbols.sqlite created and closed
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
另见
DatabasePrepare、DatabaseBind、 DatabaseBindArray、DatabaseFinalize
# 30.10 DatabaseBind
在请求中设置一个参数值。
bool DatabaseBind(
int request, // DatabasePrepare中创建的请求句柄
int index, // 请求中的参数索引
T value // 简单类型参数的值
);
1
2
3
4
5
2
3
4
5
参数
request
[in] DatabasePrepare()中创建的请求句柄。
index
[in] 应该为请求中的参数索引设置一个值。编号从0开始。
值
[in] 要设置的值。扩展类型：bool、char、uchar、short、ushart、int、uint、color、datetime、long、 ulong、float、double、string。
返回值
如果成功返回true，否则返回false。要获得错误代码，请使用GetLastError()，可能回应：
ERR_INVALID_PARAMETER (4003) – 不支持类型；
ERR_DATABASE_INVALID_HANDLE (5121) - 无效数据库句柄；
ERR_DATABASE_NOT_READY (5128) -
目前无法使用该函数发出请求。请求正在执行或已经完成。应该调用DatabaseReset()。
注意
该函数在SQL请求包含"?"或"?N"参数化值的情况下被使用，这里N表示参数索引（从1开始）。同时，DatabaseBind()中的参数索引从0开始。
例如：
INSERT INTO table VALUES (?,?,?)
SELECT * FROM table WHERE id=?
1
2
2
该函数可以在DatabasePrepare()中创建参数化请求或使用DatabaseReset()重置请求之后立即调用。
将该函数与DatabaseReset()一起使用，可以根据需要使用不同的参数值来多次执行请求。
该函数旨在使用简单的类型参数。如果应对数组检查参数，请使用DatabaseBindArray()函数。
例如：
//+------------------------------------------------------------------+
//| 脚本程序起始函数 |
//+------------------------------------------------------------------+
void OnStart()
{
MqlTick ticks[];
//--- 记住收到报价的开始时间
uint start=GetTickCount();
//--- 请求每日报价历史
ulong to=TimeCurrent()*1000;
ulong from=to-PeriodSeconds(PERIOD_D1)*1000;
if(CopyTicksRange(_Symbol, ticks, COPY_TICKS_ALL, from, to)==-1)
{
PrintFormat("%s: CopyTicksRange(%s - %s) failed, error=%d",
_Symbol, TimeToString(datetime(from/1000)), TimeToString(datetime(to/1000)), _LastError);
return;
}
else
{
//--- 接收多少报价，以及接收报价所花费的时间
PrintFormat("%s: CopyTicksRange received %d ticks in %d ms (from %s to %s)",
_Symbol, ArraySize(ticks), GetTickCount()-start,
TimeToString(datetime(from/1000)), TimeToString(datetime(to/1000)));
}
//--- 设置存储数据库的文件名称
string filename=_Symbol+" "+TimeToString(datetime(from/1000))+" - "+TimeToString(datetime(to/1000))+".sqlite";
StringReplace(filename, ":", "."); // ":" character is not allowed in file names
//--- 在常规程序端文件夹中打开/创建数据库
int db=DatabaseOpen(filename, DATABASE_OPEN_READWRITE | DATABASE_OPEN_CREATE | DATABASE_OPEN_COMMON);
if(db==INVALID_HANDLE)
{
Print("Database: ", filename, " open failed with code ", GetLastError());
return;
}
else
Print("Database: ", filename, " opened successfully");
//--- 创建TICKS表格
if(!DatabaseExecute(db, "CREATE TABLE TICKS("
"SYMBOL CHAR(10),"
"TIME INT NOT NULL,"
"BID REAL,"
"ASK REAL,"
"LAST REAL,"
"VOLUME INT,"
"TIME_MSC INT,"
"VOLUME_REAL REAL);"))
{
Print("DB: ", filename, " create table TICKS failed with code ", GetLastError());
DatabaseClose(db);
return;
}
//--- 显示TICKS表格中所有字段列表
if(DatabasePrint(db, "PRAGMA TABLE_INFO(TICKS)", 0)<0)
{
PrintFormat("DatabasePrint(\"PRAGMA TABLE_INFO(TICKS)\") failed, error code=%d at line %d", GetLastError(), __LINE__);
DatabaseClose(db);
return;
}
//--- 创建参数化请求，将报价添加到TICKS表格中
string sql="INSERT INTO TICKS (SYMBOL,TIME,BID,ASK,LAST,VOLUME,TIME_MSC,VOLUME_REAL)"
" VALUES (?1,?2,?3,?4,?5,?6,?7,?8)"; // request parameters
int request=DatabasePrepare(db, sql);
if(request==INVALID_HANDLE)
{
PrintFormat("DatabasePrepare() failed with code=%d", GetLastError());
Print("SQL request: ", sql);
DatabaseClose(db);
return;
}
//--- 设置第一个请求参数的值
DatabaseBind(request, 0, _Symbol);
//--- 记住将报价添加到TICKS表格的开始时间
start=GetTickCount();
DatabaseTransactionBegin(db);
int total=ArraySize(ticks);
bool request_error=false;
for(int i=0; i<total; i++)
{
//--- 在添加条目之前设置其余参数的值
ResetLastError();
if(!DatabaseBind(request, 1, ticks[i].time))
{
PrintFormat("DatabaseBind() failed with code=%d", GetLastError());
PrintFormat("Tick #%d line=%d", i+1, __LINE__);
request_error=true;
break;
}
//--- 如果之前的DatabaseBind()调用成功，则设置下一个参数
if(!request_error && !DatabaseBind(request, 2, ticks[i].bid))
{
PrintFormat("DatabaseBind() failed with code=%d", GetLastError());
PrintFormat("Tick #%d line=%d", i+1, __LINE__);
request_error=true;
break;
}
if(!request_error && !DatabaseBind(request, 3, ticks[i].ask))
{
PrintFormat("DatabaseBind() failed with code=%d", GetLastError());
PrintFormat("Tick #%d line=%d", i+1, __LINE__);
request_error=true;
break;
}
if(!request_error && !DatabaseBind(request, 4, ticks[i].last))
{
PrintFormat("DatabaseBind() failed with code=%d", GetLastError());
PrintFormat("Tick #%d line=%d", i+1, __LINE__);
request_error=true;
break;
}
if(!request_error && !DatabaseBind(request, 5, ticks[i].volume))
{
PrintFormat("DatabaseBind() failed with code=%d", GetLastError());
PrintFormat("Tick #%d line=%d", i+1, __LINE__);
request_error=true;
break;
}
if(!request_error && !DatabaseBind(request, 6, ticks[i].time_msc))
{
PrintFormat("DatabaseBind() failed with code=%d", GetLastError());
PrintFormat("Tick #%d line=%d", i+1, __LINE__);
request_error=true;
break;
}
if(!request_error && !DatabaseBind(request, 7, ticks[i].volume_real))
{
PrintFormat("DatabaseBind() failed with code=%d", GetLastError());
PrintFormat("Tick #%d line=%d", i+1, __LINE__);
request_error=true;
break;
}
//--- 执行插入条目的请求并检查错误
if(!request_error && !DatabaseRead(request) && (GetLastError()!=ERR_DATABASE_NO_MORE_DATA))
{
PrintFormat("DatabaseRead() failed with code=%d", GetLastError());
DatabaseFinalize(request);
request_error=true;
break;
}
//--- 在下一次参数更新之前重置请求
if(!request_error && !DatabaseReset(request))
{
PrintFormat("DatabaseReset() failed with code=%d", GetLastError());
DatabaseFinalize(request);
request_error=true;
break;
}
} //--- 完成所有报价的检查
//--- 交易事务状态
if(request_error)
{
PrintFormat("Table TICKS: failed to add %d ticks ", ArraySize(ticks));
DatabaseTransactionRollback(db);
DatabaseClose(db);
return;
}
else
{
DatabaseTransactionCommit(db);
PrintFormat("Table TICKS: added %d ticks in %d ms",
ArraySize(ticks), GetTickCount()-start);
}
//--- 关闭数据库文件并告知
DatabaseClose(db);
PrintFormat("Database: %s created and closed", filename);
}
/*
结果：
EURUSD: CopyTicksRange received 268061 ticks in 47 ms (from 2020.03.18 12:40 to 2020.03.19 12:40)
Database: EURUSD 2020.03.18 12.40 - 2020.03.19 12.40.sqlite opened successfully
#| cid name type notnull dflt_value pk
-+-----------------------------------------------
1| 0 SYMBOL CHAR(10) 0 0
2| 1 TIME INT 1 0
3| 2 BID REAL 0 0
4| 3 ASK REAL 0 0
5| 4 LAST REAL 0 0
6| 5 VOLUME INT 0 0
7| 6 TIME_MSC INT 0 0
8| 7 VOLUME_REAL REAL 0 0
Table TICKS: added 268061 ticks in 797 ms
Database: EURUSD 2020.03.18 12.40 - 2020.03.19 12.40.sqlite created and closed
OnCalculateCorrelation=0.87 2020.03.19 13:00: EURUSD vs GBPUSD PERIOD_M30
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
另见
DatabasePrepare、DatabaseReset、 DatabaseRead、DatabaseBindArray
# 30.11 DatabaseBindArray
将数组设置为参数值。
bool DatabaseBind(
int request, // DatabasePrepare中创建的请求句柄
int index, // 请求中的参数索引
T& array[] // 数组设为参数值
);
1
2
3
4
5
2
3
4
5
参数
request
[in] DatabasePrepare()中创建的请求句柄。
index
[in] 应该为请求中的参数索引设置一个值。编号从0开始。
array[]
[in] 要设置为请求参数值的数组。
返回值
如果成功返回true，否则返回false。要获得错误代码，请使用GetLastError()，可能回应：
ERR_INVALID_PARAMETER (4003) – 不支持类型；
ERR_ARRAY_BAD_SIZE (4011) - 数组大小（以字节为单位）超过INT_MAX；
ERR_DATABASE_INVALID_HANDLE (5121) - 无效数据库句柄；
ERR_DATABASE_NOT_READY (5128) -
目前无法使用该函数发出请求（请求正被执行或已经完成，应该调用DatabaseReset）。
注意
该函数在SQL请求包含"?"或"?N"参数化值的情况下被使用，这里N表示参数索引（从1开始）。同时，DatabaseBindArray()中的参数索引从0开始。
例如：
INSERT INTO table VALUES (?,?,?)
该函数可以在DatabasePrepare()中创建参数化请求或使用DatabaseReset()重置请求之后立即调用。
将该函数与DatabaseReset()一起使用，可以根据需要使用不同的参数值来多次执行请求。
例如：
//+------------------------------------------------------------------+
//| 脚本程序起始函数 |
//+------------------------------------------------------------------+
void OnStart()
{
//--- 打开用于选择带有DAT扩展名的文件的对话框
string selected_files[];
if(!FileSelectDialog("Select files to download", NULL,
"Data files (*.dat)|*.dat|All files (*.*)|*.*",
FSD_ALLOW_MULTISELECT, selected_files, "tester.dat")>0)
{
Print("Files not selected. Exit");
return;
}
//--- 获取文件大小
ulong filesize[];
int filehandle[];
int files=ArraySize(selected_files);
ArrayResize(filesize, files);
ZeroMemory(filesize);
ArrayResize(filehandle, files);
double total_size=0;
for(int i=0; i<files; i++)
{
filehandle[i]=FileOpen(selected_files[i], FILE_READ|FILE_BIN);
if(filehandle[i]!=INVALID_HANDLE)
{
filesize[i]=FileSize(filehandle[i]);
//PrintFormat("%d, %s handle=%d %d bytes", i, selected_files[i], filehandle[i], filesize[i]);
total_size+=(double)filesize[i];
}
}
//--- 检查常规文件大小
if(total_size==0)
{
PrintFormat("Total files size is 0. Exit");
return;
}
//--- 在常规程序端文件夹中创建或打开数据库
string filename="dat_files.sqlite";
int db=DatabaseOpen(filename, DATABASE_OPEN_READWRITE | DATABASE_OPEN_CREATE);
if(db==INVALID_HANDLE)
{
Print("DB: ", filename, " open failed with code ", GetLastError());
return;
}
else
Print("Database: ", filename, " opened successfully");
//--- 如果FILES表格已存在，请将其删除
if(DatabaseTableExists(db, "FILES"))
{
//--- 删除表格
if(!DatabaseExecute(db, "DROP TABLE FILES"))
{
Print("Failed to drop table FILES with code ", GetLastError());
DatabaseClose(db);
return;
}
}
//--- 创建FILES表格
if(!DatabaseExecute(db, "CREATE TABLE FILES("
"NAME TEXT NOT NULL,"
"SIZE INT NOT NULL,"
"PERCENT_SIZE REAL NOT NULL,"
"DATA BLOB NOT NULL);"))
{
Print("DB: failed to create table FILES with code ", GetLastError());
DatabaseClose(db);
return;
}
//--- 显示FILES表格中所有字段列表
if(DatabasePrint(db, "PRAGMA TABLE_INFO(FILES)", 0)<0)
{
PrintFormat("DatabasePrint(\"PRAGMA TABLE_INFO(FILES)\") failed, error code=%d at line %d", GetLastError(), __LINE__);
DatabaseClose(db);
return;
}
//--- 创建参数化请求，将文件添加到FILES表格中
string sql="INSERT INTO FILES (NAME,SIZE,PERCENT_SIZE,DATA)"
" VALUES (?1,?2,?3,?4);"; // 请求参数
int request=DatabasePrepare(db, sql);
if(request==INVALID_HANDLE)
{
PrintFormat("DatabasePrepare() failed with code=%d", GetLastError());
Print("SQL request: ", sql);
DatabaseClose(db);
return;
}
//--- 检查所有文件并将其添加到FILES表格中
bool request_error=false;
DatabaseTransactionBegin(db);
int count=0;
uint size;
for(int i=0; i<files; i++)
{
if(filehandle[i]!=INVALID_HANDLE)
{
char data[];
size=FileReadArray(filehandle[i], data);
if(size==0)
{
PrintFormat("FileReadArray(%s) failed with code %d", selected_files[i], GetLastError());
continue;
}
count++;
//--- 在将文件添加到表格之前设置参数值
if(!DatabaseBind(request, 0, selected_files[i]))
{
PrintFormat("DatabaseBind() failed at line %d with code=%d", __LINE__, GetLastError());
request_error=true;
break;
}
if(!DatabaseBind(request, 1, size))
{
PrintFormat("DatabaseBind() failed at line %d with code=%d", __LINE__, GetLastError());
request_error=true;
break;
}
if(!DatabaseBind(request, 2, double(size)*100./total_size))
{
PrintFormat("DatabaseBind() failed at line %d with code=%d", __LINE__, GetLastError());
request_error=true;
break;
}
if(!DatabaseBindArray(request, 3, data))
{
PrintFormat("DatabaseBind() failed at line %d with code=%d", __LINE__, GetLastError());
request_error=true;
break;
}
//--- 执行插入条目的请求并检查错误
if(!DatabaseRead(request)&&(GetLastError()!=ERR_DATABASE_NO_MORE_DATA))
{
PrintFormat("DatabaseRead() failed with code=%d", GetLastError());
DatabaseFinalize(request);
request_error=true;
break;
}
else
PrintFormat("%d. %s: %d bytes", count, selected_files[i],size);
//--- 在下一次参数更新之前重置请求
if(!DatabaseReset(request))
{
PrintFormat("DatabaseReset() failed with code=%d", GetLastError());
DatabaseFinalize(request);
request_error=true;
break;
}
}
}
//--- 交易事务状态
if(request_error)
{
PrintFormat("Table FILES: failed to add %d files", count);
DatabaseTransactionRollback(db);
DatabaseClose(db);
return;
}
else
{
DatabaseTransactionCommit(db);
PrintFormat("Table FILES: added %d files", count);
}
//--- 关闭数据库文件并告知
DatabaseClose(db);
PrintFormat("Database: %s created and closed", filename);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
另见
DatabasePrepare、DatabaseReset、 DatabaseRead、DatabaseBind
# 30.12 DatabaseRead
作为请求结果，移到下一个条目。
bool DatabaseRead(
int request //在DatabasePrepare中接收的请求句柄
);
1
2
3
2
3
参数
request
[in] 在DatabasePrepare()中接收的请求句柄。
返回值
如果成功返回true，否则返回false。要获得错误代码，请使用GetLastError()，可能回应：
ERR_INVALID_PARAMETER (4003) – 没有指定表格名称（空字符串或NULL）；
ERR_WRONG_STRING_PARAMETER (5040) – 将请求转换为UTF-8字符串时出现错误；
ERR_DATABASE_INTERNAL (5120) – 内部数据库错误；
ERR_DATABASE_INVALID_HANDLE (5121) – 内部数据库句柄；
ERR_DATABASE_EXECUTE (5124) – 请求执行错误；
ERR_DATABASE_NO_MORE_DATA (5126) – 不存在表格（没有错误，正常完成）。
另见
DatabasePrepare, DatabaseReadBind
# 30.13 DatabaseReadBind
移动到下一条记录，并从中将数据读入结构。
bool DatabaseReadBind(
int request, //DatabasePrepare中创建的请求句柄
void& struct_object //对读取记录结构的引用
);
1
2
3
4
2
3
4
参数
request
[in] DatabasePrepare()中创建的请求句柄。
struct_object
[out] 要读入当前记录中数据的结构的引用。该结构应该只有数值类型和/或字符串（不允许数组）作为成员，并且不能是衍生体。
返回值
如果成功返回true，否则返回false。要获得错误代码，请使用GetLastError()，可能回应：
ERR_INVALID_PARAMETER (4003) – 没有指定表格名称（空字符串或NULL）；
ERR_WRONG_STRING_PARAMETER (5040) – 将请求转换为UTF-8字符串时出现错误；
ERR_DATABASE_INTERNAL (5120) – 内部数据库错误；
ERR_DATABASE_INVALID_HANDLE (5121) – 内部数据库句柄；
ERR_DATABASE_EXECUTE (5124) – 请求执行错误；
ERR_DATABASE_NO_MORE_DATA (5126) – 不存在表格（没有错误，正常完成）。
注意
struct_object结构中的字段数不应超过DatabaseColumnsCount()。如果struct_object结构中的字段数小于记录中的字段数，则执行部分读取。使用相应的DatabaseColumnText()、 DatabaseColumnInteger()等函数可以明确获取剩余的数据。
示例：
struct Person
{
int id;
string name;
int age;
string address;
double salary;
};
//+------------------------------------------------------------------+
//| Script program start function |
//+------------------------------------------------------------------+
void OnStart()
{
int db;
string filename="company.sqlite";
//--- open
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;
}
//--- if the table COMPANY exists then drop the table
if(DatabaseTableExists(db, "COMPANY"))
{
//--- delete the table
if(!DatabaseExecute(db, "DROP TABLE COMPANY"))
{
Print("Failed to drop table COMPANY with code ", GetLastError());
DatabaseClose(db);
return;
}
}
//--- create table
if(!DatabaseExecute(db, "CREATE TABLE COMPANY("
"ID INT PRIMARY KEY NOT NULL,"
"NAME TEXT NOT NULL,"
"AGE INT NOT NULL,"
"ADDRESS CHAR(50),"
"SALARY REAL );"))
{
Print("DB: ", filename, " create table failed with code ", GetLastError());
DatabaseClose(db);
return;
}
//--- insert data
if(!DatabaseExecute(db, "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 25000.00 ); "
"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); "
"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );"
"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );"))
{
Print("DB: ", filename, " insert failed with code ", GetLastError());
DatabaseClose(db);
return;
}
//--- prepare the request
int request=DatabasePrepare(db, "SELECT * FROM COMPANY WHERE SALARY>15000");
if(request==INVALID_HANDLE)
{
Print("DB: ", filename, " request failed with code ", GetLastError());
DatabaseClose(db);
return;
}
//--- print records
Person person;
Print("Persons with salary > 15000:");
for(int i=0; DatabaseReadBind(request, person); i++)
Print(i, ": ", person.id, " ", person.name, " ", person.age, " ", person.address, " ", person.salary);
//--- delete request after use
DatabaseFinalize(request);
Print("Some statistics:");
//--- prepare new request about total salary
request=DatabasePrepare(db, "SELECT SUM(SALARY) FROM COMPANY");
if(request==INVALID_HANDLE)
{
Print("DB: ", filename, " request failed with code ", GetLastError());
DatabaseClose(db);
return;
}
while(DatabaseRead(request))
{
double total_salary;
DatabaseColumnDouble(request, 0, total_salary);
Print("Total salary=", total_salary);
}
//--- delete request after use
DatabaseFinalize(request);
//--- prepare new request about average salary
request=DatabasePrepare(db, "SELECT AVG(SALARY) FROM COMPANY");
if(request==INVALID_HANDLE)
{
Print("DB: ", filename, " request failed with code ", GetLastError());
ResetLastError();
DatabaseClose(db);
return;
}
while(DatabaseRead(request))
{
double aver_salary;
DatabaseColumnDouble(request, 0, aver_salary);
Print("Average salary=", aver_salary);
}
//--- delete request after use
DatabaseFinalize(request);
//--- close database
DatabaseClose(db);
}
//+-------------------------------------------------------------------
/*
Output:
Persons with salary > 15000:
0: 1 Paul 32 California 25000.0
1: 3 Teddy 23 Norway 20000.0
2: 4 Mark 25 Rich-Mond 65000.0
Some statistics:
Total salary=125000.0
Average salary=31250.0
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
另见
DatabasePrepare，DatabaseRead
# 30.14 DatabaseFinalize
移除在DatabasePrepare()中创建的请求参数。
void DatabaseFinalize(
int request //在DatabasePrepare中接收的请求句柄
);
1
2
3
2
3
参数
database
[in] 在DatabasePrepare()中接收的请求句柄。
返回值
无。
注意
如果句柄无效，函数设置ERR_DATABASE_INVALID_HANDLE错误。您可以使用GetLastError()检查错误。
# 30.15 DatabaseTransactionBegin
开始事务执行。
bool DatabaseTransactionBegin(
int database // 在DatabaseOpen中接收的数据库句柄
);
1
2
3
2
3
参数
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()结束。
示例:
//+------------------------------------------------------------------+
//| 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(filename, DATABASE_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_date, to_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(db, fast_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(db, fast_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 history: 2737
Transations WITH DatabaseTransactionBegin/DatabaseTransactionCommit: time=48.5 milliseconds
Transations WITHOUT DatabaseTransactionBegin/DatabaseTransactionCommit: time=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 database, string 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 database, bool 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=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 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 #%dwith 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)
{
//--- 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);
}
//+------------------------------------------------------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
另见
DatabaseExecute、 DatabasePrepare、 DatabaseTransactionCommit、 DatabaseTransactionRollback
# 30.16 DatabaseTransactionCommit
完成事务执行。
bool DatabaseTransactionCommit(
int database // 在DatabaseOpen中接收的数据库句柄
);
1
2
3
2
3
参数
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) – 请求执行错误。
注意
DatabaseTransactionCommit()函数完成调用DatabaseBeginTransaction()函数后执行的所有事务。任何事务都应该以调用DatabaseTransactionBegin()开始，以调用DatabaseTransactionCommit()结束。
# 30.17 DatabaseTransactionRollback
回滚事务。
bool DatabaseTransactionRollback(
int database // 在DatabaseOpen中接收的数据库句柄
);
1
2
3
2
3
参数
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) – 请求执行错误；
注意
DatabaseTransactionRollback()调用取消调用DatabaseTransactionBegin()函数后执行的所有事务。在执行事务发生错误时，需要DatabaseTransactionRollback()函数回滚数据库中的更改。
另见
DatabaseExecute、 DatabasePrepare、 DatabaseTransactionBegin、 DatabaseTransactionCommit
# 30.18 DatabaseColumnsCount
获取请求中的字段数。
int DatabaseColumnsCount(
int request //在DatabasePrepare中接收的请求句柄
);
1
2
3
2
3
参数
request
[in] 在DatabasePrepare()中接收的请求句柄。
返回值
字段数量，错误情况下为-1。要获得错误代码，请使用GetLastError()，可能回应：
ERR_DATABASE_INVALID_HANDLE (5121) - 无效请求句柄。 注意
不需要调用DatabaseRead()函数来获取在DatabasePrepare()中创建的请求的字段数。对于其余的DatabaseColumnXXX()函数，应该初步调用DatabaseRead()。
另见
DatabasePrepare、DatabaseFinalize、 DatabaseClose
# 30.19 DatabaseColumnName
按索引获取字段名。
bool DatabaseColumnName(
int request, // 在DatabasePrepare中接收的请求句柄
int column, // 请求中的字段索引
string& name //用于接收字段名的变量的引用
);
1
2
3
4
5
2
3
4
5
参数
request
[in] 在DatabasePrepare()中接收的请求句柄。
column
[in] 请求中的字段索引。字段编号从零开始，不能超过 DatabaseColumnsCount() - 1。
name
[out] 用于编写字段名的变量。
返回值
如果成功返回true，否则返回false。要获得错误代码，请使用GetLastError()，可能回应：
ERR_DATABASE_INVALID_HANDLE (5121) – 无效请求句柄； ERR_DATABASE_NO_MORE_DATA (5126) – 'column'索引超过DatabaseColumnsCount() -1。 注意
只有在为'request（请求）'初步发出至少一个DatabaseRead()调用时，才能获取该值。
另见
DatabasePrepare、DatabaseColumnsCount、 DatabaseColumnType
# 30.20 DatabaseColumnType
按索引获取字段类型。
ENUM_DATABASE_FIELD_TYPE DatabaseColumnType(
int request, // 在DatabasePrepare中接收的请求句柄
int column //请求中的字段索引
);
1
2
3
4
2
3
4
参数
request
[in] 在DatabasePrepare()中接收的请求句柄。
column
[in] 请求中的字段索引。字段编号从零开始，不能超过 DatabaseColumnsCount() - 1。
返回值
从ENUM_DATABASE_FIELD_TYPE枚举返回字段类型。要获得错误代码，请使用GetLastError()，可能回应：
ERR_DATABASE_INVALID_HANDLE (5121) – 无效请求句柄；
ERR_DATABASE_NO_MORE_DATA (5126) – 'column'索引超过DatabaseColumnsCount() -1.
注意
只有在为'request（请求）'初步发出至少一个DatabaseRead()调用时，才能获取该值。
ENUM_DATABASE_FIELD_TYPE
| ID | 描述 |
|---|---|
| DATABASE_FIELD_TYPE_INVALID | 获取类型错误，可使用int GetLastError()获取错误代码 |
| DATABASE_FIELD_TYPE_INTEGER | 整型 |
| DATABASE_FIELD_TYPE_FLOAT | 真实型 |
| DATABASE_FIELD_TYPE_TEXT | 字符串类型 |
| DATABASE_FIELD_TYPE_BLOB | 二进制类型 |
| DATABASE_FIELD_TYPE_NULL | 特殊NULL类型 |
另见
DatabasePrepare、DatabaseColumnsCount、 DatabaseColumnName
# 30.21 DatabaseColumnSize
获取字段大小（以字节为单位）。
int DatabaseColumnSize(
int request, // 在DatabasePrepare中接收的请求句柄
int column //请求中的字段索引
);
1
2
3
4
2
3
4
参数
request
[in] 在DatabasePrepare()中接收的请求句柄。
column
[in] 请求中的字段索引。字段编号从零开始，不能超过 DatabaseColumnsCount() - 1。
返回值
如果成功，返回字段大小（以字节为单位），否则为-1。要获得错误代码，请使用GetLastError()，可能回应：
ERR_DATABASE_INVALID_HANDLE (5121) – 无效请求句柄；
ERR_DATABASE_NO_MORE_DATA (5126) – 'column'索引超过DatabaseColumnsCount() -1。
注意
只有在为'request（请求）'初步发出至少一个DatabaseRead()调用时，才能获取该值。
另见
DatabasePrepare、DatabaseColumnBlob、 DatabaseColumnsCount、 DatabaseColumnName、DatabaseColumnType
# 30.22 DatabaseColumnText
从当前记录中获取作为字符串的字段值。
bool DatabaseColumnText(
int request, // 在DatabasePrepare中接收的请求句柄
int column, // 请求中的字段索引
string& value //用于接收值的变量的引用
);
1
2
3
4
5
2
3
4
5
参数
request
[in] 在DatabasePrepare()中接收的请求句柄。
column
[in] 请求中的字段索引。字段编号从零开始，不能超过 DatabaseColumnsCount() - 1。
value
[out] 用于编写字段值的变量的引用。
返回值
如果成功返回true，否则返回false。要获得错误代码，请使用GetLastError()，可能回应：
ERR_DATABASE_INVALID_HANDLE (5121) – 无效请求句柄；
ERR_DATABASE_NO_MORE_DATA (5126) – 'column'索引超过DatabaseColumnsCount() -1。
注意
只有在为'request（请求）'初步发出至少一个DatabaseRead()调用时，才能获取该值。
若要从下一个记录读取该值，请初步调用DatabaseRead()。
另见
DatabasePrepare、DatabaseColumnsCount、 DatabaseColumnType、DatabaseColumnName
# 30.23 DatabaseColumnInteger
从当前记录中获取int类型的值。
bool DatabaseColumnInteger(
int request, // 在DatabasePrepare中接收的请求句柄
int column, // 请求中的字段索引
int& value // 用于接收值的变量的引用
);
1
2
3
4
5
2
3
4
5
参数
request
[in] 在DatabasePrepare()中接收的请求句柄。
column
[in] 请求中的字段索引。字段编号从零开始，不能超过 DatabaseColumnsCount() - 1。
value
[out] 用于编写字段值的变量的引用。
返回值
如果成功返回true，否则返回false。要获得错误代码，请使用GetLastError()，可能回应：
ERR_DATABASE_INVALID_HANDLE (5121) – 无效请求句柄；
ERR_DATABASE_NO_MORE_DATA (5126) – 'column'索引超过DatabaseColumnsCount() -1。
注意
只有在为'request（请求）'初步发出至少一个DatabaseRead()调用时，才能获取该值。
若要从下一个记录读取该值，请初步调用DatabaseRead()。
另见
DatabasePrepare、DatabaseColumnsCount、 DatabaseColumnType、DatabaseColumnName
# 30.24 DatabaseColumnLong
从当前记录中获取long类型的值。
bool DatabaseColumnLong(
int request, // 在DatabasePrepare中接收的请求句柄
int column, // 请求中的字段索引
long& value //用于接收值的变量的引用
);
1
2
3
4
5
2
3
4
5
参数
request
[in] 在DatabasePrepare()中接收的请求句柄。
column
[in] 请求中的字段索引。字段编号从零开始，不能超过 DatabaseColumnsCount() - 1。
value
[out] 用于编写字段值的变量的引用。
返回值
如果成功返回true，否则返回false。要获得错误代码，请使用GetLastError()，可能回应：
ERR_DATABASE_INVALID_HANDLE (5121) – 无效请求句柄； ERR_DATABASE_NO_MORE_DATA (5126) – 'column'索引超过DatabaseColumnsCount() -1。 注意
只有在为'request（请求）'初步发出至少一个DatabaseRead()调用时，才能获取该值。
若要从下一个记录读取该值，请初步调用DatabaseRead()。
另见
DatabasePrepare、DatabaseColumnsCount、 DatabaseColumnType、DatabaseColumnName
# 30.25 DatabaseColumnDouble
从当前记录中获取double类型的值。
bool DatabaseColumnDouble(
int request, // 在DatabasePrepare中接收的请求句柄
int column, // 请求中的字段索引
double& value // 用于接收值的变量的引用
);
1
2
3
4
5
2
3
4
5
参数
request
[in] 在DatabasePrepare()中接收的请求句柄。
column
[in] 请求中的字段索引。字段编号从零开始，不能超过 DatabaseColumnsCount() - 1。
value
[out] 用于编写字段值的变量的引用。
返回值
如果成功返回true，否则返回false。要获得错误代码，请使用GetLastError()，可能回应：
ERR_DATABASE_INVALID_HANDLE (5121) – 无效请求句柄； ERR_DATABASE_NO_MORE_DATA (5126) – 'column'索引超过DatabaseColumnsCount() -1。 注意
只有在为'request（请求）'初步发出至少一个DatabaseRead()调用时，才能获取该值。
若要从下一个记录读取该值，请初步调用DatabaseRead()。
另见
DatabasePrepare、DatabaseColumnsCount、 DatabaseColumnType、DatabaseColumnName
# 30.26 DatabaseColumnBlob
从当前记录中获取作为数组的字段值。
bool DatabaseColumnBlob(
int request, // 在DatabasePrepare中接收的请求句柄
int column, // 请求中的字段索引
void& data[] // 用于接收值的变量的引用
);
1
2
3
4
5
2
3
4
5
参数
request
[in] 在DatabasePrepare()中接收的请求句柄。
column
[in] 请求中的字段索引。字段编号从零开始，不能超过 DatabaseColumnsCount() - 1。
data[]
[out] 用于编写字段值的数组的引用。
返回值
如果成功返回true，否则返回false。要获得错误代码，请使用GetLastError()，可能回应：
ERR_DATABASE_INVALID_HANDLE (5121) – 无效请求句柄； ERR_DATABASE_NO_MORE_DATA (5126) – 'column'索引超过DatabaseColumnsCount() -1。 注意
只有在为'request（请求）'初步发出至少一个DatabaseRead()调用时，才能获取该值。
若要从下一个记录读取该值，请初步调用DatabaseRead()。
另见
DatabasePrepare、DatabaseColumnSize、 DatabaseColumnsCount、 DatabaseColumnType、DatabaseColumnName