Devart Forums

I have the following code that takes values from one table with value form one week and updates another table with values for a whole year.
The code works just fine on MS Access and MySQL but on SQLite it won't update because of the QueryUpdate.ParamByName('flddayspecial').AsBoolean := False;
If I remove this, everything runs as it should.

procedure CalendarYearUpdate(aYear: integer; aMonth : integer = 0);
var
Query: TUniQuery;
QueryUpdate: TUniQuery;
DayNumber: integer;
DayNormal: double;
begin
Query := TUniQuery.Create(Nil);
QueryUpdate := TUniQuery.Create(Nil);
try
Query.Connection := frmDBConn.conDBserver;
QueryUpdate.Connection := frmDBConn.conDBserver;
Query.SQL.Clear;
Query.SQL.Add('SELECT * FROM ' + TableCalendarWeek);
Query.SQL.Add('WHERE (fldemployer = :fldemployer)');
Query.ParamByName('fldemployer').AsString := Employer.GUID;
Query.Execute;
StatusShow(Application.MainForm, Query.RecordCount, 0, 'Opdaterer kalender. Vent venligst.....');
try
while Not Query.Eof do
begin
DayNumber := Query.FieldByName('flddaynumber').AsInteger;
DayNormal := Query.FieldByName('flddaynormal').AsFloat;
QueryUpdate.SQL.Clear;
QueryUpdate.SQL.Add('UPDATE ' + TableCalendarYear);
QueryUpdate.SQL.Add('SET flddaynormal = :flddaynormal');
QueryUpdate.SQL.Add('WHERE (flddaynumber = :flddaynumber)');
QueryUpdate.SQL.Add('AND (flddayspecial = :flddayspecial)');
QueryUpdate.SQL.Add('AND (fldemployer = :fldemployer)');
QueryUpdate.SQL.Add('AND (fldyear = :fldyear)');
if aMonth > 0 then
begin
QueryUpdate.SQL.Add('AND (fldmonth = :fldmonth)');
QueryUpdate.ParamByName('fldmonth').AsInteger := aMonth;
end;
QueryUpdate.ParamByName('flddaynumber').AsInteger := DayNumber;
QueryUpdate.ParamByName('flddaynormal').AsFloat := DayNormal;
QueryUpdate.ParamByName('flddayspecial').AsBoolean := False;
QueryUpdate.ParamByName('fldemployer').AsString := Employer.GUID;
QueryUpdate.ParamByName('fldyear').AsInteger := aYear;
QueryUpdate.Execute;
Query.Next;
StatusPos(Query.RecNo);
end;
except
on E: exception do
begin
Query.Next;
StatusPos(Query.RecNo);
Logfile.Error('CalendarYearUpdate: ' + E.Message);
end
end;
finally
Query.Free;
QueryUpdate.Free;
StatusHide;
end;
end;

Hello,

I cannot reproduce the problem, please send your database file to alexp*devart*com and specify the version of your UniDAC.

OK - I will try that when I get back home next week.

I got home early and I have made a small sample that I have mailed to the address.

Please notice that the db3 file is located in subdir data

Hello,

I reproduced the problem on your database. But if I recreate your table and execute the same query, data is refreshed correctly. Please tell us where and how you created the table (in a visual editor or with the help of a script).

OK - the database is created by converting my MS Access db using ESF Database Convert

I will try to delete the field and recreate it using SQLiteMaestro and see what then happens.

It looks like SQLIte doesn't like False as default.
If I delete all data and change the default value to 0 (Zero) the code is working.

So once again it isn't a UniDAC issue

Hello,

SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).
You should use 0 or 1 instead of False and True to set the default value for a Boolean field.