Problematic FetchAllAsJSON using mORMot 1.18.5963/ZeosLib r6478

JD · 2020-04-27 11:05:49

Hi there everyone,

I just noticed a wierd behavior relating to dates with the FetchAllAsJson function. When I run a query I get a JSON array result where only the first object has dates. All the other objects have empty date values. Here is a sample JSON result I got from my query.

{
    "result": [
        [
            {
                "numero_sanction": 5,
                "numero_accueilli": 43,
                "date_sanction": "2019-07-07",
                "type_sanction": "Avertissement écrit",
            },
            {
                "numero_sanction": 4,
                "numero_accueilli": 43,
                "date_sanction": "",
                "type_sanction": "Mise à pied",
            },
            {
                "numero_sanction": 1,
                "numero_accueilli": 23,
                "date_sanction": "",
                "type_sanction": "Avertissement écrit",
            },
            {
                "numero_sanction": 2,
                "numero_accueilli": 23,
                "date_sanction": "",
                "type_sanction": "Avertissement écrit",
            },
            {
                "numero_sanction": 3,
                "numero_accueilli": 23,
                "date_sanction": "",
                "type_sanction": "Avertissement écrit",
            }
        ]
    ],
    "id": 9
}

This is not correct because it is impossible to save the records without a valid date. This was confirmed when I queried the PostgreSQL 11.7 database directly getting the expected result below:

numero_sanction|numero_accueilli|date_sanction|type_sanction      |
---------------|----------------|-------------|-------------------|
              5|              43|   2019-07-07|Avertissement écrit|
              4|              43|   2020-01-21|Mise à pied        |
              1|              23|   2018-03-21|Avertissement écrit|
              3|              23|   2018-08-28|Avertissement écrit|
              2|              23|   2018-08-28|Avertissement écrit|

This is the same for all my queries not just this one and this behaviour is recent. It was never like this before. I don't know if the problem is from mORMot or from ZeosLib.

Any assistance with resolving this problem will be appreciated.

Thanks,

JD

Last edited by JD (2020-04-27 11:06:55)

ab · 2020-04-27 12:28:55

Which database are you using?

Did you try with another provider (e.g. ODBC)?

JD · 2020-04-27 13:43:32

ab wrote:

Which database are you using?
Did you try with another provider (e.g. ODBC)?

PostgreSQL 11.7 and I have not tried ODBC yet. I will do so and let you know my findings.

ab · 2020-04-27 15:15:44

The easiest is to try with SynDBPostgres first.

mpv · 2020-04-27 20:11:16

What is the type of date_sanction columnus in database? Is SynDBPostgres works as expected ?

JD · 2020-04-28 08:36:50

mpv wrote:

What is the type of date_sanction columnus in database? Is SynDBPostgres works as expected ?

The "date_sanction" column is a simple date column.

I just tested SynDBPostgres and it works as expected. All dates were in the JSON result.

But soon as I changed back to a Zeos connection, I lost all but the first date. So the problem may be from Zeos.

I will try ODBC as soon as I can and report my findings.

JD

Last edited by JD (2020-04-28 13:02:45)

JD · 2020-04-28 12:03:25

ODBC is proving a pain to set up. Is what I've done below correct because my mORMot server refuses to start after I compile?

    // https://www.connectionstrings.com/postgresql-odbc-driver-psqlodbc/
    // Driver={PostgreSQL UNICODE};Server=IP address;Port=5432;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
    fDbProps := TODBCConnectionProperties.Create('','Driver=PostgreSQL Unicode'+
        {$ifdef CPU64}'(x64)'+{$endif}';Database=testdb;'+
        'Server=localhost;Port=5432;UID=testdb;Pwd=testpwd','','');
    // To prevent PostgreSQL from hitting the default maximum connection limit of 100
    // and after rejecting subsequent connections
    TODBCConnectionProperties(fDbProps).ThreadingMode := tmMainConnection;

Thanks

JD

mpv · 2020-04-28 14:22:13

@JD - Postgres have many "date" types. You can help a Zeos maintainer ( @EgonHugeist on this forum ) if you provide exactly a type of column (as it visible in DBeaver or pgAdmin).
BTW ODBC will be definitely slower compared to Zeos or SynDBPostgres - they both use a direct libpq binding, while ODBC adds one more layer.

JD · 2020-04-28 15:26:00

mpv wrote:

