Problem with connecting to MariaDB 5.5
lazarus / 16-Databases

----------------------------------------------------------------------
tormods · 2014-04-21 19:37

I had a problem with connecting to MariaDB 5.5 using
TMySQL55Connection. First problem was missing libmysql.dll
I got a lot of suggestions from the community, to make it short:
placing the file in sysWOW64 did the trick since
my PC is 64bit running windows 8.
Now I am trying to get some data from the database, but have no
success. This is my code:

procedure TSkrDatabaseConnector.__SkrDBConnect( aOwner : TComponent );

begin
  _ConnectState           := false;
  _Connector              := TMySQL55Connection.Create(aOwner);
  _Connector.HostName     := _DBHostName; // 127.0.0.1
  _Connector.UserName     := _DBUserName; // root
  _Connector.Password     := _DBPassword; // abc
  _Connector.DatabaseName := _DBDatabaseName; // skrcontractlogger
  _Connector.Port         := _DBPort; // 3333
  _Connector.CharSet      := 'utf8';
  _Connector.Open;
  if (_Connector.Connected) then begin // program comes here setting
_ConnectState to true
    _ConnectState         := true;
    _Transaction          := TSQLTransaction.Create(aOwner); // set up
transaction
    _Transaction.Database := _Connector;
    _Query                := TSQLQuery.Create(aOwner); // set up sql
query
    _Query.Database       := _Connector;
    _Query.Transaction    := _Transaction;
    _DataSource           := TDataSource.Create(aOwner); // set up
data source
    _DataSource.DataSet   := _Query;
  end else _Connector.Free;
end;

function TSkrDatabaseConnector.__SkrDBQuery(aSQLText : string) :
TSQLQuery; // do query

begin
  _Connector.Open;
  _Transaction.StartTransaction;
  _Query.SQL.Text := aSQLText;
  _Query.Open;
  Result := _Query;
  _Query.Close;
end;

// code above in a separate object, this is calling code

...
_Connect := TSkrDatabaseConnector.Create(Form1,Form1,400,200,
                                             'localhost',
                                             'SkrContractLogger',
 // calling constructor                      'root',
 // which calls __SkrDBConnect               'abc',
                                             3333);
    _Grid := TDBGrid.Create(Form1); // set up grid
    _Grid.DataSource := _Connect.dbDataSource;
    _Grid.Visible := true;
    _Grid.Enabled := true;
    _Grid.Parent := Form1;
    query := _Connect.__SkrDBQuery('SELECT * FROM users');
                                                           
Everything compiles OK and runs well - but no data shows, the table
users contain rows of users with
names, e-mailaddresses etc. I am running Lazarus and MariaDB both in
32 bit versions on a 64 bit PC with
Windows 8. I tried to collect recordlength and got the value 10, which
is default. Tried to move to last
record to get number of records in db - program terminated on an
exception, what is wrong?  Is there any
way to test results from connection.

----------------------------------------------------------------------
eara · 2014-04-21 22:13

1. have you tried this code with other db ?
2. in function TSkrDatabaseConnector.__SkrDBQuery(aSQLText : string) :
TSQLQuery
Code: [Select] _Query.Open;
  Result := _Query;
 _Query.Close; // <<<<<<<<<<<<<<<<<
seems that you return a "closed" query....

----------------------------------------------------------------------
tormods · 2014-04-22 07:04

I have searched for problems elsewhere, closing the Query was not a
good idea - it now works as it should ... Thank you

----------------------------------------------------------------------
eara · 2014-04-22 09:33

seems you thought "lets get the resultset (like in ado) and close the
resource in order not to waste db resources..."
well the resultset is the query... so closing it is made it useless.
Also, recordcount was partial calculated (in my cases if recordcount
is not right, so i do an .Last after open and then return to the
current pos in order to calculate the right recordcount, but this is
resource consuming and should be avoided if not needed i think)...

happy that i helped...