Update a table with an Autoinc field

[CraigC]

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]

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]

This is a good start

https://wiki.lazarus.freepascal.org/SQLdb_Tutorial1

At the bottom you will find tutorial 2, 3 and 4.

[CraigC]

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]

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

[CraigC]

"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]

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]

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]

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]

5) 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]

5) 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]

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]

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)

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

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

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

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

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]

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