Problem with Sqldb inserting into Firbird Time field (Time precision)
lazarus / 16-Databases

----------------------------------------------------------------------
MaartenJB · 2013-08-26 07:55

When I use this query:

Code: [Select]with myQuery do begin
  SQL.Text:= 'INSERT INTO TestTable (TestTimeField) VALUES
(:TestTimeValue)';
  ParamByName('TestTimeValue').AsString:= '21:03:56,169';   // I want
to use: .AsTime := Now;  That's giving me the same problem, for
testing purpose I use AsString
  ExecSQL;
end;

The time field value in the database will be: '21:03:56.1689' in stead
of '21:03:56.1690'.

When I change my query to: (leave out the param by name):

Code: [Select]with myQuery do begin
  SQL.Text:= 'INSERT INTO TestTable (TestTimeField) VALUES (' + #39 +
'21:03:56,169' + #39 + ')';
  ExecSQL;
end;
The time field value in the database will be: '21:03:56.1690', the
result I expected from the first query.

I check the contents of the field with this statement: "select
cast(TestTimeField as varchar(24)) from TestTable"

If I use "AsTime := Now" not all records have this problem, but many
do. I think it might be a problem somewhere with precision round off

Thanks

Lazarus: 1.0.10 (win32)
FPC 2.6.2
Firebird DB: 2.5.2

----------------------------------------------------------------------
BigChimp · 2013-08-26 08:55

Quote  ParamByName('TestTimeValue').AsString:= '21:03:56,169';  
  ExecSQL;
With FPC 2.6.2 I get EVariantError : Invalid variant type cast in
ExecSQL...

Perhaps your problem is rounding, yes. I wonder whether if can be in
the Firebird cast in your select statement though...

Edit: When storing results in tdatetime, it works for me on FPC 2.6.2.
When using your SELECT...CAST statement, you do still see a difference
(and Flamerobin confirms that).

I do seem to remember there were some improvements in millisecond
precision in trunk but that may have been folded in 2.6.2 already.

----------------------------------------------------------------------
BigChimp · 2013-08-26 09:00

Update: nope, FlameRobin shows the wrong value too:
Quote31-12-2012, 21:03:56.168

----------------------------------------------------------------------
BigChimp · 2013-08-26 09:04

Update2: yes, FPC trunk has fixed this issue (perhaps in
http://bugs.freepascal.org/view.php?id=17199):
Code: [Select]Inserting test date:   20121231 210356 169
Retrieving timestamp:  2012-12-31 21:03:56.1690

Code: [Select]program fbtimestamp;

{$mode objfpc}{$H+}

uses
  {$IFDEF UNIX}{$IFDEF UseCThreads}
  cthreads,
  {$ENDIF}{$ENDIF}
  Classes, sysutils,
  sqldb,
  ibconnection, dateutils;
const
  DateLayout='yyyymmdd hhnnss zzz';
var
  Conn: TibConnection; {*REPLACE WITH RELEVANT CONNECTION *}
  Tran: TSQLTransaction;
  Q: TSQLQuery;
  TestDate: TDateTime;
begin
  Conn:=TIBConnection.create(nil);
  Tran:=TSQLTransaction.create(nil);
  Q:=TSQLQuery.Create(nil);
  try
    // *REMOVE IDENTIFYING INFO AND EDIT AS NEEDED*
    Conn.HostName:=''; //embedded
    Conn.UserName:='SYSDBA';
    Conn.Password:='masterkey';
    Conn.DatabaseName:='employee.fdb';
    // *END IDENTIFIYING INFO*
    Conn.Transaction:=Tran;
    Q.DataBase:=Conn;
    Conn.Open;

    // Connection-level temporary table
    Tran.StartTransaction;
    Conn.ExecuteDirect('CREATE GLOBAL TEMPORARY TABLE TestTable
(TestTimeField TIMESTAMP) ON COMMIT PRESERVE ROWS');
                Tran.Commit; //Firebird DDL needs explicit commit

                Tran.StartTransaction;
    TestDate:=EncodeDateTime(2012,12,31,21,03,56,169);
    Q.SQL.Text:='INSERT INTO TestTable (TestTimeField) VALUES
(:TestTimeValue)';
    Q.ParamByName('TestTimeValue').AsTime := TestDate;
    writeln('Inserting test date:   '+FormatDateTime(DateLayout,
TestDate));
                Q.ExecSQL;
                Tran.Commit;


    Tran.StartTransaction;
                //Q.SQL.Text:='select TestTimeField from TestTable';
    Q.SQL.Text:='select cast(TestTimeField as varchar(24)) from
TestTable';
    Q.Open;
    Q.Last; //retrieve value inserted last
    //writeln('Retrieving timestamp:  '+FormatDateTime(DateLayout,
(q.Fields[0]).AsDateTime));
    writeln('Retrieving timestamp:  '+(q.Fields[0]).AsString);
    Tran.Commit;


    Tran.StartTransaction;
    Conn.ExecuteDirect('DROP TABLE TestTable');
                Tran.Commit;

    Q.Close;
    Conn.Close;
  finally
    Q.Free;
    Tran.Free;
    Conn.Free;
  end;
end.

----------------------------------------------------------------------
MaartenJB · 2013-08-26 09:21

wow, fast response, Thanks! I'm going to try the FPC trunk too,

Thanks again!

----------------------------------------------------------------------
MaartenJB · 2013-08-26 11:39

I've tried it too, it works with the trunk.

thanks!