Update a table with an Autoinc field
lazarus / 16-Databases

----------------------------------------------------------------------
CraigC · 2026-03-04 14:21

Everything I have read says just to post and apply.  But the database
sqlite doesn't like that.  What am I missing?  I am using dbedits. 
Lazarus 4.4

----------------------------------------------------------------------
JanRoza · 2026-03-04 17:41

Using SQLite daily with AutoInc fields and editing, updating and
posting and no problems at all.
You must have missed something but without an example of your code it
is impossible to comment or advise.

----------------------------------------------------------------------
Hansvb · 2026-03-04 20:25

This is a good start
Quotehttps://wiki.lazarus.freepascal.org/SQLdb_Tutorial1At the bottom
you will find tutorial 2, 3 and 4.

----------------------------------------------------------------------
CraigC · 2026-03-04 20:34

Not sure where to start.  According to what I have read, Lazarus is
supposed to take care of it. 

options in the TSQLiteConnection is
:[scoApplyUpdatesChecksRowsAffected]
TSQLTransaction isn't modified.

TSQLQuery: Upda]"]>Blockedde is upWhereKeyOnly   I've tried
upWhereChanged

procedure SaveData(qry: TSQLQuery; bSave: Boolean);
begin
//  if not dmCommonData.transLedger.Active then
dmCommonData.transLedger.Active:= True;
  qry.ParamCheck := False;
  if bSave and DataState(qry) then
    qry.ApplyUpdates
  else
  begin
    if not DataState(qry) Then exit;
    qry.CancelUpdates;
  End;
  qry.SQLTransaction.Commit;
  qry.SQLTransaction.EndTransaction;
end;

----------------------------------------------------------------------
Zvoni · 2026-03-05 07:25

We need to see the SQL_Statement of qry used to pull the data

----------------------------------------------------------------------
CraigC · 2026-03-05 12:00

"Select * from Inventory" is the SQL statement at the moment.

Table structure below.

CREATE TABLE IP_Product
(
    UID          Integer NOT NULL CONSTRAINT PK_IP_Product PRIMARY KEY
AUTOINCREMENT,
    UOM          Char(3),
    UnitPrice    Decimal(19, 2) NOT NULL DEFAULT NULL,
    AvgCost      Decimal(19, 2) DEFAULT NULL,
    StandardCost Decimal(19, 2) NOT NULL,
    Suspended    Boolean DEFAULT NULL,
    ProductNote  Text,
    ProductClass Integer,
    MfgProductNo Char(20),
    Taxable1     Boolean DEFAULT NULL,
    Taxable2     Boolean DEFAULT NULL,
    StdQty       Float NOT NULL,
    VendorUID    Integer NOT NULL,
    Allocated    Decimal(19, 2),
    OnHand       Decimal(19, 2),
    OnOrder      Decimal(19, 2),
    MonthlySales Float,
    YearlySales  Float,
    MinQty       Decimal(19, 2),
    MaxQty       Decimal(19, 2),
    ReorderQty   Decimal(19, 2),
    CoUID        Integer NOT NULL,
    ProdNo       Char(20),
    ProdDesc     Char(40)
);
CREATE INDEX ProdNo
 ON IP_Product(ProdNo);

----------------------------------------------------------------------
Zvoni · 2026-03-05 12:14

OK, i see some issues
1) Get rid of the AUTOINCREMENT in UID-primary Key. You don't need it.
SQLite will still count up automatically.
And remove the NOT NULL from UID. If a Column is Declared PRIMARY KEY,
then it automatically inherits NOT NULL and UNIQUE

2) UnitPrice    Decimal(19, 2) NOT NULL DEFAULT NULL,
SERIOUSLY?????
Unit Price is not allowed to be NULL, but you Default to NULL if no
value provided

3) Don't use SELECT * FROM... it's a ticking TimeBomb. Select explicit
Fields (incl. the Primary Key) which you need

