Database Centric Application with Lazarus
lazarus / 16-Databases

----------------------------------------------------------------------
allanregistos · 2014-04-02 00:39

Hi all,

I am now on the stage of using Lazarus mainly for my database centric
applications(previously VB.Net).  I read somewhere in this forum that
the best way to program a client/server app is to separate the UI and
the business logic into two separate modules.  But I have no way in my
current capacity to do this. So I used Lazarus mainly for UI and SQL
queries. This will work for me for the time being.

I am using these DB controls: SQLQuery, Datasource, SQLTransaction,
PQConnection to connect to PostgreSQL and TDBGrid for viewing.

My question is, what would be the best method/ways/techniques to use
these controls?

I mean, I have this error lately: Access Violation, when I tried to
use the above tools, use the SQLQuery1.SQL ='myquery' during runtime
works fine, but when I try to use the SQLQuery1.AfterScroll event to
capture data from TDBGrid, it will generate Access Violation Error.

The nature of my application is to connect to PostgreSQL, perform
database queries,inserts, etc., and create reports.
In my insert and query(eg. querying for ID) I used the DB tools and
create them during runtime by:
Code: [Select]procedure InitializeDB();
 begin
  INK_DB := TPQConnection.Create(nil);
  INK_SQL := TSQLQuery.Create(nil);
  INK_TRANSACTION := TSQLTransaction.Create(nil);
  INK_DATASOURCE := TDatasource.Create(nil);
  if not INK_DB.Connected then
  begin
    INK_DB.HostName := DATABASE_HOST;
    INK_DB.DatabaseName := DATABASE_NAME;
    INK_DB.UserName := DATABASE_USER;
    INK_DB.Password := DATABASE_PASSWORD;
    INK_DB.Connected := True;
  end;
 end;
Then free them after every use:
Code: [Select]procedure FreeDB();
begin
  INK_SQL.Close;
  INK_DB.Close;
  INK_TRANSACTION.Active := False;
  INK_DB.Free;
  INK_TRANSACTION.Free;
  INK_SQL.Free;
end;
In other words, I just want to solicit advices and ideas on how to
design my application using Lazarus with the mentioned DB tools that
can point me to resources and help me avoid complexities in the future
before going deep with Lazarus.

Regards,
Allan

----------------------------------------------------------------------
mangakissa · 2014-04-02 08:52

The first way to separate your data from GUI is using a Datamodule.
The most nonGUI databasecomponents are put there. You can call the
da]"]>Blockeddule via global variable or call it as an object.
Code: [Select]interface

uses datamodule1;

type TForm1 =  class(TForm);

private
  fDatamodule : YourDatamodule;
end;

implementation

procedure TForm1.Create(Sender : TObject);
begin
  fDatamodule := YourDatamodule.create(self);
end;
[code]
The second way is to create a formless class, which communicates with
your form and datamodule.

There are several frameworks available, but a simple one is easy to
create.

----------------------------------------------------------------------
karaba · 2014-04-02 10:19

the access violation is due to 2 main reasons.
1) something is not ready for use (haven't been created yet)
2) Accessing something after it was freed-deleted.

If you get those inside the SQLDB suit then try to create a minimal
example application that demonstrates your problem and create a bug in
the bug tracker. It will help the team iron out any synchronization
problems they might have and solidify the library. Also it would be in
your best interest to post here as well there are a couple of people
that will try find what goes wrong and help out with a work around.

When it comes to data access component it would be beneficial to
provide the SQL to create the tables used along with a couple of demo
rows ee run a update sql to change the names/addresses etc on an
existing database and export around 10 rows of each table (the
required for the demo ones only, don't export data from all the
tables) in to insert sql commands would be more than enough to show
case your problem.

----------------------------------------------------------------------
BigChimp · 2014-04-02 14:20

http://wiki.lazarus.freepascal.org/SQLdb_Tutorial1#Link_the_components

