Mysql component ?
lazarus / 16-Databases

----------------------------------------------------------------------
thaimann · 2008-09-23 00:31

Is there any current documentation on getting mysql (I am on version
5) working with Lazarus.  The Docs on the web are really out of date.
 I have mucked around and gotten it to work before, but not quite sure
what it was that got it working.  Any help appreciated.

----------------------------------------------------------------------
tpglemur · 2008-09-24 07:34

This should get you started.

Code: [Select]
procedure TForm1.Button1Click(Sender: TObject);
var
  S: String;
begin

  conn := TMySQL50Connection.Create(nil);
  query := TSQLQuery.Create(nil);
  transaction := TSQLTransaction.Create(nil);

  try

    try
      conn.HostName := '127.0.0.1';
      conn.UserName := 'username';
      conn.Password := 'password';
      conn.DatabaseName := 'database';
      conn.Connected := True;

      conn.Transaction := transaction;
      query.DataBase := conn;
      query.SQL.Text := 'select field1 from table1';
      query.Open;

      query.Last;
      S := IntToStr(query.RecordCount) + #13#10;

      query.First;
      while not query.EOF do
      begin
        S := S + query.FieldByName('field1').AsString + #13#10;
        query.Next;
      end;

    finally

      //transaction.Free;
      //query.Free;
      //conn.Free;

    end;

  except
 
    on E: Exception do
      ShowMessage(E.message);
     
  end;

  Memo1.Text:= S;
     
end;

----------------------------------------------------------------------
normanp · 2008-09-24 15:05

Thanks tpglemur
This did get me started. I also did the following though I am not sure
which of these is actually necessary:
Put libmysql.dll in the lazarus.exe directory and in the project's
directory
[Edited... NOT NEEDED AFTER ALL: Placed these components on the form:
TMySql50Connection, TSQLTransaction, TSQLQuery]

