• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

Thread and select from Database MSSQL2005 Express with ADO

Hello Experts,
Can you help me with such a thing:
I have a select statement from MSSQL 2005 Express here it is:

procedure TForm1.Button3Click(Sender: TObject);
var
czas,czas1,temp1 : String;
temp : Double;
d1,d2,c1,c2 : String;
zlicz_czas,zlicz_czase,zlicz_czasa : Integer;
begin
  DBGrid1.DataSource:=DataSource2;
  czas := TimeToStr(DateTimePicker3.Time);
  czas1 := TimeToStr(DateTimePicker4.Time);
  raportodczas:=czas;
  raportdoczas:=czas1;
  raportoddata:= DateToStr(DateTimePicker1.Date);
  raportdodata:= DateToStr(DateTimePicker2.Date);
  if ComboBox1.Text = 'Wybierz wag...' then
      ComboBox1.Text := '2-8';
  if ComboBox2.Text ='Waga od...' then
      ComboBox2.Text:='-1000';
  if ComboBox3.Text ='Waga do...' then
      ComboBox3.Text:='2000';
  d1:= FormatDateTime('yyyy-MM-dd',DateTimePicker1.DateTime)+' '+'00:00:00';
  d2:= FormatDateTime('yyyy-MM-dd',DateTimePicker2.DateTime)+' '+'00:00:00';
  c1:= '1900-01-01'+' '+FormatDateTime('HH:mm:ss',DateTimePicker3.Time);
  c2:= '1900-01-01'+' '+FormatDateTime('HH:mm:ss',DateTimePicker4.Time);
  waga_nr:= ComboBox1.Text;

//-------------------------------Base query-------------------------------------
     with ADOQuery1, SQL do
      begin
        Close;
        Clear;
        Add('SELECT ID,WAGA,MASA,RATE,DATA,CZAS,RAPORT FROM PRODUKCJA WHERE WAGA=:waga AND MASA >= :masa_od AND MASA <= :masa_do AND DATA >= Convert(DateTime, :data_od) AND DATA <= Convert(DateTime, :data_do) '+
        'AND CZAS >= Convert(DateTime, :czas_od) AND CZAS <= Convert(DateTime, :czas_do) ORDER BY DATA ASC,CZAS ASC');
        Parameters.ParamByName('masa_od').Value := StrToFloat(ComboBox2.Text);
        Parameters.ParamByName('masa_do').Value := StrToFloat(ComboBox3.Text);
        Parameters.ParamByName('waga').Value := ComboBox1.Text;
        Parameters.ParamByName('data_od').Value := d1;
        Parameters.ParamByName('data_do').Value := d2;
        Parameters.ParamByName('czas_od').Value := c1;
        Parameters.ParamByName('czas_do').Value := c2;
        Open;
      end;

//-------------------Sum of the highest values-------------------
     with ADOQuery3, SQL do
      begin
        Close;
        Clear;
        Add('SELECT SUM(MASY_DZIEN.MASA_MAX)FROM (SELECT MAX(MASA) AS MASA_MAX FROM PRODUKCJA WHERE WAGA = :waga AND MASA BETWEEN :masa_od AND :masa_do AND DATA BETWEEN Convert(DateTime, :data_od) AND Convert(DateTime, :data_do)'+
        'AND CZAS >= Convert(DateTime, :czas_od) AND CZAS <= Convert(DateTime, :czas_do) GROUP BY convert(varchar(10), DATA, 105 )) masy_dzien');
        Parameters.ParamByName('masa_od').Value := StrToFloat(ComboBox2.Text);
        Parameters.ParamByName('masa_do').Value := StrToFloat(ComboBox3.Text);
        Parameters.ParamByName('waga').Value := ComboBox1.Text;
        Parameters.ParamByName('data_od').Value := d1;
        Parameters.ParamByName('data_do').Value := d2;
        Parameters.ParamByName('czas_od').Value := c1;
        Parameters.ParamByName('czas_do').Value := c2;
        Open;
        if (ADOQuery3.Fields[0].AsString) <> ''  then
        begin
        temp := StrToFloat(ADOQuery3.Fields[0].AsString);
        temp1:=FloatToStrF(temp,ffNumber,10,3);
        Edit2.Text := temp1;
        raport:= temp1;
        end;
      end;
end;

When I select the data from database the whole app is blocked - I think it`s connected with the huge number of records in database, so I think that i should use threads.
The problem is that i have never made such think.
I think that the best option is to separate the base querry and the highest value querry. If I get such examples I`ll be able to change my app to use the threads as base.
0
Vaalar
Asked:
Vaalar
1 Solution
 
Geert GruwezOracle dbaCommented:
there are some things to keep in mind about using threads and databases:

