Database Centric Application with Lazarus

[allanregistos]

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]

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]

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]

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]

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

[allanregistos]

the 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]

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?

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;

Don'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,unit_price,status_id,dr_number,date_received,loc_id) ' +
        'VALUES (:NEWID,:NEWINKID,:SUPPLIERID,:QTYKG,:QTYPAIL,:UNITPRICE,:STATUSID,:DRNUMBER,:DATECREATED,:LOCID)';
       INK_SQL.Params.ParamByName('NEWID').AsInteger := NewReceiveInkID;
        //the rest of params here
       INK_SQL.ExecSQL;
       INK_TRANSACTION.Commit; 

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

Thank you for your tips and the resources you gave.

Regards,
Allan