Delphi tutorial – connect MySQL with ZEOS library

 

Delphi tutorial – connect MySQL with ZEOS library

After you manage to create this application, you will actually have small MySQL manager.
To achieve this you will have to have access to MySQL server.
In this tutorial I am using portable version of server called „USBWebserver“ which can be downloaded from : www.usbwebserver.net

Here you can see default port to Apache and MySQL server, and if you click on the button PHPMyadmin,
PHPMyAdmin will be opened in default web browser where you will see that default user for accessing PHPMyAdmin is „root“ and default password is „usbw“.

In this example I used these data to access MySQL.
You also have option online with some free MySQL servers, just google something like „free mysql server „ or something like that.
Just pay attention on username , password and port number.

/*****************************************************************************/
Second thing you need to have is „libmysql.dll“ file which is a part of the MySQL Connector, you can find one on official MySQL web page, or to download the one that I used in this example :
https://drive.google.com/open?id=0B_njK7HczCjDMGRVMGhGbUk1OHc
Just save it in the same folder where your exe file will be, and link to it in you application like I explained in video tutorial (ZConnection1.LibraryLocation:=ExtractFilePath(Application.ExeName)+'libmysql.dll';)
/*****************************************************************************/
The third thing you need is installed ZeosDBO components for Delphi, from ZeosLib Development Group, and the lates source code you can find at http://www.sourceforge.net/projects/zeoslib
/*****************************************************************************/
Basically that are tools that you need.
We start with creating new project , adding necesary components on the form and writting code to achieve what we want.
In the video you can see all these steps, and in the code for showed example you can see details.

Code:

