DocumentationSections
MQL5 ReferenceWorking with databases 

Working with databases

The functions for working with databases apply the popular and easy-to-use SQLite engine. The convenient feature of this engine is that the entire database is located in a single file on a user PC's hard disk.

The functions allow for convenient creation of tables, adding data to them, performing modifications and sampling using simple SQL requests:

  • receiving trading history and quotes from any formats,
  • saving optimization and test results,
  • preparing and exchanging data with other analysis packages,
  • storing MQL5 application settings and status.

Queries allow using statistical and mathematical functions.

The functions for working with databases allow you to replace the most repetitive large data array handling operations with SQL requests, so that it is often possible to use the DatabaseExecute/DatabasePrepare calls instead of programming complex loops and comparisons. Use the DatabaseReadBind function to conveniently obtain query results in a ready-made structure. The function allows reading all record fields at once within a single call.

To accelerate reading, writing and modification, a database can be opened/created in RAM with the DATABASE_OPEN_MEMORY flag, although such a database is available only to a specific application and is not shared. When working with databases located on the hard disk, bulk data inserts/changes should be wrapped in transactions using DatabaseTransactionBegin/DatabaseTransactionCommit/DatabaseTransactionRollback. This accelerates the process hundreds of times.

To start working with the functions, read the article SQLite: Native handling of SQL databases in MQL5.

Function

Action

DatabaseOpen

Opens or creates a database in a specified file

DatabaseClose

Closes a database

DatabaseImport

Imports data from a file into a table

DatabaseExport

Exports a table or an SQL request execution result to a CSV file

DatabasePrint

Prints a table or an SQL request execution result in the Experts journal

DatabaseTableExists

Checks the presence of the table in a database

DatabaseExecute

Executes a request to a specified database

DatabasePrepare

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

DatabaseReset

Resets a request, like after calling DatabasePrepare()

DatabaseBind

Sets a parameter value in a request

DatabaseBindArray

Sets an array as a parameter value

DatabaseRead

Moves to the next entry as a result of a request

DatabaseReadBind

Moves to the next record and reads data into the structure from it

DatabaseFinalize

Removes a request created in DatabasePrepare()

DatabaseTransactionBegin

Starts transaction execution

DatabaseTransactionCommit

Completes transaction execution

DatabaseTransactionRollback

Rolls back transactions

DatabaseColumnsCount

Gets the number of fields in a request

DatabaseColumnName

Gets a field name by index

DatabaseColumnType

Gets a field type by index

DatabaseColumnSize

Gets a field size in bytes

DatabaseColumnText

Gets a field value as a string from the current record

DatabaseColumnInteger

Gets the int type value from the current record

DatabaseColumnLong

Gets the long type value from the current record

DatabaseColumnDouble

Gets the double type value from the current record

DatabaseColumnBlob

Gets a field value as an array from the current record

Statistical functions:

  • mode – mode
  • median – median (50th percentile)
  • percentile_25 – 25th percentile
  • percentile_75
  • percentile_90
  • percentile_95
  • percentile_99
  • stddev or stddev_samp — sample standard deviation
  • stddev_pop — population standard deviation
  • variance or var_samp — sample variance
  • var_pop — population variance

Mathematical functions

Example:

select
  count(*) as book_count,
  cast(avg(parent) as integer) as mean,
  cast(median(parent) as integer) as median,
  mode(parent) as mode,
  percentile_90(parent) as p90,
  percentile_95(parent) as p95,
  percentile_99(parent) as p99
from moz_bookmarks;