4) What's the name of the Table now? "Inventory" or "IP_Product"?
"SELECT * FROM Inventory" vs. "CREATE TABLE IP_Product"

5) BOOLEAN Default NULL is nonsense. It's either 0 or nonzero

6) Don't use Decimal(19,2). It gets NUMERIC Affinity, and that can be
pretty much anything. Rather use Double
And never ever store decimal precision in the Database. If you need
that precision, use integers with factors (the use of REAL (Double)
Datatype not withstanding)
e.g. a Value of "1234.56" gets stored as "123456" (multiply with 100
before Inserting/Updating), and for Displaying you just divide by 100.
And even this division for displaying is easily done with a generated
Column, so you don't have to do it in the Frontend

7) Where is the Definition of this Constraint --> CONSTRAINT
PK_IP_Product

8 ) qry.ParamCheck := False; --> This one gives me the creeps.....the
only scenario you would need ParamCheck:=False is if you want to pass
a Statement containing Parameters as literals

That's on first glance

----------------------------------------------------------------------
CraigC · 2026-03-05 18:43

Thank you for taking the time to reply.  I'm getting my feet wet with
all the SQL and you proved that I have a way to go.  I do appreciate
your time. 

I'm going to print your reply out and  use it as a guide.  I still
have a lot of tables to add to the DB.

Craig

----------------------------------------------------------------------
Zvoni · 2026-03-06 08:50

Quote from: CraigC on March 05, 2026, 07:43:56 pm
Thank you for taking the time to reply.  I'm getting my feet wet with
all the SQL and you proved that I have a way to go.  I do appreciate
your time. 

I'm going to print your reply out and  use it as a guide.  I still
have a lot of tables to add to the DB.

Craig
Your "Problem" with SQL is secondary, or even way later

First thing to tackle is the design of your table(s).

Like
This Field has to be alphanumeric (e.g. Product Description)
This Field has to be UNIQUE (e.g. Product Number)
This Field has to be a floating point number (e.g. Price)
This Field will never have decimals/Fractions (e.g. Packaging Unit)
This Field must always have a Value (e.g. StockQty) --> NOT NULL in
combination with DEFAULT 0
etc. etc.
choosing DataTypes and additional constraints comes after that

Next would be:
Which operations can be automated by the Database itself (e.g. with
Triggers, Generated Columns and similar)
Which are the informations i'm going to use regularly? --> Views
What's the amount of Data i'm going to handle? Creating/Using Indexes

When all of this is done, then you develop your SQL-Statements you're
going to need (SELECT, INSERT, UPDATE, DELETE)

After you have all of that: THEN you start coding your Frontend, using
e.g. DB-Bound controls like a DBEdit, DBGrid or whatever else

----------------------------------------------------------------------
wp · 2026-03-06 10:05

Quote from: Zvoni on March 05, 2026, 01:14:15 pm5) BOOLEAN Default
NULL is nonsense. It's either 0 or nonzero
I do  not have much experience with sqlite. But it is my impression
that a default NULL value for a boolean field does make sense: it
indicates that no decision on the value has been made - the
application could prompt the user "This is an important field which
decides how the program will continue - you MUST select either true or
false." But when the field is prefilled with 0 or something else such
a question cannot be asked.

----------------------------------------------------------------------
Zvoni · 2026-03-06 10:36

Quote from: wp on March 06, 2026, 11:05:07 amQuote from: Zvoni on
March 05, 2026, 01:14:15 pm5) BOOLEAN Default NULL is nonsense. It's
either 0 or nonzero
I do  not have much experience with sqlite. But it is my impression
that a default NULL value for a boolean field does make sense: it
indicates that no decision on the value has been made - the
application could prompt the user "This is an important field which
decides how the program will continue - you MUST select either true or
false." But when the field is prefilled with 0 or something else such
a question cannot be asked.
The "Problem" with Boolean and SQLite is: SQLite doesn't have a
dedicated DataType for Booleans, and assigns internally an Integer for
it (Which actually is pretty common, see also how a Boolean is
basically a TypeDef for an Integer in FreePascal).

