SQLite: adding a requered field produces error

BIZ TMS Aurelius
1

In an existing entity (that works over an SQLite db) I added the following column:

    [Column('relat', [TColumnProp.Required], 40)]
    Frelat: string;

When executing UpdateDatabase in TdatabaseManager I am getting the following error:
'Error: Cannot add a NOT NULL column with default value NULL'
Sql query:
'ALTER TABLE pat
ADD relat TEXT NOT NULL'

Is this the "missing feature to set column's default value" ?
Thank you in advance

2

Yes. To update such database you have to create and execute a custom SQL statement to create the field.

1 Like
3

I am not sure because today, I delete the table and it was recreated by Aurelius without problem.
At the same time, if I run the above sql query from SQL Expert, it runs without errors and the table is altered.
Am I missing something?
My call stack:

:7714a6e2 KERNELBASE.RaiseException + 0x62
Aurelius.Drivers.SQLite.Classes.TSqliteDatabase.RaiseError('ALTER TABLE pat'#$D#$A'  ADD relat TEXT NOT NULL')
Aurelius.Drivers.SQLite.Classes.TSqliteStatement.Create($8BED380,'ALTER TABLE pat'#$D#$A'  ADD relat TEXT NOT NULL')
Aurelius.Drivers.SQLite.Classes.TSqliteDatabase.Prepare('ALTER TABLE pat'#$D#$A'  ADD relat TEXT NOT NULL')
Aurelius.Drivers.SQLite.TSQLiteNativeStatementAdapter.PrepareStatement
Aurelius.Drivers.SQLite.TSQLiteNativeStatementAdapter.Execute
Aurelius.Commands.AbstractSqlPerformer.TAbstractSQLPerformer.Execute('ALTER TABLE pat'#$D#$A'  ADD relat TEXT NOT NULL',nil,1)
Aurelius.Commands.AbstractCommandPerformer.TSQLPerformer.Execute('ALTER TABLE pat'#$D#$A'  ADD relat TEXT NOT NULL',nil,1)
Aurelius.Commands.AbstractCommandPerformer.TSQLPerformer.ExecuteSQL('ALTER TABLE pat'#$D#$A'  ADD relat TEXT NOT NULL')
Aurelius.Engine.DatabaseManager.TDatabaseManager.ExecuteSQLStatements($8FE68D0,False)
Aurelius.Engine.DatabaseManager.TDatabaseManager.UpdateDatabase

procedure dbinit;
begin 
dbc:=TSQLiteConnection.Create('Database='+tpath.Combine(dirint,'sb.db')+';EnableForeignKeys=True');
dbmap:=TMappingExplorer.Default; 
with TDatabaseManager.Create(dbc,dbmap) do 
    try 
     UpdateDatabase; 
    finally 
       Free; 
    end;
end;
4

Why the same ALTER sql command runs from SQL Expert but gives error when it runs during databaseUpdate?
How can the table be created by aurelius but not altered?

5

The SQL will fail if the table exists and has records. If it's a new table, or if the table doesn't have records, it will execute ok.

1 Like