Recent

Author Topic: DB Application design  (Read 1040 times)

luca

  • Jr. Member
  • **
  • Posts: 57
DB Application design
« on: November 03, 2014, 04:48:08 pm »
In my app I use DataModule to centralize db Access. In this DataModule I've inserted some queries with connection ad transaction configured but SQL string empty.

I use these queries to execute SQL statement in my code.

At the beginning all is fine but due to the increase of code lines I risk to call a procedure that use same query object as the caller one. This provokes strange bugs because I open the query, call another procedure that use same query and when return back, caller proc cannot access data anymore because query has been changed.

In my mind I'm thinking to create queries on the fly, use it and destroy at the end of the procedure.

Do you have some suggestion to improve my code maintenance?

Regards
Luca


kpeters58

  • Sr. Member
  • ****
  • Posts: 267
Re: DB Application design
« Reply #1 on: November 03, 2014, 05:48:56 pm »
I do this all the time (though I use also use design time queries concurrently). I wrote a complete (for my purposes) library with functions like the example one below. So far that has been working very well for me without any noticeable speed degradation.


Code: [Select]
function GetValue(Conn: TSQLConnector; const Expr, ReturnFieldname: String): String;  // expects a query for a unique key
var
  query: TSqlQuery;

begin
  Result := '';
  try
    try
      DebugMessage('Lib.GetValue -> Expression prior to execution:' + #13 + Expr);
      //
      query             := TSqlQuery.Create(nil);
      query.DataBase    := Conn;
      query.Transaction := (query.Database as TSQLConnection).Transaction;
      query.SQL.Text    := Expr;
      query.Open;
      //
      if (query.RecordCount > 0) then
        Result := query.FieldByName(ReturnFieldname).AsString;
      //
      query.Close;
    except
      on E:Exception do
      begin
        Result := '';
        MessageDlg('Lib.GetValue -> Failed to execute SQL statement:' + #13 + Expr + #13 + E.Message + #13 + E.ClassName, mtError, [mbOK], 0);
      end;
    end;
  finally
    if Assigned(query) then
      query.Free;
  end;
end;
Lazarus 2.0.4/FPC 3.0.4/Win 64

 

TinyPortal © 2005-2018