I disagree with you regarding the "no decision made": If there is no
decision, then it's false. Period! (Or True depending on the UseCase)
Or the Semantic of a Boolean is the wrong tool for this.
Boolean has 2 states: True or False.
If you need more than that, then boolean is the wrong type.

Nevermind the following scenario:
Let's pretend OP leaves the BOOLEAN Default NULL like it is now:
he has a record, and that Boolean-Field is actually really a NULL.
He now pulls that record from the Database.
And what now?
What happens when he does: MyQuery.FieldByName('Suspended').AsBoolean
Will Freepascal translate the NULL to a False?
Or is the code going to KABOOM?

As for the Booleans used in OP's table: (Suspended, Taxable1,
taxable2):
It's either taxable, or it's not. There is nothing in between
It's either suspended, or it's not. There is nothing in between

No offense to differing opinions

EDIT: On a sidenote.
I was just reading his CREATE-Statement again, and noticed the 4 CHAR-
Fields
SQlite accepts the Datatype "CHAR(20)" but completely IGNORES the size
"(20)" of the Field.
It just gets stored in the Metadata, but nothing is going to prevent
you inserting a String of Length 100
Any sanitizing has to happen in Frontend-Code

----------------------------------------------------------------------
CraigC · 2026-03-06 12:36

As far as Boolean field type goes, I chose NULL because I wanted a
default of False.  Which it isn't.    But, I will be setting it to
False on the New Record event.

As far as setting up SQL statements for SELECT, UPDATE, I currently
let Lazarus or SQLite handle that with where changed.  Since I am
fairly (read, Noob) new to this, is that bad or how do you write and
update where you don't know what has changed?  Adding a static
statement doesn't seen correct to me.  Please set me straight.

Craig

----------------------------------------------------------------------
Zvoni · 2026-03-06 13:18

Quote from: CraigC on March 06, 2026, 01:36:44 pm
As far as Boolean field type goes, I chose NULL because I wanted a
default of False.  Which it isn't.    But, I will be setting it to
False on the New Record event.
Use DEFAULT 0 ("Zero" as an Integer). No need for "NewRecord" or
whatever
SQLite has no dedicated Boolean-Datatype. Boolean gets "translated" to
integer in the background.
I do have such an application currently, where i have Boolean-Fields
in the Database, and they are just Integers (0=False, everything
else=True)

QuoteAs far as setting up SQL statements for SELECT, UPDATE, I
currently let Lazarus or SQLite handle that with where changed.  Since
I am fairly (read, Noob) new to this, is that bad or how do you write
and update where you don't know what has changed?  Adding a static
statement doesn't seen correct to me.  Please set me straight.

Craig
Don't!
As i wrote: Don't use "SELECT * FROM" for your Select-Statement. Write
it out, and assign it to the SQL-Property

As for INSERT/UPDATE: The same: write it out, but you have to follow
the "Rules"

