Solved

Selecting 1 row(rand) and updating it (Firebird+Delphi)

Posted on 2009-04-12
20
1,792 Views
Last Modified: 2013-12-09
Hello,

I have a query that picks out 1 random result from a table based on different factors; however, after that result is picked the field "Klubbint" needs to be updated to intklubb.caption.

How to achieve this?

Tried some different methods, but failed to succeed and often got the msg "cannot update a complex query with more then one table." Any ideas(using Firebird)?
spelare.sql.Clear;
Spelare.sql.add('select First 1 S.*, L.* from Lag L join Spelare S on L.Klubb = S.Klubb');
Spelare.Sql.add('where S.Off > 14 and S.Personlighet < 5 and L.Ekonomi > S.Varde+300');
spelare.sql.add('and NOT S.klubb = '''+intklubb.caption+'''  ORDER BY IIF(S.Nation = '''+intland.caption+''', .2, .8) * RAND();');
Spelare.Open;
Spelare.First;

Open in new window

0
Comment
Question by:MerlaP83
  • 10
  • 5
  • 4
  • +1
20 Comments
 
LVL 5

Expert Comment

by:xr1140
ID: 24127164
i think this should work:


spelare.sql.Clear;
Spelare.sql.add('select First 1 S.*, L.* from Lag L join Spelare S on L.Klubb = S.Klubb');
Spelare.Sql.add('where S.Off > 14 and S.Personlighet < 5 and L.Ekonomi > S.Varde+300');
 
spelare.sql.add('and NOT S.klubb = :param1  ORDER BY IIF(S.Nation = :param2, .2, .8) * RAND();');
spelare.sql.Params[0].AsString := QuotedStr( intklubb.caption );
spelare.sql.Params[1].AsString := QuotedStr( intland.caption );
 
Spelare.Open;
Spelare.First;

Open in new window

0
 

Author Comment

by:MerlaP83
ID: 24127212
Thanks for your reply.

Is there something I have to do to make "Params[0]" etc work? As of now, I get "undeclared identifier: 'Params'".
0
 
LVL 5

Expert Comment

by:xr1140
ID: 24127455
oh ... i assumed (wrong) spelare to be a TQuery or a TQuery descendant and beside that I think i`ve misread you question so ignore my previous post.

now is that spelare object will be a TQuery then you can attach a TUpdateSQL to modify the content of the TQuery and only affect the table that you need.

0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:MerlaP83
ID: 24128434
Ah, alright. Never used TUpdateSQL (or since I'm using Zeos/Firebird I assume its ZUpdateSQL).

But not sure how to use it, tried the code below and other solutions but it's not working.
Spelare.UpdateObject := ZUpdateSQL1;
 
if intrykte.caption = '95' then
if inttyp.caption = 'F' then
begin
spelare.sql.Clear;
 
Spelare.sql.add('select First 1 S.*, L.* from Lag L join Spelare S on L.Klubb = S.Klubb');
Spelare.Sql.add('where S.Off > 14 and S.Personlighet < 5 and L.Ekonomi > S.Varde+300');
spelare.sql.add('and NOT S.klubb = '''+intklubb.caption+''' ORDER BY IIF(S.Nation = '''+intland.caption+''', .2, .8) * RAND()');'
 
Spelare.Open;
Spelare.First;
 
ZUpdateSQL1.ModifySQL.Add('update spelare set klubbint = ''' + intklubb.caption+'''');

Open in new window

0
 
LVL 5

Expert Comment

by:xr1140
ID: 24128480
add this lines at the end
//...
 
//ZUpdateSQL1.ModifySQL.Add('update spelare set klubbint = ''' + intklubb.caption+'''');
 
ZUpdateSQL1.ExecSQL(ukModify);
Spelare.ApplyUpdates;
Spelare.Refresh;

Open in new window

0
 

Author Comment

by:MerlaP83
ID: 24128515
That code will unfortunately also give me "Undeclared identifier" on ExecSQL. Tried changing "ZUpdateSQL1.ExecSQL(ukModify); to "Spelare.ExecSQL", but that would only give me "Cannot perform this operation on a closed dataset".
Spelare.sql.add('select First 1 S.*, L.* from Lag L join Spelare S on L.Klubb = S.Klubb');
Spelare.Sql.add('where S.Off > 14 and S.Personlighet < 5 and L.Ekonomi > S.Varde+300');
spelare.sql.add('and NOT S.klubb = '''+intklubb.caption+''' ORDER BY IIF(S.Nation = '''+intland.caption+''', .2, .8) * RAND()');
 
Spelare.Open;
Spelare.First;
 
ZUpdateSQL1.ModifySQL.Add('update spelare set klubbint = ''' + intklubb.caption+'''');
ZUpdateSQL1.ExecSQL(ukModify);
Spelare.ApplyUpdates;
Spelare.Refresh;

Open in new window

0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 24128846
"after that result is picked the field "Klubbint" needs to be updated to intklubb.caption"
Are you trying to do this:
intklubb.caption := Spelare.Fieldbyname('Klubbint').AsString;
or are you trying to do this:
//Query Spelare has already been populated with a result set
Spelare.First;
While Spelare.eof = false do
begin
  Spelare.Edit;
  Spelare.Fieldbyname('Klubbint').value := strtoint(intklubb.caption);
  Spelare.Post;
  Spelare.Next;
end;
Or is Klubbint not part of your original query and you have to update it using a separate Query? In that case you will need an ID to refer to the records you want. You SQL would say something like UPDATE MyTable SET MyTable.Klubbint = ' + intklubb.caption + ' WHERE RecordID = 123
0
 

Author Comment

by:MerlaP83
ID: 24129102
Query has already been populated with a result set (1 record). However, even if I do add that code nothing seems to happen.. the Klubbint field is still empty (even after a refresh).

Intklubb.caption is a string, but changing "value" into "asstring" etc made no difference. I do get an error when using "strtoint" and value, but when correcting this it just seems to skip by the code.
spelare.sql.Clear;
 
Spelare.sql.add('select First 1 S.*, L.* from Lag L join Spelare S on L.Klubb = S.Klubb');
Spelare.Sql.add('where S.Off > 14 and S.Personlighet < 5 and L.Ekonomi > S.Varde+300');
spelare.sql.add('and NOT S.klubb = '''+intklubb.caption+''' ORDER BY IIF(S.Nation = '''+intland.caption+''', .2, .8) * RAND()');
 
Spelare.Open;
Spelare.First;
 
While Spelare.eof = false do
begin
 Spelare.Edit;
 Spelare.Fieldbyname('Klubbint').asstring := intklubb.caption;
 Spelare.Post;
 Spelare.Next;
end;
 
if not Spelare.IsEmpty then
begin
 
main_nyheter.lines.insert(3, '[INTRESSE] '+intklubb.caption+' är intresserade av '+Spelare['FNAMN']+' '+Spelare['ENAMN']+' i '+Spelare['Klubb']+'.');
 
main_nyheter.lines.insert(4, Spelare['FNamn']+' har den här säsongen spelat '+inttostr(Spelare['Matcher'])+' matcher och producerat '+inttostr(Spelare['Mal'])+' mål, '+inttostr(Spelare['Ass'])+' ass (totalt '+inttostr(Spelare['Poang'])+' poäng).');
 
main_nyheter.lines.Insert(5,'');
 
end;

Open in new window

0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 24129357
"However, even if I do add that code nothing seems to happen.. the Klubbint field is still empty (even after a refresh)."
That's strange... Perhaps it is being updated in the database but your dataset isn't refereshing the data properly. You really should have Primary Keys and IDs in your tables so that you can update records that way in some cases. Also in some cases Query's can return read-only resultsets which would definitely prevent you from editing records. I don't think that's the case here because you would get an error.
0
 

Author Comment

by:MerlaP83
ID: 24130480
Found that one of the problems I had was that I had ZUpdateSQL.

But now that I have removed that line and tried your solution, I get the message "Cannot update a complex query with more then one table".

//Using
if intrykte.caption = '95' then
if inttyp.caption = 'F' then
begin
spelare.sql.Clear;
 
Spelare.sql.add('select First 1 S.*, L.* from Lag L join Spelare S on L.Klubb = S.Klubb');
Spelare.Sql.add('where S.Off > 14 and S.Personlighet < 5 and L.Ekonomi > S.Varde+300');
spelare.sql.add('and NOT S.klubb = '''+intklubb.caption+''' ORDER BY IIF(S.Nation = '''+intland.caption+''', .2, .8) * RAND()');
 
Spelare.Open;
Spelare.First;
 
While Spelare.eof = false do
begin
 Spelare.Edit;
 Spelare.Fieldbyname('Klubbint').asstring := intklubb.caption;
 Spelare.Post;
 Spelare.Next;
 end;

Open in new window

0
 
LVL 5

Expert Comment

by:xr1140
ID: 24130986
i`ve managed to do exactly what you want with a TQuery and a TUpdateSQL. I don`t have Zeos installed so i can`t help you the exact syntax ... look in the help of ZUpdateSQL to find out the proper way to call it.
0
 

Author Comment

by:MerlaP83
ID: 24131863
I know.. I have pretty much the same query for other things in my code, and they are working.

Can't get by this error though - and when I do, well it doesnt update the table :/ Any ideas?

Posted all code from that button:
var i,bajs,x: integer;
begin
Randomize;
 
bajs := Random(10);
antalskadadespelare.caption := inttostr(bajs);
 
for x := 1 to (strtoint(antalskadadespelare.caption)) do begin
 
i := Random(2);
if i = 0 then
inttyp.caption := 'F'
else if i = 1 then
inttyp.caption := 'T';
 
Lag.close;
Lag.sql.Clear;
Lag.sql.add('select First 1 * from Lag where Rykte = ''95'' and NOT Division = ''AHL'' order by rand()');
Lag.Open;
Lag.First;
intklubb.caption := Lag['Klubb'];
intrykte.caption := Lag['Rykte'];
intland.caption := Lag['Nation'];
 
if intrykte.caption = '95' then
if inttyp.caption = 'F' then
begin
 
spelare.close;
spelare.sql.Clear;
 
Spelare.sql.add('select First 1 S.*, L.* from Lag L join Spelare S on L.Klubb = S.Klubb');
Spelare.Sql.add('where S.Off > 14 and S.Personlighet < 5 and L.Ekonomi > S.Varde+300');
spelare.sql.add('and NOT S.klubb = '''+intklubb.caption+''' ORDER BY IIF(S.Nation = '''+intland.caption+''', .2, .8) * RAND()');
 
Spelare.Open;
Spelare.First;
 
While Spelare.eof = false do
begin
 Spelare.Edit;
 Spelare.Fieldbyname('Klubbint').asstring := intklubb.caption;
 Spelare.Post;
 Spelare.Next;
 end;
end;
end;

Open in new window

0
 
LVL 13

Accepted Solution

by:
rfwoolf earned 250 total points
ID: 24133183
So are you getting that error about complex update or something like that? What I think is happening is this: in some databases you can't send an UPDATE statement(s) with SELECT statements -- the one is to request data the other is to set data. Your Zeoslib components do everything using SQL behind the scenes. I'm guess that when you try to update your table the SQL that it sends is too complex.
So... your best bet is to change the Klubbint field using a separate update query, instead of editing the dataset.. I also think that this will be the "best practise" way of doing things because best practise is to not post to a dataset while you're busy iterating/cycling through it (because your dataset will have an index and for example if you change a record that was at the beginning, and the index now says it should go to the end, it will change the order of your records).
So, you will need to do separate SQL update statements. You will need a primary key / ID to refer to the record(s) you want to update.

I do not know the field structure of your table. Does it have a primary key / ID field? and if not can you create one?
0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 24133228
If you really can't create a primary key, then the WHERE part of your UPDATE statement will just have to be really long, listing all of your fields, something like this:
UPDATE myTable SET MyTable.KlubbInt = " + intklubb.caption
+ ' WHERE MyTable.Field1 = ' + Spelare.Fieldbyname('Field1').Asstring
+ ' and WHERE MyTable.Field2 = ' + Spelare.Fieldbyname('Field2').Asstring
+ ' and WHERE MyTable.Field3 = ' + Spelare.Fieldbyname('Field3').Asstring
+ ' and WHERE MyTable.Field4 = ' + Spelare.Fieldbyname('Field4').Asstring
+ ' and WHERE MyTable.Field5 = ' + Spelare.Fieldbyname('Field5').Asstring
+ ' and WHERE MyTable.Field6 = ' + Spelare.Fieldbyname('Field6').Asstring
etc etc etc

By the way my UPDATE SQL might not be 100% but at least you get the idea.
As long as the combination of fields in your WHERE statement will make sure that the correct record is updated then it will work. It is not best practise at all. This is the reason why they teach you to create an unique ID for every record called the Primary Key. You might have 2 players with the same name, so you need a way to identify each record by itself.
I hope some of this helps
0
 

Author Comment

by:MerlaP83
ID: 24136256
Thanks for your effort and help.

My table doesn't have any Primary key / ID, but I am trying to create one using Firebird Maestro. I have had problems with 2 players having the same name, so I feel that I really do need some kind of unique ID/Key.

Tried creating it using triggers/generator and the code below(created by FB Maestro and according to http://www.firebirdsql.org/manual/generatorguide-rowids.html it should work), but I get the error:

"SQL Error:  unsuccessful metadata update DEFINE GENERATOR failed attempt to store duplicate value (visible to active transactions) in unique index "RDB$INDEX_11". Error Code: -607. This operation is not defined for system tables. The SQL: /* Autoincrement for field (ID) */
CREATE GENERATOR GEN_SPELARE_ID;"
/* Autoincrement for field (ID) */
CREATE GENERATOR GEN_SPELARE_ID;
 
SET TERM ^ ;
 
CREATE TRIGGER SPELARE_BI FOR SPELARE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
  NEW.ID = GEN_ID(GEN_SPELARE_ID,1);
END^
 
SET TERM ; ^

Open in new window

0
 

Author Comment

by:MerlaP83
ID: 24136263
Or do I get this message because I already have lots of records (since its "active before Insert" and therefore it will not change my current records?)
0
 

Author Comment

by:MerlaP83
ID: 24136469
Managed to set an ID for each record now using a generator and "update Spelare set ID=gen_id(Test, 1)"

So each record has an unique ID now, what would you recommend for updating it all now? Using the previously mentioned querys. Never used ID's before.
0
 
LVL 19

Expert Comment

by:NickUpson
ID: 24138306
to sort out the id run:
update spelare set id = NULL;
this will for the trigger to be applied and each record to have an id
 
0
 

Author Comment

by:MerlaP83
ID: 24138949
I have finally got it all working.

This is probably not the correct or most efficient way to use ID's though? At least its working, but if you have any other solution I'd be more than happy to learn it.

//Updating procedure
 
{Previous query's}
 
id.caption := Spelare['ID'];
end;
 
with Spelare do
      begin
      Close;
      SQL.Clear;
      SQL.Add('Update Spelare Set klubbint = '''+intklubb.caption+''' where ID = '''+id.caption+'''');
      ExecSQL;
      end;
end;

Open in new window

0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 24139604
Well your code seems right, with some observations:
1) Your "with Spelare do" statement does not cycle through all its records - it will only update the Spelare table once, and only for whatever record it is currently on. If you want it to do all the records showing in the resultset then you will need to say FIRST, WHILE EOF = FALSE and NEXT etc.
2) I'm not sure why you are storing your parameters in TLabels or whatever. Normally you would just say:
      SQL.Add('Update Spelare Set klubbint = ''' + MyDataSet.Fieldbyname('klubbint').asstring + '' where ID = ''' + MyDataSet.Fieldbyname('ID').asstring  + ''';
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question