#1 2013-03-26 19:19:47

wai-kit
Member
From: Amsterdam, the Netherlands
Registered: 2012-11-27
Posts: 90

EdatabaseError using JSONToDataset

Hi everyone,

I have this function as part of an interface (IStockPortfolio) on my server :

fDatabase: TSQLDBConnectionProperties;

function TStockPortfolio.GetSQLQueryResults(aSQLQuery:RawUTF8):RawJSON;
var
  Res:ISQLDBRows;
begin
  if fDatabase=nil then
    ConnectDatabase;

  res := fDatabase.ExecuteInlined(aSQLQuery, true);

  if res=nil then
    result := ''
  else
    result := res.FetchAllAsJSON(true);
end;


On the client, I would like to do the following:

  fTableJSON := fService.GetSQLQueryResults('SELECT * FROM country');
  DataSource1.DataSet := JSONToDataSet(Self, fTableJSON, fClient);

An EDatabaseError get raised in the JSONToDataset with message 'No fields defined. Cannot create dataset'.

What seems to be the problem here?
Any help much appreciated.


Cheers,
Wai


fpcdeluxe, FPC 3.2 / Lazarus 2.0, mORMot on Windows 10 ...

Offline

#2 2013-03-26 19:33:57

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: EdatabaseError using JSONToDataset

What is the content of the RawJSON returned data?

"No fields defined" means that the internal TSQLTableJSON does not have any data column.

Offline

#3 2013-03-27 08:28:12

wai-kit
Member
From: Amsterdam, the Netherlands
Registered: 2012-11-27
Posts: 90

Re: EdatabaseError using JSONToDataset

The JSON data looks like this:

[{"Country_code":"AUS","name":"Australia","currency":"AUD"},{"Country_code":"CAN","name":"Canada","currency":"CAD"},{"Country_code":"CN","name":"China","currency":"RMD"},{"Country_code":"DE","name":"Germany","currency":"EUR"},{"Country_code":"FR","name":"France","currency":"EUR"},{"Country_code":"HK","name":"Hong Kong ","currency":"HKD"},{"Country_code":"JP","name":"Japan","currency":"YEN"},{"Country_code":"NL","name":"The Netherlands","currency":"EUR"},{"Country_code":"UK","name":"Great Britain","currency":"PND"},{"Country_code":"US","name":"The United States of America","currency":"US"}]

I have also tried the code from example 16:

  aTable := TSQLTableJSON.Create([],'',pointer(fTableJSON),Length(fTableJSON));
  TSQLTableToGrid.Create(StringGrid1, aTable, fClient);

The data is displayed nicely in the grid.


fpcdeluxe, FPC 3.2 / Lazarus 2.0, mORMot on Windows 10 ...

Offline

#4 2013-03-27 14:11:34

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: EdatabaseError using JSONToDataset

Are you using the latest 1.18 unstable version?

See http://synopse.info/fossil/wiki?name=Get+the+source

Offline

#5 2013-03-27 22:27:27

wai-kit
Member
From: Amsterdam, the Netherlands
Registered: 2012-11-27
Posts: 90

Re: EdatabaseError using JSONToDataset

I got the version from 26/3.

I just got the latest version from 27/3. Unfortunately, the error still get raised. Also the JSON message is incomplete. Instead of the previous
JSON message I now get : [{"Country_code


fpcdeluxe, FPC 3.2 / Lazarus 2.0, mORMot on Windows 10 ...

Offline

#6 2013-03-27 22:54:47

wai-kit
Member
From: Amsterdam, the Netherlands
Registered: 2012-11-27
Posts: 90

Re: EdatabaseError using JSONToDataset

I just discovered that the following statements produces the error:

  fTableJSON := fService.GetCountries;
  aTable := TSQLTableJSON.Create([],'',pointer(fTableJSON),Length(fTableJSON));
  TSQLTableToGrid.Create(StringGrid1, aTable,fClient);
  DataSource1.DataSet := JSONToDataSet(Self, fTableJSON, fClient);

Whereas :

  fTableJSON := fService.GetCountries;
  DataSource1.DataSet := JSONToDataSet(Self, fTableJSON, fClient);

gives no error and produces the correct output i.e. the dbgrid connected to Datasource1 showing the
correct data.


fpcdeluxe, FPC 3.2 / Lazarus 2.0, mORMot on Windows 10 ...

Offline

#7 2013-03-28 07:16:48

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: EdatabaseError using JSONToDataset

Calling TSQLTableJSON.Create([],'',pointer(fTableJSON),Length(fTableJSON)) will change the fTableJSON content in-place, as stated by the documentation.
It unescape the JSON strings, and add #0 at the end of each field.

So, when  JSONToDataSet(Self, fTableJSON, fClient) is executed, fTableJSON will already be parsed, so won't be JSON any more.

Make a private copy of  fTableJSON is you want to re-use the JSON buffer.
That is, call the other TSQLTableJSON.Create() constructor.

Making a private copy will use twice as memory, but will allow to re-parse the JSON buffer.
You can also assign the same TSQLTableJSON instance to the TClientDataSet.

Offline

#8 2013-03-28 09:55:18

wai-kit
Member
From: Amsterdam, the Netherlands
Registered: 2012-11-27
Posts: 90

Re: EdatabaseError using JSONToDataset

Guess I didn't read the small letters in the documentation :-).
Thanks for the info, Arnaud.

Cheers,
Wai


fpcdeluxe, FPC 3.2 / Lazarus 2.0, mORMot on Windows 10 ...

Offline

#9 2013-03-28 10:00:50

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: EdatabaseError using JSONToDataset

Always read the small letters before signing!
smile

Offline

#10 2013-03-28 13:46:12

eraldo
Member
From: Brasil
Registered: 2010-07-22
Posts: 69
Website

Re: EdatabaseError using JSONToDataset

wai-kit wrote:

Guess I didn't read the small letters in the documentation :-).
Thanks for the info, Arnaud.

Cheers,
Wai

Hello, Could you post the solution to your problem?
Thus facilitate the understanding to others.

Thank you for your kindness

Offline

#11 2013-03-28 13:58:57

wai-kit
Member
From: Amsterdam, the Netherlands
Registered: 2012-11-27
Posts: 90

Re: EdatabaseError using JSONToDataset

Hi Eraldo,

The solution is already explained in post #6 and #7 in this thread.

Cheers,
Wai


fpcdeluxe, FPC 3.2 / Lazarus 2.0, mORMot on Windows 10 ...

Offline

#12 2015-03-04 16:29:33

automacaosamos
Member
Registered: 2015-02-19
Posts: 20

Re: EdatabaseError using JSONToDataset

iI am trying to fill a dataset with the json
follows and I am not getting,
someone help me with a sample ?.

   fData    := TSQLTableJSON.Create([],'','{"ID":"00001","NAME":"MANAGER E CIA LTDA"}');
   fDataSet := TDataSet.Create(nil);
   fDataSet := JSONToDataSet(self, fData);
   fDataSet.Open;

   showmessage(IntToStr(fDataSet.recordCount));

Offline

#13 2015-03-04 17:58:55

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: EdatabaseError using JSONToDataset

Did you see https://github.com/synopse/mORMot/blob/ … CLUnit.pas ?

Try to use
- JSONToClientDataSet() - slower but potentially read/write
- or JSONToDataSet() - faster but read/only

Offline

Board footer

Powered by FluxBB