[SOLVED] Access Violation - Database is Locked
lazarus / 16-Databases

----------------------------------------------------------------------
1HuntnMan · 2025-09-26 16:21

Doing testing on an application that's mostly finished.  Testing
adding, deleting and editing records. But, after maybe adding 3 new
records to one form, i.e. Clients or adding a couple of Reference
records. I get an access violation-Ok/Abort.  Have to abort because
it's locked if you click OK. Reading online it can be caused by
multiple issues: null pointers (dereferencing, dangles, uninitialized
pointers). Memory mgt.??? Tried step-by-step but that's almost a waste
of time.

Anyone have any advice how to home in on the issue?

----------------------------------------------------------------------
H₂SO₄ · 2025-09-29 04:59

Run it with the debugger; the program will stop at the line where the
AV occurs (make sure to compile with debug information enabled and
with  -O1  or even  -O0).
If you still can't figure out the root cause, take a look at valgrind
(compile with  -gv).
If that still doesn't cut it, you might want to compile FPC with LLVM
support enabled and enable Address Sanitizer (ASAN) with 
-Clfsanitize=address.

----------------------------------------------------------------------
jamie · 2025-09-30 00:22

Enable HeapTRc and then add only one record or perform one duty just
prior to where you know it's going to crash on the next step and exit
the app, read the Heap report for leaks.

 You can also try this with just starting and exiting the app to see
if you have memory still alive, which shouldn't be.

  You could also be doing some buffer overruns during your app which
is corrupting your memory space.

 Etc. The list goes on, but ensure you have HeapTrc running so you can
check the memory report on exit, having some sort of leak indication
of unclean memory can mean a couple of things, you forgot to free
something, which isn't too bad or your code is doing something that
corrupts your code and messes around with the head manager, which will
give you bad memory reports at times and or just overwrite your code.

 Other things like doing multi-threading accessing common LCL code
from more than a single thread can do nice things to your code, too,
along with running timers.

Jamie

----------------------------------------------------------------------
1HuntnMan · 2025-10-17 19:05

Back to a point where I need to figure out these ACCESS VIOLATION's,
i.e. Project raised exception class 'External: ACCESS VIOLATION' with
message: Access violation executing address $00000000000000, I didn't
count the zeros.
This doesn't mean anything to me. For example, some of my forms in the
app have no issue but a few do for example my Clients form, I can
test, add, delete, etc. testing. No issue. I can save/post or just
launch it and not make any changes.  But, if I even just execute this
form from the main form the second time I get ACCESS VIOLATION error
above when I close it the second time and have to close the
application.  Here's my code for the FormClose procedure at the
bottom.

Khrys mentioned using valgrind (compile with  -gv). I'm going to
download ValGrind but also when Khrys advised compile with -gv. Where
do you add the -gv parameters?  Is that in Run Parameters in Command
line parameters, just add -gv?

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 procedure
TFrmClientsMgt.FormClose(Sender: TObject;  var CloseAction:
TCloseAction);begin  DSrcClients.OnDataChange:= Nil; 
QryClients.AfterPost:= Nil;  QryClients.AfterDelete:= Nil; 
QryCntks.DataSource:= Nil;  keys.Free;  QryCountClnts.Close;  with
QryClients do    begin      if State in [dsEdit, dsInsert] then      
if MessageDLg('Changes pending, save current updates?',
mtConfirmation,         [mbYes, mbNo],0) = mrYes then          begin 
         Post;            ApplyUpdates;           
PMSDataModule.TransPMSDB.CommitRetaining;          end      else
Cancel;    end;  QryCntks.Close;  QryClients.Close; 
PMSDataModule.CloseLookUpTables;  //if (cDestroying in ComponentState)
then Exit; //=> error cDestroying...end; 
Also, when I'm testing about every other time I exit without making
any modifications, the main form which is just a menu with
SpeedButtons for the various parts of the application, I will get an
access violation with a different address...

----------------------------------------------------------------------
1HuntnMan · 2025-10-17 19:11

Okay, just figured out that valgrind is for Linux.  I'll look at
something similar for Winders 11...
Tks

----------------------------------------------------------------------
cdbc · 2025-10-17 19:46

Hi
QuoteProject raised exception class 'External: ACCESS VIOLATION' with
message: Access violation executing address $00000000000000This
address is equivalent to NIL ~ you're calling something/method of a
class that is Free'd / unassigned / never assigned / FreeAndNil'ed
etc...
Try stepping the debugger until you reach this error, around there,
will be something that is nil, got free'd on form closing, so the 2.
time it's nil 
Do you close or disconnect you db-components on form-close?!?
Regards Benny

----------------------------------------------------------------------
Zvoni · 2025-10-20 06:15

Don't Close/Free objects in Form-Close.
That belongs to FormDestroy.

And why in blazes are you setting the Events (and one Property) to
Nil???
Don't.

The only fishy thing i see:
Line 5 you Nil AfterPost
Line 16 you do Post

----------------------------------------------------------------------
1HuntnMan · 2025-10-21 19:35

Okay, thanks for all the help.  No more issues. After getting rid of
Nil'ing. I overkilling, LoL.

But, I still get an Access Violation most of the time when I'm running
and testing the application and closing the Main Form. The Main Form
shouldn't have any Queries, tables, etc. Those are all closed when
exiting/closing a particular form.  The Main Form is just a menu File
- Tools - Help and Speedbtns under the menu across the top to access
Clients, Contacts, Appointments, etc.  I have one Close btn to close
the app.  I'm checking to make sure no tables are open, etc.  The
error is always "Project PMS raised exception class 'External ACCESS
VIOLATION" with message: Access violation executing address
$00000000064DC530 at address 64DC530. Which doesn't mean anything to
me. But, if I run the application from the .exe file not within Laz, I
usually never get this Access Violation but once in awhile.  The last
time I saw it was a week ago. So, I'm continue finishing and then
attack this issue.

----------------------------------------------------------------------
Zvoni · 2025-10-22 07:06

Quote from: 1HuntnMan on October 21, 2025, 09:35:15 pmOkay, thanks for
all the help.  No more issues. After getting rid of Nil'ing. I
overkilling, LoL.

But, I still get an Access Violation most of the time when I'm running
and testing the application and closing the Main Form. The Main Form
shouldn't have any Queries, tables, etc. Those are all closed when
exiting/closing a particular form.  The Main Form is just a menu File
- Tools - Help and Speedbtns under the menu across the top to access
Clients, Contacts, Appointments, etc.  I have one Close btn to close
the app.  I'm checking to make sure no tables are open, etc.  The
error is always "Project PMS raised exception class 'External ACCESS
VIOLATION" with message: Access violation executing address
$00000000064DC530 at address 64DC530. Which doesn't mean anything to
me. But, if I run the application from the .exe file not within Laz, I
usually never get this Access Violation but once in awhile.  The last
time I saw it was a week ago. So, I'm continue finishing and then
attack this issue.
As has been said many times here: activate the Debugger (gdb or
fpdebug), and you can see which line causes it

