[SOLVED]Update large batch of records keeps, stop exception at different points lazarus / 16-Databases ---------------------------------------------------------------------- What I can do · 2024-08-24 00:06 Window 10@64bit Lazarus 3.2 Database MS Jeti with *.mdb format Problem: big data base with 3650+ records need to update all the records on two fields that always change field Order is integer field bal is float Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i //...............Type  TCheckRowData =Record                   r:integer;                   n:string;                   d:TDate;    //chkDate                   p:string;                   m:String;                 pr:String;    //CHKPurpose                   Db:Real;    //chkDebit                   cl:String;    //CHKCL                   cr:Real;      //CHKDep                   b:real;       //chkBal                 o:integer;    //CHKOrder                  end;VAR chk:TCheckRowData;         //...............Procedure TForm1.ReadCHKRec;begin with DBGrid2.DataSource.Dataset do  begin   CHK.r:=RecNo;   CHK.n:=FieldByName('CK_Num').AsAnsiString;   CHK.d:=TRUNC(FieldByName('CK_Date').AsDateTime);   CHK.p:=FieldByName('CK_PaidTo').AsAnsiString;   CHK.m:=FieldByName('CK_Memo').AsAnsiString;   CHK.pr:=FieldByName('CK_Purpose').AsAnsiString;   CHK.Db:=FieldByName('CK_Debit').AsFloat;   CHK.cl:=LeftStr(FieldByName('CK_CLR').AsAnsiString,1);   CHK.cr:=FieldByName('CK_Dep').AsFloat;   CHK.b:=FieldByName('CK_Bal').AsFloat;   CHK.o:=FieldByName('ck_Order').AsInteger;  end;end;//...............procedure TForm1.DoRunUpdate(aStart: integer);var o,x,r:Integer; b:real;begin// DBGrid2.BeginUpdate;   SQLEventsActive(false);  //turn off all events on Query and Dataset aStart:=aStart+32; if aStart>DBGrid2.DataSource.DataSet.RecordCount then  aStart:=DBGrid2.DataSource.DataSet.RecordCount;{sorted Desend so most current on top} for x:=aStart downto 1 do  begin   DBGrid2.DataSource.DataSet.RecNo:=x; //---------------------sometime stop exception here   ReadCHKRec;   r:=CHK.r;   if x=aStart then     begin      if r=x then       begin        b:=CHK.cr-CHK.Db;        o:=0;       end     else      begin       b:=CHK.b;       o:=CHK.o;    end;     end   else     begin      b:=b+CHK.cr-CHK.Db;      o:=o+OrderSpace;      DBGrid2.DataSource.DataSet.Edit;      DBGrid2.DataSource.DataSet.FieldByName('CK_Bal').AsFloat:=b;      DBGrid2.DataSource.DataSet.FieldByName('ck_Order').AsInteger:=o;      if DBGrid2.DataSource.DataSet.State in [dsInsert,dsEdit] then       DBGrid2.DataSource.DataSet.Post; //-------------------sometimes stop exception here      CHKSQLQuery.ApplyUpdates;      //CHKSQLQuery.Close;      SQLTransaction1.CommitRetaining;     end;   DBGrid2.Refresh;  end;// DBGrid2.EndUpdate;  DBGrid2.DataSource.DataS et.RecNo:=r; SQLEventsActive(TRUE);end; I have tested the errors and checked the RecNo and it stops at different records processing cycles and errors out at aroun 145~271 range of processed records. Get this error........ the search results so far talk more about one or a few records but not a big batch. ---------------------------------------------------------------------- Handoko · 2024-08-24 03:24 I am not sure if it still applies now but in the past I frequently had problems with Access mdb database, a simply compact and repair always fixed the problems. ---------------------------------------------------------------------- What I can do · 2024-08-24 14:42 Hey Handoko, Greetings... Quotea simply compact and repair is that some sort of Pascal code procedure or SQL scrip, or a painful reminder with the consequences of using outdated engines. ...you still made me smile. Can this be done with script, or a Component Set installed. or is this the Big SQL3 engine rewrite... ---------------------------------------------------------------------- Handoko · 2024-08-24 16:12 To do the compact and repair, start the Microsoft Access. In the menu, you can find the Compact and Repair. Can it be done using a script or component? I am not sure. I have abandoned Access database for a very long time, if I remember correctly last time I used it was in 2000. ---------------------------------------------------------------------- What I can do · 2024-08-24 20:03 OK, I tested a few SQL3 one labeled in the examples but it turned out to be DBase. I found a online package but it crashed my system so hard I had to remove and install lazarus again. What do you suggest as a package and or examples to learn from. ---------------------------------------------------------------------- What I can do · 2024-08-24 20:21 OK, I tested a few SQL3 one labeled in the examples but it turned out to be DBase. I found a online package but it crashed my system so hard I had to remove and install lazarus again. What do you suggest as a package and or examples to learn from. I do have all my Delphi EXE's and MDB files that open and manipulate all me data without a hitch so I think the data files are well and intact.  I tested a lot of the setting, properties, and options in the whole DB configuration chain from Connection to DBGrid and the only result was the stop happens quicker. I remember Delphi had a component that was just for updating large amount of records and some can be done in SQL  So I got some homework. The Dataset was for quick and simple edits easy code but for a Command write took a lot more code but man it was much faster. Do you know of any packages. ---------------------------------------------------------------------- What I can do · 2024-08-25 23:34 Ok, started researching SQL https://wiki.lazarus.freepascal.org/SQLdb_Tutorial0 -OVERLOAD- The SQL tutorial suggest using FireBird I really don't like having to install an engine,(the SQL3 package that came from the Lazarus online packages) crashed my system hard, never even got the first compile to launch) so I have very cold feet about me installing any kind of engine and I certainly don't want any user to install an engine. but.... Oh well keep gathering info... ---------------------------------------------------------------------- dsiders · 2024-08-25 23:51 Quote from: What I can do on August 26, 2024, 01:34:15 amOk, started researching SQL https://wiki.lazarus.freepascal.org/SQLdb_Tutorial0 The SQL tutorial suggest using FireBird I really don't like having to install an engine, What exactly do you think Sqlite3 is? Yes, it's a single file vs the multiple files required for Firebird. It's also a single user, amorphous toy compared to Firebird. Quote from: What I can do on August 26, 2024, 01:34:15 am(the SQL3 package that came from the Lazarus online packages) crashed my system hard, never even got the first compile to launch) so I have very cold feet about me installing any kind of engine and I certainly don't want any user to install an engine. Honestly, you don't need what ever it is that you downloaded from OPM to use Sqlte3 with FPC or Lazarus. You do need the Sqlite DLL or SO on any system using the database. Guess what... that has to be installed unless you're on a Linux distro that already includes it. All the talk about engines and cold feet is just analysis paralysis. My .02USD... ---------------------------------------------------------------------- Handoko · 2024-08-26 03:36 Quote from: What I can do on August 26, 2024, 01:34:15 am... (the SQL3 package that came from the Lazarus online packages) crashed my system hard, never even got the first compile to launch) Just as what dsiders said, if I remember correctly I never installed any package in Lazarus to be able to write Firebird and SQLite programs. For SQLite, it is installed by default on Linux but as far as I know it is not installed on Windows by default. You don't have to install SQLite actually, you just need to copy the SQLite library to the program folder, or the project folder. If you develop SQLite programs on Linux, you have to install firebird-dev package first (I recommend using Synaptic Package Manager). For Firebird, you need to manually install it on your computer because it is not installed by default on Windows and Linux. SQLite probably is the easiest to start with for beginners. The SQLite library (or some call it client lib) is only 1 single file. To distribute your application, you just need to copy the client lib to the program folder. On Linuxes, you don't need to do anything because it is usually installed by default. Distributing your program that uses Firebird database, is a bit challenging. For multi-client-connection you have to install and configure the server first. For embedded/local database usage, you need to provide the client lib, which consists of several files, make sure it also includes the license files and set the system environment variables. One good thing about learning SQL language is, you learn one then you will able to work with the other SQL-based databases. So if you learn SQLite, you should be able to understand and write Firebird programs too. Each SQL-based database has their own small different commands. For example Firebird has SKIP command but in SQLite you use OFFSET. But most of their commands are the same. I spend a quite time testing the performance of SQLite vs Firebird embedded, I found SQLite run several times faster than Firebird embedded. I used the code in the link below, but added a loop for about 50 repetitions: https://forum.lazarus.freepascal.org/index.php/topic,65185.msg496461.h tml#msg496461 I searched to web to learn about why SQLite runs faster than Firebird. They said SQLite is optimized for single connection so it should be faster, but on multi connection environment Firebird will run better. That may be true but I haven't tested Firebird in any multi connection environment. In SQLite website, they said SQLite is capable to handle multiple connections. But still, right tool for the job, if you want to write multi-client database programs you should consider the others that are specialized for the task first. Firebird is okay. But the documentation is poor. I once had problem and decided to join in to their forum and asked questions. To my surprise, there was $500 fee. I understand they need to make money but that makes me think, if I need a multi-client database I will go directly to MySQL or PostgreSQL, which have better documentation and larger user base. I personally ever had bad experience installing MySQL on Windows 7, the problem solved by installing MariaDB. If you ever have problem installing MySQL, you can consider MariaDB. MariaDB is said can be used as a drop in replacement for MySQL. Quote from: What I can do on August 24, 2024, 02:06:39 amGet this error........ Back to your original question. I would be interested to solve the mystery but I am currently busy with my design jobs, only have time maybe a week later. If you want, you can send me the whole source code and the database. Access mdb file is known can be corrupted easily, so I need to inspect the file. And please don't just send me some lines of code. Bug can hide anywhere in the code. I need the whole compile-able source code. ---------------------------------------------------------------------- What I can do · 2024-08-26 18:48 OK, Thank everyone  for your input. After a lot of testing and research. https://www.sqlservercurry.com/2011/02/fastest-way-to-update-rows-in- large.html https://www.mssqltips.com/sqlservertip/5636/optimize-large-sql-server- insert-update-and-delete-processes-by-using-batches/ but this is what I was looking for https://stackoverflow.com/questions/13030954/updating-ms-access- database-record-in-lazarus I have to use this process... Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Form1.SQLQu eryMdb.Edit;Form1.SQLQueryMdb.UpdateRecord;Form1.SQLQueryMdb.FieldByNa me('State Name').AsString:= Edit1.Text;Form1.SQLQueryMdb.Post;Form1.SQ LQueryMdb.ApplyUpdates;DBGrid1.Refresh; Where i was using SQLQueryMdb.SQL with the 'UPDATE ...' script  the SQLQueryMdb.UpdateRecord; was the change that I needed I have read a lot about SQL3 and looking forward to diving into it but for right now I have to get a fully working app. I have found a SQL Server in the Admin tools OBDC manager but never could get it to work.