Firebird Batch DateTime Problem

danielkuettner · 2014-10-06 08:30:03

I've found an error when batch-inserting relating to TDateTime fields in a firebird db.

root/ZV/Batch

{"ZV":["POST",{"ENAME":"Telekom Deutschland GmbH","EKONTONR":"DE98700100800593231804","EBLZ":"PBNKDEFFXXX","betrag":-5319,"AKONTOID":47,"Status":0,"Datum_von":"2014-10-05"}]}

After successful batch insert value of field Datum_von in db is always empty ("").
I tried also with "2014-10-05T10:25:00" with same result.
In SQL-Model Type of Datum_von is TDateTime, but I also test it with RawUTF8.

With SQLite3 as db it works.

When I use normal insert (without batch) then it works with firebird too.

root/ZV

{"ENAME":"Telekom Deutschland GmbH","EKONTONR":"DE98700100800593231804","EBLZ":"PBNKDEFFXXX","betrag":-5319,"AKONTOID":47,"Status":0,"Datum_von":"2014-10-05"}

Should I create a ticket?

ab · 2014-10-06 08:41:02

I suspect you should use DateToSQL/DateTimeToSQL/TimeLogToSQL functions, as documented.

danielkuettner · 2014-10-06 09:08:33

I'm using javascript and the format of the JSON should looks ok.

ab · 2014-10-06 10:21:35

Yes, I understand your POV.

In fact, TSQLRestStorageExternal.JSONDecodedPrepareToSQL() should have recognized the column date/time as ftDate.
Why is it not the case for your external DB?
Does TSQLRestStorageExternal.Create() recognize the "Datum_von" column as ftDate?
How are you connecting to FireBird? (Zeos/ZDBC is recommended)
Was the table created by the ORM? Is the "Datum_von" column a date/time?

danielkuettner · 2014-10-06 10:53:02

I'm using Zeos.

ORM is here:

TSQLZV = class(TSQLRecord)
  private
    fZV_DATUM_VON: TDateTime;
  published
    property Datum_von: TDateTime read fZV_DATUM_VON write fZV_DATUM_VON;
  end;

I've also tried this:

TSQLZV = class(TSQLRecord)
  private
    fZV_DATUM_VON: RawUTF8;
  published
    property Datum_von: RawUTF8read fZV_DATUM_VON write fZV_DATUM_VON;
  end;

Without Batch it works.

I will debug and look for JSONDecodedPrepareToSQL...

danielkuettner · 2014-10-06 10:59:00

ab wrote:

In fact, TSQLRestStorageExternal.JSONDecodedPrepareToSQL() should have recognized the column date/time as ftDate.
Why is it not the case for your external DB?
Does TSQLRestStorageExternal.Create() recognize the "Datum_von" column as ftDate?

JSONDecodedPrepareToSQL gives back ftDate (wether Datum_von has TDateTime or RawUTF8 in ORM).

danielkuettner · 2014-10-06 11:06:36

In SynDBZeos.pas after line 820:

if fParamsArrayCount>0 then
    with (fConnection.Properties as TSQLDBZEOSConnectionProperties) do
    if fSupportsArrayBindings then
      arrayBinding := TZeosArrayBinding.Create(self) else
      raise ESQLDBZEOS.CreateUTF8(
...

arrayBinding.fDateDynArray is (0) for field Datum_von.

EgonHugeist · 2014-10-06 12:21:21

Doe's Zeos have an issue here?

danielkuettner · 2014-10-06 13:16:20

Hi Michael,

I don't know where fParams will set first time. In TZeosArrayBinding.Create(self) the param type is already false.

Daniel

ab · 2014-10-06 14:50:24

danielkuettner wrote:

I don't know where fParams will set first time. In TZeosArrayBinding.Create(self) the param type is already false.

Does it mean that fParams[p].VType is not ftDate, as expected?

Does it execute the following block of TZeosArrayBinding.Create:

          ftDate: begin
            SetLength(fDateDynArray[n],fParamsArrayCount);
            for j := 0 to fParamsArrayCount -1 do
              if not fNullArray[p][j] then
                fDateDynArray[n][j] := Iso8601ToDateTime(VArray[j]);
            fStatement.SetDataArray(p+1,fDateDynArray[n],stTimeStamp);
          end;

Query.BindArray() is called line 1036 of mORMot.pas, in procedure TSQLRestStorageExternal.InternalBatchStop.
So local Types[] variable should be ftDate, and the corresponding Values[] should contain the ISO-8601 date value.

danielkuettner · 2014-10-07 05:43:34

Sorry for late reply...

In mORMotDB.pas line 1036 the type is right.

ab wrote:

Does it mean that fParams[p].VType is not ftDate, as expected?

Yesterday it seem so, but

fParams[p].VType is ftDate and in TZeosArrayBinding.Create

ftDate: begin
            SetLength(fDateDynArray[n],fParamsArrayCount);
            for j := 0 to fParamsArrayCount -1 do
              if not fNullArray[p][j] then
                fDateDynArray[n][j] := Iso8601ToDateTime(VArray[j]);
            fStatement.SetDataArray(p+1,fDateDynArray[n],stTimeStamp);

is called, but this line:

fDateDynArray[n][j] := Iso8601ToDateTime(VArray[j]);

returns: 30.12.1899 (Input was "2014-10-05").

@ab
IMPOV in SynCommons line 22685 Iso8601ToDateTimePUTF8CharVar is the problem.

Last edited by danielkuettner (2014-10-07 07:35:50)

ab · 2014-10-07 07:38:58

Is text date quoted?

danielkuettner · 2014-10-07 07:59:33

Yes. As you can see in JSON:

"Datum_von":"2014-10-05"}]}

