Solved

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

Posted on 2009-04-12
20
1,775 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:MerlaP83
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
"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
Comment Utility
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
Comment Utility
"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
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 5

Expert Comment

by:xr1140
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now