[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2009-04-12
20
Medium Priority
?
1,878 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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 1000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
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…

650 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