----------------------------------------------------------------------
1HuntnMan · 2025-10-22 13:20

Okay, activate the debugger?? Haven't tried this but researching. My
Build mode is Default, no Checks and assertion, Run uses the debugger
is Checked, Generate info for the debugger is checked, Debugger info
is set to Dwarf 3 (beta) (-gw3) with other choices Dwarf 2 (-gw2),
Dwarf 2 with sets and Stabs (-gs).  I need to research and learn the
correct way when I get back to these issue after finishing the other
forms.  Thanks...

----------------------------------------------------------------------
cdbc · 2025-10-22 13:33

Hi
The default values are pretty decent, just use them as is... It should
work right out of the box \o/
You're set to begin debugging, just for kicks - click in the left
gutter at a codeline in a method, you should see a red spot marking
the breakpoint, where execution will stop... when it stops hover the
mouse over some value...
Regards Benny

----------------------------------------------------------------------
1HuntnMan · 2025-10-22 17:38

Benny, thanks.  I have seen the red dot before but thought it was
something to do when I was stepping thru after Run to Cursor. I
probably had accidentally click in the gutter.  Alrighty then, thanks.

----------------------------------------------------------------------
1HuntnMan · 2025-11-13 17:22

What is a dummy explanation for including this in a DataModule just
after the Implementation and uses:

{%CLASSGROUP 'System.Classes.TPersistent'}

I'm trying to eliminate these DataBase is Locked and Access Violations
in this application.  I'm thinking to start all over.  Also, what's
the difference between a DataModule and just a form/unit.  Basically
what I use the DataModule for is for the Connection and Transaction
for the rest of the application. But, all the queries/datasouces for
LookUp Tables I have on the DataModule, i.e. Countries, States,
Payment Status, Priorities, etc.  These little tables are just
lookup's for populating data to other tables on forms such as Clients
Mgt., Contacts Mgt., Appointments, Proposals, Sales Orders, etc. 
these forms use the DataModule for the Connection/Transaction. So, to
sum it up, DataModule is where the Connection/Transaction is set, then
the queries/datasources are all on the DataModule for the LookUp
tables.

Clients, Contacts, Appointments, etc. have their own
Queries/DataSources on their forms.  I USE the datamodule for the
connection/trans and for DropDownLookUps for a Country/States, etc. 
Here's the LPR:

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 program
PMS; {$mode objfpc}{$H+} uses  {$IFDEF UNIX}  cthreads,  {$ENDIF} 
{$IFDEF HASAMIGA}  athreads,  {$ENDIF}  Interfaces, //-> This includes
the LCL widgetset  Forms,  { The PMS units are listed below ... } 
PMSDM, PMSMain, datetimectrls, printer4lazarus, lazcontrols;  {$R
*.res} begin  RequireDerivedFormResource:=True; 
Application.Scaled:=True;  Application.Initialize; 
Application.CreateForm(TPMSDataModule, PMSDataModule); 
Application.CreateForm(TFrmPMSMain, FrmPMSMain); 
Application.Run;end.{ EoF: PMS.lpr } 
Here's the new LPR:
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 program
PMS; {$mode objfpc}{$H+} uses  {$IFDEF UNIX}  cthreads,  {$ENDIF} 
{$IFDEF HASAMIGA}  athreads,  {$ENDIF}  Interfaces, // This includes
the LCL widgetset  Forms,  { The Main PMS unit is listed below... } 
PMSDataBase, PMSMain; {$R *.res} begin 
RequireDerivedFormResource:=True;  Application.Scaled:=True; 
{$PUSH}{$WARN 5044 OFF}  Application.MainFormOnTaskbar:=True;  {$POP} 
Application.Initialize;  Application.CreateForm(TFrmPMSData,
FrmPMSData);  Application.CreateForm(TFrmPMSMain, FrmPMSMain); 
Application.Run;end. 
Why a DataModule???

----------------------------------------------------------------------
Lulu · 2025-11-13 18:29

Hi, I see i one thing that can produce issue: the data module is
created BEFORE the main form.
Someone more experimented than me can say if it is ?

----------------------------------------------------------------------
1HuntnMan · 2025-11-13 21:27

Well, I learned a while back that the DataModule should be launched
first, then the Main Form. But, can't remember, it's been more than a
year ago. But, back then I had issues with a different application.
But, it was small and just ditched the DataModule and put Connection
and Transaction on the main form and the other couple of forms I put
the Queries and DataSources on the those particular forms called by
the main form.  This application has 7 forms called by the main form.
And as I was designing with a DataModule, when testing after awhile I
started getting Access Violations and sometimes Database is Locked.  I
run stepping thru and the Database is Locked just after I exited the
Main Form.

So, I'm testing to use just a form/unit instead of a DataModule.  I
could just skip and put all the lookup tables on the main form but
quite cluttered.  Also, I designed this about a year ago with TDbf
tables, never had issues with Access Violations.

I'll be back later and going to start over and test every step of the
way...

----------------------------------------------------------------------
egsuh · 2025-11-14 01:45

How is FrmClientsMgt created? It is not created automatically.

----------------------------------------------------------------------
Thausand · 2025-11-14 02:22

Quote from: 1HuntnMan on November 13, 2025, 06:22:12 pmWhy a
DataModule???
Manual https://www.freepascal.org/docs-
html/rtl/classes/tdatamodule.html

QuoteTDataModule is a container for non-visual objects which can be
used in an IDE to group non-visual objects which can be used by
various other containers (forms) in a project. Notably, data access
components are typically stored on a datamodule. Web components and
services can also be implemented as descendants of datamodules.

TDataModule introduces some events which make it easier to program,
and provides the needed streaming capabilities for persistent storage.

An IDE will typically allow to create a descendant of TDataModule
which contains non-visual components in it's published property list.
That is short why 

Then have think. Why have form make clutter with non-visual component
? When use datamodule then have group database function for one
location. That important for more easy for maintain. When have
database components for form then have multiple form then when want
change for database then have visit and change all form (unit
code/component property).

Use datamodule not have mandator but have convenient. Can make
convenient when use normal unit. When have database then is no require
visual component. Visual database component is same when datamodule
and is convenient.

----------------------------------------------------------------------
Thausand · 2025-11-14 02:33

Quote from: Lulu on November 13, 2025, 07:29:53 pmHi, I see i one
thing that can produce issue: the data module is created BEFORE the
main form.
Someone more experimented than me can say if it is ?
I no expert. but have sense logic

Answer is depend. When want database setup before form show and form
have property connect then form most have depend database then
datamodule first, form after. When form is make open database and
connect runtime then is form first then have datamodule.

