Recent

Author Topic: [SOLVED]Update large batch of records keeps, stop exception at different points  (Read 4445 times)

What I can do

  • Full Member
  • ***
  • Posts: 116
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][+][-]
  1. //...............
  2. Type
  3.   TCheckRowData =Record
  4.                    r:integer;
  5.                    n:string;
  6.                    d:TDate;      //chkDate
  7.                    p:string;
  8.                    m:String;
  9.                    pr:String;    //CHKPurpose
  10.                    Db:Real;      //chkDebit
  11.                    cl:String;    //CHKCL
  12.                    cr:Real;      //CHKDep
  13.                    b:real;       //chkBal
  14.                    o:integer;    //CHKOrder
  15.                   end;
  16. VAR
  17.  chk:TCheckRowData;        
  18. //...............
  19. Procedure TForm1.ReadCHKRec;
  20. begin
  21.  with DBGrid2.DataSource.Dataset do
  22.   begin
  23.    CHK.r:=RecNo;
  24.    CHK.n:=FieldByName('CK_Num').AsAnsiString;
  25.    CHK.d:=TRUNC(FieldByName('CK_Date').AsDateTime);
  26.    CHK.p:=FieldByName('CK_PaidTo').AsAnsiString;
  27.    CHK.m:=FieldByName('CK_Memo').AsAnsiString;
  28.    CHK.pr:=FieldByName('CK_Purpose').AsAnsiString;
  29.    CHK.Db:=FieldByName('CK_Debit').AsFloat;
  30.    CHK.cl:=LeftStr(FieldByName('CK_CLR').AsAnsiString,1);
  31.    CHK.cr:=FieldByName('CK_Dep').AsFloat;
  32.    CHK.b:=FieldByName('CK_Bal').AsFloat;
  33.    CHK.o:=FieldByName('ck_Order').AsInteger;
  34.   end;
  35. end;
  36. //...............
  37. procedure TForm1.DoRunUpdate(aStart: integer);
  38. var
  39.  o,x,r:Integer;
  40.  b:real;
  41. begin
  42. // DBGrid2.BeginUpdate;  
  43.  SQLEventsActive(false);  //turn off all events on Query and Dataset
  44.  aStart:=aStart+32;
  45.  if aStart>DBGrid2.DataSource.DataSet.RecordCount then
  46.   aStart:=DBGrid2.DataSource.DataSet.RecordCount;
  47. {sorted Desend so most current on top}
  48.  for x:=aStart downto 1 do
  49.   begin
  50.    DBGrid2.DataSource.DataSet.RecNo:=x; //---------------------sometime stop exception here
  51.    ReadCHKRec;
  52.    r:=CHK.r;
  53.    if x=aStart then
  54.      begin
  55.       if r=x then
  56.        begin
  57.         b:=CHK.cr-CHK.Db;
  58.         o:=0;
  59.        end
  60.      else
  61.       begin
  62.        b:=CHK.b;
  63.        o:=CHK.o;
  64.       end;
  65.      end
  66.    else
  67.      begin
  68.       b:=b+CHK.cr-CHK.Db;
  69.       o:=o+OrderSpace;
  70.       DBGrid2.DataSource.DataSet.Edit;
  71.       DBGrid2.DataSource.DataSet.FieldByName('CK_Bal').AsFloat:=b;
  72.       DBGrid2.DataSource.DataSet.FieldByName('ck_Order').AsInteger:=o;
  73.       if DBGrid2.DataSource.DataSet.State in [dsInsert,dsEdit] then
  74.        DBGrid2.DataSource.DataSet.Post; //-------------------sometimes stop exception here
  75.       CHKSQLQuery.ApplyUpdates;
  76.       //CHKSQLQuery.Close;
  77.       SQLTransaction1.CommitRetaining;
  78.      end;
  79.    DBGrid2.Refresh;
  80.   end;
  81. // DBGrid2.EndUpdate;
  82.  
  83.  DBGrid2.DataSource.DataSet.RecNo:=r;
  84.  SQLEventsActive(TRUE);
  85. 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.
« Last Edit: August 26, 2024, 08:50:08 pm by What I can do »

Handoko

  • Hero Member
  • *****
  • Posts: 5340
  • My goal: build my own game engine using Lazarus
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

  • Full Member
  • ***
  • Posts: 116
Hey Handoko, Greetings...
Quote
a simply compact and repair
:D 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

  • Hero Member
  • *****
  • Posts: 5340
  • My goal: build my own game engine using Lazarus
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

  • Full Member
  • ***
  • Posts: 116
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

  • Full Member
  • ***
  • Posts: 116
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.
« Last Edit: August 24, 2024, 10:24:35 pm by What I can do »

What I can do

  • Full Member
  • ***
  • Posts: 116
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

  • Hero Member
  • *****
  • Posts: 1258
Ok, 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.

(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...
Preview Lazarus 3.99 documentation at: https://dsiders.gitlab.io/lazdocsnext

Handoko

  • Hero Member
  • *****
  • Posts: 5340
  • My goal: build my own game engine using Lazarus
... (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.html#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.

Get 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.
 
« Last Edit: August 26, 2024, 11:10:24 am by Handoko »

What I can do

  • Full Member
  • ***
  • Posts: 116
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][+][-]
  1. Form1.SQLQueryMdb.Edit;
  2. Form1.SQLQueryMdb.UpdateRecord;
  3. Form1.SQLQueryMdb.FieldByName('State Name').AsString:= Edit1.Text;
  4. Form1.SQLQueryMdb.Post;
  5. Form1.SQLQueryMdb.ApplyUpdates;
  6. 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. 
« Last Edit: August 26, 2024, 08:56:38 pm by What I can do »

 

TinyPortal © 2005-2018