e.g. INSERT (That's one of my own Statements currently in Production)
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
tbl_employees(EmployeeNumber, FirstName, LastName, IsActive,
PowerUserID) VALUES(:EmployeeNumber, :FirstName, :LastName, :IsActive,
:PowerUserID);Note the Colons in the Value-Part --> ParameterNames
MUST be the Same as the Fieldnames prepended with a colon
Note thate my Primary Key "ID" doesn't appear here!
Write it out, test it, then assign it to "InsertSQL"

e.g. UPDATE (That's one of my own Statements currently in Production)
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i UPDATE
tbl_employees SET EmployeeNumber=:EmployeeNumber,
FirstName=:FirstName, LastName=:LastName, IsActive=:IsActive,
PowerUserID=:PowerUserID WHERE ID=:OLD_ID;Important: Note the LAST
Parameter :OLD_ID
"ID" is my Primary Key
In your case it would be ":OLD_UID"
Write it out, test it, then assign it to "UpdateSQL"

There is a reason, why i'm a proponent of "writing out" the
statements:
There might be Fields in your Database, you don't want to or are not
allowed to Update
There might be Fields in your Database, you are not allowed to Insert
a Value
A "classic" is a Generated Column "FullName", which Combines FirstName
and LastName on the DB-Side!! --> No INSERT or UPDATE allowed

Another reason is, if your initial SELECT-Statement contains JOINS of
2 or more tables, then you'll get a problem with "automagically"
created InsertSQL/UpdateSQL

I'll leave the DeleteSQL for you

----------------------------------------------------------------------
CraigC · 2026-03-06 13:27

Thank you for the reply.  I'm going to get my head around this and
just enter standard data to get a feel for this before I get into more
complicated accounting routines.

I'm in the beginning stages of converting my Delphi code for what used
to be a local set of tables (i.e. Borland BDE) style application.   I
did dable a bit with Interbase and Firebird a while back and liked
Stored Procedures. I'll have to look into Triggers wtih SQLite.

----------------------------------------------------------------------
Zvoni · 2026-03-06 13:31

Quote from: CraigC on March 06, 2026, 02:27:24 pm
Thank you for the reply.  I'm going to get my head around this and
just enter standard data to get a feel for this before I get into more
complicated accounting routines.

I'm in the beginning stages of converting my Delphi code for what used
to be a local set of tables (i.e. Borland BDE) style application.   I
did dable a bit with Interbase and Firebird a while back and liked
Stored Procedures. I'll have to look into Triggers wtih SQLite.
Note: SQLite doesn't support "stored procedures" in the "classical"
sense you might be used to from e.g. Firebird

Triggers (in any DB-System) are more like "Events" you are used to
from Lazarus/Freepascal or Delphi
I use Triggers extensively for Auditing purposes ("who changed when
which value for which record in this table" etc.)

----------------------------------------------------------------------
CraigC · 2026-03-06 13:37

I have a log table that I post a comment in.  UserName, what area and
what did they do.  A trigger can get all this info or better to use my
log procedure?

Thanks again.

----------------------------------------------------------------------
Zvoni · 2026-03-06 13:46

Quote from: CraigC on March 06, 2026, 02:37:52 pm
I have a log table that I post a comment in.  UserName, what area and
what did they do.  A trigger can get all this info or better to use my
log procedure?

Thanks again.
If you use a separate Call from your Frontend (your Log Procedure?),
i'll open your SQLite Database with an external Tool (HeidiSQL,
DBeaver, DB Browser for SQLite), delete the record, and you'll be none
the wiser

With a Trigger, the Inserts and Updates are pretty easy, since you can
access all the Fields inside the Trigger (Keywords: "OLD" and
"NEW"-Tablenames)

A Delete is a bit trickier since you're basically only getting the ID
for the Record to delete (no User-Information), but as tricky as it
might sound: It is doable with triggers, but it needs "ouside-the-
box"-thinking
Keyword: a "dummy"-table you do an INSERT on with ID AND UserName,
Area, whatever,
which has an AFTER INSERT-Trigger.
And inside THAT Trigger you do the
DELETE FROM SomeTable WHERE ID=New.ID
INSERT INTO LogTable.........
DELETE FROM DummyTable WHERE 1=1

The beauty of Triggers is, that they are part of the Transaction
calling the operation.
meaning: If Your INSERT/UPDATE/DELETE fails (or is rolled back), the
whole chain gets rolled back, incl. your logging

----------------------------------------------------------------------
wp · 2026-03-06 15:16

Quote from: Zvoni on March 06, 2026, 11:36:18 amI disagree with you
regarding the "no decision made": If there is no decision, then it's
false. Period! (Or True depending on the UseCase)
Suppose a dish ordering system in a restaurant having a variety of
dishes, and each dish has the option "with dessert" or, with some
price discount, "without dessert". The database managing all this has
a boolean field "Dessert" for this option. I am absolutely convinced
that the default value for this field must be NULL. Otherwise, if it
were true, and the waitress would forget to ask the guest whether he
wants a dessert or not, he would get a dessert even if he would not
want it. (and even would have to pay for it).

In such a case, a boolean field has three states: true, false,
undefined. The visual controls, TCheckbox and TDBCheckbox, (which
specialized for boolean fields!) are able to display the disabled
state ("grayed" in the old days, today that strange '-' symbol).

QuoteNevermind the following scenario:
Let's pretend OP leaves the BOOLEAN Default NULL like it is now:
he has a record, and that Boolean-Field is actually really a NULL.
He now pulls that record from the Database.
And what now?
What happens when he does: MyQuery.FieldByName('Suspended').AsBoolean
Will Freepascal translate the NULL to a False?
When a database is allowed to contain NULL values every query of a
field value must be accompanied by an IsNull check before, and the
program must decide what to do since the user did not yet provide an
input in this field. In case of the dish ordering system above, the
waitress would not be able to place the order (store the record) as
long as the "Dessert" field is NULL.

I am not saying that this is the general use. But I certainly would
not say "Boolean DEFAULT is nonsense".

----------------------------------------------------------------------
CraigC · 2026-03-07 12:56

Well, I have added SQL statements for Update and Insert. If I enter
data for all fields it works great.  No errors.  I would leave the
AcctNotes field empty because it's optional and it's not set as not
null.  I get an error . see screenshot.  It was a blob field so I
thought I will change it to a stringfield and I still get the error.
Even if it has text in it.  The error says "near AcctNotes".  The
error is on a new record and the field is blank and it I edit the
record and add text to the field.   I'm at a loss here.


 qryGLAccts_.UpdateSQL.Text := 'UPDATE GLACCTS SET
AcctNumber=:AcctNumber, AcctDescription=:AcctDescription '+
    'AcctNotes=:AcctNotes, AcctType=:AcctType, AcctClass=:AcctClass,
AcctCat=:AcctCat, AcctSuspended=:AcctSuspended, '+
    'AcctParent=:AcctParent, UID=:UID';

  qryGLAccts_.InsertSQL.Text := 'INSERT INTO GLAccts(AcctNumber,
AcctDescription, AcctNotes, AcctType, AcctClass, '+
    'AcctCat, AcctSuspended, AcctParent, UID) VALUES(:AcctNumber,
:AcctDescription, :AcctNotes, :AcctType, :AcctClass, '+
    ' :AcctCat, :AcctSuspended, :AcctParent, :UID)';

----------------------------------------------------------------------
Zvoni · 2026-03-08 06:12

There’s a comma missing in your update
After acctdescription
And no where-clause?

----------------------------------------------------------------------
CraigC · 2026-03-08 11:29

I could look at that for who knows how long and not see that.  As I
have found out in my experience, it's usually something simple.  Thank
you, Zvoni!

----------------------------------------------------------------------
Zvoni · 2026-03-09 06:56

Quote from: CraigC on March 08, 2026, 12:29:38 pm
I could look at that for who knows how long and not see that.  As I
have found out in my experience, it's usually something simple.  Thank
you, Zvoni!
You do realize you don't have a WHERE-Clause in your UpdateSQL?
It means ALL records will be updated with the Values for the Record
you are updating
resp. no record at all should be updated, since you included your
Primary Key in the Values-Section, and that should go KABOOM

Correct:
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
 qryGLAccts_.UpdateSQL.Text := 'UPDATE GLACCTS SET
AcctNumber=:AcctNumber, AcctDescription=:AcctDescription, '+  //Comma
added    'AcctNotes=:AcctNotes, AcctType=:AcctType,
AcctClass=:AcctClass, AcctCat=:AcctCat, AcctSuspended=:AcctSuspended,
'+    'AcctParent=:AcctParent WHERE UID=:OLD_UID';  //Where-Clause
added 
Next: Don't pass your Primary Key in an INSERT. NEVER EVER!
Exception to the Rule: If you use "natural" Primary Keys, that are
"created" in your Frontend
If you use surrogate Primary Keys (like you do), never pass the
Primary key in an INSERT
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
qryGLAccts_.InsertSQL.Text := 'INSERT INTO GLAccts(AcctNumber,
AcctDescription, AcctNotes, AcctType, AcctClass, '+    'AcctCat,
AcctSuspended, AcctParent) VALUES(:AcctNumber, :AcctDescription,
:AcctNotes, :AcctType, :AcctClass, '+  //UID removed    ' :AcctCat,
:AcctSuspended, :AcctParent)';  //UID removed

----------------------------------------------------------------------
Zvoni · 2026-03-09 07:08

Quote from: wp on March 06, 2026, 04:16:29 pmQuote from: Zvoni on
March 06, 2026, 11:36:18 amI disagree with you regarding the "no
decision made": If there is no decision, then it's false. Period! (Or
True depending on the UseCase)
Suppose a dish ordering system in a restaurant having a variety of
dishes, and each dish has the option "with dessert" or, with some
price discount, "without dessert". The database managing all this has
a boolean field "Dessert" for this option. I am absolutely convinced
that the default value for this field must be NULL. Otherwise, if it
were true, and the waitress would forget to ask the guest whether he
wants a dessert or not, he would get a dessert even if he would not
want it. (and even would have to pay for it).