It can have complicate many things when user make use many event
connection when develop. Example: when have event formshow and then
have code for query database then database is first setup, after then
form. There many event both form and database. So when event depend
when database or form available then have impact order creation.

So I have personal think that create database visual and have set
event properties IDE is no good for understand how is impact form and
database. Is happen many time that user have violation access (and why
have no idea because have not learn how is work this impact)

----------------------------------------------------------------------
Handoko · 2025-11-14 03:31

Quote from: Thausand on November 14, 2025, 03:33:07 amSo I have
personal think that create database visual and have set event
properties IDE is no good for understand how is impact form and
database.
Me too avoid setting events for database visual components. In the
past I like the idea of database visual components, setting the
AfterInsert, AfterDelete events, etc. At the beginning that seemed
good, but it became harder to understand the flow. Now, I put all the
database related code into a single unit. Functions and procedures are
all called by code not events. I separate properly the database and UI
parts. The code now becomes easier to follow, extend and debug.

----------------------------------------------------------------------
Thaddy · 2025-11-14 05:23

I totally agree with that. This week, I happened to stumble into some
code I wrote in the 1996/7/8 era with Delphi 1 and 2:
OMG what a mess. Can't believe I got any money for that! I must have
been so into that code that nobody else could make any sense of it and
that includes me after 28 odd years. It was around then that we
decided all the database stuff should be done in code, not through the
screendrawing properties. Things got better when the actionlist and
actions were introduced and we introduced the rule that we only write
IDE's based on actions, never direct. That code, from the early 2000's
looks a lot better!
But as with any programming it takes some discipline, but makes a team
and yourself a lot more productive.
The 1996/7 code shows its age: hey Delphi is great, we need to do
everything visually. NOT. It happened to be a false promise in many
ways.
Things like Datamodules and Action lists were the onset of better
programming when combined.
Writing more in code helps a lot regarding quality and maintenance.
After all, a good program is usually not the userinterface, although
it is part of its usefulness.
Many should un-learn user interface centric programming, a.k.a. screen
drawing, because it is plain wrong.
Wrong in the sense that perfectly good tools are used in the wrong
way.

All this because I found an USB floppy drive in the attick along with
some floppies that were not deceased yet..
Now all I need is an USB ide interface for my BigFoots to be able to
see what other damage I have caused in the past...
<shiver>

(D2 was distributed on around 20! floppies)

----------------------------------------------------------------------
1HuntnMan · 2025-11-16 15:48

Yup, I finally figured out most of all my issues.  When I first
started on this project, I had all the Queries defined on the
DataModule and then changed my designed and just had the Queries for
just the lookup tables, i.e. States/Provinces, Countries, etc. on the
DM and moved my Queries for the main units/forms to their forms/units,
i.e. Appointments, Clients, ContactsMgt., etc.  Just discovered that
all the Query-Field declarations were left in the DM-interface
section, i.e. QryAppmts: TSQLQuery, QryAppmtsACTIVE:TBooleanField;,
etc.  So, those queries were deleted and moved to their own units and
all this was left behind but when I compiled, it didn't catch these
left behind.  So, decided to move everything back like before and the
Clients. Appointments, ContactsMgt forms just have DataSouces
components on their forms pointing back to their Queries. But just
working with just the Appointments form which now just has DataSource
components on the form for all the DBEdits, DBNav, DBGrid, etc.  I can
code for example:
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
DSrcAppmts.DataSet:= PMSDataModule.QryAppmts;DSrcCntks.DataSet:=
PMSDataModule.QryCntks;etc. 
But, the actual Appointment form DataSource component can't see the
DataModule.  I can assign in code but if I click on the DataSource on
the form and attempt to assign the DataSet property, the dropdown
doesn't reveal the DataModule Queries.  I have the datamodule in the
USES in the InterFace so tried defining a USES DM in the
implementation but Lazarus doesn't see the DM???????

----------------------------------------------------------------------
1HuntnMan · 2025-11-17 22:09

Figured this out, had to learn a bit about inline I guess, use of
procedures since all database everything is now in the DataModule. 
So, an AfterInsert or AfterDelete in a unit that's using the
DataModule learned this and works:

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
QryPropsls.AfterInsert:=
@PMSDataModule.QryPropslsAfterInsert;QryPropsls.AfterDelete:=
@PMSDataModule.QryPropslsAfterDelete; 
That's all folks, thanks for all the education!

----------------------------------------------------------------------
Thausand · 2025-11-17 22:29

What write Thaddy is good example why I think is better for make setup
and have use write code.

When have old project and use visual property setting then I no know
or have remember what property is set. This special true for database.

When make setup when use code then can
- see what is code do
- have code line comment for explain what code is do

I have make many waste time when work for project that have many coder
because I have make watch all property and see what is set and
connect.

All person that work on project make change personal and have make
time for detection what is change to make fix problem I have work for.

Visual develop is concept but when have example many database and have
team work then is no practible.

This personal think. It no wrong when have different practice. For me
is simple no practible. When example have problem and post problem on
forum and not show all property (or have database same) then how can
see or tell what wrong ? I have see this write happen many time on
forum and I think  is waste many time.

Thank Thaddy for example and good explain.

----------------------------------------------------------------------
1HuntnMan · 2025-12-05 14:23

Okay, put this back from SOLVED to UNSOLVED.  Discovered something by
accident. This morning, I didn't launch Lazarus.  Testing by running
from within Lazarus I kept getting "Database is Locked" everytime I
run debugging and getting this error whenever I saved an edit to a
record see the code below. But, ran the app from the executable
[PMS.exe] and no errors?????? WTF!

On Save event code:
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 procedure
TFrmAppmnts.BitBtnSaveClick(Sender: TObject);begin  if QryAppmts.State
in [dsEdit, dsInsert] then    begin      QryAppmts.Post;     
QryAppmts.ApplyUpdates;     
{SaveQueryChanges(QryAppmts,PMSDataModule.TransPMSDB);     
EnsureQueryReady(QryAppmts, True);}    end;end; 
The SaveQueryChanges and EnsureQueryReady is just a procedure(s) I put
in the DataModule but above I Commit the Transaction in the Query
property:

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 procedure
SaveQueryChanges(Q: TSQLQuery; Trans: TSQLTransaction);begin  if Q =
nil then Exit;   // 1) Finalize the record (if in edit/insert)  if
Q.State in [dsEdit, dsInsert] then    Q.Post;   // 2) Put all changes
to the DB (harmless if sqoAutoApplyUpdates already ran) 
Q.ApplyUpdates;   // 3) commit (use Commit; CommitRetaining is not
present on all builds)  if Assigned(Trans) and Trans.Active then 
begin    Trans.CommitRetaining;  //=> Durable Write    { If you want
“retaining-like” behavior during normal use,      immediately restart:
Trans.StartTransaction;      CommitRetaining keeps the transaction
open/active →      calling StartTransaction again throws “already
active”.      Commit ends the transaction → you may want to
immediately      StartTransaction again for long-lived UIs. }   
Trans.StartTransaction;  end;end; 
I'm happy but my last question, most of the advice was to hard code
the properties for the Query???? Are you saying to not put a Query
component on the DataModule or app unit form or the Transaction
component on the form, hard code that plus all the properties of the
Connection/Transaction and the query and datasource????

