[fpc-pascal] Cannot write datetime field on sqlite3 database on ARM

[Toru Takubo]

On 2021/03/08 16:54, Michael Van Canneyt via fpc-pascal wrote:
> 
> 
> On Mon, 8 Mar 2021, Toru Takubo via fpc-pascal wrote:
> 
>> Hi,
>>
>> I am developing my app on Windows and building apps for other
>> platforms by using cross compiler. Now I have a problem only
>> occurred on Linux ARM.
>>
>> The problem is that it cannot write datetime field on sqlite3
>> database. It can read/write other fields like int, varchar
>> or blob, but always write zero in datetime (maybe float as well)
>> field.
>>
>> Does anyone have an idea about this issue? I am not sure it is
>> fpc issue, but better to report bug?
> 
> It sounds like a floating point problem. As you probably know, a TDateTime
> type is actually a double type. Did you try with a float value ?
> 
> The DB explorer tools probably just use strings to read/write from the
> database, so they will not be bothere by such things, but FPC stores dataset
> values in 'native' formats in memory.
> 
> I don't know what to advise to further investigate the issue, One thing to
> try would be to test whether normal float arithmetic or date arithmetic works.
> If not, then the compiler people will need to give more advice.
> 

I created a simple test code, and ran on linux-i386 and linux-arm.
Test on linux-i386 is OK but on linux-arm, I got unexpected values
"30-12-99" as datetime and "2.12199579096527E-314" as float, which are different
from values previously set. (except INT and VARCHAR fields)

---linux-i386--------------------------------------------
INT, VARCHAR,           DATETIME,           DOUBLE
0, 9-3-21 08:50:23, 9-3-21 08:50:23, 0
1, 9-3-21 12:16:06, 9-3-21 12:16:06, 0.142857142857143
2, 9-3-21 15:41:49, 9-3-21 15:41:49, 0.285714285714286
3, 9-3-21 19:07:32, 9-3-21 19:07:32, 0.428571428571429
4, 9-3-21 22:33:15, 9-3-21 22:33:15, 0.571428571428571
5, 10-3-21 01:58:58, 10-3-21 01:58:58, 0.714285714285714
6, 10-3-21 05:24:40, 10-3-21 05:24:40, 0.857142857142857
7, 10-3-21 08:50:23, 10-3-21 08:50:23, 1
8, 10-3-21 12:16:06, 10-3-21 12:16:06, 1.14285714285714
9, 10-3-21 15:41:49, 10-3-21 15:41:49, 1.28571428571429

---linux-arm---------------------------------------------
INT, VARCHAR,           DATETIME,           DOUBLE
0, 9-3-21 08:55:53, 30-12-99, 2.12199579096527E-314
1, 9-3-21 12:21:36, 30-12-99, 2.12199579096527E-314
2, 9-3-21 15:47:18, 30-12-99, 2.12199579096527E-314
3, 9-3-21 19:13:01, 30-12-99, 2.12199579096527E-314
4, 9-3-21 22:38:44, 30-12-99, 2.12199579096527E-314
5, 10-3-21 02:04:27, 30-12-99, 2.12199579096527E-314
6, 10-3-21 05:30:10, 30-12-99, 2.12199579096527E-314
7, 10-3-21 08:55:53, 30-12-99, 2.12199579096527E-314
8, 10-3-21 12:21:36, 30-12-99, 2.12199579096527E-314
9, 10-3-21 15:47:19, 30-12-99, 2.12199579096527E-314

I used a shared library libsqlite3.so included in the "DB Browser for SQLite",
so it should work. What can cause such a problem only in the specific field
on the specific platform?

Thank you in advance for any advice.

Toru



-------------- next part --------------
program project1;

uses
  {$IFDEF UNIX}
  cthreads,
  {$ENDIF}
  SysUtils, sqlite3conn, sqldb;

var
  DBC: TSQLite3Connection;
  QRY: TSQLQuery;
  TRN: TSQLTransaction;
  i: Integer;
  nw: TDateTime;

begin
  DBC:=TSQLite3Connection.Create(nil);
  QRY:=TSQLQuery.Create(nil);
  TRN:=TSQLTransaction.Create(nil);
  try
    //Initialize Connection
    DBC.DatabaseName:=ExtractFileDir(ParamStr(0))+PathDelim+'test.db';
    QRY.DataBase:=DBC;
    TRN.DataBase:=DBC;
    QRY.Transaction:=TRN;

    //create database
    DeleteFile(DBC.DatabaseName);
    DBC.CreateDB;
    QRY.SQL.Text:=
    'create table TEST ('+
    'VALUE_INT int,'+
    'VALUE_VARCHAR varchar(20),'+
    'VALUE_DATETIME datetime,'+
    'VALUE_DOUBLE double)';
    TRN.StartTransaction;
    try
      QRY.ExecSQL;
      TRN.Commit;
    except
      TRN.Rollback;
      raise;
    end;

    //insert values into table
    for i:=0 to 9 do begin
      nw:=Now+i/7;
      QRY.SQL.Text:=
      'insert into TEST ('+
      'VALUE_INT,'+
      'VALUE_VARCHAR,'+
      'VALUE_DATETIME,'+
      'VALUE_DOUBLE'+
      ') values ('+
      ':VALUE_INT,'+
      ':VALUE_VARCHAR,'+
      ':VALUE_DATETIME,'+
      ':VALUE_DOUBLE)';
      QRY.ParamByName('VALUE_INT').AsInteger:=i;
      QRY.ParamByName('VALUE_VARCHAR').AsString:=DateTimeToStr(nw);
      QRY.ParamByName('VALUE_DATETIME').AsDateTime:=nw;
      QRY.ParamByName('VALUE_DOUBLE').AsFloat:=i/7;
      QRY.Prepare;
      try
        QRY.ExecSQL;
        TRN.Commit;
      except
        TRN.Rollback;
        raise;
      end;
    end;

    //show table
    QRY.SQL.Text:='select * from TEST';
    try
      QRY.Open;
      Writeln('INT, VARCHAR,           DATETIME,           DOUBLE');
      while not QRY.EOF do begin
        Writeln(
          IntToStr(QRY.FieldByName('VALUE_INT').AsInteger)+', '+
          QRY.FieldByName('VALUE_VARCHAR').AsString+', '+
          DateTimeToStr(QRY.FieldByName('VALUE_DATETIME').AsDateTime)+', '+
          FloatToStr(QRY.FieldByName('VALUE_DOUBLE').AsFloat)
        );
        QRY.Next;
      end;
      TRN.Commit;
    except
      TRN.Rollback;
      raise;
    end;
  except
    on E:Exception do begin
      Writeln('ERROR:'+E.Message);
    end;
  end;
  DBC.Close;
  TRN.Free;
  QRY.Free;
  DBC.Free;
  Readln;
end.