?
Solved

Updatding Firebird db using random statements?

Posted on 2009-04-30
18
Medium Priority
?
986 Views
Last Modified: 2013-12-09
Hey all,

Using Firebird 2.1 and need to update the table using a Random function. It is for a sports sim and there are several factors that need to take place.

Each player has a "potential" value between 1-20 (basically how much they can increase), this will be added to either their Def or Off skill (random which one is increased). It is also based on the player's age.

I will use some examples:

If a player is 18 yrs old and has 14 in Potential and the DEF skill is chosed using random, also if the DEF skill is between 5-10 then something like the code pasted below would be used.

But by implementing this, it would take ages to go through each records in a table filled with 5000+ records. Especially since there are so many different factors. Any better way to achieve this?

Or should I use some kind of "update Spelare set" ? Not sure how to use it with the Random() function though?
if (strtoint(Spelare['Def']) >= 5) and (strtoint((Spelare['Def']) <= 10) then
if (strtoint(Spelare['Potential']) >= 13) and (strtoint((Spelare['Potential']) <= 16) then
if (strtoint(Spelare['Age']) >= 16) and (strtoint((Spelare['Age']) <= 20) then
begin
i := Random(3)
Spelare.Edit;
Spelare['Def'] := inttostr(strtoint(Spelare['Def']) + strtoint(inttostr(i));
Spelare.Post;
end;
Spelare.Next;

Open in new window

0
Comment
Question by:MerlaP83
  • 9
  • 6
  • 2
17 Comments
 
LVL 13

Expert Comment

by:rfwoolf
ID: 24283342
Is there a way to send this using SQL? Firebird would have to support all sorts of case statements. I would have added the SQL zone to the question.
Also you could do a stored procedure, this is where a procedure takes place on the database server instead of in your application. For example if you are concerned that your application has to first retrieve 5,000 records into its dataset, then process them, then send them back to the server, then yes I suppose it would take a long time. This way this only happens on the server, so there's almost no network traffic.
Finally you could do this in your application basically how you have it in your example.

So what exactly is your concern here? If you are worried about transporting 5,000 records to your application then you need to look at way of doing this in SQL or a stored procedure.
I would look up Firebird case statements and Firebird stored procedures.

good luck
0
 

Author Comment

by:MerlaP83
ID: 24283485
I would love to do all of this using SQL.

Basically this procedure will only happen once in the application so it doesn't matter if it takes like 5-15 secs (just not much longer).

The db is only used locally so there will not be any problems with network traffic (used Paradox db before but when I reached 2000-3000 records it became too slow).

I assume CASE statements will be the best and "fastest" way to go, right? Any examples on how to use it? Will look it up as well. The Edit/Post with a loop is much slower, isn't it?

Also, there's a big possibility the records will be increased to about 10,000 so it needs to work with more than 5k records.
0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 24283515
Each implementation of SQL has its limitations, some do not support CASE statements. Firebird is a great database and there's a good chance that it does, but it's not my area unfortunately. You can wait to see if any other experts have anything to add (e.g. Nickupson seems to be our resident Firebird master (by master I don't mean the EE certification, I mean master)), you could also post a question on the Firebird Yahoo Group (which I loathe because it's almost impossible to navigate/search), or you could also try adding this question to the SQL zone to see what they think. If you have trouble adding to the SQL zone then click on the Request Attention button somewhere on this page and ask one of the mods to do it for you (or post a new question in the SQL zone).
However, you might find your quickest response will be if you do a bit of googling, and searching through some of the firebird websites to look for things like CASE statements.

But then again, if you're lazy, just do it in Delphi like you have in your example. At least there won't be any network traffic because you say the database will be local, and you could make your mouse an hourglass and put up a box saying "Please be patient" - but that would not be the best way.

If I get bored and some time I will try help you research this, but don't count on it :)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:MerlaP83
ID: 24283649
Haha, no worries. Thanks a lot for your information. I'll look into it tomorrow(bedtime in Sweden now :).

By the way, I have always wondered how to add an hourglass and put up a box saying "Please be patient"? Would be a lot better than just watching it "freeze". Or if one can do it using a ProgressBar somehow? OT, sorry - but always wondered:)
0
 
LVL 46

Expert Comment

by:aikimark
ID: 24294495
I'm not sure about the random function, but this is an example of standard SQL to do this update.  You might need to create a user defined function in your database to accomplish the random function.
Update Spelare 
Set Def = Def + random(3)
Where (Def Between 5 And 10) 
And (Potential Between 13 And 16) 
And (Age Between 16 And 20)

Open in new window

0
 
LVL 46

Expert Comment

by:aikimark
ID: 24294512
change Random(3) to Rand(3)
0
 

Author Comment

by:MerlaP83
ID: 24296168
Thank you for your reply.

I get the following error though:

SQL Error:  function RAND could not be matched. Error Code: -171. Invalid data type, length, or value The SQL: Update Spelare Set Def = Def + rand(3) Where (Def Between 15 And 20) And (Potential Between 13 And 16) And (Alder Between 16 And 20)
;
0
 
LVL 46

Expert Comment

by:aikimark
ID: 24296579
try
Set Def = Def + Floor(rand() * 3)

Open in new window

0
 

Author Comment

by:MerlaP83
ID: 24296620
Yay, that seems to be working. Thank you.

Will this always produce a Random number? (like Randomize function in Delphi).

And will the rand() * 3) generate a number between 0-3, or is it 1-3 ?
0
 
LVL 46

Expert Comment

by:aikimark
ID: 24296662
should be 0-2.

you should do some tests to make sure that the RAND function is returning different results for different rows.
0
 

Author Comment

by:MerlaP83
ID: 24297552
Could I get the result of the Rand() function as well?

I would have liked to use it like this:

Set Def = Def + Floor(rand() * 6), Potential = Potential - The random number picked..
0
 

Author Comment

by:MerlaP83
ID: 24297776
Also, to make it complete - I would like the random number, if there's any chance without making the code too complex, to have a bigger chance of being 0 than any other number.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 24299824
1 (SQL) The only way to preserve the value of the Rand function within a row's update is to add a column to the table to capture hold it.

1 (stored proc) if doing the update with cursors in a stored procedure, you would assign the value to a variable.

2. What do you mean by a 'bigger chance of being 0' ?

0
 

Author Comment

by:MerlaP83
ID: 24321336
Basically, what I mean with "bigger chance of being is" that a random(2) should basically return a 0 in 75% of the cases and a 1 in the remaining 25%.

Like in Delphi where you can do something like:

I :=Random(5);
Case I Of
1 : stringgrid1.cells[3,2] := '0';
2 : stringgrid1.cells[3,2] := '0';
3 : stringgrid1.cells[3,2] := '0';
4 : stringgrid1.cells[3,2] := '1';
0
 
LVL 46

Accepted Solution

by:
aikimark earned 1600 total points
ID: 24321849
use a case clause
Set temprandcol = Floor(rand() * 7), 
Def = Def + 
Case When temprandcol  >= 3
Then 0
Else temprandcol 
End

Open in new window

0
 

Author Comment

by:MerlaP83
ID: 24338474
Excellent. Thank you for all your help!
0
 

Author Closing Comment

by:MerlaP83
ID: 31576466
Thanks for your great help! Works just as wanted.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

In this article, we’ll look at how to deploy ProxySQL.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

829 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