when you don't want your threads to block your app,
you must get the data in the thread using a different connection
it can be the same connectionstring, except the name of the connection
after getting the data, you need to copy it from the thread to the main thread
to a memory dataset

updatable queries will not work as the query is in memory and not from the same connection

first thing you need is an in memory dataset:
www.devarc.com
they have a lot of commercial products, but the TVirtualTable is a free in memory dataset

as I don't have Delphi here i couldn't compile it,
but this is what it should look like.
Tomorrow, i'll try give a working sample

type
  TForm1 = class(TForm)
    VTDataSet: TVirtualTable;
  private
    fDataThread: TDataThread;
    procedure DataLoaded(aMessage: string; aMessageInfo: integer = 0);
  end;

procedure TForm.ButtonLoadDataClick(Sender: TObject);
var aSelect: string;
begin
  aSelect := 'SELECT ID,WAGA,MASA,RATE,DATA,CZAS,RAPORT FROM PRODUKCJA WHERE WAGA=:waga AND MASA >= :masa_od AND MASA <= :masa_do AND DATA >= Convert(DateTime, :data_od) AND DATA <= Convert(DateTime, :data_do) '+
        'AND CZAS >= Convert(DateTime, :czas_od) AND CZAS <= Convert(DateTime, :czas_do) ORDER BY DATA ASC,CZAS ASC';
  fDataThread := TDataThread.Create(aConnectionString, aSelect,
    VarrArrayCreate([ComboBox2.Text, ComboBox3.Text, ComboBox1.Text, d1, d2, c1, c2]), DataLoaded);
end;

procedure TForm1.DataLoaded(aMessage: string; aMessageInfo: integer = 0);
begin
  VTDataset.Active := True;
  VTDataset.Assign(fDataThread.Dataset);
end;
type
 
  TCallbackProc = procedure (aMessage: string; aMessageInfo: Integer = 0) of object;
 
  TCallbackThread = class(TThread)
  private
    FCallBack: TCallbackProc;
    FCallbackMsg: string;
    FCallbackMsgInfo: integer;
    procedure SynchedCallback;
  protected
    procedure DoCallback(aMsg: string; aMsgInfo: integer = 0); virtual;
    property Callback: TCallbackProc read FCallback;
  public
    constructor Create(aCallback: TCallbackProc; CreateSuspended: Boolean = False); reintroduce; virtual;
  end;
 
  TDataThread = class(TCallbackThread)
  private
    fConnection: TADOConnection;
    fQuery: TADOQuery;
  protected
    procedure Execute; override;
  public
    constructor Create(aConnectionString: string; aQuery: string; aParams: Variant; aCallback: TCallbackProc);
    destructor Destroy; override;
    property Dataset: TDataset read GetDataset;
  end;
 
{ TCallbackThread }
 
constructor TCallbackThread.Create(aCallback: TCallbackProc; CreateSuspended: Boolean = False);
begin
  inherited Create(CreateSuspended);
  FreeOnTerminate := True;
  FCallback := aCallback;
end;
 
procedure TCallbackThread.DoCallback(aMsg: string; aMsgInfo: Integer = 0);
begin
  FCallbackMsg := aMsg;
  FCallbackMsgInfo := aMsgInfo;
  Synchronize(SynchedCallback);
end;
 
procedure TCallbackThread.SynchedCallback;
begin
  if Assigned(FCallback) then
    FCallBack(FCallbackMsg, FCallbackMsgInfo);
end;
 
constructor TDataThread.Create(aConnectionString: string; aQuery: string; Params: Variant; aCallback: TCallbackProc);
var I: Integer;
begin
  inherited Create(aCallback);
  CoInitialize(nil, 0);
  fConnection := TAdoConnection.Create(nil, 'thread_connection', aConnectionString);  
  fQuery := TADOQuery.Create(nil);
  fQuery.Connection := fConnection;
  fQuery.SQL.Text := aQuery;
  for I := VarArrayLowBound(Params, 1) to  VarrArrayHighBound(Params, 1) do
    fQuery.Parameters[I].Value := Params[I];
end;
 
destructor TDataThread.Destroy; 
begin
  FreeAndNil(fQuery);
  FreeAndNil(fConnection);
  inherited Destroy;
end;
 
procedure TDataThread.Execute;
begin
  try
    fConnection.Connect;
    try
      fQuery.Open;
      DoCallback('Data loaded', 0);
      fQuery.Close;
    finally
      fConnection.Disconnect;
    end;
  except
    on E: Exception do 
      DoCallback(E.Message, 1);
  end;
end;
 
function TDataThread.GetDataset: TDataset;
begin
  Result := fQuery;
end;

Open in new window

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now