EdatabaseError using JSONToDataset

wai-kit · 2013-03-26 19:19:47

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

ab · 2013-03-26 19:33:57

What is the content of the RawJSON returned data?

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

wai-kit · 2013-03-27 08:28:12

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.

ab · 2013-03-27 14:11:34

Are you using the latest 1.18 unstable version?

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

wai-kit · 2013-03-27 22:27:27

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

wai-kit · 2013-03-27 22:54:47

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.

ab · 2013-03-28 07:16:48

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.

wai-kit · 2013-03-28 09:55:18

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

Cheers,
Wai

ab · 2013-03-28 10:00:50

Always read the small letters before signing!

eraldo · 2013-03-28 13:46:12

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

wai-kit · 2013-03-28 13:58:57

Hi Eraldo,

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

Cheers,
Wai

automacaosamos · 2015-03-04 16:29:33

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));

ab · 2015-03-04 17:58:55

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

mORMot Open Source

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

EdatabaseError using JSONToDataset

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

Re: EdatabaseError using JSONToDataset

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

Re: EdatabaseError using JSONToDataset

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

Re: EdatabaseError using JSONToDataset

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

Re: EdatabaseError using JSONToDataset

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

Re: EdatabaseError using JSONToDataset

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

Re: EdatabaseError using JSONToDataset

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

Re: EdatabaseError using JSONToDataset

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

Re: EdatabaseError using JSONToDataset

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

Re: EdatabaseError using JSONToDataset

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

Re: EdatabaseError using JSONToDataset

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

Re: EdatabaseError using JSONToDataset

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

Re: EdatabaseError using JSONToDataset

Board footer