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.

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.

Working with the functions requires the minimum knowledge of SQL.

Function

Action

DatabaseOpen

Opens or creates a database in a specified file

DatabaseClose

Closes a database

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()

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