@JD - Postgres have many "date" types. You can help a Zeos maintainer ( @EgonHugeist on this forum ) if you provide exactly a type of column (as it visible in DBeaver or pgAdmin).
BTW ODBC will be definitely slower compared to Zeos or SynDBPostgres - they both use a direct libpq binding, while ODBC adds one more layer.

The column is just "DATE". The same table has two "TIMESTAMPZ" columns to track record creation & modification that don't have this problem.

I know about the "slow" ODBC. That is why I very rarely use it. It is often my last resort if nothing else exists/works.

Isn't Zeos the fastest method for accessing external databases? I've been using it since my Delphi days and it is one of the first things I install in Lazarus/FPC. It has always been very reliable.
The last time I had a problem with it was in 2016 https://zeoslib.sourceforge.io/viewtopi … 40&t=41785 (due to my inexperience with PostgreSQL back then when switching from Firebird) and curiously it was SynDBPostgres that saved the day back then too.

This time I think I will keep Zeos & SynDBPostgres options going forward.

Do you think I should post this problem on the Zeos forums also or is it unnecessary since EgonHugeist is here as well?

Thanks,

JD

Last edited by JD (2020-04-28 16:17:51)

ttomas · 2020-04-28 16:40:36

Can you change some date to day<13, all bad dates have day > 12(months) just a hint

JD · 2020-04-28 17:14:57

ttomas wrote:

Can you change some date to day<13, all bad dates have day > 12(months) just a hint

The constraints take care of bad dates so that is not the problem. Querying the database directly or using SynDBPostgres/FetchAllAsJSON gives me the right results; Zeos/FetchAllAsJSON does not.

mpv · 2020-04-28 17:17:09

To check is the problem in Zeos itself or in SynDBZeos you can debug a TSQLDBZEOSStatement.ColumnsToJSON function. Here in line 1278 there is a condition define. Depends on it either Zeos serialization or Syn is used. In case of Syn serialization you can check is date returned correctly from Zeos layer.

ttomas · 2020-04-28 22:07:59

Do you try i see it is valid date in postgres but maybe some check in zeosdbo, syndbzeos or...

EgonHugeist · 2020-04-29 05:50:39

@JD
Which complier are you using? And which compile target(platform)?

Try to comment line

{$DEFINE INTEL_ASM} //allow intel syntax assembler

in \src\Zeos.inc. I didn't change many things lately in Postgres except switching procedure Revers4Bytes and Reverse8Bytes to asm. Maybe this fails. I'm not a asm guru.. It perfectyl works on Windows even with 64Bit..

Regards, Michael

JD · 2020-04-29 07:56:42

EgonHugeist wrote:

@JD
Which complier are you using? And which compile target(platform)?
Try to comment line
{$DEFINE INTEL_ASM} //allow intel syntax assembler
in \src\Zeos.inc. I didn't change many things lately in Postgres except switching procedure Revers4Bytes and Reverse8Bytes to asm. Maybe this fails. I'm not a asm guru.. It perfectyl works on Windows even with 64Bit..
Regards, Michael

Hello Michael,

I am using Lazarus 2.1/FPC 3.2rc1 Win32 svn 62815 running on Windows 10 Professional (x64). I only create Win32 executables.

I tried your suggestion and it did not work, unfortunately. The problem remains.

JD

ab · 2020-04-29 08:05:12

@Egon

I checked your Reverse4/8Bytes asm.
It is not correct for the Linux x86_64 ABI. There is also a problem with FPC and 64-bit values - you should use "constref" if you want the same behavior than Delphi (passed by reference).
The easiest is to use the parameter names instead of the registers.

Check for instance what I did in SynCommons:
https://gist.github.com/synopse/f76a6d2 … 53e5e3ddd1

JD · 2020-04-29 08:19:50

mpv wrote:

To check is the problem in Zeos itself or in SynDBZeos you can debug a TSQLDBZEOSStatement.ColumnsToJSON function. Here in line 1278 there is a condition define. Depends on it either Zeos serialization or Syn is used. In case of Syn serialization you can check is date returned correctly from Zeos layer.

The line

fResultSet.ColumnsToJSON(WR,fJSONComposeOptions);

is disabled for me because

{.$DEFINE USE_SYNCOMMONS} //enable JSON content support by using SynCommons.pas from Synopse project

is disabled in line 70 of Zeos.inc.

When I enabled it to try the ColumnsToJSON function, I get the following error messages in SynDBZeos and the compilation fails

