mORMot Open Source

alexpirate

Member

Registered: 2013-11-16

Posts: 3

How to connect to MySQL Server via ZEOS or UNIDAC with Mormot

In this examples I’ll use the demo database WORLD, which comes with the MySQL.

How to use ZEOS

To connect ZEOS to MySQL Server you need to provide MySQL connector library.
Go to http://www.mysql.com/products/connector/ , download http://dev.mysql.com/downloads/connector/c/  - Windows (x86, 32-bit), ZIP Archive.
Extract the archive. We need only libmysql.dll .
Put the libmysql.dll in the windows PATH.

Now the connection string look like this. How you see the name of the database is declared two times. One time in the aServerName and a second time in the aDatabaseName.

  fConnection := TSQLDBZEOSConnectionProperties.Create('zdbc:mysql://192.168.2.60:3306/world?username=root;password=dev', 'world', '', '');

this is because in the SynDBZEOS unit,
you have

fURL.Database := UTF8ToString(aDatabaseName); 

instead

  if fURL.Database='' then
    fURL.Database := UTF8ToString(aDatabaseName);

if you want you can fix this and then the connection string will be normal
  fConnection := TSQLDBZEOSConnectionProperties.Create('zdbc:mysql://192.168.2.60:3306/world?username=root;password=dev', '', '', '');

How to use UNIDAC

First you need to make some fixes in the SynDBUniDAC unit.
Go to

constructor TSQLDBUniDACConnection.Create(aProperties: TSQLDBConnectionProperties);

And replace it with

constructor TSQLDBUniDACConnection.Create(aProperties: TSQLDBConnectionProperties);
var options: TStrings;
    PortNumber : Integer;
begin
  inherited Create(aProperties);
  fDatabase := TUniConnection.Create(nil);
  fDatabase.ProviderName := UTF8ToString(fProperties.ServerName);
  case aProperties.DBMS of
  dSQLite, dFirebird, dPostgreSQL, dMySQL:
    fDatabase.Database := UTF8ToString(fProperties.DatabaseName);
  else
    fDatabase.Server := UTF8ToString(fProperties.DatabaseName);
  end;
  options := (fProperties as TSQLDBUniDACConnectionProperties).fSpecificOptions;
  if fDatabase.Server='' then // see TSQLDBUniDACConnectionProperties.URI()
    fDatabase.Server := options.Values['Server'];
  if fDatabase.Database='' then
    fDatabase.Database := options.Values['Database'];
  if fDatabase.Port=0 then
     if TryStrToInt(options.Values['Port'], PortNumber) then
     begin
       fDatabase.Port := PortNumber;
     end;
  fDatabase.Username := UTF8ToString(fProperties.UserID);
  fDatabase.Password := UTF8ToString(fProperties.PassWord);
  fDatabase.SpecificOptions.AddStrings(options);
end;

now the connection here is tricky like this:

  fConnection := TSQLDBUniDACConnectionProperties.Create('MySQL', 'world', 'root', 'dev');
  fConnection.SpecificOptions.Values['Server'] := '192.168.2.60';
  fConnection.SpecificOptions.Values['Port'] := '3306'; - don’t set it if it is the default port

and now this is simple example how to retrieve data from the database.

Unit1.dfm

