mORMot Open Source

nirnir

Member

Registered: 2013-11-11

Posts: 66

JSONToDataSet - preserve column type

On the client side I use devexpress grid expecting tclientdataset .
I get the data from server (external MSsql) as json and using JSONToDataSet to convert it to dataset
with this process I loose the field column type which makes  me problems with displaying dates and booleans .

Anyway to add the json string the column type and parse it when building the dataset ?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: JSONToDataSet - preserve column type

If your client is a mORMot client, you have at hand the TSQLModel to retrieve all column information.

We do not store any column type information in the JSON yet.
And we did not plan to put any such information yet, since it makes JSON bigger, and not standard.

You may retrieve column layout from the server or set it on the client side code.
I suspect you know on the client side what is your data layout...

nirnir

Member

Registered: 2013-11-11

Posts: 66

Re: JSONToDataSet - preserve column type

The problem is that the dataset is created by jsonTodataset .
Maybe you can add OnColumnCreated event/callback   to jsonTodataset.
it will allow to specify the column type instead of guessing by value .

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: JSONToDataSet - preserve column type

You can do it at TSQLTableJSON level, which is the one responsible of the JSON parsing and per column/row process.
You can specify an array of TSQLRecordClass to let column types be identified as expected.

Do you think an overloaded constructor with proper column types could make sense?

I've just added new TSQLTable[JSON].CreateFromTables/CreateWithColumnTypes() constructors, able to specify the column type information to be used.
It is now used by mORMotVCL's TSynSQLTableDataSet component (and JSONToDataSet function) on client side, if necessary.
See http://synopse.info/fossil/info/d45cfaa42f

Hope it matches your needs.

nirnir

Member

Registered: 2013-11-11

Posts: 66

Re: JSONToDataSet - preserve column type

Thanks ab,
But
on the client side I get json string from server and immediately convert it to dataset ,
The client  doesn't  know the exact fields order  and therefore can't  pass the matching column type array .
can you create constructor with Tpair<fieldname,fieldtype> array or TDictionary or callback function resolveFieldType(p_fieldName:string):TSQLFieldType

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: JSONToDataSet - preserve column type

I've added TSQLTable.SetFieldType() method to specify a column type.

See http://synopse.info/fossil/info/ca75547e88

In your case:
- Create your TSQLTableJSON from your JSON content;
- Call SetFieldType() to specify the expected column types;
- Then assign it to a TSynSQLTableDataSet.

Note that the TSQLTableJSON instance is not to be released until the TSynSQLTableDataSet is released, since this TSynSQLTableDataSet will in fact use the TSQLTableJSON to access the data.

nirnir

Member

Registered: 2013-11-11

Posts: 66

Re: JSONToDataSet - preserve column type

Thanks , it works .
As I understand the TSQLTableJSON  is automatically freed when I free TSynSQLTableDataSet  (when use CreateOwnedTable with Table<>nil ), am I right ?

You have small compilation error under XE2

function JSONToDataSet(aOwner: TComponent; const aJSON: RawUTF8;
  const ColumnTypes: array of TSQLFieldType
  {$ifndef UNICODE}; aForceWideString: boolean=false{$endif}): TSynSQLTableDataSet; overload;
{$ifdef HASINLINE}inline;{$endif}

[DCC Error] mORMotVCL.pas(158): E2439 Inline function must not have open array argument

Last edited by nirnir (2013-12-11 08:10:56)

nirnir

Member

Registered: 2013-11-11

Posts: 66

Re: JSONToDataSet - preserve column type

Another problem with that :

Null datetime values are translated to zeros in the Tclientdataset instead of null , that makes the grids display 30/12/1899 instead of empty column .

How can we solve that ?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: JSONToDataSet - preserve column type

As I understand the TSQLTableJSON  is automatically freed when I free TSynSQLTableDataSet  (when use CreateOwnedTable with Table<>nil ), am I right ?

Indeed.
As stated by the class documentation (i.e. the comments in the source interface).

You have small compilation error under XE2

Fixed.
See http://synopse.info/fossil/info/811d34cdf1

Thanks for the feedback!

nirnir

Member

Registered: 2013-11-11

Posts: 66

Re: JSONToDataSet - preserve column type

Thanks,
Any idea about the nulls translated to zeros ?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: JSONToDataSet - preserve column type

That's it.
Null values should now be handled as such when converted to TDataSet rows, not converted to 0 or ''.

See http://synopse.info/fossil/timeline