While loop

[TRon]

Since your actions seem to suggest you are a little impatient, the quickest thing i can think of atm is to just take the next record and fetch it's selling-price (*)
--> that sounds pretty good.

Yes, but that is more leaning towards the "doing it the other way around"  As a matter of fact, you would then have to alter things in such a way that it would work as i suggested earlier.

the second while loop make sense

It has a drawback. You are never able to tell if there is no match because there simply wasn't a match (yet), or that the selling price that you check for has already reached the top value of selling-prices that are stored inside the database.

Of course that can be checked by making another query, where you look for the highest possible selling-price, and bail out the while-loop (or entire routine)  if the price you are checking against is higher then that highest price that is stored inside the table.

It sounds a bit too flaky for my taste 

sorry, that i am impatient. I just want to help und suggest some possibilites

That is no problem, but as i said i do not speak SQL so i need more time to think things through. Every possible solution i have to check/test how that works in practise as i am unfamiliar with any of the used classes, and sql statements. So i am at the same time familiarising myself with the available documentation and explanations as well... i am a slow reader   

[TRon]

btw this is what i meant with other way around (e.g. let sql do all the hard work), but no idea if something like this statement can be accomplished in a single go

Code: SQL  [Select][+][-]

1. SELECT price FROM sell ORDER BY price WHERE price <= UserBuyPrice AND userBuyVolume > SellVolume AND SOLD = FALSE
2.  

Do you see what i mean ? if so then you can already play around with that idea 

The idea behind it is that it should produce a list of records that you can iterate, going from price low to price high and select the one that meets your (other, not sql'ed) criteria best.

edit:
And depending on how you have things setup it can even be that there are no matches at all, in which case you would have to decide what must be done in that situation.

[Paulnewyork]

For the last Situation there  is after the while loop an update statement to set one row value.

If there are more matches at same price the earliest startdatetime gets matched
So there should be a second loop inside the price loop for the start date/time. Maybe we can handle that also with the next value instead of increasing Every second

[Paulnewyork]

I have to think about the idea with the Sql statement, if it can work

[Paulnewyork]

Your idea sounds good, but I don’t know if nothing matched how to get the higher sell price.
I can work tomorrow on that topic again

[Paulnewyork]

Also If the volume is higher a lower than the other, i have to check the Rest volume again with the next higher Price or it stays in the dB of nothing fits.
But this can maybe done with the function last_inserted_id in the sql statement, and then the check starts again?! Until nothing fits.
I thought this would be easier if I store the volume etc direct in variables. But this can be done maybe also when the sql statement gets a match

[TRon]

Hi Paulnewyork,

First of all thank you for having provided your initial snippet in (semi) translated form and, the fact that you have reduced it to a much smaller portion then your original code 

I was already afraid you were going to bring in other (not yet mentioned) secondary requirements 

So there should be a second loop inside the price loop for the start date/time. Maybe we can handle that also with the next value instead of increasing Every second

The moment you start thinking about increasing things with a particular value in the hopes that it matches a value inside your table, then there should be an alarm-bell going off like crazy (one of those that give you a headache for the upcoming five years) 

So, yes (indeed) if possible (and in case not we must make it happen so) try to use to embed such a thing into the same query or if not possible then, open up another one that is able to aid/assist.

Your idea sounds good, but I don’t know if nothing matched how to get the higher sell price.

You would have to seriously think about that, because if there isn't then there simply isn't such a value. You can't make things up out of thin air, you know 

That is unless you are talking about a sale that never happened before so you have nothing to match against (that is if i understood your code correctly).

But also then, you would have to come up with a solution on how to handle that situation. I can't make that decision for you.

I can work tomorrow on that topic again

No problem. I was kind of working on optimising my sql test-bed environment, to make it more flexible with regards to adapting to new situations. Since i'm new to sql that goes literally with small steps at a time 

Also If the volume is higher a lower than the other, i have to check the Rest volume again with the next higher Price or it stays in the dB of nothing fits.
But this can maybe done with the function last_inserted_id in the sql statement, and then the check starts again?! Until nothing fits.
I thought this would be easier if I store the volume etc direct in variables. But this can be done maybe also when the sql statement gets a match

... and another (not mentioned before) secondary requirement 

Just take a (good) moment to think about how you wish to approach this, also taking into consideration other influences that you not have thought of earlier.

The more you know beforehand, the less likely you'll get stuck when trying to solve the initial problem/issue.

As far as I am able to tell, for the snippet you showed, the query as presented works. But it works (only) for that snippet. So the more you come up with other requirements, the more likely it could become that the solution used isn't sustainable in the end.

So again, I please urge you to take a good moment and think really hard/clear about things.

Also be aware that the more complicated this gets, the less likely I will be able to offer help as I simply miss a) the insight in your whole code-base b) miss required data and c) i will most likely reach the top of what I learned/know about sql, especially since you seem to be stepping things up a notch. And that is if you even considered being as help so far 

