• Latest

    Thursday, October 20, 2016

    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
    

    Fashion

    Beauty

    Travel