Compile Project, Mode: Debug, Target: C:\....\Serveur.exe: Exit code 1, Errors: 5
SynDBZeos.pas(334,26) Error: Identifier not found "TZJSONComposeOptions"
SynDBZeos.pas(334,46) Error: Error in type definition
SynDBZeos.pas(413,34) Error: Identifier not found "TZJSONComposeOptions"
SynDBZeos.pas(414,15) Error: Property cannot have a default value
SynDBZeos.pas(414,16) Error: Identifier not found "jcoEndJSONObject"

This happens ONLY after I uncomment/enable line 70 of Zeos.inc

JD

EgonHugeist · 2020-05-01 07:14:14

Hello JD, your regressions are not reproducable for me. Hower i trust in AB's look at my ASM approaches and did comment the code until it's resolved properly.

@AB according the FPCX64 abi i did add the asmmode switch for intel asm. (i don't like that ATT quirk) so what's wrong there?
IIRC are you using volatile values. I made the two methods as pointers only because i've to swap Single and Double values also.
So my idea was have two(i had a two byte version using

rol [register], 8

too) methods which are doing the swaps on the addressed values so it's always are reference.
Hope you understand my POV. Any hint or help would be welcome

ab · 2020-05-01 08:22:56

@EgonHugeist
It is not enough.
1. The parameters are not passed on the same registers on Win64 and Linux x86_64.
So I use the parameter name in the asm code, and Delphi/FPC is able to use the correct register for it. It is easier than maintaining a separate asm code with registers for each platform.
2. On FPC i386, const QWord are NOT passed by reference as they do with Delphi Win32: you have to use constref.
3. on i86_64, FPC expects nostackframe; attribute whereas Delphi expects .noframe asm directive.

Please check the code I pasted.
They do exactly what you expect (32-bit and 64-bit reverse) in all platforms.

mORMot Open Source

#1 2020-04-27 11:05:49

Problematic FetchAllAsJSON using mORMot 1.18.5963/ZeosLib r6478

#2 2020-04-27 12:28:55

Re: Problematic FetchAllAsJSON using mORMot 1.18.5963/ZeosLib r6478

#3 2020-04-27 13:43:32

Re: Problematic FetchAllAsJSON using mORMot 1.18.5963/ZeosLib r6478

#4 2020-04-27 15:15:44

Re: Problematic FetchAllAsJSON using mORMot 1.18.5963/ZeosLib r6478

#5 2020-04-27 20:11:16

Re: Problematic FetchAllAsJSON using mORMot 1.18.5963/ZeosLib r6478

#6 2020-04-28 08:36:50

Re: Problematic FetchAllAsJSON using mORMot 1.18.5963/ZeosLib r6478

#7 2020-04-28 12:03:25

Re: Problematic FetchAllAsJSON using mORMot 1.18.5963/ZeosLib r6478

#8 2020-04-28 14:22:13

Re: Problematic FetchAllAsJSON using mORMot 1.18.5963/ZeosLib r6478

#9 2020-04-28 15:26:00

Re: Problematic FetchAllAsJSON using mORMot 1.18.5963/ZeosLib r6478

#10 2020-04-28 16:40:36

Re: Problematic FetchAllAsJSON using mORMot 1.18.5963/ZeosLib r6478

#11 2020-04-28 17:14:57

Re: Problematic FetchAllAsJSON using mORMot 1.18.5963/ZeosLib r6478

#12 2020-04-28 17:17:09

Re: Problematic FetchAllAsJSON using mORMot 1.18.5963/ZeosLib r6478

#13 2020-04-28 22:07:59

Re: Problematic FetchAllAsJSON using mORMot 1.18.5963/ZeosLib r6478

#14 2020-04-29 05:50:39

Re: Problematic FetchAllAsJSON using mORMot 1.18.5963/ZeosLib r6478

#15 2020-04-29 07:56:42

Re: Problematic FetchAllAsJSON using mORMot 1.18.5963/ZeosLib r6478

#16 2020-04-29 08:05:12

Re: Problematic FetchAllAsJSON using mORMot 1.18.5963/ZeosLib r6478

#17 2020-04-29 08:19:50

Re: Problematic FetchAllAsJSON using mORMot 1.18.5963/ZeosLib r6478

#18 2020-05-01 07:14:14

Re: Problematic FetchAllAsJSON using mORMot 1.18.5963/ZeosLib r6478

#19 2020-05-01 08:22:56

Re: Problematic FetchAllAsJSON using mORMot 1.18.5963/ZeosLib r6478

Board footer