[Paulnewyork]

Hey,
the idea with the sql statement works perfect!
Also i select all * information from both tables, because i need it for inserts later and i also implemented the earlist time condition. (eg. ORDER BY price ASC)
the if else condition seems to work as well. (e.g. buyvolume > sellvolume)
so the statement is final.

so I just have two things to do:
if the buy volume is higher than the sell volume, i need to add the remaining volume to the db and then i have to check again, if another sell entry fits (to the same price but later time, or to a higher price). for that situation there should be a loop inside the IF ELSE condition?! the sql statement remains the same.


just one problem with the remaining volume:
If the stringRemaining/TempRemaing is 10 the INSERT works!
But when the TempRemaining is decimal (e.g. 2.5) the INSERT doesn`t work and i get the error message: ERROR executin query: Data truncated for column  'volume' at row 1.'
the data typ of volume is double in the db

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

1. TempRemaining : double;
2. stringRemaining :string;
3.  
4. ...
5.  
6. TempRemaining:= doubleBuyVolume - doubleSellVolume;
7. stringRemaining := FloatToStr(TempRemaining);  
8.  
9.  

so this are actually the last things to do.
the other IF ELSE condition are pretty easy. (its like volume = volume)

[egsuh]

Not sure following is your final code. I did some clean up to follow only logic.

In the following code, there is one possibility that you will never get out of the while loop --- if the first IF condition (Point B) is never met, you'll never set stringCheck to 1, and the while condition will be true always.

Also BuyVolume and SellVolume are not changed at least in this codes. So, once the first IF condition is not met by this sub-condition, then you will never enter into this section.

And at Point A, if the query returns no records, sellPrice will be '', and FloatSellPrice := StrToFloat(sellPrice) will raise error, I guess.

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

1.    
2. stringCheck := '0';
3. While ((floatSellPrice <= floatBuyPrice ) OR (stringCheck < '1'))   DO
4.    BEGIN
5.           // 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
6.           Form2.SQLQuery1.SQL.Text := 'SELECT * from sell WHERE price ='''+SellPrice+''';';  
7.           Form2.SQLQuery1.Open;        // Point A.    When this returns no record
8.  
9.           FloatSellPrice := Form2.SQLQuery1.FieldByName('price').AsFloat;
10.  
11.           sell_id := Form2.SQLQuery1.FieldByName('id').AsString;
12.           name := Form2.SQLQuery1.FieldByName('name').AsString;
13.           FloatsellVolume := Form2.SQLQuery1.FieldByName('volume').AsFloat;
14.  
15.           sold := Form2.SQLQuery1.FieldByName('sold').AsString;
16.  
17.  
18.           // Buyprice is higher or the same than sell price and buy volume is higher than sell volume; sold a checkpoint for the sells, it has to be unequal 1
19.           IF ((floatSellPrice <= floatBuyPrice) AND (stringCheck = '0') AND (floatBuyVolume > floatSellVolume)  {-- and? --}   (sold <>1))    // Point B. THIS CONDITION
20.          THEN BEGIN
21.                  floatTempVolume := floatBuyVolume - floatSellVolume;
22.                  stringTempVolume  :=FloatToStr(floatTempVolume);
23.                  Form2.SQLQuery1.SQL.Text := 'INSERT INTO table 2 (id, price,volume) VALUE ('''+sell_id+''','''+name+''','''+stringTempVolume+''');';
24.                  Form2.SQLQuery2.SQL.Text := 'Update buy SET check = "1", date = current_timestamp WHERE buy_id ='''+buy_ID+''';';
25.                  Form2.SQLQuery3.SQL.Text := 'Update sell SET sold ="1", date = current_timestamp  WHERE  id = '''+sell_id+''';';
26.                  stringCheck:= '1';
27.                  Form2.SQLQuery1.ExecSQL;
28.                  Form2.SQLQuery2.ExecSQL;
29.                  Form2.SQLQuery3.ExecSQL;
30.                  Form2.SQLTransaction1.Commit;
31.           END
32.           ELSE  SellPrice:=FloatToStr( floatSellPrice + 0.1);
33.    end ;   // end of while

[TRon]

Hey,
the idea with the sql statement works perfect!

Good ! 

Also i select all * information from both tables, because i need it for inserts later and i also implemented the earlist time condition. (eg. ORDER BY price ASC) the if else condition seems to work as well. (e.g. buyvolume > sellvolume)
so the statement is final.

Ok, that is good to know that it works also with adding the additional requirements. thanks for the feedback.

so I just have two things to do:

ok.

if the buy volume is higher than the sell volume, ...

You first determine if the action that you need to take is or isn't (can or can't be) part of the current active query (buyvolume > sellVolume), which in this instance seems to be (taken that there are no pre-requirements conditions that must be met in order to obtain the corect resultset), but....

... i need to add the remaining volume to the db and then i have to check again,

If that adding is done into the same DB then things can become complicated, and it then would probably be better to do either an additional query (followed by a update statement) or do it code-wise (just try to keep it apart from the queries as you initially did with your original issue).

if another sell entry fits (to the same price but later time, or to a higher price). for that situation there should be a loop inside the IF ELSE condition?! the sql statement remains the same.

Initially that sounds to me like an addition to the original query. If that cannot be done with using the same query (because you would narrow down the selection too much otherwise) then simply add another query.

Only resort to actual coding that makes database related decisions when you only have to (or want to).

just one problem with the remaining volume:
If the stringRemaining/TempRemaing is 10 the INSERT works!
But when the TempRemaining is decimal (e.g. 2.5) the INSERT doesn`t work and i get the error message: ERROR executin query: Data truncated for column  'volume' at row 1.'
the data typ of volume is double in the db