object Form1: TForm1
  Left = 0
  Top = 0
  Caption = 'Form1'
  ClientHeight = 528
  ClientWidth = 760
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  OnCreate = FormCreate
  PixelsPerInch = 96
  TextHeight = 13
  object Label1: TLabel
    Left = 24
    Top = 16
    Width = 19
    Height = 13
    Caption = 'ID='
  end
  object Label2: TLabel
    Left = 616
    Top = 96
    Width = 109
    Height = 13
    Caption = 'Filter - Not Working'
    Font.Charset = DEFAULT_CHARSET
    Font.Color = clRed
    Font.Height = -11
    Font.Name = 'Tahoma'
    Font.Style = [fsBold]
    ParentFont = False
  end
  object Button1: TButton
    Left = 192
    Top = 8
    Width = 75
    Height = 25
    Caption = 'ZEOS'
    TabOrder = 0
    OnClick = Button1Click
  end
  object Button2: TButton
    Left = 288
    Top = 8
    Width = 75
    Height = 25
    Caption = 'UNIDAC'
    TabOrder = 1
    OnClick = Button2Click
  end
  object Memo1: TMemo
    Left = 24
    Top = 48
    Width = 577
    Height = 193
    ScrollBars = ssBoth
    TabOrder = 2
  end
  object StringGrid1: TStringGrid
    Left = 24
    Top = 264
    Width = 577
    Height = 217
    TabOrder = 3
  end
  object Edit1: TEdit
    Left = 49
    Top = 13
    Width = 121
    Height = 21
    TabOrder = 4
    Text = '10'
  end
  object Button3: TButton
    Left = 632
    Top = 360
    Width = 75
    Height = 25
    Caption = 'Fill Grid'
    TabOrder = 5
    OnClick = Button3Click
  end
  object Button4: TButton
    Left = 632
    Top = 139
    Width = 75
    Height = 25
    Caption = 'add to Memo'
    TabOrder = 6
    OnClick = Button4Click
  end
  object Edit2: TEdit
    Left = 607
    Top = 112
    Width = 145
    Height = 21
    TabOrder = 7
    Text = 'District=:("Zuid-Holland"):'
  end
end

Unit1.pas

unit Unit1;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs,

  SynCommons,
  mORMot,
  mORMotDB,

  mORMotUI,  // needed for the grid

  SynSQlite3Static,
  mORMotSQLite3,

  SynDBZeos, // needed for Zeos
  ZDbcMySql, // needed for Zeos

  SynDBUniDAC,       // needed for UniDAC
  MySQLUniProvider,  // needed for UniDAC

  Vcl.StdCtrls, Vcl.Grids;


type
  TSQLCityExt = class(TSQLRecord)
  private
    fName: RawUTF8;
    fDistrict: RawUTF8;
    fCountryCode: RawUTF8;
    fPopulation: Int32;
  published
    property Name: RawUTF8 read fName write fName;
    property District: RawUTF8 read fDistrict write fDistrict;
    property CountryCode: RawUTF8 read fCountryCode write fCountryCode;
    property Population: Int32 read fPopulation write fPopulation;
  end;

type
  TForm1 = class(TForm)
    Button1: TButton;
    Button2: TButton;
    Memo1: TMemo;
    StringGrid1: TStringGrid;
    Edit1: TEdit;
    Label1: TLabel;
    Button3: TButton;
    Button4: TButton;
    Edit2: TEdit;
    Label2: TLabel;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
var
  CExt: TSQLCityExt;
  fConnection: TSQLDBZEOSConnectionProperties;
  fModel: TSQLModel;
  fClient: TSQLRestClientDB;

begin

  fConnection := TSQLDBZEOSConnectionProperties.Create('zdbc:mysql://192.168.2.60:3306/world?username=root;password=dev', 'world', '', '');

  fModel := TSQLModel.Create([TSQLCityExt]);
  VirtualTableExternalRegister(fModel, TSQLCityExt, fConnection, 'city');
  fClient := TSQLRestClientDB.Create(fModel, nil, 'test.db', TSQLRestServerDB);
  DeleteFile('test.db');
  fClient.Server.CreateMissingTables;

  CExt := TSQLCityExt.Create(fClient, 'ID=?', [StrToInt(Edit1.Text)]);

  memo1.Lines.Add(CExt.Name + ' ' + CExt.District + ' ' + CExt.CountryCode + ' ' + IntToStr(CExt.Population));

end;

procedure TForm1.Button2Click(Sender: TObject);
var
  CExt: TSQLCityExt;
  fConnection: TSQLDBUniDACConnectionProperties;
  fModel: TSQLModel;
  fClient: TSQLRestClientDB;

