Update a table with an Autoinc field

[CraigC]

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]

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]

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)

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).

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?

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]

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]

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

[CraigC]

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]

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][+][-]

1.  qryGLAccts_.UpdateSQL.Text := 'UPDATE GLACCTS SET AcctNumber=:AcctNumber, AcctDescription=:AcctDescription, '+  //Comma added
2.     'AcctNotes=:AcctNotes, AcctType=:AcctType, AcctClass=:AcctClass, AcctCat=:AcctCat, AcctSuspended=:AcctSuspended, '+
3.     'AcctParent=:AcctParent WHERE UID=:OLD_UID';  //Where-Clause added
4.  

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][+][-]

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

[Zvoni]

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)

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).

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?

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][+][-]

1. 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]

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]

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]

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...

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]

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]

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]

Colon missing between „=„ and „O“

[CraigC]

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

Thank you.