asked on

Firebird, Delphi - Need help in an efficient way of updating a table

Hi all,

Well, I am using 2 tables (Players+teams) and at the moment I am using a rather slow process using Listboxes etc to get the result I want. But I think I can manage to achieve it using only FB SQL commands which I would think improves the speed a lot.

What I want to achieve:

I want to select the first 6 names(field is called NAME) where Club = clubchosen.caption and Injury = 0 and Pos = D. No problems doing this using:

SELECT First 6 * FROM SPELARE where klubb = '''+clubchosencaption+'''' and Pos = 'D' and Injury = '0' order by Def DESC;

Here's basically what I need help with:

From those 6 records chosen, the first 2 needs to have its field "Lineup" updated to '1', the following two results(3+4) should have the field "Lineup" set to 2 and finally the last 2 records(5+6) should have the field "Lineup" set to 3.

At the same time, the Players table holds 2 fields called Def and Off (basically the strength of the player)

From the query mentioned above and the 6 records that shows up, the Def and Off(holds a number from 1-20) should be added and the final result should be placed in LineupDef.Caption(for Def field) and LineupOff.Caption(for Off field).

What would be the most efficient way to achieve this, especially the update method where it also needs to do some counting? (The db will only be used locally.. used Paradox before but it was way too slow when I reached 10k records).

Hope you can understand me and bare with my poor english.

Geert G

the fastest way is using calculated fields

the thing i don't see is what you do with the select ...
are you using this only in your program in memory or writing this back into a third table ?

MerlaP83

ASKER

The thing with the Select function is that I want to edit the 6 records(players) with the highest Def skill set. The other records are not to be updated(there are about 30 records per team, only 6 are to be edited).

I am only using this in my program in memory - need to do the calculation to get the team skill, so I need to do a fast calculation.

MerlaP83

ASKER

Forgot to ask what it means with "calculated fields" ? How to use and take advantage of this?

Geert G

a calculated field is a field you add to the dataset
put a query on a form
right click and click edit fields
then you can add fields
you set the type to calculated

then assign the oncalculate event handler
this gets called for each record on loading the data, and you can set the value of your fields to what you want

procedure TForm1.Query1OnCalculate(Dataset: TDataset);
begin
DataSet.FieldByName('TOTAL').AsFloat := Dataset.FieldByName('QUANTITY').AsFloat * DataSet.FieldByName('PRICE').AsFloat;
end;

MerlaP83

ASKER

Sounds good, but didn't get it to work with Zeos? Any good tutorial link for calc?

The main problem I have though is the following:

I have a query that returns, say 5 results and I would like the Off field (number 1-20) from those 5 results to be added to count_off.caption.

with Spelare do
      begin
      Close;
      SQL.Clear;
      SQL.text := 'select first 5 * from spelare where klubb = ''Tomteland'' and Pos = ''F'' order by Off DESC';
      Open;
end;
 
//Adding this, which is probably not that efficient, will only give me the number of the first result's Off field (not the total amount from the returned results).
 
count_off.caption.caption := inttostr(strtoint( count_off.caption) + strtoint(Spelare['Off']));
 
//

Open in new window

rfwoolf

"//Adding this, which is probably not that efficient, will only give me the number of the first result's Off field (not the total amount from the returned results)."
Okay, well then why don't you cycle/iterate through your dataset and add up the off field that way?
var
i : integer;
i := 0;
MyDataset.First;
While MyDataset.eof = false do
begin
i := i + MyDataSet.Fieldbyname('Off').asInteger;
MyDataset.Next;
end;

MerlaP83

ASKER

Thanks, that did the trick; however, my final question before I close and accept it as a solution is how I, at the same time, can update the results?

with Spelare do
      begin
      Close;
      SQL.Clear;
      SQL.text := 'select first 3 skip 3 * from spelare where klubb = ''+nuvklubb.caption+''' and Pos = ''B'' order by Def DESC';
      Open;
end;
i := 0;
Spelare.First;
While Spelare.eof = false do
begin
 i := Spelare.Fieldbyname('Def').asInteger;
 Spelare.sql.text := 'update spelare set platsar = ''2''';
 Spelare.Next;
end;

Open in new window

ASKER CERTIFIED SOLUTION

rfwoolf

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rfwoolf

Hey I see an error in your code that I have repeated above. You say:
i := Spelare.Fieldbyname('Def').asInteger
that should be:
i := i + Spelare.Fieldbyname('Def').asInteger;
otherwise i will not count.

MerlaP83

ASKER

Thanks for all your information and tips, very useful.

Sorry if I've said different things, its because I haven't found the best solution for the query yet. Unfortunately, I do not have any ID for the table.

LineUp has nothing to do with the DB, its just a label.

6 records will be chosen.

First 2 should have its 'Def' attributes added to Line1Def, and their 'Platsar' should be set to '1'.
#3 and #4 should have its 'Def' attribute added to Line2Def, and their 'Platsar' should be set to '2'.
#5 and #6 should have its 'Def' attribute added to Line3Def and their 'Platsar' should be set to '3'.

It needs to be a fast procedure since it will be used a lot in updating lineups for the teams.

//By adding this I will pull the first 2 results of the 6 (seemed like an easy method, but requires a lot of coding (copy/change/paste) and will probably be a little bit slow.
 
with Spelare
do
      begin
      Close;
      SQL.Clear;
      SQL.text := 'select first 2 * from spelare where klubb = ''+nuvklubb.caption+''' and Pos = ''B'' order by Def DESC';
      Open;
end;
i := 0;
Spelare.First;
While Spelare.eof = false do
begin
 i := i + Spelare.Fieldbyname('Def').asInteger;
 Spelare.Next;
end;
Line1Def.Caption := inttostr(i);
 
//Doing the following will give the error "Expression expected but '>' found. Looked like a good solution otherwise though since it will be a lot less coding to go through. But do I have to go with Edit/Post? It's a bit slower, isn't it?
 
  While Spelare.eof = false do
  begin
   if (Spelare.RecNo => 0) and (Spelare.RecNo <= 1) then
   begin

Open in new window

rfwoolf

sorry change => to >=
i forget that it likes to say "..is greater than or equal to" but doesn't like "is equal to or greater than".

rfwoolf

I'm also slightly confused about which are your datasets and which are your table names. I suspect you have a TZTable or TZQuery called "Spealer" and also a table in your database called "Spealer". It's probably my fault, I should read through all of this carefully to understand 100%

MerlaP83

ASKER

I'm sorry, I'm not too easy to understand sometimes with my somewhat poor english :)

You were correct in the fact that I have a TZQuery called "Spelare" and a table in my db called "Spelare".

Finally I got everything settled and working perfectly (A LOT faster than it used to be + a lot less code). Thanks for your patience and excellent help, really appreciated as always rfwoolf.