Connect to Database using TSQLConnector - Example Class
lazarus / 16-Databases

----------------------------------------------------------------------
goldenfox · 2012-06-14 07:51

Attached is a database app demo which I tried to create using the
materials/documents I found in the wiki and in this forum.

It's is a simple class and is not 100% complete but I hope it will be
useful to all who wish to learn how to access a database using
freepascal.

I'm just beginning to learn Lazarus/FPC so any
criticism/comments/suggestions/code enhancement are valuable. :-)

Code Examples
Create - creates an instance
Code: [Select]var db : TMyDBO;
...
db = TMyDBO.Create(dboSqlite3,'test.db');Code: [Select]db =
TMyDBO.Create(dboMySQL55);
db.Hostname := 'localhost';
db.Database := 'testdb';
db.Username := 'user';
db.Password := 'password';
Select - useful when doing select queries, inserts and updates.
returns TSQLQuery
Code: [Select]db.Select('SELECT * FROM tbl_test');
while not db.ResultSet.EOF do
begin
  writeln(db.ResultSet.FieldByName('name').AsString);
  db.ResultSet.Next;
end;
Execute - executes a query without returning result (true = success,
false = error)
Code: [Select]db.PrepareSQL('INSERT INTO tbl_test
VALUES(:id,:name);');
db.Param('id',1);
db.Param('name','hello world');
if not db.Execute then writeln(db.LastError);
ExecuteScalar - executes a query and return a single result
Code: [Select]babyname := db.ExecuteScalar('SELECT name FROM tbl_test
WHERE id=1');
Destroy - Free an instance
Code: [Select]db.Destroy;
More example
Code: [Select]function GetTrucks(var a : trucks) : boolean;
var db : TMyDBO; i : integer = 0;
begin
  db := TMyDBO.Create(dboSQLite3,'test.db');
  try
    db.Select('select * from trucks');

    with db.ResultSet do
    begin

      SetLength(a, RecordCount);

      while not EOF do
      begin
        a[i].recno   := FieldByName('truckid').AsInteger;
        a[i].truckno := FieldByName('truckno').AsString;
        a[i].driver  := FieldByName('driver').AsString;
        Next;
        inc(i);
      end;

    end;

    result := true;

  except
    on E: exception do
    begin
      LastError := e.Message;
      result := false;
    end;
  end;

  db.Destroy;

end;

Notes:
* Thanks to BigChimp for suggestion using TSQLConnector
* You will need to have the required libraries (e.g. sqlite3.dll) for
the demo to work)

Change Log
JUN 26 2012
* Added new SQLite3 specific mydbo + demo

Github Project Repository - https://github.com/goldenfox/mydbo

Download zip from Github

----------------------------------------------------------------------
BigChimp · 2012-06-14 08:29

Had a quick glance - seems well thought out!

You're creating and deleting a database (test.db) for SQLite; you
could move that as a method to the mydbo unit - and provide that for
Firebird embedded, too.

You could use parametrized queries - which would do all the string
quoting, date quoting etc for you.
Just thinking out loud: e.g. this code:
Code: [Select]db.Execute('insert into test values (' + inttostr(i) +
',' + quotedstr('A-'+datastr) + ');');
would turn to something like this:
Code: [Select]db.ExecutePrepare('insert into test values :testvalue');
db.Params[0]:='A-'+datastr;
db.ExecuteParams;
or perhaps something like
Code: [Select]db.Execute('insert into test values
:testvalue',['A-'+datastr]);
(Nah, the latter wouldn't work if you're mixing e.g. dates etc... hope
some others have better ideas)
However, I noticed you provide a way to add parameters etc in the
mydbo unit. Ok, so perhaps you're just using a simple example here.

For Sybase, you're creating a TMSSQLConnection; you should create a
TSybaseConnection instead (it's in the same mssqlconn unit as the
TMSSQLConnection).

Perhaps you could use TSQLConnector and fill in database type instead
of creating the database specific connectors (TODBCConnection,
TMSSQLConnection etc). Depends on how much database specific
functionality you want to use and which would only be available
through properties for that specific db connector.

You could add some conditional compilation defines depending on
operating system to match availability of connectors on various
platforms (e.g. OSX has no Sybase/SQL Server, IIRC; Win64 has no
Oracle connector etc)

Your insert method 2 seems to insert multiple records. You mention
ODBC doesn't support this. I suspect Firebird won't support this
either, perhaps some more dbs.

----------------------------------------------------------------------
goldenfox · 2012-06-28 03:07

@BigChimp, I have updated the code and integrated some of your
suggestion. I deleted my previous post btw.

Thanks.