MQL5参考使用数据库DatabaseReset 

DatabaseReset

重置请求,比如调用DatabasePrepare()之后。

int  DatabaseReset(
   int  request      //在DatabasePrepare中接收的请求句柄
   );

参数

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(symbolSYMBOL_DESCRIPTION)))
        {
         PrintFormat("DatabaseBind() failed at line %d with code=%d"__LINE__GetLastError());
         request_error=true;
         break;
        }
      if(!DatabaseBind(request, 2, SymbolInfoString(symbolSYMBOL_PATH)))
        {
         PrintFormat("DatabaseBind() failed at line %d with code=%d"__LINE__GetLastError());
         request_error=true;
         break;
        }
      if(!DatabaseBind(request, 3, SymbolInfoInteger(symbolSYMBOL_SPREAD)))
        {
         PrintFormat("DatabaseBind() failed at line %d with code=%d"__LINE__GetLastError());
         request_error=true;
         break;
        }
      if(!DatabaseBind(request, 4, SymbolInfoDouble(symbolSYMBOL_POINT)))
        {
         PrintFormat("DatabaseBind() failed at line %d with code=%d"__LINE__GetLastError());
         request_error=true;
         break;
        }
      if(!DatabaseBind(request, 5, SymbolInfoInteger(symbolSYMBOL_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(symbolSYMBOL_DIGITS);
   string json="{"+
               "\n"+indent1+"\"ConfigSymbols\":["+
               "\n"+indent2+"{"+
               "\n"+indent3+"\"Symbol\":\""+symbol+"\","+
               "\n"+indent3+"\"Path\":\""+SymbolInfoString(symbolSYMBOL_PATH)+"\","+
               "\n"+indent3+"\"CurrencyBase\":\""+SymbolInfoString(symbolSYMBOL_CURRENCY_BASE)+"\","+
               "\n"+indent3+"\"CurrencyProfit\":\""+SymbolInfoString(symbolSYMBOL_CURRENCY_PROFIT)+"\","+
               "\n"+indent3+"\"CurrencyMargin\":\""+SymbolInfoString(symbolSYMBOL_CURRENCY_MARGIN)+"\","+
               "\n"+indent3+"\"ColorBackground\":\""+ColorToString((color)SymbolInfoInteger(symbolSYMBOL_BACKGROUND_COLOR))+"\","+
               "\n"+indent3+"\"Digits\":\""+IntegerToString(SymbolInfoInteger(symbolSYMBOL_DIGITS))+"\","+
               "\n"+indent3+"\"Point\":\""+DoubleToString(SymbolInfoDouble(symbolSYMBOL_POINT), digits)+"\","+
               "\n"+indent3+"\"TickBookDepth\":\""+IntegerToString(SymbolInfoInteger(symbolSYMBOL_TICKS_BOOKDEPTH))+"\","+
               "\n"+indent3+"\"ChartMode\":\""+IntegerToString(SymbolInfoInteger(symbolSYMBOL_CHART_MODE))+"\","+
               "\n"+indent3+"\"TradeMode\":\""+IntegerToString(SymbolInfoInteger(symbolSYMBOL_TRADE_EXEMODE))+"\","+
               "\n"+indent3+"\"TradeCalcMode\":\""+IntegerToString(SymbolInfoInteger(symbolSYMBOL_TRADE_CALC_MODE))+"\","+
               "\n"+indent3+"\"OrderMode\":\""+IntegerToString(SymbolInfoInteger(symbolSYMBOL_ORDER_MODE))+"\","+
               "\n"+indent3+"\"CalculationMode\":\""+IntegerToString(SymbolInfoInteger(symbolSYMBOL_TRADE_CALC_MODE))+"\","+
               "\n"+indent3+"\"ExecutionMode\":\""+IntegerToString(SymbolInfoInteger(symbolSYMBOL_TRADE_EXEMODE))+"\","+
               "\n"+indent3+"\"ExpirationMode\":\""+IntegerToString(SymbolInfoInteger(symbolSYMBOL_EXPIRATION_MODE))+"\","+
               "\n"+indent3+"\"FillFlags\":\""+IntegerToString(SymbolInfoInteger(symbolSYMBOL_FILLING_MODE))+"\","+
               "\n"+indent3+"\"ExpirFlags\":\""+IntegerToString(SymbolInfoInteger(symbolSYMBOL_EXPIRATION_MODE))+"\","+
               "\n"+indent3+"\"Spread\":\""+IntegerToString(SymbolInfoInteger(symbolSYMBOL_SPREAD))+"\","+
               "\n"+indent3+"\"TickValue\":\""+StringFormat("%G", (SymbolInfoDouble(symbolSYMBOL_TRADE_TICK_VALUE)))+"\","+
               "\n"+indent3+"\"TickSize\":\""+StringFormat("%G", (SymbolInfoDouble(symbolSYMBOL_TRADE_TICK_SIZE)))+"\","+
               "\n"+indent3+"\"ContractSize\":\""+StringFormat("%G",(SymbolInfoDouble(symbolSYMBOL_TRADE_CONTRACT_SIZE)))+"\","+
               "\n"+indent3+"\"StopsLevel\":\""+IntegerToString(SymbolInfoInteger(symbolSYMBOL_TRADE_STOPS_LEVEL))+"\","+
               "\n"+indent3+"\"VolumeMin\":\""+StringFormat("%G",(SymbolInfoDouble(symbolSYMBOL_VOLUME_MIN)))+"\","+
               "\n"+indent3+"\"VolumeMax\":\""+StringFormat("%G",(SymbolInfoDouble(symbolSYMBOL_VOLUME_MAX)))+"\","+
               "\n"+indent3+"\"VolumeStep\":\""+StringFormat("%G",(SymbolInfoDouble(symbolSYMBOL_VOLUME_STEP)))+"\","+
               "\n"+indent3+"\"VolumeLimit\":\""+StringFormat("%G",(SymbolInfoDouble(symbolSYMBOL_VOLUME_STEP)))+"\","+
               "\n"+indent3+"\"SwapMode\":\""+IntegerToString(SymbolInfoInteger(symbolSYMBOL_SWAP_MODE))+"\","+
               "\n"+indent3+"\"SwapLong\":\""+StringFormat("%G",(SymbolInfoDouble(symbolSYMBOL_SWAP_LONG)))+"\","+
               "\n"+indent3+"\"SwapShort\":\""+StringFormat("%G",(SymbolInfoDouble(symbolSYMBOL_SWAP_SHORT)))+"\","+
               "\n"+indent3+"\"Swap3Day\":\""+IntegerToString(SymbolInfoInteger(symbolSYMBOL_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
*/
 

另见

DatabasePrepareDatabaseBindDatabaseBindArrayDatabaseFinalize