----------------------------------------------------------------------
Handoko · 2025-12-05 17:24

By no means am I an expert in database programming. But I want to
share some tips in coding. I don't mean to criticize, oppositely I
really appreciate 1HuntnMan's persistent effort to make the code work.
I hope my explanation can be useful.


Quote from: 1HuntnMan on December 05, 2025, 03:23:43 pmOn Save event
code:
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 procedure
TFrmAppmnts.BitBtnSaveClick(Sender: TObject);begin  if QryAppmts.State
in [dsEdit, dsInsert] then    begin      QryAppmts.Post;     
QryAppmts.ApplyUpdates;     
{SaveQueryChanges(QryAppmts,PMSDataModule.TransPMSDB);     
EnsureQueryReady(QryAppmts, True);}    end;end; 
The code above is a big no. All experienced programmers should tell
you "separate the UI part and the logic part", the logic part here
means the database related commands. The BitBtnSaveClick should call
the related procedure (or a function) in your database unit, not
accessing the database commands directly. All the database related
commands or procedures or functions should be put in a single unit
file, or module, or library or package.

You may ask why. If the project contains just 300 lines of code or
less, your approach should work fine. But if it needs to access
multiple tables, master-detail relationship, and many technical rules,
then the code will be very hard to extend and debug. Many 'weird'
things may happen just as what you experiencing now. Programming
doesn't just mean to make it work, you have to put effort to make your
life easier in the future by properly separate and group them into
several easy to maintain parts.

So, how to make the code above better? Something like this:

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 procedure
TFrmAppmnts.BitBtnSaveClick(Sender: TObject);begin  if
not(DataUnit.SaveAppmnts(param1, param2, param3, ...)) then   
ShowMessage('Failed to save appointment data');end; 
In the code above, the form does only what it should do: collecting
user inputs and actions, and showing message back to the user. If it
needs to access the database, it simply call a related
procedure/function and pass all the needed information via its
parameters. The BitBtnSave should not call any database commands
directly.

It may seem trivial, but if you abandon your old code but rewrite them
as what I said, you will thank yourself doing so. I dare to say so
because when I started learning database programming, I wrote the code
just as the way you're doing now. Visual programming sounded good, I
dropped some components onto the form, setting some properties and put
some database related commands in the form. What a pain maintaining
and debugging the code. My code worked but it's really messy.

Below is the upper part of uniData.pas - my recent project, for you to
analyze. I can't post the whole code here because it has more than
1000 lines. It is in Indonesian.

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 unit
uniData; // SQLite client version: 3.42.0 on Linux, 3.45.1 {$mode
ObjFPC}{$H+} interface uses  Classes, SysUtils, Controls, Dialogs,
StdCtrls, Grids, ExtCtrls, Math,  uniStorage; type  TSetting = record 
  DataFileName:       string[30];    ThresholdTransaksi: (ttJual,
ttBeli);    NamaMataUangUtama:  string[10];    DeskMataUangUtama:
 string[20];    NamaMataUangThres:  string[10];    DeskMataUangThres:
 string[20];    NamaMataUangLen:    Byte;    DeskMataUangLen:  
 Byte;    NilaiMaksimum:      integer;    NilaiDesimal:       Byte; 
end; var  Setting: TSetting; Type  TTableName = (tblMataUang,tblRate,
tblNasabah, tblNasabahJenis,    tblWargaNegara, tblPekerjaan,
tblTransaksi); function  isStringTooLong(var S: string; Len: Integer):
Boolean;function  isEditTooLong(Edit: TEdit; Len: Integer):
Boolean;function  isEditTooLong(lbeEdit: TLabeledEdit; Len: Integer):
Boolean;function  isValueBad(Value: string): Boolean;procedure
SanitizeValue(var Value: Double);procedure SanitizeValue(var Value:
string);procedure SanitizeValue(lbeEdit: TLabeledEdit);function
 ErrorMessage: string;procedure CreateDatabase;function
 ConfirmDelete(const Info: string): Boolean;procedure TableBrowse(tbl:
TTableName; Grid: TStringGrid; Page: Integer);procedure
TableDelete(tbl: TTableName; const ID: string);function
 TableRecordCount(tbl: TTableName) : Integer; // Transaksiprocedure
AddTransaksi(const Nasabah, MataUang, Rate, Waktu, Beli, Jual,  Total,
Threshold: string);procedure EditTransaksi(const ID, Nasabah,
MataUang, Rate, Beli, Jual,  Total, Threshold: string);function
 GetTransaksiNasabah(const ID: string): string;function
 GetTransaksiMataUang(const ID: string): string;function
 GetTransaksiWaktu(const ID: string; Format: Boolean): string;function
 isTransaksiBad(const Nasabah, MataUang, Rate, Waktu, Beli, Jual: 
string): Boolean;// Mata Uangprocedure AddMataUang(const Nama,
Deskripsi: string);procedure EditMataUang(const ID, Nama, Deskripsi:
string);function  GetMataUangNama(const ID: string): string;function
 isMataUangBad(const ID, Nama, Deskripsi: string): Boolean;//
Rateprocedure AddRate(const Waktu, MataUang, Jual, Beli:
string);procedure EditRate(const ID, MataUang, Jual, Beli:
string);function  GetRateMataUang(const ID: string): string;function
 GetRateWaktu(Const ID: string; Format: Boolean): string;function
 isRateBad(const ID, Waktu, MataUang, Jual, Beli: string):
Boolean;function  GetRate(const MataUang, Waktu: string; strResult:
TStringList):          Boolean;function  GetRateBeli(const MataUang,
Waktu: string): string;function  GetRateJual(const MataUang, Waktu:
string): string;function  GetRateMiddle(const Waktu: string):
string;// Nasabahprocedure AddNasabah(const Nama, Jenis, Identitas,
WargaNegara, Pekerjaan:  string);procedure EditNasabah(const ID, Nama,
Jenis, Identitas, WargaNegara, Pekerjaan:  string);function
 GetNasabahNama(const ID: string): string;function
 GetNasabahJenis(const ID: string): string;function
 GetNasabahWargaNegara(Const ID: string): string;function
 GetNasabahPekerjaan(Const ID: string): string;function
 isNasabahBad(const ID, Nama, Jenis, Identitas, WargaNegara,
Pekerjaan:          string): Boolean;// Nasabah Jenisprocedure
AddNasabahJenis(const Deskripsi: string);procedure
EditNasabahJenis(const ID, Deskripsi: string);function
 GetJenisDeskripsi(const ID: string): string;function
 isNasabahJenisBad(const ID, Deskripsi: string): Boolean;// Warga
