MySql and Delphi direct connection

i have been looking and using for years many tools, free and paid.

1st free is weak or difficult, at least u need too much code to do simple tasks which can be done in one or two functions in paid tools.

Paid tools i have used Devart MyDAC and microOLAP for MySQL which are the top rated tools in this area. i used mydac for around 2 years, but lately i moved to MicroOLAP DAC for MySQL as a better alternative for many reasons

• microlap mydac is much smaller, much easier to maintain and install and to use, mydac is much bigger details and many of these details and properties give unclear errors when used in some wrong way. since microolap is smaller, it is almost error free, and easier to use.
• mydac has a unicode issue in TmyDump vcl component, which repeatly appear and fixed in next update and reappear in the next one, fixed in the next and so on. this bug creats a nonunicode backup file containing unicode data, which when restore with the fixed version will cause wrong unicode conversion and damage of data, MicroOLAP tool has no issue with that with its similar component and smaller amount of details.
• i believe simplicity is much better than complications, easier, and much bug free. that is main reasons for me to convert to DAC for MySQL from MicroOLAP.

that is my simple experiement with this important basic subject for any serious database busniness developer.

i tried also the new FireDAC, i highly discourage to use it, too much complications, difficult unicode handling, difficult to deploy.

Have luck.

You can use dbExpress. The only thing you will need is libmysql.dll from 5.1.X server.

I have been using MyDac since years, which is one of best DAC components for Delphi.

AFAIK it's the only native component that offer Direct Connection to MySql (No ODBC, No OLEDB, No libmysql.dll).

This is what I use in Delphi XE4 (works in previous versions too). It creates components during runtime. Note: if you want to create databases, the default MySQL database 'mysql' needs to be used. Make sure you check if you have access to it, put try..except..end; in your code. And yes, it requires having dbxmys.dll and libmysql.dll in the same folder as your *.exe. This video might give you some hints http://www.youtube.com/watch?v=6mRGAB4LsEE

unit MainUnit;

interface

uses
  System.SysUtils, System.Types, System.UITypes, System.Rtti, System.Classes,
  System.Variants, FMX.Types, FMX.Controls, FMX.Forms,
  FMX.StdCtrls, Data.DBXMySQL, FMX.Edit, Data.DB, Data.SqlExpr, FMX.Dialogs, Windows,
  Data.FMTBcd, FMX.Layouts, FMX.Memo, FMX.ListBox, FMX.ListView.Types,
  FMX.ListView;

type
  TForm3 = class(TForm)
    Button1: TButton;
    Label1: TLabel;
    Edit1: TEdit;
    Memo1: TMemo;
    ListBox1: TListBox;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    MySQLConnection: TSQLConnection;
    MySQLQuery: TSQLQuery;
    Function ConnectToMySQLDatabase(szHostName, szUserName, szPassword, szDatabaseName: String): boolean;
  end;

var
  Form3: TForm3;

implementation

{$R *.fmx}


procedure TForm3.Button1Click(Sender: TObject);
begin
  if ConnectToMySQLDatabase('localhost', 'root', 'passw_ord', 'table_name') = False then
    Caption := 'Not Connected'
  else
    begin
      Caption := 'Connected';
      try
        MySQLQuery.SQL.Clear;
        {MySQLQuery.SQL.Add('insert into table_name(vardas_pavarde, asmens_kodas, kodas, pazym_nr, registravimo_data, '+
          'data_nuo_kada_taikomas, isregistravimo_data, negalioja_nuo, paskelbimas_negaliojanciu, priezastis, pastabos) '+
          'values ("Edijs Test", "3001000", "38", "PazPK122", "2013.05.03", "2013.06.01", NULL, NULL, NULL, "Tuščia", '+
          '"ąčęėįšįųūž");');}
        MySQLQuery.SQL.Add('select * from table_name where vardas_pavarde="edIJS tEst";');
        MySQLQuery.Open;
        Memo1.Lines.Add(VarToSTr(MySQLQuery['vardas_pavarde']));
        Memo1.Lines.Add(VarToSTr(MySQLQuery['asmens_kodas']));
        Memo1.Lines.Add(VarToSTr(MySQLQuery['pastabos']));
        MySQLQuery.Close;
      except
        on E: Exception do
            MessageBox(0, PWideChar(E.Message), 'Error', MB_ICONERROR);
      end;
    end;
end;

Function TForm3.ConnectToMySQLDatabase(szHostName, szUserName, szPassword, szDatabaseName: String): boolean;
begin
  MySQLConnection := FindComponent('MySQLConnection') as TSQLConnection;
  if not Assigned(MySQLConnection) then
    MySQLConnection := TSQLConnection.Create(Self);
  MySQLConnection.DriverName := 'MySQL';
  MySQLConnection.GetDriverFunc := 'getSQLDriverMYSQL';
  MySQLConnection.LibraryName := 'dbxmys.dll';
  MySQLConnection.VendorLib := 'LIBMYSQL.dll';
  MySQLConnection.Params.Values['HostName'] := szHostName;
  MySQLConnection.Params.Values['Database'] := szDatabaseName;
  MySQLConnection.Params.Values['User_Name'] := szUserName;
  MySQLConnection.Params.Values['Password'] := szPassword;
  MySQLConnection.Params.Values['ServerCharSet'] := 'utf8';
  MySQLConnection.LoginPrompt := False;
  try
    MySQLConnection.Connected := True;
    MySQLQuery := FindComponent('MySQLQuery') as TSQLQuery;
    if not Assigned(MySQLQuery) then
      MySQLQuery := TSQLQuery.Create(Self);
    MySQLQuery.SQLConnection := MySQLConnection;
    Result := True;
  except
    on E: Exception do
    begina
      MessageBox(0, PWideChar(E.Message), 'Error', MB_ICONERROR);
      Result := False;
    end;
  end;
end;

end.

Use DB Express with MySQL 5.x in Delphi 7 See this link

http://www.justsoftwaresolutions.co.uk/delphi/dbexpress_and_mysql_5.html