DB Application design
lazarus / 16-Databases

----------------------------------------------------------------------
luca · 2014-11-03 15:48

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 · 2014-11-03 16:48

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;