The following program now works (running MySQL using Lighty2Go)
EXCEPT that the btnNewClick procedure does not write data back to the
db - it is somewhere in memory and is seen when
clicking the btnFirst then btnNext. What must I do to get the record
written back to the db?
Many thanks.
Code: [Select]
unit unt_db;
//Using a MySQL database
//Example based on:
//http://www.lazarus.freepascal.org/index.php?name=PNphpBB2&file;=view
topic&t;=5761
//from tpglemur on that forum
{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, LResources, Forms, Controls, Graphics, Dialogs,
  mysql50conn, sqldb, StdCtrls;

type

  { TForm1 }

  TForm1 = class(TForm)
    btnTest: TButton;
    btnNext: TButton;
    btnFirst: TButton;
    btnNew: TButton;
    edtPersonID: TEdit;
    edtSurname: TEdit;
    edtDOB: TEdit;
    Memo1: TMemo;
    MySQL50Connection1: TMySQL50Connection;
    SQLQuery1: TSQLQuery;
    SQLTransaction1: TSQLTransaction;
    procedure btnFirstClick(Sender: TObject);
    procedure btnNewClick(Sender: TObject);
    procedure btnNextClick(Sender: TObject);
    procedure btnTestClick(Sender: TObject);
  private
    { private declarations }
    conn : TMySQL50Connection;
    query : TSQLQuery;
    transaction : TSQLTransaction;
    procedure Display;
  public
    { public declarations }
  end;

var
  Form1: TForm1;

implementation

{ TForm1 }

procedure TForm1.btnTestClick(Sender: TObject);
var
  S: String;
begin

  conn := TMySQL50Connection.Create(nil);
  query := TSQLQuery.Create(nil);
  transaction := TSQLTransaction.Create(nil);

  try

    try
      conn.HostName := '127.0.0.1';
      conn.UserName := 'root';
      conn.Password := '';
      conn.DatabaseName := 'test1';
      conn.Connected := True;

      conn.Transaction := transaction;
      query.DataBase := conn;
      query.SQL.Text := 'select * from tperson';
      query.Open;

      query.Last;
      S := IntToStr(query.RecordCount) + #13#10;

      query.First;
      while not query.EOF do
      begin
        S := S + query.FieldByName('surname').AsString + #13#10;
        query.Next;
      end;

    finally

      //transaction.Free;
      //query.Free;
      //conn.Free;

    end;

  except

    on E: Exception do
      ShowMessage(E.message);

  end;

  Memo1.Text:= S;

end;

procedure TForm1.Display;
begin
  edtPersonID.Text := query.FieldByName('PersonID').AsString;
  edtSurname.Text := query.FieldByName('surname').AsString;
  edtDOB.Text := query.FieldByName('dob').AsString;
end;

procedure TForm1.btnFirstClick(Sender: TObject);
begin
  query.First;
  Display;
end;

procedure TForm1.btnNewClick(Sender: TObject);
begin
  query.Append;
  query.FieldValues['PersonID'] := edtPersonID.Text;
  query.FieldValues['surname'] := edtSurname.Text;
  query.FieldValues['dob'] := edtDOB.Text;
  query.Post;
end;

procedure TForm1.btnNextClick(Sender: TObject);
begin
  query.Next;
  Display;
end;

initialization
  {$I unt_db.lrs}
 

end.

----------------------------------------------------------------------
normanp · 2008-09-24 15:40

OK - I have the answers, though maybe I have more than strictly
needed:
This all works fine now:
Code: [Select]
unit unt_db;
//Using an MySQL database
//Example based on:
//http://www.lazarus.freepascal.org/index.php?name=PNphpBB2&file;=view
topic&t;=5761
//from tpglemur on that forum
{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, LResources, Forms, Controls, Graphics, Dialogs,
  mysql50conn, sqldb, StdCtrls;

type

  { TForm1 }

  TForm1 = class(TForm)
    btnTest: TButton;
    btnNext: TButton;
    btnFirst: TButton;
    btnNew: TButton;
    edtPersonID: TEdit;
    edtSurname: TEdit;
    edtDOB: TEdit;
    Memo1: TMemo;
    procedure btnFirstClick(Sender: TObject);
    procedure btnNewClick(Sender: TObject);
    procedure btnNextClick(Sender: TObject);
    procedure btnTestClick(Sender: TObject);
  private
    { private declarations }
    conn : TMySQL50Connection;
    query : TSQLQuery;
    transaction : TSQLTransaction;
    procedure Display;
  public
    { public declarations }
  end;

var
  Form1: TForm1;

implementation

{ TForm1 }

procedure TForm1.btnTestClick(Sender: TObject);
var
  S: String;
begin

  conn := TMySQL50Connection.Create(nil);
  query := TSQLQuery.Create(nil);
  transaction := TSQLTransaction.Create(nil);

  try

    try
      conn.HostName := '127.0.0.1';
      conn.UserName := 'root';
      conn.Password := '';
      conn.DatabaseName := 'test1';
      conn.Connected := True;

      conn.Transaction := transaction;
      query.DataBase := conn;
      query.ParseSQL := true; //to allow updates
      query.ReadOnly := false; //to allow updates
      query.SQL.Text := 'select * from tperson';
      query.Open;

      query.Last;
      S := IntToStr(query.RecordCount) + #13#10;

      query.First;
      while not query.EOF do
      begin
        S := S + query.FieldByName('surname').AsString + #13#10;
        query.Next;
      end;

    finally

      //query.Free;
      //conn.Free;

    end;

  except

    on E: Exception do
      ShowMessage(E.message);

  end;

  Memo1.Text:= S;

end;

procedure TForm1.Display;
begin
  edtPersonID.Text := query.FieldByName('PersonID').AsString;
  edtSurname.Text := query.FieldByName('surname').AsString;
  edtDOB.Text := query.FieldByName('dob').AsString;
end;

procedure TForm1.btnFirstClick(Sender: TObject);
begin
  query.First;
  Display;
end;

procedure TForm1.btnNewClick(Sender: TObject);
begin
  query.Append;
  query.FieldValues['PersonID'] := edtPersonID.Text;
  query.FieldValues['surname'] := edtSurname.Text;
  query.FieldValues['dob'] := edtDOB.Text;
  query.Post;
  query.ApplyUpdates; //to apply updat
  transaction.Commit;
end;

procedure TForm1.btnNextClick(Sender: TObject);
begin
  query.Next;
  Display;
end;

initialization
  {$I unt_db.lrs}
 

end.

----------------------------------------------------------------------
thaimann · 2008-09-24 23:11

I don't think you understood my problem.  I know how to use the
components and have done so on my desktop.  It's getting the
components installed and working is my problem.  In the past I have
used the docs that say to put links to the mysql.so files all the
place.  But that hasn't been updated since early mysql 4.0 and I can't
get it to work on my laptop.  Everytime I try to link to a database I
get the ubiquitous:

 Can not load library "libmysqlclient.so" Please check you
installation.

----------------------------------------------------------------------
tpglemur · 2008-09-25 02:26

Thanks for posting your code normanp. It helped me as well.

----------------------------------------------------------------------
normanp · 2008-09-26 19:00

thaimann and tpglemur
I have posted an improved version of the above and a version that uses
the components dropped onto the form in the Lazarus Wiki, Tutorials,
Lazarus Database Tutorial as section 3 (hope it's still there!).

thaimann - try using my components on the form version and then adapt
it. You have some property set wrong somewhere or are doing things in
the wrong order. I got messages like yours while messing around trying
to get this to work. Those messages about .so files are misleading I
think and go away when the app is written right!

For me it's a Hello World moment - once something works I can proceed
from there!

I'm not sure when (or if) I should free the objects in the first
version (that Creates the components). I am still experimenting...
mustn't have memory leaks.

I don't like the TDBNavigator - hard for the user to understand and
seems to cause errors (almost certainly my fault). However with
query.Prior etc who needs it?

----------------------------------------------------------------------
thaimann · 2008-09-27 04:49

Ok,

I copied the directory with my toy program on it to a memory stick and
took it over to my desktop.  It compiled, it ran. It connected to the
database.  End of story.
Now my laptop is running FC8 while my desktop is running FC7,  Both
are running MySql 5.0.45.  The desktop has Lazarus 0.9.22, while the
laptop has 0.9.24.
So lets go back to the beginning and ask what exact steps do I need to
do to configure Lazarus so that it can talk to Mysql?  And what we
really need is more up to date documentation.  And lets be honest, the
docs for Lazarus stinks.  I get most of my docs from books on Delphi,
in fact I mostly do Delphi, but I have an app that I would like to
move to Linux and I don't want to do c++.

----------------------------------------------------------------------
normanp · 2008-09-27 11:05

Hi thaimann
Glad you got something working. I have been experimenting with Laz on
XP and the MySql is part of Lighty2Go on the same machine + I have
only been using Lazarus for about 2 weeks ...
I did not configure Lazarus at all. I just created the prog as above
and messed about with it until it worked - that's all!
This makes me think that your probs are outside Lazarus - for example
a firewall problem. Port 3306 needs to be open for MySQL (unless you
change this in MySQL's setup). Maybe you could check if a MySQL client
(I use sqlyog on xp) can talk with the MySQL dbms on your laptop?
Sorry not to be more helpful.
I agree about docs for Lazarus - I suppose it is still beta so that is
some excuse... also I suppose that there is lots out there for Delphi
which applies to Lazarus. Personally I think that for Lazarus to
really take off it will need a well-indexed set of core documentation
with examples. I am trying to contribute on the wiki - perhaps you
could do the same. For example it took me days to find out that you
need {$ASMMODE intel} above a section of inline assembler!

----------------------------------------------------------------------
thaimann · 2008-09-27 12:41

I can access my mysql databases from Delphi apps.  Delphi & the apps
both are running under wine.  I also have a test Wiki on my laptop
which uses mysql.

----------------------------------------------------------------------
normanp · 2008-09-28 09:06

Hmmm. I suppose the logged on user has rw access to the Lazarus
directory? I noticed that during compilation Laz needs to write to a
dir in the components directory (I discovered this when I put it in a
ro directory for my students - they could use laz except when they
came to try the MySQL components). Try checking the permissions...
It would be useful if a Lazarus expert could comment please - do we
have any out there who watch this forum???