Dear all,
I am using SQLite db in my application and I found a problem using NUMERIC field type.
In order to reproduce it, create a new project, drop a sqliteconnection, sqltransaction, sqlquery and a button on the form. In the OnButton drop the following code:
procedure TForm1.Button1Click(Sender: TObject);
var
id: integer;
fl: double;
begin
SQLite3Connection1.DatabaseName:= 'e:\temp\db.sqlite';
if FileExists(SQLite3Connection1.DatabaseName) then
DeleteFile(SQLite3Connection1.DatabaseName);
SQLite3Connection1.Transaction:= SQLTransaction1;
SQLQuery1.DataBase:= SQLite3Connection1;
SQLQuery1.Transaction:= SQLTransaction1;
SQLite3Connection1.ExecuteDirect('CREATE TABLE T1 (ID INTEGER, VAL
NUMERIC (10,3))');
SQLQuery1.SQL.Text:= 'INSERT INTO T1 (ID, VAL) VALUES (1, 10.13)';
SQLQuery1.ExecSQL;
SQLTransaction1.CommitRetaining;
SQLQuery1.SQL.Text:= 'SELECT * FROM T1';
SQLQuery1.Open;
SQLQuery1.First;
while not SQLQuery1.EOF do
begin
id:= SQLQuery1.FieldByName('ID').AsInteger;
fl:= SQLQuery1.FieldByName('VAL').AsFloat;
MessageDlg(Format('ID: %s, VAL: %s', [IntToStr(id),
FloatToStr(fl)]), mtInformation, [mbOK],0);
SQLQuery1.Next;
end;
SQLite3Connection1.Close;
end;In the fl variable I get 0.001. The same works well using firebird (it gets 10.13). The insert works well, I verified it by using other sqlite administration tools (like sqlite extension for firefox). If I look at T1 table using sqlite extension for firefox I correctly get 10.13 in the VAL field of the first record.
If I use REAL field type instead NUMERIC I correctly get 10.13. I tested it both on Linux an Windows (FPC 2.2.2 Lazarus SVN), it doesn't work both on Linux and Windows.
Best regards,
Andrea