Negaraprocedure AddWargaNegara(const Deskripsi: string);procedure
EditWargaNegara(const ID, Deskripsi: string);function
 GetWargaNegaraDeskripsi(const ID: string): string;function
 isWargaNegaraBad(const ID, Deskripsi: string): Boolean;//
Pekerjaanprocedure AddPekerjaan(const Deskripsi: string);procedure
EditPekerjaan(const ID, Deskripsi: string);function
 GetPekerjaanDeskripsi(const ID: string): string;function
 isPekerjaanBad(const ID, Deskripsi: string): Boolean;//
Settingprocedure AddSetting(const Threshold, Maksimum, Desimal:
string);procedure ReadSettings; const  DataFileName                =
'vnsData.sqlite';  DateTimeFormat              = 'dd-mm-yy hh:nn:ss'; 
MataUangNamaLength          = 10;  MataUangDeskripsiLength     = 20; 
NasabahNamaLength           = 30;  NasabahIdentitasLength      = 20; 
NasabahJenisDeskripsiLength = 20;  WargaNegaraDeskripsiLength  = 35; 
PekerjaanDeskripsiLength    = 30; implementation uses 
fruWaktu; function  TableName(tbl: TTableName): string;
forward;procedure PrepareData; forward;procedure DoQuery(const S:
string); forward;function  isTableHasID(tbl: TTableName; const ID:
string): Boolean; forward;procedure RateCallBack(Sender: TObject;
AData: Pointer); forward; const  // Transaksi  sqlCreateTransaksi =  
'CREATE TABLE tblTransaksi ( '                    +    'ID INTEGER
PRIMARY KEY AUTOINCREMENT NOT NULL, ' +    'Nasabah INTEGER NOT NULL,
'                      + // NasabahID    'MataUang INTEGER NOT NULL, '
                    + // MataUangID    'Rate INTEGER NOT NULL, '     
                  + // RateID    'Waktu REAL NOT NULL, '             
            +    'Beli REAL NOT NULL, '                            + 
 'Jual REAL NOT NULL, '                            +    'Total REAL