After a quick read: in your example code you did not set the
connection's transaction property to the transaction
... or the query's database property to the conneciton
... or the datasource's dataset property to the query.

but perhaps you do that after your InitializeDB procedure?

Don't know if you're using starttransaction and commit or
commitretaining for your transaction.

see also
http://www.freepascal.org/docs-
html/fcl/sqldb/tsqlconnection.transaction.html
and related documentation

Note: the sqldbtutorial series may be worth a look at if you haven't
already

----------------------------------------------------------------------
allanregistos · 2014-04-03 04:50

Thank you Karaba, mangakissa, Bigchimp, I will certainly look into
this and provide more sample code when possible.

----------------------------------------------------------------------
allanregistos · 2014-04-04 02:49

Quote from: karaba on April 02, 2014, 12:19:09 pmthe access violation
is due to 2 main reasons.
1) something is not ready for use (haven't been created yet)

This is it.  I set the SQLQuery opened before assigning a datasource
to the datagrid.
Fixed:
Code: [Select]InkGrid.DataSource := Datasource1;
SQLQuery1.Open;         

Access violation happened because I did not yet assigned a Datasource
to the TDBGrid, and in SQLQuery's Afterscroll event, I tried to access
TDBGrid's data.

Thank you Karaba for the tips...

----------------------------------------------------------------------
allanregistos · 2014-04-04 06:39

Quote from: BigChimp on April 02, 2014, 04:20:28 pmhttp://wiki.lazarus
.freepascal.org/SQLdb_Tutorial1#Link_the_components

After a quick read: in your example code you did not set the
connection's transaction property to the transaction
... or the query's database property to the conneciton
... or the datasource's dataset property to the query.

but perhaps you do that after your InitializeDB procedure?
Yes.. I now transferred it to InitilizeDB proc:
Code: [Select]procedure InitializeDB();
 begin
  INK_DB := TPQConnection.Create(nil);
  INK_SQL := TSQLQuery.Create(nil);
  INK_TRANSACTION := TSQLTransaction.Create(nil);
  INK_DATASOURCE := TDatasource.Create(nil);
  if not INK_DB.Connected then
  begin
    INK_DB.HostName := DATABASE_HOST;
    INK_DB.DatabaseName := DATABASE_NAME;
    INK_DB.UserName := DATABASE_USER;
    INK_DB.Password := DATABASE_PASSWORD;
    INK_DB.Connected := True;
    INK_SQL.database := INK_DB;
    INK_TRANSACTION.DataBase := INK_DB;
    INK_TRANSACTION.Active := True;
    INK_DATASOURCE.DataSet := INK_SQL;
    INK_SQL.Transaction := INK_TRANSACTION;
  end;
 end;
Quote from: BigChimp on April 02, 2014, 04:20:28 pmDon't know if
you're using starttransaction and commit or commitretaining for your
transaction.
 Not using starttransaction, for I think that this is not needed since
I am only dealing with a single table.
Code to insert and commit:
Code: [Select]InitializeDB();
     INK_SQL.SQL.Text :=
        'INSERT INTO ink_inv(id,ink_id,supplier_id,qty_kg,qty_pails,un
it_price,status_id,dr_number,date_received,loc_id) ' +
        'VALUES (:NEWID,:NEWINKID,:SUPPLIERID,:QTYKG,:QTYPAIL,:UNITPRI
CE,:STATUSID,:DRNUMBER,:DATECREATED,:LOCID)';
       INK_SQL.Params.ParamByName('NEWID').AsInteger :=
NewReceiveInkID;
        //the rest of params here
       INK_SQL.ExecSQL;
       INK_TRANSACTION.Commit;  Quote from: BigChimp on April 02,
2014, 04:20:28 pmsee also
http://www.freepascal.org/docs-
html/fcl/sqldb/tsqlconnection.transaction.html
and related documentation

Note: the sqldbtutorial series may be worth a look at if you haven't
already

Thank you for your tips and the resources you gave.

Regards,
Allan