In such a case, a boolean field has three states: true, false,
undefined. The visual controls, TCheckbox and TDBCheckbox, (which
specialized for boolean fields!) are able to display the disabled
state ("grayed" in the old days, today that strange '-' symbol).

QuoteNevermind the following scenario:
Let's pretend OP leaves the BOOLEAN Default NULL like it is now:
he has a record, and that Boolean-Field is actually really a NULL.
He now pulls that record from the Database.
And what now?
What happens when he does: MyQuery.FieldByName('Suspended').AsBoolean
Will Freepascal translate the NULL to a False?
When a database is allowed to contain NULL values every query of a
field value must be accompanied by an IsNull check before, and the
program must decide what to do since the user did not yet provide an
input in this field. In case of the dish ordering system above, the
waitress would not be able to place the order (store the record) as
long as the "Dessert" field is NULL.

I am not saying that this is the general use. But I certainly would
not say "Boolean DEFAULT is nonsense".

Let's agree to disagree.
In your "side-dish"-scenario/usecase, the Default would be False in my
opinion ("no extra side-dish" --> It's the task of the waiter to
"sell" that damned thing)

Any definition of a Boolean (and this is not only in programming, also
in math) states, it can have 2 values: True and False.
In pretty much all programming languages a Boolean is a TypeDef for an
Integer, which will never have an undefined value.
And yes: "Garbage" is still a value.

