Integrating MySQL Database with Delphi Application: A Guide to Retrieving Data from a Remote Server using Web API

You may need to use a database to store and retrieve data for your applications. MySQL is one of the most popular databases used on servers, and it can be easily integrated with Delphi applications. In this blog, we will discuss how to use MySQL on the server and pull information into your Delphi application.

Step 1: Install MySQL Server

The first step is to install MySQL Server on your server. You can download the latest version from the MySQL website and follow the instructions to install it on your server. Once installed, you can create a new database and tables to store your data.

Step 2: Connect to MySQL Server

To connect to the MySQL server from your Delphi application, you need to use a database component. Delphi provides several database components, including ADO, BDE, and dbExpress. In this example, we will use dbExpress.

To connect to the MySQL server, you need to add a TSQLConnection component to your form. Set the DriverName property to MySQL and the Params property to specify the server, database, and login credentials. Here’s an example:

SQLConnection1.DriverName := 'MySQL';
SQLConnection1.Params.Values['HostName'] := 'localhost';
SQLConnection1.Params.Values['Database'] := 'mydatabase';
SQLConnection1.Params.Values['User_Name'] := 'myusername';
SQLConnection1.Params.Values['Password'] := 'mypassword';
SQLConnection1.Open;

Step 3: Retrieve Data from MySQL Server

Once you are connected to the MySQL server, you can retrieve data from the tables using a TSQLQuery component. Set the SQL property to the SQL statement you want to execute, and then call the Open method to retrieve the data. Here’s an example:

SQLQuery1.SQL.Text := 'SELECT * FROM mytable';
SQLQuery1.Open;

Step 4: Display Data in Delphi Application

To display the data in your Delphi application, you can use a TDBGrid component. Set the DataSource property to the TDataSource component that is linked to your TSQLQuery component. Here’s an example:

DBGrid1.DataSource := DataSource1;
DataSource1.DataSet := SQLQuery1;

Step 5: Source Code

Here’s the complete source code for a simple Delphi application that connects to a MySQL database and displays data in a grid:

unit Unit1;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, DB, Grids, DBGrids, SqlExpr;

type
TForm1 = class(TForm)
SQLConnection1: TSQLConnection;
SQLQuery1: TSQLQuery;
DataSource1: TDataSource;
DBGrid1: TDBGrid;
procedure FormCreate(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;

var
Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.FormCreate(Sender: TObject);
begin
SQLConnection1.DriverName := 'MySQL';
SQLConnection1.Params.Values['HostName'] := 'localhost';
SQLConnection1.Params.Values['Database'] := 'mydatabase';
SQLConnection1.Params.Values['User_Name'] := 'myusername';
SQLConnection1.Params.Values['Password'] := 'mypassword';
SQLConnection1.Open;

SQLQuery1.SQL.Text := 'SELECT * FROM mytable';
SQLQuery1.Open;

DBGrid1.DataSource := DataSource1;
DataSource1.DataSet := SQLQuery1;
end;

end.

If your database is hosted on the web and you can’t use localhost, you need to use the hostname or IP address of the server where the database is hosted. Here’s how you can modify the source code to connect to a remote MySQL server:

SQLConnection1.DriverName := 'MySQL';
SQLConnection1.Params.Values['HostName'] := 'yourhostname.com'; // replace with the hostname or IP address of the server
SQLConnection1.Params.Values['Database'] := 'mydatabase';
SQLConnection1.Params.Values['User_Name'] := 'myusername';
SQLConnection1.Params.Values['Password'] := 'mypassword';
SQLConnection1.Open;

Make sure you replace ‘yourhostname.com‘ with the actual hostname or IP address of the server where your database is hosted. If your database server requires a specific port number, you can add it to the hostname or IP address like this: ‘yourhostname.com:portnumber’.

Also, keep in mind that connecting to a remote database over the internet can be slower than connecting to a local database, so you may experience some latency when retrieving data.

If you don’t have direct access to the MySQL server or if it’s not possible to connect to it using the TSQLConnection component, you can try using a web API to retrieve data from the server.

A web API is a set of HTTP endpoints that allow you to retrieve data from a remote server using standard web protocols. The server exposes the data in a format such as JSON or XML, which can be easily consumed by your Delphi application using a REST client component.

Here’s an example of how you can use a REST client component to retrieve data from a web API that exposes MySQL data in JSON format:

1. Create a new Delphi application and add a TRESTClient component to the form.
2. Set the BaseURL property of the TRESTClient component to the URL of the web API that exposes the MySQL data.
3. Add a TRESTRequest component to the form and set its Client property to the TRESTClient component you added in step 2.
4. Set the Method property of the TRESTRequest component to GET.
5. Set the Resource property of the TRESTRequest component to the endpoint of the web API that retrieves the MySQL data.
6. Add a TRESTResponse component to the form and set its Request property to the TRESTRequest component you added in step 3.
7. Add a TJSONObject and a TJSONArray component to the form.
8. Add a button to the form and add the following code to its OnClick event:

procedure TForm1.Button1Click(Sender: TObject);
var
json: TJSONObject;
jsonArray: TJSONArray;
i: Integer;
value: TJSONValue;
begin
RESTRequest1.Execute;
json := RESTResponse1.JSONValue as TJSONObject;
jsonArray := json.GetValue('data') as TJSONArray;
for i := 0 to jsonArray.Count - 1 do
begin
value := jsonArray.Items[i];
// process the JSON data here
end;
end;

This code sends a GET request to the web API, retrieves the JSON data from the response, and processes it using a loop. You can modify the loop to extract the data you need from the JSON objects and display it in your Delphi application.

Note that this is just a basic example of how to use a web API to retrieve MySQL data in JSON format. The specific implementation will depend on the web API you are using and the format of the data it exposes.

To create a web API for your MySQL database, you can use a server-side scripting language such as PHP, Python, or Node.js. Here’s an example of how you can create a simple PHP API that retrieves data from a MySQL database and returns it in JSON format:

1. Create a new PHP file on your web server and add the following code:

<?php
header('Content-Type: application/json');

// connect to the MySQL server
$servername = "localhost";
$username = "myusername";
$password = "mypassword";
$dbname = "mydatabase";
$conn = new mysqli($servername, $username, $password, $dbname);

// check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// retrieve data from the MySQL database
$sql = "SELECT * FROM mytable";
$result = $conn->query($sql);

// create a JSON array of the data
$data = array();
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $data[] = $row;
    }
}

// return the JSON data
echo json_encode(array('data' => $data));

// close the MySQL connection
$conn->close();
?>

This code connects to the MySQL server, retrieves data from a table called ‘mytable’, creates a JSON array of the data, and returns it in a JSON object with a ‘data’ key.

2. Save the PHP file on your web server and note its URL.
3. Modify the Delphi application code from my previous answer to use the URL of the PHP file as the BaseURL property of the TRESTClient component:

RESTClient1.BaseURL := 'http://yourserver.com/api.php';

4. Modify the Resource property of the TRESTRequest component to the endpoint of the web API that retrieves the MySQL data. In this case, it would be an empty string since the entire PHP file is the endpoint.

RESTRequest1.Resource := '';

5. Modify the loop in the Button1Click event to extract the data from the ‘data’ key of the JSON object:

value := jsonArray.Items[i];
row := value as TJSONObject;
id := row.GetValue('id').Value;
name := row.GetValue('name').Value;
// process the data here

Note that you will need to modify the loop to match the structure of the data returned by your web API.

This is just a basic example of how to create a web API for a MySQL database using PHP. The specific implementation will depend on the structure of your database and the requirements of your application.