NOT NULL, '                           +    'Threshold REAL NOT NULL )
STRICT;';  sqlAddTransaksi =    'INSERT INTO tblTransaksi (Nasabah,
MataUang, Rate, Waktu, Beli, Jual, ' +    'Total, Threshold) VALUES
(''';  sqlDeleteTransaksi =    'DELETE FROM tblTransaksi WHERE
ID=''';  sqlEditTransaksi =    'UPDATE tblTransaksi SET '; 
sqlBrowseTransaksi =    'Transaksi.ID, Waktu, Nsb.Nama, Uang.Nama,
Jual, Beli, Total, Threshold ' +    'FROM tblTransaksi AS Transaksi '
                              +    'LEFT JOIN tblMataUang AS Uang ON
Transaksi.MataUang=Uang.ID '        +    'LEFT JOIN tblNasabah AS Nsb
ON Nasabah=Nsb.ID';  // Mata Uang  sqlCreateMataUang =    'CREATE
TABLE tblMataUang ( '                     +    'ID INTEGER PRIMARY KEY
AUTOINCREMENT NOT NULL, ' +    'Nama TEXT NOT NULL UNIQUE, '         
          +    'Deskripsi TEXT NOT NULL UNIQUE, '                +   
'CHECK (length(Nama) <= 10 AND length(Deskripsi) <= 20) ) STRICT;'; 
sqlAddMataUang =    'INSERT INTO tblMataUang (Nama, Deskripsi) VALUES
(''';  sqlDeleteMataUang =    'DELETE FROM tblMataUang WHERE ID='''; 
sqlEditMataUang =    'UPDATE tblMataUang SET ';  sqlTestMataUangNama
=    'SELECT * FROM tblMataUang WHERE Nama='''; 
sqlTestMataUangDeskripsi =    'SELECT * FROM tblMataUang WHERE
Deskripsi=''';  // Rate  sqlCreateRate =    'CREATE TABLE tblRate ( '
                       +    'ID INTEGER PRIMARY KEY AUTOINCREMENT NOT
NULL, ' +    'Waktu REAL NOT NULL, '                           +   
'MataUang INTEGER NOT NULL, '                     +    'Beli REAL NOT
NULL, '                         +    'Jual REAL NOT NULL, '          
     +    'Tengah REAL NOT NULL ) STRICT;';  sqlAddRate =    'INSERT
INTO tblRate (Waktu, MataUang, Beli, Jual, Tengah) VALUES ('''; 
sqlDeleteRate =    'DELETE FROM tblRate WHERE ID=''';  sqlEditRate = 
 'UPDATE tblRate SET ';  sqlBrowseRate =    'Rate.ID, Waktu,
Uang.Nama, Jual, Beli, Tengah FROM tblRate ' +    'AS Rate LEFT JOIN
tblMataUang AS Uang ON Rate.MataUang=Uang.ID';  // Nasabah 
sqlCreateNasabah =    'CREATE TABLE tblNasabah ( '                   
 +    'ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, ' +    'Nama
TEXT NOT NULL, '                            +    'Jenis INTEGER NOT
NULL, '                        +    'Identitas TEXT NOT NULL UNIQUE, '
               +    'WargaNegara INTEGER NOT NULL, '                
 +    'Pekerjaan INTEGER NOT NULL, '                    +    'CHECK
(length(Nama) <= 30 AND length(Identitas) <= 20) ) STRICT;'; 
sqlAddNasabah =    'INSERT INTO tblNasabah ' +    '(Nama, Jenis,
Identitas, WargaNegara, Pekerjaan) VALUES (''';  sqlDeleteNasabah =  
'DELETE FROM tblNasabah WHERE ID=''';  sqlEditNasabah =    'UPDATE
tblNasabah SET ';  sqlTestNasabahIdentitas =    'SELECT * FROM
tblNasabah WHERE Identitas=''';  sqlBrowseNasabah =    'Nsb.ID, Nama,
Jns.Deskripsi, Identitas, Wga.Deskripsi, '    +    'Pkj.Deskripsi FROM
tblNasabah AS Nsb '                      +    'LEFT JOIN
tblNasabahJenis AS Jns ON Nsb.Jenis=Jns.ID '      +    'LEFT JOIN
tblWargaNegara AS Wga ON Nsb.WargaNegara=Wga.ID ' +    'LEFT JOIN
tblPekerjaan As Pkj On Nsb.Pekerjaan=Pkj.ID';  // NasabahJenis 
sqlCreateNasabahJenis =    'CREATE TABLE tblNasabahJenis ( '         
      +    'ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, ' +   
'Deskripsi TEXT NOT NULL UNIQUE, '                +   
'CHECK(length(Deskripsi) <= 20) ) STRICT;';  sqlAddNasabahJenis =   
'INSERT INTO tblNasabahJenis (Deskripsi) VALUES ('''; 
sqlDeleteNasabahJenis =    'DELETE FROM tblNasabahJenis WHERE ID='''; 
sqlEditNasabahJenis =    'UPDATE tblNasabahJenis SET Deskripsi='''; 
sqlTestNasabahJenisDeskripsi =    'SELECT * FROM tblNasabahJenis WHERE
Deskripsi=''';  // WargaNegara  sqlCreateWargaNegara =    'CREATE
TABLE tblWargaNegara ( '                  +    'ID INTEGER PRIMARY KEY
AUTOINCREMENT NOT NULL, ' +    'Deskripsi TEXT NOT NULL UNIQUE, '    
          +    'CHECK(length(Deskripsi) <= 35) ) STRICT;'; 
sqlAddWargaNegara =    'INSERT INTO tblWargaNegara (Deskripsi) VALUES
(''';  sqlDeleteWargaNegara =    'DELETE FROM tblWargaNegara WHERE
ID=''';  sqlEditWargaNegara =    'UPDATE tblWargaNegara SET
Deskripsi=''';  sqlTestWargaNegaraDeskripsi =    'SELECT * FROM
tblWargaNegara WHERE Deskripsi=''';  // Pekerjaan  sqlCreatePekerjaan
=    'CREATE TABLE tblPekerjaan ( '                    +    'ID
INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, ' +    'Deskripsi TEXT NOT
NULL UNIQUE, '                +    'CHECK(length(Deskripsi) <= 30) )
STRICT;';  sqlAddPekerjaan =    'INSERT INTO tblPekerjaan (Deskripsi)
VALUES (''';  sqlDeletePekerjaan =    'DELETE FROM tblPekerjaan WHERE
ID=''';  sqlEditPekerjaan =    'UPDATE tblPekerjaan SET
Deskripsi=''';  sqlTestPekerjaanDeskripsi =    'SELECT * FROM
tblPekerjaan WHERE Deskripsi=''';  // General  sqlWhereID =    ' WHERE
ID=''';  // Setting  sqlCreateSetting =    'CREATE TABLE tblSetting (
'                                      +    'ThresholdTransaksi
INTEGER NOT NULL, '                           +    'NilaiMaksimum
INTEGER NOT NULL, '                                +    'NilaiDesimal
INTEGER NOT NULL, '                                 +    'CHECK
(ThresholdTransaksi >= 0 AND ThresholdTransaksi <= 1 AND ' +   
'NilaiMaksimum >= 1000000 AND NilaiMaksimum <= 1000000000 AND '   +  
'NilaiDesimal >= 0 AND NilaiDesimal <= 3) ) STRICT;';  sqlAddSetting
=    'INSERT INTO tblSetting ' +    '(ThresholdTransaksi,
NilaiMaksimum, NilaiDesimal) VALUES ('''; const  // Transaksi 
ErrTransaksiRateNotFound =    'Belum ada rate untuk mata uang
tersebut,' + LineEnding +    'silahkan diisikan ratenya terlebih
dahulu.';  ErrTransaksiEmpty =    'Semua item tidak boleh kosong.'; 
ErrTransaksiNilai =    'Nilai harus di atas 0 dan maksimum ';  // Mata
Uang  ErrMataUangIDNotFound =    'Internal error, MataUangID.'; 
ErrMataUangEmpty =    'Nama dan Keterangan tidak boleh kosong.'; 
ErrMataUangDuplicate =    'Mata uang tersebut sudah ada,' + LineEnding
+    'silahkan isikan yang lain.';  ErrMataUangLengthNama =   
'Maksimum 10 character untuk nama mata uang.';  ErrMataUangLengthDesc
=    'Maksimum 20 character untuk keterangan mata uang.';  // Rate 
ErrRateEmpty =    'Semua item tidak boleh kosong.';  ErrRateBeliValue
=    'Rate beli harus di atas 0 dan maksimum ';  ErrRateJualValue =  
'Rate jual harus di atas 0 dan maksimum ';  // Nasabah 
ErrNasabahIDNotFound =    'Internal error, NasabahID.'; 
ErrNasabahEmpty =    'Semua item tidak boleh kosong.'; 
ErrNasabahDuplicateDesc =    'Nasabah dengan identitas tersebut sudah
ada.';  ErrNasabahLengthNama =    'Maksimum 30 character untuk nama
nasabah.';  ErrNasabahLengthIdentitas =    'Maksimum 20 character
untuk identitas nasabah.';  // NasabahJenis  ErrNasabahJenisIDNotFound
=    'Internal error, NasabahJenisID.';  ErrNasabahJenisEmpty =   
'Jenis nasabah tidak boleh kosong.';  ErrNasabahJenisDuplicateDesc = 
 'Jenis nasabah tersebut sudah ada,' + LineEnding +    'silahkan
isikan jenis nasabah yang lain.';  ErrNasabahJenisLengthDesc =   
'Maksimum 20 character untuk jenis nasabah.';  // WargaNegara 
ErrWargaNegaraIDNotFound =    'Internal error, WargaNegaraID.'; 
ErrWargaNegaraEmpty =    'Warga negara tidak boleh kosong.'; 
ErrWargaNegaraDuplicateDesc =    'Warga negara tersebut sudah ada,' +
LineEnding +    'silahkan isikan nama warga negara yang lain.'; 
ErrWargaNegaraLengthDesc =    'Maksimum 35 character untuk nama warga
negara.';  // Pekerjaan  ErrPekerjaanIDNotFound =    'Internal error,
PekerjaanID.';  ErrPekerjaanEmpty =    'Pekerjaan tidak boleh
kosong.';  ErrPekerjaanDuplicateDesc =    'Pekerjaan tersebut sudah
ada,' + LineEnding +    'silahkan isikan nama pekerjaan yang lain.'; 
ErrPekerjaanLengthDesc =    'Maksimum 30 character untuk nama
pekerjaan.'; var  Data:     TSQLite = nil;  ErrorMsg: string  =
'';... 
1. Line #14..#28
I used TSetting because the code was intended to allow users to open
different data files each with its own settings.

2. Line #30..#32
The program has 7 tables as defined in the TTableName. I needed to do
so because I want to centralize similar code into same location. See
no. 5 below.

3. Line #34..#40
These are for user input validation and sanitizing bad input. I passed
TEdit and TLabelEdit directly because I don't want to convert them to
string when passing them as parameters.

4. Line #41
The last database error, user incorrect input message and validation
error is stored and can be showed by calling ErrorMessage function.

5. Line #42..#46
These are the basic database access. Notice the use of TTableName, I
can use same code but for different tables.

6. Line #48..#103
These are for the form to connect to the database, like loading data,
clicking save and edit buttons.

7. Line #108..#114
These are the allowed lengths for the user inputs, used for validating
user inputs. See no. 8 below.

8. Line #56, #62, #68, #83, #89, #94, #99
These are for validating user inputs before saving them to its related
tables.  If the value provided by user is unacceptable, it will store
the error message (see no. 10) and the value can be accessed by the
user input form by calling ErrorMessage function (see no. 4).

9. Line #127..#268
I put all the sql query code here. So I can easily read, compare, and
extend. If I need a new table, I just copy-paste here and do some
edit. All of them are centralized in the same location. For easy
maintain and debugging.

10. Line #270..#338
Putting all the error messages in the same location.


As you can see, my code is well organized. If I want to add new
feature for the data, I know where to add. If I found something weird,
I can easily test each part.

----------------------------------------------------------------------
cdbc · 2025-12-05 18:19

Hi
Well done Handoko =^
Regards Benny

----------------------------------------------------------------------
Thausand · 2025-12-05 19:25

Quote from: cdbc on December 05, 2025, 07:19:09 pmWell done Handoko =^
That +1 

When do what do Handoko many time then have come experience that many
things are same. Then can have create separate class(es) that have
this functionality. Then can make separation gui en database logic
more easy. That is when is use very big database and have many many
tables. It have logic progress.

When design database and use IDE design this nice for quick small
database and have learn how is work database basic very quick (RAD).
Then want extend and have problem and error as write  Handoko. Then is
better have use code and no use IDE design and have mixing GUI and
database code.

Many time problem that make newbie is use formcreate and have all
connection auto and give many problem and not find database, not find
library, database is lock, directory no exist, server no connect etc.
When use separate routine as write Handoko then error is not
GUI/property but have code error. That more easy for debug and more
easy for maintain (can have better customize).

This experience have come when make database program many time. Every
person have favorite but when have need for maintain then better for
maintain is have use code and no IDE design for database. Have note
that is no need for manual create all database component and when make
approach Handoko. When start then can have have use manual code create
for connection (database, transaction, query). Is no judge but when is
child then have little step then grow and make step more big. Many
small step make travel for have reach same goal

----------------------------------------------------------------------
1HuntnMan · 2025-12-14 18:01

Okay, Handoko. I did some of what you advised, i.e. the
SaveQueryChanges procedure in the DataModule.  So, if I'm
understanding properly, for my app. in the DataModule, consolidate
everything that manipulates the data in tables in the DataModule. So,
I can create queries, datasources, Connections and transactions
without putting those components on the DataModule form, correct? Then
code the properties needed and let's say for example, my Client Form
is just the unit/form with just a DBNavigator or DBGrid if needed and
just DBEdits, DBLookUps and maybe a DBMemo, DBDates, etc. on the form
but all the database management is coded in the datamodule from what I
understand and back at the Clients unit, I just use data manipulation
designed on the data module.

I'm learning a lot here, thanks all.  Also, figured out the Database
is Locked was attempting to save data without the transaction active.

----------------------------------------------------------------------
Xenno · 2025-12-15 05:21

Maybe database transaction needs attention. AFAIK, SQLdb automatically
begin transaction on every SQL statement. I handle database
transaction manually to override this behaviour.

I employ 3 subroutines like below with one variable to to track nested
transaction:

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   ... 
FTransCount: Integer;  ... procedure DMMain.PrcTransBegin;begin  if
(FTransCount = 0) then    MyDB.Transaction.StartTransaction; 
Inc(FTransCount);end; procedure DMMain.PrcTransCommit;begin  if
(FTransCount = 1) then    MyDB.Transaction.Commit; 
Dec(FTransCount);end; procedure DMMain.PrcTransRollback;begin  if
(FTransCount = 1) then    MyDB.Transaction.Rollback; 
Dec(FTransCount);end;
Then, for every database related action (SQL execution), I wrap it in
a try-except:

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
PrcTransBegin;try  // ..  // database related action here  // .. 
PrcTransCommit;except  PrcTransRollback;   raise;end;
This model ensures database transaction is under our control.

----------------------------------------------------------------------
1HuntnMan · 2025-12-18 20:57

Okay, revamped my DataModule and moved most code for the SQLIte3 table
management to the DataModule as suggested. Here goes in the
DataModule...

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 procedure
TPMSDataModule.PMSDataModuleCreate(Sender: TObject);var  PMS_DB:
String;begin  Randomize;  ShuttingDown := False;   PMS_DB :=
SysUtils.ExtractFilePath(ParamStr(0)) + 'PMSDB.sqlite3';  if not
FileExists(PMS_DB) then  begin    ShowMessage('PMS Database file ' +
PMS_DB + ' is missing, exiting!');    Exit;  end;   try    // 1) Main
R/W Connection    ConnectPMSDB.DatabaseName := PMS_DB;   
ConnectPMSDB.Connected := True;     // 2) Pragmas (run AFTER connect) 
  ConnectPMSDB.ExecuteDirect('PRAGMA journal_mode=WAL;');   
ConnectPMSDB.ExecuteDirect('PRAGMA synchronous=NORMAL;');   
ConnectPMSDB.ExecuteDirect('PRAGMA foreign_keys=ON;');   
ConnectPMSDB.ExecuteDirect('PRAGMA busy_timeout=5000;');     // 3)
Transaction wiring (not starting StartTransaction here)   
TransPMSDB.DataBase := ConnectPMSDB;    TransPMSDB.Action :=
caCommitRetaining;    TransPMSDB.Active:= True;     // 4) Initialize
and open lookups: States, Countries, PayStatus, etc.    SetupDB;   
OpenLookUpTables;     // 5) Read-Only Connection/Transaction, mirror
but decided I don't need...    {ConnectPMSDB_RO.DatabaseName :=
PMS_DB;    ConnectPMSDB_RO.Connected := True;   
ConnectPMSDB_RO.ExecuteDirect('PRAGMA journal_mode=WAL;');   
ConnectPMSDB_RO.ExecuteDirect('PRAGMA synchronous=NORMAL;');   
ConnectPMSDB_RO.ExecuteDirect('PRAGMA foreign_keys=ON;');   
ConnectPMSDB_RO.ExecuteDirect('PRAGMA busy_timeout=5000;');   
TransRO.Database := ConnectPMSDB_RO;    TransRO.Action := caNone;}  
except    on E: Exception do      ShowMessage('PMS DataBase
initialization failed: ' + E.Message);  end;end; 
Then decided to take care of each of the main procedures queries in
the forms, i.e. Clients Mgt., Contacts Mgt. Invoice Mgt., Referrals
Mgt., etc. Here's an example of the Clients form Create procedure:

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 procedure
TFrmClientsMgt.FormShow(Sender: TObject);begin  //=> Bootstrap... 
PMSDataModule.EnsureConnected; //=> Ensure SQL Connection... 
PMSDataModule.OpenLookUpTables; //=> Open the lookup tables... 
QryClients.Open;  QryCntks.Open;  //EnsureQueryReady(QryClients,
True); //=> Open QryClients...  //EnsureQueryReady(QryCntks);  if not
PMSDataModule.TransPMSDB.Active then   
PMSDataModule.TransPMSDB.StartTransaction; 
PMSDataModule.QryRefType.FieldByName('REFCODE').DisplayWidth:= 5; 
PMSDataModule.QryRefType.FieldByName('DESCRIPT').DisplayWidth:= 15; 
PMSDataModule.QryStates.FieldByName('STPROVCODE').DisplayWidth:= 3; 
PMSDataModule.QryStates.FieldByName('STPROVNAME').DisplayWidth:= 12; 
PMSDataModule.QryCntries.FieldByName('CODE').DisplayWidth:= 3; 
PMSDataModule.QryCntries.FieldByName('COUNTRY').DisplayWidth:= 12; 
PMSDataModule.QryTypeFirm.FieldByName('TYPEFIRM').DisplayWidth:= 10; 
PMSDataModule.QryTypeFirm.FieldByName('DESCRIPTION').DisplayWidth:=
20;  if QryClients.IsEmpty then    QryClients.Append  else   
UpDateTTLClients;  Screen.Cursor:= crDefault; 
DBEditClntName.SetFocus;end; 
Now, according to what I'm learning, in the datamodule the
Transactions are set to CommitRetaining which keeps the transaction
open which was set in the datamodule.  So, to save a record btnSave
click procedure:

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 procedure
TFrmClientsMgt.BitBtnSaveClick(Sender: TObject);begin  if
QryClients.State in [dsEdit, dsInsert] then    begin     
QryClients.Post;      QryClients.ApplyUpdates;      //=> Close/Open
instead of Refresh with SQLite...      QryClients.Close;     
QryClients.Open;    end;end; 
So, my understanding the Transactions are set from the get-go to
Commit.Retaining (transactions are open). So, in my form close I check
in case the user clicks the close button... below is the FormClose
procedure.

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 procedure
TFrmClientsMgt.FormClose(Sender: TObject;  var CloseAction:
TCloseAction);begin  QryCntks.DataSource:= Nil; 
PMSDataModule.CloseLookupTables;  QryCountClnts.Close; 
QryCntks.Close;  with QryClients do    begin      if State in [dsEdit,
dsInsert] then        if MessageDlg('Changes pending, save current
updates?', mtConfirmation,         [mbYes, mbNo],0) = mrYes then     
   begin            Post;            ApplyUpdates;            Close; 
         Open;          end      else Cancel;    end; 
PMSDataModule.TransPMSDB.Commit; { Ends the transaction for this form
}  QryClients.Close;  FreeAndNil(keys);end; 

So, this is what I understanding.  But, the above line:
TransPMSDB.Commit, causes a DataBase is locked error.

I'm not understanding or something isn't in the right order. Any
advice would be greatly appreciated. This DataBase is Locked is
holding up the completion of this app. Thanks!!!

----------------------------------------------------------------------
1HuntnMan · 2025-12-19 13:21

I think as Xenno alerted me of the problem. I'm not handling/managing
transactions properly. That's the culprit that's holding up the whole
show.  Trying to learn from Handoko's advice.  Thinking about
revamping the whole project but going to try to figure out this
transactions issue.  I shouldn't be that much of an issue. Then start
over on this project and approach it with all database manipulation in
the DataModule and let the Clients Mgt, Invoices, Sales Orders just be
presentation to the user, no database components, move all that to the
datamodule.  But, would like to learn just how to save a frigging
record! LoL!

----------------------------------------------------------------------
Xenno · 2025-12-20 07:30

Great, 1HuntnMan.

Actually, I am a little curious, did you trace on your code below?

Quote from: 1HuntnMan on December 18, 2025, 09:57:17 pmCode: Pascal
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i   ...  try 
  // 1) Main R/W Connection    ConnectPMSDB.DatabaseName := PMS_DB;  
ConnectPMSDB.Connected := True;     // 2) Pragmas (run AFTER connect) 
  ConnectPMSDB.ExecuteDirect('PRAGMA journal_mode=WAL;');   
ConnectPMSDB.ExecuteDirect('PRAGMA synchronous=NORMAL;');   
ConnectPMSDB.ExecuteDirect('PRAGMA foreign_keys=ON;');   
ConnectPMSDB.ExecuteDirect('PRAGMA busy_timeout=5000;');     // 3)
Transaction wiring (not starting StartTransaction here)   
TransPMSDB.DataBase := ConnectPMSDB;    TransPMSDB.Action :=
caCommitRetaining;    TransPMSDB.Active:= True;  ... 
It should be error "transaction not set" on the line of PRAGMA
journal_mode.

If we change the code like below, we will have another error: "cannot
change to into wal mode"

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   ...  try 
  // 1) Main R/W Connection    ConnectPMSDB.DatabaseName := PMS_DB;  
ConnectPMSDB.Connected := True;     // 3) Transaction wiring (not
starting StartTransaction here)    TransPMSDB.DataBase :=
ConnectPMSDB;    TransPMSDB.Action := caCommitRetaining;   
TransPMSDB.Active:= True;     // 2) Pragmas (run AFTER connect)   
ConnectPMSDB.ExecuteDirect('PRAGMA journal_mode=WAL;');  ... 
You may want to skip PRAGMA journal_mode line and see how it is going.
I agree WAL is the recommended mode for multi connections or
multithreaded. WAL helps such as running lengthy database processes
(involves writing) in separate thread smoother. If the main thread
tries reading the busy database, WAL works good preventing locking
issues and maintains performance.

Usually we need a hack to change journal_mode. I use SQLite in my apps
with journal mode WAL or TRUNCATE but I don't use SQLdb. Especially
because I don't use Data Controls components in those apps.

----------------------------------------------------------------------
1HuntnMan · 2025-12-21 14:19

Thanks for that, I finally fixed all these issues and mainly learning
from another post, I was freeing objects in FormClose that are already
freed and doing things in FormClose that should be in FormTerminate. 
Also, when I was running not in Lazarus with debugger running but just
running the app.exe I wasn't getting these DataBase is locked or
Access errors.  So, the culpret was freeing objects that are
automatically freed and doing things in form close that shouldn't be
done. Awesome and thanks!