(code style formatted by http://hilite.me/ )

unit MainForm;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, ZAbstractRODataset, ZAbstractDataset, ZDataset,
  ZAbstractConnection, ZConnection, ComCtrls, StdCtrls, Grids, DBGrids;

type
  TfrmMain = class(TForm)
    GroupBox1: TGroupBox;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    txtHost: TEdit;
    txtPort: TEdit;
    txtUser: TEdit;
    Label4: TLabel;
    txtPass: TEdit;
    Button1: TButton;
    Button2: TButton;
    StatusBar1: TStatusBar;
    ZConnection1: TZConnection;
    ZQuery1: TZQuery;
    GroupBox2: TGroupBox;
    GroupBox3: TGroupBox;
    Label5: TLabel;
    txtDBName: TEdit;
    Button3: TButton;
    cboDatabases: TComboBox;
    cmdDelDB: TButton;
    cmdConnectDB: TButton;
    GroupBox4: TGroupBox;
    cboTables: TComboBox;
    cmdDelTable: TButton;
    cmdOpenTable: TButton;
    Label6: TLabel;
    Button4: TButton;
    txtNewTable: TEdit;
    Label7: TLabel;
    Label8: TLabel;
    txtName: TEdit;
    txtSurname: TEdit;
    Button5: TButton;
    DBGrid1: TDBGrid;
    DataSource1: TDataSource;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure ZConnection1AfterConnect(Sender: TObject);
    procedure ZConnection1AfterDisconnect(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure cmdDelDBClick(Sender: TObject);
    procedure cmdConnectDBClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure cmdOpenTableClick(Sender: TObject);
    procedure Button4Click(Sender: TObject);
    procedure cmdDelTableClick(Sender: TObject);
    procedure Button5Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frmMain: TfrmMain;

implementation

{$R *.dfm}

procedure TfrmMain.Button1Click(Sender: TObject);
var i:Integer;
begin
with ZConnection1 do begin
  Disconnect;
  Protocol:='mysql';
  LibraryLocation:=ExtractFilePath(Application.ExeName)+'libmysql.dll';
  HostName:=txtHost.Text;
  Port:=StrToInt(txtPort.Text);
  User:=txtUser.Text;
  Password:=txtPass.Text;
  Connect;
end;
cboDatabases.Items.Clear;
ZConnection1.GetCatalogNames(cboDatabases.Items);
cboDatabases.ItemIndex:=0;
end;

procedure TfrmMain.Button2Click(Sender: TObject);
begin
ZConnection1.Disconnect;
end;

procedure TfrmMain.Button3Click(Sender: TObject);
begin
ZQuery1.Close;
ZQuery1.SQL.Clear;
ZQuery1.SQL.Add('CREATE DATABASE '+txtDBName.Text+' DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;');
ZQuery1.ExecSQL;
Button1.Click;
cboDatabases.Items.Clear;
ZConnection1.GetCatalogNames(cboDatabases.Items);
cboDatabases.ItemIndex:=0;
end;

procedure TfrmMain.Button4Click(Sender: TObject);
begin
ZQuery1.Close;
ZQuery1.SQL.Clear;
ZQuery1.SQL.Add('CREATE TABLE IF NOT EXISTS '+txtNewTable.Text
+'(id MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT, Name VARCHAR(50), Surname VARCHAR(50),'
+'PRIMARY KEY(id) )DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;');
ZQuery1.ExecSQL;
cmdConnectDB.Click;
end;

procedure TfrmMain.Button5Click(Sender: TObject);
var nmx,surnmx :string;
begin
nmx:=txtName.Text;
surnmx:=txtSurname.Text;
ZQuery1.Close;
ZQuery1.SQL.Clear;
ZQuery1.SQL.Add('INSERT INTO '+cboTables.Text+'(Name,Surname)'
+' VALUES ("'+nmx+'", "'+surnmx+'")');
ZQuery1.ExecSQL;
cmdOpenTable.Click;
end;

procedure TfrmMain.cmdConnectDBClick(Sender: TObject);
begin
ZConnection1.Disconnect;
ZConnection1.Database:=cboDatabases.Text;
ZConnection1.Connect;

cboTables.Items.Clear;
ZConnection1.GetTableNames('',cboTables.Items);
cboTables.ItemIndex:=0;
end;


procedure TfrmMain.cmdDelDBClick(Sender: TObject);
begin
if cboDatabases.Text<>'' then   begin
ZConnection1.Database:='';
ZQuery1.Close;
ZQuery1.SQL.Clear;
ZQuery1.SQL.Add('Drop database '+cboDatabases.Text);
ZQuery1.ExecSQL;
end;
    Button1.Click;
cboDatabases.Items.Clear;
ZConnection1.GetCatalogNames(cboDatabases.items);
cboDatabases.ItemIndex:=0;
end;

procedure TfrmMain.cmdDelTableClick(Sender: TObject);
begin
if cboTables.Text<>'' then begin
  ZQuery1.Close;
  ZQuery1.SQL.Clear;
  ZQuery1.SQL.Add('Drop table '+cboTables.Text);
  ZQuery1.ExecSQL;
end;
cmdConnectDB.Click;
end;

procedure TfrmMain.cmdOpenTableClick(Sender: TObject);
var i:Integer;
begin
 if cboTables.Text<>'' then begin
 ZQuery1.Close;
 ZQuery1.SQL.Clear;
 ZQuery1.SQL.Add('Select * from '+cboTables.Text);
 ZQuery1.Active:=True;
 end;
 for I := 0 to DBGrid1.Columns.Count-1 do
   begin
     DBGrid1.Columns[i].Width:=100;
   end;
end;


procedure TfrmMain.FormClose(Sender: TObject; var Action: TCloseAction);
begin
ZConnection1.Disconnect;
end;

procedure TfrmMain.ZConnection1AfterConnect(Sender: TObject);
begin
StatusBar1.Panels[0].Text:='Connected';
end;

procedure TfrmMain.ZConnection1AfterDisconnect(Sender: TObject);
begin
StatusBar1.Panels[0].Text:='Disconnected';
end;

end.

//end code