Bug - int64 values from a database view wrongly parsed as float values

edwinsn · 2015-09-29 16:45:47

The table:

CREATE TABLE CustInv (
    ID                INTEGER PRIMARY KEY AUTOINCREMENT,
...
);

The view:

CREATE VIEW v_event_inv AS
    SELECT header.ID AS DocId,
           ...
      FROM CustInv header

The delphi code:

var
  myFieldType: TSQLFieldType;
  myFieldTypeName: string;
  myFieldValue: string;
  tmpTable: TSQLTableJSON;
begin
  tmpTable := AppCtx.Db.ExecuteList([], 'SELECT * FROM v_event_inv');

  //some testing code
    {$IFDEF DEBUG}
    myFieldType := tmpTable.FieldType(0);//expected: sftID. Actual: sftFloat
    myFieldTypeName := GetEnumName(TypeInfo(TSQLFieldType), Integer(myFieldType));
    myFieldValue := tmpTable.FieldValue('DocId', 1); //correct
    CodeSite.Send(myFieldValue, myFieldTypeName);
    {$ENDIF}

PS: I noticed the bug after using ToClientDataSet, but tracked it down in ExecuteList()...

Last edited by edwinsn (2015-09-29 16:47:46)

edwinsn · 2015-09-29 17:10:55

A sample JSON response I got while debugging this issue:

[{"DocId":2078946242580065800,"DocDate":"2015-09-15","EventType":"e_po","ProductNr":"000020"}]

note 1: the DocId value is long...
note 2: I'm using the latest version from github.

Last edited by edwinsn (2015-09-29 17:15:03)

ab · 2015-09-30 12:03:41

Default is a fast "guess" from the actual JSON content, which may be not exact in such cases.

You have to force the expected field types in tmpTable, using TSQLTable.SetFieldType() method.

edwinsn · 2015-09-30 12:33:19

Thanks ab, I'll utilize the SetFieldType method. I'm curious - doesn't the sqlite engine return field type info for the result set?

ab · 2015-09-30 12:38:03

Yes, it does, but there is no SQL field type returned within the JSON response from the server.
Just values are returned, not types.

So TSQLTableJson has to guess the field type from actual values (with some potential errors), unless TSQLTable.SetFieldType() method is used.

edwinsn · 2015-09-30 14:30:33

So do you have a plan to fix it?

ab · 2015-09-30 16:01:55

I can't " fix " it since there is not the type info within the json, and there never will.

edwinsn · 2015-10-01 03:37:36

IMHO, the JSON response returned from the server should include the field type info.

ab · 2015-10-01 06:56:10

REST is about transmission per representation.
There is never low-level information like "backend DB column types" in the JSON transmitted in such REST services.
The field type info would not be included with the JSON data.

ab · 2015-10-01 06:56:49

REST is about transmission per representation.
There is never low-level information like "backend DB column types" in the JSON transmitted in such REST services.
The field type info would not be included with the JSON data.

If you want to have the type information, use a DB-based technology, like SynDBRemote.

edwinsn · 2015-10-02 11:57:23

But even working in REST mode, SQL has its advantage and we need to execute complex SQL to generate reports.

ab · 2015-10-02 12:04:47

I do not see what is the problem here.

If you generate a report, you know the result layout of the JSON generated by your SQL, so you can define the field types before using it in a TDataSet.

edwinsn · 2015-10-03 15:51:55

Hi Arnaud,

Yes, I can solve the problem that way. But I still tend to think the ExecuteList() should either correctly determine all field types or the user have to manually set type for all fields, otherwise it's error-prone.

ab · 2015-10-03 17:48:13

It is error prone only if you do not use the ORM, but SQL and TDataSet...

miab3 · 2015-10-03 18:09:14

May be add an additional question about metadata, and answer receive in JSON?

mpv · 2015-10-03 19:17:35

Passing such big numbers in JSON is not good solution IMHO. In case your service is used from browser it will lose precision during json.parse. Internally in JavaScript numbers are double. In my services I either limit a int64 values to 10^16 or pass it as string if limitation is not possible

edwinsn · 2015-10-19 16:13:54

ab wrote:

It is error prone only if you do not use the ORM, but SQL and TDataSet...

Yes, but I guess it's very common to use SQL directly for complex reports that require multiple level of joins even union joins.

edwinsn · 2015-10-19 16:16:18

mpv wrote:

Passing such big numbers in JSON is not good solution IMHO. In case your service is used from browser it will lose precision during json.parse. Internally in JavaScript numbers are double. In my services I either limit a int64 values to 10^16 or pass it as string if limitation is not possible

Thanks MPV, I'm not using it in the browser, but that seems a limit of JS, is there a way to get around it, like treating the IDs as strings?

mORMot Open Source

#1 2015-09-29 16:45:47

Bug - int64 values from a database view wrongly parsed as float values

#2 2015-09-29 17:10:55

Re: Bug - int64 values from a database view wrongly parsed as float values

#3 2015-09-30 12:03:41

Re: Bug - int64 values from a database view wrongly parsed as float values

#4 2015-09-30 12:33:19

Re: Bug - int64 values from a database view wrongly parsed as float values

#5 2015-09-30 12:38:03

Re: Bug - int64 values from a database view wrongly parsed as float values

#6 2015-09-30 14:30:33

Re: Bug - int64 values from a database view wrongly parsed as float values

#7 2015-09-30 16:01:55

Re: Bug - int64 values from a database view wrongly parsed as float values

#8 2015-10-01 03:37:36

Re: Bug - int64 values from a database view wrongly parsed as float values

#9 2015-10-01 06:56:10

Re: Bug - int64 values from a database view wrongly parsed as float values

#10 2015-10-01 06:56:49

Re: Bug - int64 values from a database view wrongly parsed as float values

#11 2015-10-02 11:57:23

Re: Bug - int64 values from a database view wrongly parsed as float values

#12 2015-10-02 12:04:47

Re: Bug - int64 values from a database view wrongly parsed as float values

#13 2015-10-03 15:51:55

Re: Bug - int64 values from a database view wrongly parsed as float values

#14 2015-10-03 17:48:13

Re: Bug - int64 values from a database view wrongly parsed as float values

#15 2015-10-03 18:09:14

Re: Bug - int64 values from a database view wrongly parsed as float values

#16 2015-10-03 19:17:35

Re: Bug - int64 values from a database view wrongly parsed as float values

#17 2015-10-19 16:13:54

Re: Bug - int64 values from a database view wrongly parsed as float values

#18 2015-10-19 16:16:18

Re: Bug - int64 values from a database view wrongly parsed as float values

Board footer