In Debugger I see:

("'2014-10-05'")

Length of P: PUTF8Char is 12
and test of P[0] is $255 and then exit.

Last edited by danielkuettner (2014-10-07 08:25:33)

EgonHugeist · 2014-10-07 09:15:04

@AB,

seems to me like a parsing issue. Can't find something broken on ZDBC+FB. What about UnQuoting the strings by default?

@Daniel,

Did some small performance refactoring accordingly NULL determinations of FB+ArrayBindings -> update from SVN.

danielkuettner · 2014-10-07 09:59:27

In mORMot line 19400:

            if Params=pNonQuoted then
              // returned directly as RawUTF8
              FieldValues[ndx] := res else
              { escape SQL strings, cf. the official SQLite3 documentation:
                "A string is formed by enclosing the string in single quotes (').
                 A single quote within the string can be encoded by putting two
                 single quotes in a row - as in Pascal." }
              FieldValues[ndx] := QuotedStr(res,''''); <-- here are the problem

all values will be quoted and this makes the problem in SynCommons Iso8601ToDateTimePUTF8CharVar (line 22685).

Perhaps this could be a solution in SynCommons line 22800:

function Iso8601ToDateTime(const S: RawByteString): TDateTime;
var
  _s: RawUTF8;
begin
  UnQuoteSQLStringVar(pointer(S), _s);

  //Iso8601ToDateTimePUTF8CharVar(pointer(S),length(S),result);
  Iso8601ToDateTimePUTF8CharVar(pointer(_s),length(_s),result);
end;

Last edited by danielkuettner (2014-10-07 10:21:42)

ab · 2014-10-07 16:47:56

Should be fixed by http://synopse.info/fossil/info/211d15b2b2

Thanks for the feedback!

EgonHugeist · 2014-10-07 18:07:01

Arnaud,

may i ask .. is this issue Daniel reports a Zeos issue only? The patch you made seems to say YES whereas i think there is a common issue.
As Daniel describes there is a generic problem to convert TDateTime value if the values are quoted.

So what is wrong? Either Daniels JSON with quotes, mORMots conversion or simply my batch implementation for mORMot? Is this case handled for all providers and your own access units too?

Just to be clear...

ab · 2014-10-08 05:52:45

Afaik the issue did appear only for our zeos batch process.
In other places, ftDate text values are unquoted as expected.

EgonHugeist · 2014-10-08 10:44:34

Hi Arnaud,

don't want to bother you

Just a little tweak: Wouldn't it be faster for have a Iso8601ToDateTime function or overload which checks against quotes and returns the binary expected value? I mean instead of localize a new unquoted string and than call Iso8601ToDateTime? It's just a very minmal performance thing. What do you think?

ab · 2014-10-08 16:31:54

I was thinking also about it, but it would just make no performance change in this case.
The same local variable will be re-used by FastMM4, during the loop.
The bottleneck would never be at this level.
"Premature optimization is the root of all evil"!

EgonHugeist · 2014-10-08 18:49:12

Oh ok, thanks for sharing such knowledges. So let's say my proposal would hit some mirco-benchmarks with FastMM4.

Some weeks ago i did some research about FPC + FastMM4. I found some mailing lists of a user and Florian.
What i found out was: They did implement !some! parts which makes GetMem/ReallocMem comparable with FastMM4. But all other features are ignored AFAIRC.

Just a note, since mORMot seems to make the final steps for FPC cross-compiling..

Cheers, Michael

Edit, except you're checking against len+refcount for the result of the unquoted string, of course.

Last edited by EgonHugeist (2014-10-08 18:53:14)

mORMot Open Source

#1 2014-10-06 08:30:03

Firebird Batch DateTime Problem

#2 2014-10-06 08:41:02

Re: Firebird Batch DateTime Problem

#3 2014-10-06 09:08:33

Re: Firebird Batch DateTime Problem

#4 2014-10-06 10:21:35

Re: Firebird Batch DateTime Problem

#5 2014-10-06 10:53:02

Re: Firebird Batch DateTime Problem

#6 2014-10-06 10:59:00

Re: Firebird Batch DateTime Problem

#7 2014-10-06 11:06:36

Re: Firebird Batch DateTime Problem

#8 2014-10-06 12:21:21

Re: Firebird Batch DateTime Problem

#9 2014-10-06 13:16:20

Re: Firebird Batch DateTime Problem

#10 2014-10-06 14:50:24

Re: Firebird Batch DateTime Problem

#11 2014-10-07 05:43:34

Re: Firebird Batch DateTime Problem

#12 2014-10-07 07:38:58

Re: Firebird Batch DateTime Problem

#13 2014-10-07 07:59:33

Re: Firebird Batch DateTime Problem

#14 2014-10-07 09:15:04

Re: Firebird Batch DateTime Problem

#15 2014-10-07 09:59:27

Re: Firebird Batch DateTime Problem

#16 2014-10-07 16:47:56

Re: Firebird Batch DateTime Problem

#17 2014-10-07 18:07:01

Re: Firebird Batch DateTime Problem

#18 2014-10-08 05:52:45

Re: Firebird Batch DateTime Problem

#19 2014-10-08 10:44:34

Re: Firebird Batch DateTime Problem

#20 2014-10-08 16:31:54

Re: Firebird Batch DateTime Problem

#21 2014-10-08 18:49:12

Re: Firebird Batch DateTime Problem

Board footer