An "undefined" boolean in a Database is even worse, when it comes to
stuff like this:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT *
FROM SomeTable WHERE (Taxable1 OR Taxable2) AND NOT IsSuspended
As i said: No offense to you or your opininion, and your arguments are
legit (TDBCheckBox etc.), IMO it's the wrong "tool" to represent more
than 2 states
I could list many more examples, where a DEFAULT NULL for a Boolean is
nonsense

EDIT: Out of curiosity i looked up TCheckBox/TDBCheckBox
Both have "Checked"-Property, which is a Boolean
BUT: That Property actually reads/writes the "State"-Property, which
is an Enum-Type, and "cbUnchecked" is the first one, meaning ordinal
"0", meaning "False",
which takes us back, that Boolean is the wrong DataType in a Database,
if you need more than 2 states. Nevermind allowing a NULL for it
I'd use a simple Integer, whose value directly translates to the
ordinal of the Enum

----------------------------------------------------------------------
wp · 2026-03-09 10:52

Basically you are questioning the usage of NULL in databases. How then
do you handle the case of a numeric field which did not yet get an
input, or where the value is not known? Assign 0 to it as default? Or
999? Or the famous date fields suddenly displaying the unknown birth
date of a person as 0, i.e. as Dec-30 1899...

----------------------------------------------------------------------
CraigC · 2026-03-09 11:03