Ok, i have you seen mentioning this for a couple of times now. I will be brief and firm in my answer 

1) never ever use strings in order to work with databases, unless the contents of the database-field actually is a string and only when it represents actual text (so not a field type that is stored as type text)
2) always use parameters in your queries

If you do not follow those rules then you have a serious problem ahead of you, because of (if only) the following reason:
- https://www.freepascal.org/docs-html/rtl/sysutils/tformatsettings.html

If you do not take that into account then your code will never work somewhere else as those values are taken from your system/setup. It is a problem in its own, working for one platform but you open am additional can of worms if you wish to take multiplatform into account.

Take a look at the code i posted. It is based on one of my first attempts with sql, to which i added some changes. It is a stand-alone Free Pascal program and i have adapted it a bit so you can copy-paste it into a Lazarus project (never ever do that to a working existing lazarus project, unless you don't mind screwing up that project).

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

1. program smalltest;
2.  
3. {$MODE OBJFPC}{$H+}
4.  
5. uses
6.   classes, sysutils, db, sqldb, sqlite3conn;
7.  
8. var
9.   DBConnection  : TSQLite3Connection;
10.   DBTransaction : TSQLTransaction;
11.   DBQ           : TSQLQuery;
12.  
13.   {$IFDEF LCL}
14.   // For Lazarus Change MainForm.YourLogMem to point to a (active) Memo on your Form
15.   LogMemo   : TMemo absolute MainForm.YourLogMemo;  
16.   {$ENDIF}
17.  
18.   procedure WriteLog(S:String);inline;begin{$IFDEF LCL}LogMemo.Append{$ELSE}WriteLn(S){$ENDIF};end;
19.   procedure WriteLog;begin WriteLog('')end;
20.   procedure WriteLog(S:String;const a:array of const);begin WriteLog(Format(S,a));end;
21.  
22.  
23. procedure CreateDB;
24. begin
25.   DBConnection := TSQLite3Connection.Create(nil);
26.  
27.   DBConnection.DatabaseName := ':memory:';
28.   DBTransaction := TSQLTransaction.Create(DBConnection);
29.   DBTransAction.Database := DBConnection;
30.  
31.   DBQ             := TSQLQuery.Create(DBConnection);
32.   DBQ.Database    := DBConnection;
33.   DBQ.Transaction := DBTransaction;
34. end;
35.  
36. procedure DestroyDB;
37. begin
38.   DBConnection.Free;
39. end;
40.  
41. procedure CreateTable(aTableName: String; aFields: String);
42. begin
43.   DBQ.SQL.Clear;
44.   DBQ.SQL.Add('CREATE TABLE ' + aTableName.QuotedString);
45.   DBQ.SQL.Add('( ');
46.   DBQ.SQL.Add(aFields);
47.   DBQ.SQL.Add(' )');
48.   DBQ.ExecSQL;
49. end;
50.  
51. procedure test;
52. var
53.   Statement : string;
54.   Price     : currency;
55.   doubt     : double;
56.   isSold    : boolean;
57. begin
58.   // i have taken real instead of double, but it is the same affinity and should therefor work similar
59.   CreateTable( 'test', '"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT , "price" REAL, "sold" BOOLEAN DEFAULT 0, "date" DATETIME' );
60.   Statement := 'INSERT into test(price, date) values';
61.   DBConnection.ExecuteDirect(Statement + '(   100.01, date(''now'') );');
62.   DBConnection.ExecuteDirect(Statement + '(   200.02, date(''now'') );');
63.   DBConnection.ExecuteDirect(Statement + '(   400.03, date(''mow'') );');
64.   DBConnection.ExecuteDirect(Statement + '(   800.04, date(''now'') );');
65.   DBConnection.ExecuteDirect(Statement + '(  1600.05, date(''now'') );');
66.   DBConnection.ExecuteDirect(Statement + '(  3200.06, date(''now'') );');
67.   DBConnection.ExecuteDirect(Statement + '(  6400.07, date(''now'') );');
68.   DBConnection.ExecuteDirect(Statement + '( 12800.08, date(''now'') );');
69.   DBConnection.ExecuteDirect(Statement + '( 25600.09, date(''now'') );');
70.   DBConnection.ExecuteDirect(Statement + '( 51200.10, date(''now'') );');
71.  
72.   DBQ.SQL.Text := 'SELECT "id", "price", "sold", "date" FROM "test"';
73.   DBQ.Open;
74.  
75.   WriteLog('RecordCount = %d', [DBQ.RecordCount]);
76.  
77.   while not DBQ.EOF do
78.   begin
79.     WriteLog('Current record number is = %d', [DBQ.RecNo]);
80.  
81.     // for testing purpose, only make changes to the first record
82.     if DBQ.RecNo = 1 then
83.     begin
84.       WriteLog('Test with record number %d', [DBQ.RecNo]);
85.  
86.       // try a boolean
87.       isSold := DBQ.FieldByName('sold').AsBoolean;
88.       // we are using booltostr here, because the helper gains us no advantage (its dumb)
89.       WriteLog('Is current record sold ? %s', [BoolToStr(isSold, 'yes', 'no')]);
90.  
91.       // try with currency
92.       price := DBQ.FieldByName('price').AsCurrency;
93.       WriteLog('Price as currency = %.2f', [price]);
94.  
95.       price := price + 10.20;
96.       DBQ.Edit;
97.       DBQ.FieldByName('price').AsCurrency := price;
98.       DBQ.Post;
99.  
100.       price := DBQ.FieldByName('price').AsCurrency;
101.       WriteLog('Price as currency = %.2f', [price]);
102.  
103.       // try with double
104.       doubt := DBQ.FieldByName('price').AsFloat;
105.       WriteLog('Price as double = %.2f', [doubt]);
106.  
107.       doubt := doubt + 30.30;
108.       DBQ.Edit;
109.       DBQ.FieldByName('price').AsFloat := doubt;
110.       DBQ.Post;
111.  
112.       doubt := DBQ.FieldByName('price').AsFloat;
113.       WriteLog('Price as double = %.2f', [doubt]);
114.     end;
115.     DBQ.Next;
116.   end;
117.   DBQ.Close;
118. end;
119.  
120. procedure RunningMan;
121. begin
122.   WriteLog('begin');
123.   CreateDB;
124.   try
125.     test;
126.   finally
127.     DestroyDB;
128.   end;  
129.   WriteLog('end');
130. end;
131.  
132. begin
133.   RunningMan;
134. end.
135.  

The same logic can be applied for the date/time, but i left that open so that you can try/test that yourself 

edit: crap copy-paste error, change

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

1.  procedure WriteLog(S:String);inline;begin{$IFDEF LCL}LogMemo.Append{$ELSE}WriteLn(S){$ENDIF};end;

into

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

1.  procedure WriteLog(S:String);inline;begin{$IFDEF LCL}LogMemo.Append{$ELSE}WriteLn{$ENDIF}(S);end;

[TRon]

Not sure following is your final code. I did some clean up to follow only logic.

I had to work with that as well, so ... 

In the following code, there is one possibility that you will never get out of the while loop --- if the first IF condition (Point B) is never met, you'll never set stringCheck to 1, and the while condition will be true always.

That is correct. That is why i suggested to entirely get rid of the loop and replace it by a proper query. That query has a resultset, and if that is empty then you'll know that beforehand, to which the proper decision can be made code-wise.

I do not know if you already had seen my comment before you posted (in which case, sorry): in case you see your code run into such a loop then there should be an atomic bomb going off inside your head as a warning 

Especially loops make a perfect candidate to solve with a sql statement. And it circumvents the drawback that you already mentioned of being "stuck" inside the loop, as you are able to check the result of such query beforehand.

Nice cleanup btw , as i got confused by earlier posts.

[TRon]

just one problem with the remaining volume:
If the stringRemaining/TempRemaing is 10 the INSERT works!
But when the TempRemaining is decimal (e.g. 2.5) the INSERT doesn`t work and i get the error message: ERROR executin query: Data truncated for column  'volume' at row 1.'
the data typ of volume is double in the db

I already made my say about it but, for my own curiosity can you share the actual statement ? I am seriously unable to cause that error-message, no matter what i try (and did some very strange/naughty/nasty things). 

All research i was able to find that generate that message is pretty obvious in that someone tries to store into a field that is too small to be able to contain the posted data, and attempting to simulate that situation for a double fails in my test-cases.

[Paulnewyork]

I am not at home now.
I found on the internet that thing with parameters and decimal problems. That should be the solution. I can tell more tommorrow when i change it.

Thank you so much!

[Paulnewyork]

yes, it was the thing with parameter, now it works