While loop

[Paulnewyork]

Hello
I have a Problem with the while loop:
the if else condition in the loop works But the program freezes and the CPU for this process is at 30 percent.
I think the loop doesnt stop. It should stop at p=1 or variablePrice has reached stringPrice (it can be the same value). But i have to Check every Price when StringPrice is higher than Variable Price.
And i Tested also a higher Price and changed  in the the of else cobdition in the loop
To StringPrice >= variablePrice But than nothing happens, but the string price I choose is higher.


IF  no if elese matches and ervery price is checked with every if elses than p:=1
but i don`t know where to write tis. because inside the loop is actually wrong
I have three more if else, but they are not in the code already, because I just wanted to try with this first.

[Blaazen]

At first, do proper indentation and place missing semicolons. Also, put your code to tag < code >.
Even the beginning of your code is wrong and wouldn't compile (begin end else):

Code: Pascal  [Select][+][-]

1. IF ( stringPrice < variablePrice)
2. THEN
3. P:= '1';
4. BEGIN
5. END
6.  
7.  
8.  
9. ELSE

[Paulnewyork]

The beginning was something I added later just here.
But I changed that now.

[TRon]

As Blaazen already wrote: please USE CODE TAGS (click on me to learn all about them) 

[Paulnewyork]

Sorry, I am new to that. Here is the code.....looks totally better to read. Thank you for support!

Code: Pascal  [Select][+][-]

1.  
2.  
3. stringPrice:= Form2.SQLQuery1.FieldByName('price').AsString;
4. stringPruefpunkt Form2.SQLQuery1.FieldByName('p').AsString;
5. stringName:= Form2.SQLQuery1.FieldByName('name').AsString;
6. stringNachname:= Form2.SQLQuery1.FieldByName('nachname').AsString;
7.  
8.  
9.  
10. Form2.connectDB;
11. Form2.SQLQuery3.SQL.Text := 'SELECT price FROM tabelle2 WHERE price= (SELECT min(price)FROM tabelle2)';
12. Form2.SQLQuery3.Open;
13.  
14. variablePrice := Form2.SQLQuery3.FieldByName('price').AsString;
15.  
16. IF ( stringPrice < variablePrice)
17.     THEN
18.     BEGIN
19.     P:= '1';
20. END
21.  
22.  
23.  
24. ELSE
25.      While ((variablePrice <= stringPrice) OR (p < '1'))
26.      DO
27.      BEGIN
28.      Form2.SQLQuery1.SQL.Text := 'SELECT * from sold WHERE price ='''+variablePrice+''';';
29.      Form2.SQLQuery1.Open;
30.  
31.       // IF variablePrice = null
32.       // THEN
33.       // BEGIN
34.       // variablePreis := variablePrice + '2';
35.       // end
36.  
37.      // ELSE
38.      //Begin
39.  
40.        variablePrice := Form2.SQLQuery1.FieldByName('price').AsString;
41.        variableName := Form2.SQLQuery1.FieldByName('name').AsString;
42.        variableNachname:= Form2.SQLQuery1.FieldByName('nachname').AsString;
43.  
44. // First condition....everything has to fit    but i stringprice >= variable nothing happens
45. // stringPrice = variablePrice  than the program IF ELSE makes the insert into....
46.        IF ((stringPrice = variablePrice) AND (p = '0') AND (stringNachname = variableNachname)
47.        AND (stringName= variableName))
48.        THEN
49.        BEGIN
50.        Form2.SQLQuery1.SQL.Text := 'INSERT INTO tabelle3 (....) values (...)';
51.        p := '1';
52.        Form2.closeDB;
53.        END
54.  
55. // here are two more IF ELSE Conditions which i am going to add later and they are the same like above but the differen is
56. //    stringNachname > variableNachname   and stringNachname < variableNachname
57.  
58.  
59.  
60.  
61.  
62. // this is something that should happend after the loop when every price in the db was checked, but i dont no where to put this
63.      ELSE
64.      p := '1';
65.      END
66.  
67.       variablePrice := variablePrice + '2';
68. END
69.  
70. END;
71. END;
72.  
73.  

[TRon]

Thank you Paulnewyork,

Perhaps not the most elegant answer (i apologise in advance for that), but in order to help you improve:

For starters:
1 Formatting (see below)
2 This code does not compile
3 Even if it is a snippet (which does not necessarily have to compile), it should be at least valid code (f.e. see Line 3, but also sloppy 57, 58 )
4 SQL Insert can not be done with method Open but should be done with method ExecSQL
5 variablePrice is a string. if i read your code "variablePrice := variablePrice + '2'" and "(p < '1')" then i have a suspicion that you expect a numeric returned answer ?

It is nearly impossible to help you because of a) the above and b) we have no idea what you are trying to accomplish (although you did made an attempt in your first post, sorry it was unclear for me) and c) you are guessing what is happening ("i think ..")

Those are all bad ingredients for retrieving help. It would be helpful for yourself if you could improve that situation. For example by describing step by step what exactly you expect your code to accomplish. For example on a personal note, i do not speak SQL, so your code makes even less sense for me personally 


Somewhat better formatted code:

Code: Pascal  [Select][+][-]

1. begin
2.   stringPrice:= Form2.SQLQuery1.FieldByName('price').AsString;
3.   stringPruefpunkt Form2.SQLQuery1.FieldByName('p').AsString;
4.   stringName:= Form2.SQLQuery1.FieldByName('name').AsString;
5.   stringNachname:= Form2.SQLQuery1.FieldByName('nachname').AsString;
6.  
7.  
8.   Form2.connectDB;
9.   Form2.SQLQuery3.SQL.Text := 'SELECT price FROM tabelle2 WHERE price= (SELECT min(price)FROM tabelle2)';
10.   Form2.SQLQuery3.Open;
11.  
12.   variablePrice := Form2.SQLQuery3.FieldByName('price').AsString;
13.  
14.   if ( stringPrice < variablePrice) then
15.   begin
16.     P:= '1';
17.   end
18.  
19.   else
20.     while ((variablePrice <= stringPrice) or (p < '1')) do
21.     begin
22.       Form2.SQLQuery1.SQL.Text := 'SELECT * from sold WHERE price ='''+variablePrice+''';';
23.       Form2.SQLQuery1.Open;
24.  
25.       // IF variablePrice = null
26.       // THEN
27.       // BEGIN
28.       // variablePreis := variablePrice + '2';
29.       // end
30.  
31.       // ELSE
32.       //Begin
33.  
34.       variablePrice := Form2.SQLQuery1.FieldByName('price').AsString;
35.       variableName := Form2.SQLQuery1.FieldByName('name').AsString;
36.       variableNachname:= Form2.SQLQuery1.FieldByName('nachname').AsString;
37.  
38.       // First condition....everything has to fit    but i stringprice >= variable nothing happens
39.       // stringPrice = variablePrice  than the program IF ELSE makes the insert into....
40.       if ((stringPrice = variablePrice) and (p = '0') and (stringNachname = variableNachname) and (stringName= variableName)) then
41.       begin
42.         Form2.SQLQuery1.SQL.Text := 'INSERT INTO tabelle3 (....) values (...)';
43.         p := '1';
44.         Form2.closeDB;
45.       end
46.       // here are two more IF ELSE Conditions which i am going to add later and they are the same like above but the differen is
47.       //    stringNachname > variableNachname   and stringNachname < variableNachname
48.  
49.       // this is something that should happend after the loop when every price in the db was checked, but i dont no where to put this
50.       else
51.         p := '1';
52.     end;  // while
53.  
54.   variablePrice := variablePrice + '2';
55. end;
56.  
57. END;
58. END;
59.  

Please do not let my post discourage you.

[Paulnewyork]

Sorry for all this mistakes :/

I am going to answer the questions:

4. After the insert  I oben a procedure:        Form2.closeDB;
In this procedure there is the Exec command.

5. In this strings are prices, which are varchar in mysql.
Thats maybe the reason why this is not working: variablePrice := variablePrice + '2'

stringPrice is a price which was entered whith some other information.
VariablePrice is the price in the database. stringprice is for a buying price and variablePrice is for sell.
If the buy price is smaller than sell price, no trade is done. than the variable p is true/1
p is something like "check done". just for better understanding.

If not the While starts at the lowest variablePrice. First i select all information at this lowest price.
and then i check if my IF ELSE the strings with the variables, i select from the database.
If one IF ELSE fits than i assign P:=1

if no IF ELSE fits, than the variablePrice is +2
Than i select every information which belongs to the new variablePrice.
Than I check my if else conditions again.
If nothing fits I add to the variablePrice 2 again.
and select the information which belongs to this new variablePrice again
and so own

the while should stop when the variablePrice is the same like stringPrice
or it should stop when p=1;

When the while is finished I Want to :   P:=1
So this should be done actually pretty easy  after the while and not inside
The p is just for comparison.



I hope it is now better to understand. It is a little bit difficult to post the complete code.
Otherwise i have to change some information inside.

But maybe you can help me. I think i have to convert string to int:

Code: Pascal  [Select][+][-]

1. var
2. intVariablePric : int;
3. intstringPrice : int;
4. begin
5. intVariablePrice:= StrToInt(VariablePrice);  
6. intstringPrice:= StrToInt(StringPrice);
7.  
8.  
9.   intstringPrice := intstringPrice +2;
10.  

And After intstringPrice := intstringPrice +2;
I have to convert to string again to get all the new information from the database where the new variable price is??

Is this understandable?

[TRon]

Sorry for all this mistakes :/

There is no need to be sorry, we all have been a rookie once in our lives 

5. In this strings are prices, which are varchar in mysql.
Thats maybe the reason why this is not working: variablePrice := variablePrice + '2'

You are adding two string, which means that the characters in variablePrice are being concatenated with '2';

For example let''s assume the initial 'value' of VariablePrice was literally the text "600". then you add "2" then variablePrice then becomes "6002". The next iteration VariableProce becomes "60022", etc.

But maybe you can help me. I think i have to convert string to int:

Yes, your code example is correct 

Now that they are numeric values the comparison will make sense and act like you expected it to act: like a number.

PS: do not forget about your "P" value, you are also there comparing either a character or a string (i did not see the declaration of the variable P).

I have to convert to string again to get all the new information from the database where the new variable price is??

Yes.

Just as an observation, if i store numbers into a database then it is common to have them stored as a numeric field inside the database. That way you can retrieve and store the numeric field directly into a number (integer) variable (or compare them), without he need to convert them forth and back.

Is this understandable?

It is better, at least i am now able to make some sense of what you are trying to accomplish in combination with your code.

It is a little bit difficult to post the complete code.
Otherwise i have to change some information inside.

If you really can not share the code then that is ok. It is not always possible to share it, and we all can understand that.

What you could do though (it is merely a suggestion, you do not have to take up on it), is to create a small test-project that 'mimics' the situation from your work project. Usually that helps in better understanding your own code as well, and you would not be the first to 'fix' his/her own bugs wile making such a test-project :-)

I will address some things in another post, this way you can at least continue and perhaps post a better and improved snippet ?

[Paulnewyork]

Thank you. I try this later. Because I have another problem :/

I created a new form, which is doing someting like form1 and form2.

But If i start my program and press the button for these three forms i get the message: SQL Statement not set.
in form 1 and 2 there is no Data from the db now.
I didnt changed anything in FOrm 1 and 2.
If i press form 3 i get the same message but i can see the data from db.

Does anyone  know where this error is caused by? The internet is not the helpful.
If i restore a backup of the whole lazarus file, form 1 and 2 are working. there should be something with form 3?

I created form 3 again and get the same problems....
form 3 is below

[TRon]

In answer to your reply with regarding your explanations

I made this little table

Code: [Select]

database  |  query    |  field  |  variable
=================================================
sold      | SQLQuery1 |  price  |  stringprice    (selling)
tabelle2  | SQLQuery3 |  price  |  variablePrice  (buying)


4. After the insert  I oben a procedure:        Form2.closeDB;
In this procedure there is the Exec command.

Ah ok. Then all is ok. But be aware that 'omitting' that kind of information can cause serious confusion :-)

In case you can not share that part of the code, then just add some comment to your snippet:

Code: Pascal  [Select][+][-]

1.      // Perform the Exec function for the query so that the data get inserted,
2.      // do this
3.      // do that
4.      // and finally close the database.
5.      Form2.closeDB;
6.  

VariablePrice is the price in the database. stringprice is for a buying price and variablePrice is for sell.

Then why not name your variables BuyingPrice and SellingPrice :-)

That seems much clearer to me (and for anyone else that is looking at the code) ?

If the buy price is smaller than sell price, no trade is done. than the variable p is true/1
p is something like "check done". just for better understanding.

Ok.

But as another suggestion, if that variable can only have two 'states' "0" and "1", why then not make it a boolean ?

And would it not be more clear to then name that variable NoTrade, or DoneTrade, or in case you prefer the opposite YesTrade/WillTrade/CanTrade ?

if not .... the While starts at the lowest variablePrice.

Ok, then perhaps i misunderstand something. Your code states:

Code: [Select]

while ((variablePrice <= stringPrice) or (p < '1')) do
or in human language: if (BuyingPrice is smaller then or same as SellingPrice) or (Not(DoneTrade))

That is where i am starting to get a little worried as the comparison does not make sense to me (unless you wish to increase the BuyingPrice with the value two, up to the point it will be just at or over the SellingPrice).

And should you not be doing that loop for as long trading was not Done ? (both conditions should match ?)

First i select all information at this lowest price.

I assume you are refering to:

Code: [Select]

Form2.SQLQuery1.SQL.Text := 'SELECT * from sold WHERE price ='''+variablePrice+''';';
or in human-talk: Select all fields from database sold where the field price matches BuyingPrice

Which brings the question does the sold database actually have a SellingPrice that matches BuyingPrice ?

and then i check if my IF ELSE the strings with the variables, i select from the database.
If one IF ELSE fits than i assign P:=1

Or with my used variable name: DoneTrade := true;

if no IF ELSE fits, than the variablePrice is +2
Than i select every information which belongs to the new variablePrice.
Than I check my if else conditions again.
If nothing fits I add to the variablePrice 2 again.
and select the information which belongs to this new variablePrice again
and so own

I can see some conflicting information there with regards to your code.

I still assume that variablePrice is the BuyingPrice.

Code: [Select]

variablePrice := Form2.SQLQuery1.FieldByName('price').AsString;
which was taken from the query:

Code: [Select]

Form2.SQLQuery1.SQL.Text := 'SELECT * from sold WHERE price ='''+variablePrice+''';';
In my understanding you are assigning the field price from the sold database to the BuyingPrice variable ?

In case that is the intention of that code then (for better reading) there should be a third variable name to swap  around the numbers (but perhaps that is the reason why you named your variable "variablePrice" ?)

Also, if you are doing this by adding 2 to the buying price and loop around, then would it not be more convenient to order the last sqlquery (by price) and take the approach by comparing the other way around ?

Perhaps none of what i wrote made any sense. If that is the case then that is ok, as i am also still struggling to fully understand your current code, so it might be i made a wrong assumption somewhere along the line.

Please feel free to correct me if that is the case, although i do hope you are able to get something positive out of my response (instead of you having other troubles right now).

[Paulnewyork]

Then why not name your variables BuyingPrice and SellingPrice :-)
--> yes thats true ...sorry again


p is a defined as a boolean (or tinyint(1) thats the same). but i had to use 1 or 0. with "true" or "false" there was nothing in the row.


while ((variablePrice <= stringPrice) or (p < '1')) do
while     sellingPrice  <=  buyingPrice       p = 0 means not checked and P=1 checked
p ist just to get outside the loop if on IF ELSE matches.



database  |  query    |  field  |  variable
=================================================
sold      | SQLQuery1 |  price  |  variablePrice  (selling)
tabelle2  | SQLQuery3 |  price  |  stringPrice   (buying)

--> i changed the last row variable, there was a mistake

Code: Pascal  [Select][+][-]

1. Form2.SQLQuery1.SQL.Text := 'SELECT * from sold WHERE price ='''+variablePrice+''';';
2.  

--> oh there is a mistake.....Form2.SQLQuery3.SQL.Text
should be query 3  omg :/



I have to correct everything when i find a solution for the other problem with the error message: sql state not set


Than i can write again my code with clear variables

[TRon]

Then why not name your variables BuyingPrice and SellingPrice :-)
--> yes thats true ...sorry again

Again, there i no need to apologise. That advise is based on years of experience with not naming variables using a logical scheme 

I have learned that if you are able to apply a little logic with regards to naming then the code becomes more readable and at the same time makes it easier to spot errors.

p is a defined as a boolean (or tinyint(1) thats the same). but i had to use 1 or 0. with "true" or "false" there was nothing in the row.

Ok, it is forced upon you.

In that case, you still can use a boolean if you want, something like:

Code: [Select]

SomeBooleanName := p <> '0';


while ((variablePrice <= stringPrice) or (p < '1')) do
while     sellingPrice  <=  buyingPrice       p = 0 means not checked and P=1 checked
p ist just to get outside the loop if on IF ELSE matches.

I understand that it is used as an escape to be able to get out of the loop.

My concern was that your current while loop keeps looping when one of the two conditions matches (logical or table)

Let's say that i am still a bit confused over what p < '1' exactly means in your program As long as this is the correct condition that is required to let your code run properly, then that is ok.

database  |  query    |  field  |  variable
=================================================
sold      | SQLQuery1 |  price  |  variablePrice  (selling)
tabelle2  | SQLQuery3 |  price  |  stringPrice   (buying)

--> i changed the last row variable, there was a mistake

Thank you for the correction, however as a reminder :
line 2: stringPrice:= Form2.SQLQuery1.FieldByName('price').AsString;
line 14: variablePrice := Form2.SQLQuery3.FieldByName('price').AsString;

Somehow that doesn't exactly add up in my mind 

--> oh there is a mistake.....Form2.SQLQuery3.SQL.Text
should be query 3  omg :/

Congratulations as you figured that out all by yourself (I am also glad as i then understand the code a little better). Fixing bugs _can_ sometimes be easy 

I have to correct everything when i find a solution for the other problem with the error message: sql state not set

No problem, please take your time. Unfortunately i am unable to give any helpful advise for the problem that you now have. It was not my intention to get you into troubles with advising to make a small (stand-alone) test-project. I wish you luck with figuring out what goes wrong there. You are in good company in the other thread.

cheers,

[Paulnewyork]

So here is the new code. I change int to extended (because of decimal values). I added some comments, I hope it is now understandable. Does the while stop conditions makes sense or does it stop when the prices are the same?

It seems like the stop conditions is not workring because the programme freezes.

Code: Pascal  [Select][+][-]

1. procedure TForm3.ButtonStartClick(Sender: TObject);                  
2. var
3.  
4. start : TDateTime;
5. stringCheck :string;
6. stringBuyPrice : string;
7. stringBuyVolume :: string;
8. buy_id : string;
9.  
10. sold : string;
11.  
12. sell_id : string;
13. name : string;
14.  
15. SellPrice : string;
16. floatBuyPrice : Extended;
17. floatSellPrice : Extended;
18.  
19. //for the calculation of the restvolume
20. floatBuyVolume : Extended;
21. floatSellVolume: Extended;
22. floatTempVolume : Extended;
23. stringTempVolume : string;  
24. sellVolume : string;
25.  
26.  
27. begin  
28. stringCheck := '0';
29. buy_id := EditBuy_ID.Text;
30. stringBuyPrice := Edit2_ID.Text;
31. stringBuyVolume := Edit3_ID.Text;
32.  
33.  
34.  
35.    Form2.SQLQuery3.SQL.Text := 'SELECT price, start FROM sell WHERE price =  (SELECT min(price) FROM sell)';
36.    Form2.SQLQuery3.Open;
37.  
38.         SellPrice := Form2.SQLQuery3.FieldByName('price').AsString;
39.         floatSellPrice :=StrToFloat(SellPrice);
40.         floatBuyPrice := StrToFloat(stringBuyPrice);
41.  
42.         floatBuyVolume:= StrToFloat(stringBuyVolume);
43.  
44.  
45.  
46.      
47.     While ((floatSellPrice <= floatBuyPrice ) OR (stringCheck < '1'))
48.      DO
49.       BEGIN
50.         // I collect the data which belongs to the lowest price, i have to do this every loop again, because the price is getting higher 0.1 after every loop
51.      Form2.SQLQuery1.SQL.Text := 'SELECT * from sell WHERE price ='''+SellPrice+''';';
52.      Form2.SQLQuery1.Open;
53.  
54.      SellPrice := Form2.SQLQuery1.FieldByName('price').AsString;
55.      floatSellPrice :=StrToFloat(SellPrice);
56.  
57.      sell_id := Form2.SQLQuery1.FieldByName('id').AsString;
58.      name := Form2.SQLQuery1.FieldByName('name').AsString;
59.      sellVolume := Form2.SQLQuery1.FieldByName('volume').AsString;
60.      floatSellVolume:= StrToFloat(sellVolume);
61.  
62.      sold := Form2.SQLQuery1.FieldByName('sold').AsString;
63.  
64.  
65.    ELSE
66.    BEGIN
67.    floatSellPrice := floatSellPrice + 0.1;  
68.    SellPrice:=FloatToStr(floatSellPrice)
69.    END
70.  
71.        
72.  end;
73.  Form2.SQLQuery2.SQL.Text := 'Update buy SET check = "1" WHERE buy_id ='''buy_id+''';';
74.  stringCheck:= '1';
75.  Form2.SQLQuery2.ExecSQL;
76.  Form2.SQLTransaction1.Commit;
77.  Form2.SQLQuery1.Close;
78.  
79.  
80.    end
81.  end;
82.  
83.  

                 

[Paulnewyork]

I made some tests with different values.
if if have the same or higher buy price than the lowest sell price fits. so it gets the tag 1 (in row sold).

If i do a higher buy price, and there is a sell price which actually fits, nothing happens.

there should be something wrong with the "while  loop" i guess.


I found out one big problem: I get the wrong lowest price. Iadded one lower price in mysql and one via my program, bute none of them are the returned with the following command in mysql. I always get a higher price returned. had someone the same experience?
SELECT * FROM sell WHERE price=  (SELECT min(price) FROM sell)

[TRon]

So here is the new code.

Ok, i see we have to start at the basics.

Does the following overview matches yours ?

Code: [Select]

database | fields
========================================================
sell     | start, price, preis, id, name, volume, sold
table    | id, price, volume
buy      | check, date, buy_id

Because that is what your snippet is telling me (give or take a reading error on my part)

The i see you use the same query for doing multiple things, which is fine i guess bu i doubt it is doing what yo want it to (i did not looked into that yet because i can't get my tables up to match yours)

I change int to extended (because of decimal values).

Please don't use extended, use single or double instead (float if you must)