From mAirListWiki

Starting with mAirList 2.1.38, you can access SQL databases from within mAirListScript. This is implemented by exposing the interface of the database library used by mAirList -- ZeosLib - through the scripting engine (Pascal Script).

ZeosLib

ZeosLib is a database component for Borland Delphi. It can be used to connect to various kinds of SQL-based database servers, including MySQL, PostgreSQL, SQlit, Microsoft SQL Server and many others. The API is said to be very similar to JDBC, so anyone familiar with database programming in Java will be able to write ZeosLib code easily.

For more information and full documentation, see the ZeosLib homepage.

Connecting to the Server

In order to connect to a database server, you need to call ZeosDriverManager.GetConnection or ZeosDriverManager.GetConnectionWithLogin. Here's an example for MySQL (with the v3.23 client DLL):

var 
  connection: IZConnection;

begin
  connection := ZeosDriverManager.GetConnectionWithLogin
    ('zdbc:mysql-3.23://serverhostname/mydatabase', 'username', 'secret');
end.

The function returns an IZConnection interface which you must keep and use throught the script execution process.

Querying Data

The IZConnection interface obtained during the connection process is used to send SQL commands or queries to the database server. This is usually done by first preparing a statement, possibly filling in parameters, and then executing it, either with ExecuteQueryPrepared (for queries which expect a result) or with ExecuteUpdatePrepared (for other SQL commands like UPDATE or DELETE). Here's a first example:

procedure DeleteAllSongs;
var
  stmt: IZPreparedStatement;
begin
  stmt := connection.PrepareStatement('DELETE FROM songs');
  stmt.ExecuteUpdatePrepared;
end; 

You can use "?" as a placeholder for parameters and then fill them in using the Set... methods of IZPreparedStatement. Parameters are addressed by numbers, where 1 is the first question mark in the SQL code. ZeosLib automatically takes care of correct quotation, so you do not need to add quotation marks or the like.

procedure DeleteUglySongs;
var
  stmt: IZPreparedStatement;
begin
  stmt := connection.PrepareStatement('DELETE FROM songs WHERE id=? OR artist=?');
  stmt.SetInt(1, 87);
  stmt.SetString(2, 'Britney Spears');
  stmt.ExecuteUpdatePrepared;
end; 

For queries, we use ExecuteQueryPrepared, which returns an IZResultSet interface. IZResult has a built-in iterator, so we just need to call rs.Next until it returns false, and we can use the Get... methods inside the loop in order to retrieve the data of the current dataset:

procedure ShowCoolSongs;
var
  stmt: IZPreparedStatement;
  rs: IZResultSet;
begin
  stmt := connection.PrepareStatement('SELECT title FROM songs WHERE artist=?');
  stmt.SetString(1, 'Pink Floyd');
  rs := stmt.ExecuteQueryPrepared;
  while rs.Next do
    ShowMessage(rs.GetString(1));
end;