begin
  fConnection := TSQLDBUniDACConnectionProperties.Create('MySQL', 'world', 'root', 'dev');
  fConnection.SpecificOptions.Values['Server'] := '192.168.2.60';
  fConnection.SpecificOptions.Values['Port'] := '3306';
  fModel := TSQLModel.Create([TSQLCityExt]);
  VirtualTableExternalRegister(fModel, TSQLCityExt, fConnection, 'city');
  fClient := TSQLRestClientDB.Create(fModel, nil, 'test.db', TSQLRestServerDB);
  DeleteFile('test.db');
  fClient.Server.CreateMissingTables;

  CExt := TSQLCityExt.Create(fClient, 'ID=?', [StrToInt(Edit1.Text)]);

  memo1.Lines.Add(CExt.Name + ' ' + CExt.District + ' ' + CExt.CountryCode + ' ' + IntToStr(CExt.Population));

end;

procedure TForm1.Button3Click(Sender: TObject);
var
  fConnection: TSQLDBZEOSConnectionProperties;
  fModel: TSQLModel;
  fClient: TSQLRestClientDB;
  table: TSQLTable;

begin

  fConnection := TSQLDBZEOSConnectionProperties.Create('zdbc:mysql://192.168.2.60:3306/world?username=root;password=dev', 'world', '', '');

  fModel := TSQLModel.Create([TSQLCityExt]);
  VirtualTableExternalRegister(fModel, TSQLCityExt, fConnection, 'city');
  fClient := TSQLRestClientDB.Create(fModel, nil, 'test.db', TSQLRestServerDB);
  DeleteFile('test.db');
  fClient.Server.CreateMissingTables;

  table := fClient.List([TSQLCityExt],'*');

  TSQLTableToGrid.Create(StringGrid1,table,fClient);

end;

procedure TForm1.Button4Click(Sender: TObject);
var
  CExt: TSQLCityExt;
  fConnection: TSQLDBZEOSConnectionProperties;
  fModel: TSQLModel;
  fClient: TSQLRestClientDB;
begin

  fConnection := TSQLDBZEOSConnectionProperties.Create('zdbc:mysql://192.168.2.60:3306/world?username=root;password=dev', 'world', '', '');

  fModel := TSQLModel.Create([TSQLCityExt]);
  VirtualTableExternalRegister(fModel, TSQLCityExt, fConnection, 'city');
  fClient := TSQLRestClientDB.Create(fModel, nil, 'test.db', TSQLRestServerDB);
  DeleteFile('test.db');
  fClient.Server.CreateMissingTables;

  CExt := TSQLCityExt.CreateAndFillPrepare(fClient, 'District=:("Zuid-Holland"):' , []);

// I Don't know why i can't call it like this
// [dcc32 Error] Unit1.pas(158): E2250 There is no overloaded version of 'CreateAndFillPrepare' that can be called with these arguments
//  CExt := TSQLCityExt.CreateAndFillPrepare(fClient, Edit2.Text , []);


  while CExt.FillOne do
    memo1.Lines.Add(CExt.Name + ' ' + CExt.District + ' ' + CExt.CountryCode + ' ' + IntToStr(CExt.Population));

end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  // needed only if you want a logfile - good for development
  with TSQLLog.Family do
  begin
    Level := LOG_VERBOSE;
    EchoToConsole := LOG_VERBOSE; // log all events to the console end;
  end;
end;

end.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: How to connect to MySQL Server via ZEOS or UNIDAC with Mormot

With Zeos/ZDBC: now it won't override Database name if already set at URI parameter level (e.g. zdbc:mysql://192.168.2.60:3306/world?username=root;password=dev).
See http://synopse.info/fossil/info/448229557861d

With UniDAC, I've enhanced the TSQLDBUniDACConnectionProperties.URI() method to handle an optional port number.
See http://synopse.info/fossil/info/5f418f2a75
So you should be able to connect using:

PropsMySQL := TSQLDBUniDACConnectionProperties.Create(
  TSQLDBUniDACConnectionProperties.URI(dMySQL,'192.168.2.60:3306'),
  'world', 'root', 'dev');

The demo is nice.
We may write e.g. the more "mORMotish":

CExt := TSQLCityExt.CreateAndFillPrepare(fClient, 'District=?' , ['Zuid-Holland']);

Thanks a lot for the very valuable input!

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: How to connect to MySQL Server via ZEOS or UNIDAC with Mormot

Note that this information has been included within the main documentation.

Do not forget to checkout the reference sample http://synopse.info/fossil/finfo?name=S … rfMain.pas for some code able to create the connection properties.