Oracle Array Binding - overflow with years > 4096

mattsbg · 2020-02-07 14:33:18

Hi,

in our legacy application we're using SynDbOracle. I realized it is not possible saving years > 4096 using array binding. In our application the date 9999-12-31 is used as infinite. With "normal" parameter binding it is no problem saving those values.

The problem is the conversion from ISO string to TOracleDate in TSQLDBOracleStatement.ExecutePrepared

....
ftUTF8,ftDate: begin
              L := length(VArray[j])-2; // -2 since quotes will be removed
              if VType=ftDate then
                if L<=0 then
                  oDataDAT^[j].From(0) else
>>>>>>    oDataDAT^[j].From(PUTF8Char(pointer(VArray[j]))+1,L)
....

The problem can also easily be reproduced using a TOracleDate variable.

1) do a From('9999-01-01T15:00:00')
2) do a ToIso8601

You'll see that the resulting value is 1807-01-01T15:00:00 which can also be found in a inserted record.

Is this behaviour known ?

best regards,
Matthias

mpv · 2020-02-07 18:08:11

We also use 9999-12-31 as infinity date but inserts it without array binding ( binds to single insert stmt). In this case all works as expected. So I think there is some diffs in array and single value binding sources.... I'll look more close tomorrow

Last edited by mpv (2020-02-07 18:09:29)

mpv · 2020-02-08 09:25:23

@mattsbg I think you use a textual representation of MaxDate. Using a TDateTime instead should be faster. In my codebase we define constant MAX_DATE_AS_DATETIME calculated as

TryEncodeDate(9999, 12, 31, MAX_DATE_AS_DATETIME);

And use it in future in bindings.

With iso8601 DateTime I hope problem is here - Y := (Value shr (6+6+5+5+4)) and 4095;
Most likely "and 4095" can be removed ( Y := (Value shr (6+6+5+5+4)) )- can you try, please?

Last edited by mpv (2020-02-08 09:26:26)

ab · 2020-02-08 17:47:14

9999 is indeed far above the TIso8601 highest value. If you use 4000 it would work as expected. 9999 is not a true year neither...

mpv · 2020-02-08 18:26:50

@ab - I think 9999 is a valid Iso8601 year - see https://en.wikipedia.org/wiki/ISO_8601#Years
Or in browser console

 new Date(9999, 01, 01).toISOString()
//will output "9999-01-31T22:00:00.000Z"
new Date("9999-01-31T22:00:00.000Z") // this is valid Date object

The problem for sure in `and 4095`

ab · 2020-02-09 14:45:01

I was talking about TTimeLog, which is the new name for the old/deprecate TIso8601 64-bit type.
TIso8601/TTimeLog was/is our internal date/time stored with bits patterns, in which, by design, year is within 0..4095 range.
So 9999 is a valid Iso-8601 year, but 9999 is not a valid TTimeLog year.
The framework would therefore have problems with years >= 4096.

I may upgrade the year mask to 16383, i.e. TTimeLog would use up to 40 bits instead of 38 bits, and would be able to store 9999 year.
But I don't see really the relevance of it.
If you want to define some "out of range" year, you can use 3999 instead of 9999. It wouldn't appear as real data, but it would be handled with no problem.
I guess some databases may have issues with year 9999.

mpv · 2020-02-09 18:30:15

I"m and @mattsbg talks about low level SynDBOracle, and in this line of code
https://github.com/synopse/mORMot/blob/ … e.pas#L602
4096 year problem can be easy fixed.
I"m shure 9999-01-31 is a valid Date for at last Oracle, Postgres, SQLServer, MySQL and SQLight - we use it as a MaxDate for legacy reasons.

On the ORM level TTimeLog can continue to use 38 bit, changes can be made only on SynDBOracle level and only in line of code I note above.

Last edited by mpv (2020-02-09 18:31:14)

ab · 2020-02-10 11:31:57

Please check https://synopse.info/fossil/info/c04412b0b6

I have also changed TTimeLog to use up to 40 bits, therefore allow year 9999 as per the ISO-8601 standard.
See https://synopse.info/fossil/info/139f30b6c0

mattsbg · 2020-02-17 13:42:20

thanks for your effort! I can confirm that date 9999-12-31 it is working now with array insert!

Sorry for my delay (I was out of office last week).

Thanks,
Matt

mORMot Open Source

#1 2020-02-07 14:33:18

Oracle Array Binding - overflow with years > 4096

#2 2020-02-07 18:08:11

Re: Oracle Array Binding - overflow with years > 4096

#3 2020-02-08 09:25:23

Re: Oracle Array Binding - overflow with years > 4096

#4 2020-02-08 17:47:14

Re: Oracle Array Binding - overflow with years > 4096

#5 2020-02-08 18:26:50

Re: Oracle Array Binding - overflow with years > 4096

#6 2020-02-09 14:45:01

Re: Oracle Array Binding - overflow with years > 4096

#7 2020-02-09 18:30:15

Re: Oracle Array Binding - overflow with years > 4096

#8 2020-02-10 11:31:57

Re: Oracle Array Binding - overflow with years > 4096

#9 2020-02-17 13:42:20

Re: Oracle Array Binding - overflow with years > 4096

Board footer