Next: Don't pass your Primary Key in an INSERT. NEVER EVER!

Ok!  I removed it.     I changed to UID=:OLD_UID and it works.  But,
is the variable OLD_UID a SQL variable? Because I have not declared
one.

Thank you again.  I owe you a beer.

Craig

----------------------------------------------------------------------
Zvoni · 2026-03-09 11:21

Quote from: wp on March 09, 2026, 11:52:04 amBasically you are
questioning the usage of NULL in databases. How then do you handle the
case of a numeric field which did not yet get an input, or where the
value is not known? Assign 0 to it as default? Or 999? Or the famous
date fields suddenly displaying the unknown birth date of a person as
0, i.e. as Dec-30 1899...
Not at all.
It has more to do with Database-  resp. Table-design, and the Data it
represents.


There are legitimate cases, where NULL actually really represents a
Value in the real World.
IMO, a "classic" for that would bei a "Price" --> "I created a new
Item in the Table, but i have no idea about the price, yet"
--> a Default of "0" would be very wrong for this, since "0.00" is a
valid price (Think of having "Give-Aways" in your records)
Here a NULL can help you "You have to set a Price"

Another classic would be "Middle-Name" for a Person's Data (though one
can argue to DEFAULT to an empty String)
Another classic is "Gender" for a Person's Data --> Think Person's
Data, where you can't derive a Person's Gender from the Rest of the
Data
--> e.g. FirstName = "Chris" --> is that male, female or whatever
else?
The same with "DateOfBirth" --> that one is definitly a classic for
DEFAULT NULL, as in "DoB unknown on creation of record"

OTOH, let's say there is also a Column called "QuantityOnStock", which
would be an Integer or Float (if you have fractions).
If i create a new Record for an item that Column "QuantityOnStock" is
a part of the Record, what Purpose, what Information would a NULL
represent in the real world? None!
The "Physics" of it: The Quantity for that Item is "0" (not NULL).
Always! At exactly that point in Time the record is created

My Paradigm is always: What's the "Physics" behind the Information a
Column has to represent? What is happening in the real world?

So in my example:
Column "QuantityOnStock" --> NOT NULL DEFAULT 0
Column Price --> DEFAULT NULL

I've done enough SQL in the last 20 years to know, that special care
has to be applied to anything that can result in a Database-NULL
The "Usage" (for lack of a better term) of Database-NULL's is in 99%
of cases for OUTER JOINS (e.g. LEFT JOINS).
Some other things people tend to "forget":
1) you cannot filter "directly" on a NULL ("...WHERE SomeField=NULL")
--> You have to use specific Syntax
This also implies direct comparison -->
You can't compare NULL-Values ("...WHERE
FirstFieldThatIsNULL=SecondFieldThatIsNull") --> This will never
return records
2) You cannot use "NULL" as a Parameter-Value (!!)


Everything said:
It always depends on the "Entity" a Table is representing, and thusly
what the Attributes (Columns) are representing in the real world.

And my "grievance" with those Booleans DEFAULT NULL was specific to
OP's Table-Design, not a "general" Opinion
(though i stay with my "Boolean NULL is nonsense").
The "Physics" of those 3 Boolean-Fields in OP's table, ARE 2 possible
states (and not 3)
I'm not against NULL's, it's just that i haven't come across a
scenario i could see a legitimate use of a BOOLEAN DEFAULT NULL

