Pages: 1

#1 mORMot 1 » How to connect to MySQL Server via ZEOS or UNIDAC with Mormot » 2013-12-26 23:26:13

alexpirate
Replies: 2

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.

#2 Re: mORMot 1 » propsal from Newbie to mORMot » 2013-11-19 08:35:17

alexpirate

Hi Again,

We are OK about a and c and I have additional question about b.
for b what gui client you use for fossil repository? for the git we using smartgit for IDE like Embarcadero and for our PHP Development the work with the Jira and the Git are very good integrated into current PHP IDE which we are using and for it we are not using smartgit.

last night I have successfully completed the connection string for MySQL via ZDBC/Zeos and now we have connection to the MySQL. from here I'll leave the examination of mORMot to my colleagues because with Delphi I never used ORM, MVC or Web Services and it's to hard for me to cover all this thinks with just debugging, but my colleagues are using all this thinks in their work will our PHP Projects.

I have some other thinks which I like to discus with you which is not for public forum discussions. is it ok to contact you about them via the email in the forum or you prefer other way?

#3 mORMot 1 » propsal from Newbie to mORMot » 2013-11-16 20:59:11

alexpirate
Replies: 11

Hi AB,

My colleagues and I are next Newbie to the project, which are here to give a chance to mORMot to create Delphi projects, using all possible best practices and tips.
We already have a lot of experience with PHP and all the goodies such as ORM, MVC, framework, etc.
Also we have developed own PHP framework reached version 2.5, because a lot of things that we do not like how they were implemented in other similar PHP framework.
A small part of us have a good experience with Delphi, but as we all know, those of us who are using Delphi usually are misled, not to use any good programming practice while using  Delphi.
Because we have several new projects – some of them are purchases from our clients, other are ours, we would really want to eradicate bad practices acquired in working with Delphi.
Looking through a lot in this direction and reviewing a lot of products like Remobjects Data Abstract, kbmMW and etc., we have come to Synopse mORMot Framework. Reviewing the documentation of mORMot, I got the impression that would cover all of our needs that we miss so far with Delphi programing and are available in other program languages like Microsoft .Net, PHP and etc.
Alas, it turns out that no matter how great your product documentation is and how many  examples are available for how to use mORMot, it is my great disappointment that all the documentation and examples are only for how fast is mORMot and don’t cover anything from the actual real-life examples .
We wanted to run a simple project - Server with services and client to use these services, with MySQL database server. The choice of MySQL- because of the long time period which we have using it in our web products and also the possibility to work on Linux and Window.
Somehow we managed to create the server and to publish several services, but we can’t accomplish to create correct connection string for TSQLDBUniDACConnectionProperties for MySQL. Everywhere (documentation and examples) we are finding only SQLite here there occurs and MSSQL.
While debugging TSQLDBUniDACConnectionProperties and we have seen how the connection string was processes, we were amazed what actually happens and how the things was handled. Finally, as a cover in the forum I found your discussion that the use of MySQL in UniDAC, only works in theory, but before that the documentation clearly states that mORMot is working with any external databases like NexusDB, DBExpress, FireDAC, AnyDac, UniDAC, BDE etc.
From what I have seen during debugging and the result I've gotten is that in the current version we will never be able to connect to MySQL with UniDAC. That is there to be fixed a lot of things to make it usable.
After a struggle with example 12 - SynDB Explorer, I was able to create the correct ODBC connection string to connect to MySQL.
Now comes the next fun story, years ago I gave up to use ODBC MySQL connector because there were too many errors in the ODBC MySQL connector and it really was not usable. So then we decided to use UniDAC.
And now, after these discoveries on our side again sit several issues on the agenda:
1. To continue the examination of mORMot, since we know so far that it can’t provide us with one of the main features, ORM for MySQL with UniDAC?
2. To continue the examination of mORMot, using the PostgreSQL with UniDAC, for which I also saw in the forum mentioning similar complaints, also this which will be a major challenge for acquaintance it as a database and again the lack of visual examples of how to use mORMot with PostgreSQL?
3. To abandon Delphi as an option for development and move to .Net or Mono? I personally do not like .Net and its alternative Mono, but my other colleagues which are not familiar with Delphi, but have some though little experience with C # and its MVC, ORM Framework, don’t have the same opinion as me. Their opinion is clear - there is no documentation and no real examples for mORMot work with external databases, while in C # and MVC, ORM Framework for the C# are available.
4. My colleagues still have not given up completely. They asked me to give them a drive somehow mORMot to work with MySQL, to be able to continue to view other things promised in the documentation of mORMot whether they are true or not.

Ways that we can take from here are several:
1. Development of own Framework for Delphi, to provide all our needs described in the documentation of mORMot - now that we have a vast experience with the creation of Framework for PHP and know very well how much time we took, we certainly will not invest in that endeavor starts from the zero with Delphi.
2. Using a combination of other products for Delphi, which are commercial and much to our surprise, the documentation and the examples which they provided was also absolutely not usable for various reasons (if the examples are for their older versions, if the examples are valid for the current versions of Delphi and etc.) - We will not paid for broken or not documented products. That is when you offer paid product, then we expect that it works, has the necessary documentation, etc. and we don’t want to become the top reporters for the paid product because it is full with errors. We have played that game with some demo versions of several products and determined us last year that we will not believe any promises from the product developer. If something works we will buy it, if don’t works then we don’t buy it.
3. Forget about Delphi and however personally I do not like .Net and Mono, to start using them.
4. However, we would prefer another option, which I think would be very beneficial for all of us. We can offer our help to develop mORMot several things:
a. Because anyway to develop this several products for which we must have the functionality that  exists in mORMot, with help from the creator and community of the mORMot, we can overcome our current problems and to create real examples and documentation on how to use mORMot with external databases, documentation and examples for the rest of the functionality mORMot, so that it can become an indispensable Framework for Delphi, and to become a mORMot in the product, without which Delphi programmers can not live.
b. I have seen complaints in the forum for the use of Fossil instead Git (http://synopse.info/forum/viewtopic.php?id=1266). Here we can also help with both the selection and transfer of information from Fossil to Redmine and github. Until less than a year we used to Redmine to organize all of our work and definitely this choice brought us many benefits. Now we are using more serious systems - Jira, Confluence, stash, etc. Because Jira is paid product as it is nice and strong it will not be suitable for this OpenSorce project. As for the use of git, before we move to it, I was convinced that git is not superior who knows how SVN, but it turned out that my error is great. I was just amazed, how many of the situations in which you might appear with SVN and was wondering how to overcome it, are no problem when using git. And when you talk about working together on joint projects with more than a few participants, git excels in times SVN. But to can see git advantages you have to work with it. As you add to this ever-increasing his popularity because of the ease with which it enjoyed and experienced people would say that switching to it will lead to many more happy people.

If you are interest in some of my suggestions, or you would have thought of other things that we can be mutually beneficial, I'm ready to see what we can achieve together.

Pages: 1

Board footer

Powered by FluxBB