----------------------------------------------------------------------
Zvoni · 2026-03-09 11:22

Quote from: CraigC on March 09, 2026, 12:03:44 pm
Next: Don't pass your Primary Key in an INSERT. NEVER EVER!

Ok!  I removed it.     I changed to UID=:OLD_UID and it works.  But,
is the variable OLD_UID a SQL variable? Because I have not declared
one.

Thank you again.  I owe you a beer.

Craig

"OLD_UID" is used by Lazarus itself within the UpdateSQL/DeleteSQL.
It's how the Name of the Primary Key is parsed.
and the "OLD"-Part is the Parametername, not a Field in your Table or
a variable you use.
You could say it's "Lazarus internal"-stuff

----------------------------------------------------------------------
CraigC · 2026-03-10 20:16

Ok!  I removed it.     I changed to UID=:OLD_UID and it works.  But,
is the variable OLD_UID a SQL variable? Because I have not declared
one.

Thank you again.  I owe you a beer.

Craig
[/quote]

"OLD_UID" is used by Lazarus itself within the UpdateSQL/DeleteSQL.
It's how the Name of the Primary Key is parsed.
and the "OLD"-Part is the Parametername, not a Field in your Table or
a variable you use.
You could say it's "Lazarus internal"-stuff
[/quote]

    UPDATESQL.Text := 'UPDATE UOM SET UOM_DESC=:UOM_DESC WHERE
UID=OLD_UID'; 

I tested updating a single record and it doesn't recognize "OLD_UID"

----------------------------------------------------------------------
Zvoni · 2026-03-10 21:10

Colon missing between „=„ and „O“

----------------------------------------------------------------------
CraigC · 2026-03-10 21:54

I kept working on it and found my typos.  I would not have found them
unless I wrote the post.  :'(

Thank you.

----------------------------------------------------------------------
1HuntnMan · 2026-03-19 15:58

CraigC, when you are using SQLite3 I would setup your main table and
hardcode your SELECT, UPDATE, INSERT and DELETE in your FormCreate or
FormShow. With your table having an AutoInc field that's internally
updated, don't include your AutoInc field in your UPDATE or INSERT
statements.  You need the AutoInc field in your SELECT and DELETE
statements. Also, you don't need fields included it they aren't
required for a record to be UPDATED or INSERTED. With an AutoInc field
you UPDATE fields and at the end of your UPDATE statement WHERE
AUTOINC-FIELD=:OLD_AUTOINC-FIELD, not := but =:

The reason for hardcoding is I learned awhile back when I was first
using SQLite3, I was putting the SQL Statement, UPDATE, INSERT and
DELETE in the query component: SQL [TStringList], DeleteSQL
[TStringList], InsertSQL [TStringList], and UpdateSQL [TStringList].
But I would forget to update these stringlists if I added a field as I
was first programming the app.

Another issue when first designing your queries and your modifying a
table, even just adding a field to the table up at the top of your
unit in the interface under the type section, you will see all the
fields in your table listed with their type: QryClientsCLIENTID:
TAutoIncField; (your AutoInc field) QryClientsCOMPANY: TStringField; 
QryClientsADDRESS: TStringField; etc. Even though I have the SQL,
INSERT, UPDATE, DELETE statements hardcoded, you can right-click your
mouse on your query component and Edit Fields... at the top of the
listing. There will be a listing of every field included in the
query.  Now if you modify with DBBrowser and modify your hardcoded SQL
statements, that listing isn't updated.  So, anytime you modify a
table whether you remove a field or add a field, you need to check
that listing because it's not updated if you just modify your coded
statements.  All your fields in the TYPE area at the top of your Unit
should be in sync with your query statements.

Have fun programming...

----------------------------------------------------------------------
CraigC · 2026-03-19 16:41

Thanks for the reply.  I've learned a bit about SQLite and Lazarus
since I just starting my conversion.  Delphi 7 was my favorite version
of Delphi too.