Recent

Author Topic: TDBGrid, TBuffDataset and JSON  (Read 5061 times)

hakuna

  • New member
  • *
  • Posts: 7
  • Taking Lazarus seriously
TDBGrid, TBuffDataset and JSON
« on: January 19, 2018, 01:10:47 am »
Hello guys,


I'm trying to show in a TDBGrid data that come from a url that returns json. It looks like the data is obtained but it shows the empty cells. This is my code:

Code: Pascal  [Select][+][-]
  1.  
  2. procedure TFormInicio.FormActivate(Sender: TObject);
  3. var
  4.   Body: string;
  5.   Json: TJSONData;
  6.   Study: TJSONEnum;
  7. begin
  8.   Client:= TFPHTTPClient.Create(nil);
  9.     try
  10.       //get json data
  11.       Body:= Client.Get('https://someurl');
  12.     except
  13.          ShowMessage('Algo falló al conectar');
  14.     end;
  15.  
  16.     //parse json data
  17.     Json:= GetJSON(Body);
  18.  
  19.     BufDatasetStudies.Close;
  20.     BufDatasetStudies.CreateDataset;
  21.  
  22.     for Study in Json do
  23.     begin
  24.  
  25.        BufDatasetStudies.AppendRecord([
  26.              AvoidNull(Study.Value.Items[0].Value),
  27.              AvoidNull(Study.Value.Items[1].Value),
  28.              AvoidNull(Study.Value.Items[2].Value),
  29.              AvoidNull(Study.Value.Items[3].Value),
  30.              AvoidNull(Study.Value.Items[4].Value),
  31.              AvoidNull(Study.Value.Items[5].Value),
  32.              AvoidNull(Study.Value.Items[6].Value),
  33.              AvoidNull(Study.Value.Items[7].Value),
  34.              AvoidNull(Study.Value.Items[8].Value),
  35.              AvoidNull(Study.Value.Items[9].Value),
  36.              AvoidNull(Study.Value.Items[10].Value),
  37.              AvoidNull(Study.Value.Items[11].Value),
  38.              AvoidNull(Study.Value.Items[12].Value)
  39.  
  40.        ]);
  41.  
  42.     end;
  43.  
  44.     //BufDatasetStudies.Active:= true;
  45.     BufDatasetStudies.Open;
  46.     DataSourceStudies.DataSet:= BufDatasetStudies;
  47.  
  48.  
  49.      //free
  50.     Json.FreeInstance;
  51. end;                                  
  52.  
  53.  


Attached some captures. I appreciate any help.

Regards


mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: TDBGrid, TBuffDataset and JSON
« Reply #1 on: January 19, 2018, 08:28:00 am »
First open the TBufdataset and then filling it with data. Use
Code: Pascal  [Select][+][-]
  1.   try
  2.     BufDatasetStudies.open;
  3.     BufDatasetStudies.disablecontrols;
  4.     .......
  5.   finally
  6.     BufDatasetStudies.enablecontrols;
  7.   end;
  8.  
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

Thaddy

  • Hero Member
  • *****
  • Posts: 14159
  • Probably until I exterminate Putin.
Re: TDBGrid, TBuffDataset and JSON
« Reply #2 on: January 19, 2018, 08:38:26 am »
You can not simply append records if the TBufDataset doesn't know the structure: Set the fielddefs too before appending.
Specialize a type, not a var.

hakuna

  • New member
  • *
  • Posts: 7
  • Taking Lazarus seriously
Re: TDBGrid, TBuffDataset and JSON
« Reply #3 on: January 19, 2018, 06:05:50 pm »
Thanks for the help,

mangakissa I modified the code as you indicate but the same result keeps appearing. This is my code now:

Code: Pascal  [Select][+][-]
  1.  
  2. //parse json data
  3.     Json:= GetJSON(Body);
  4.  
  5.     BufDatasetStudies.Close;
  6.     BufDatasetStudies.CreateDataset;
  7.  
  8.     try
  9.  
  10.       BufDatasetStudies.Open;
  11.       BufDatasetStudies.DisableControls;
  12.  
  13.  
  14.       for Study in Json do
  15.       begin
  16.  
  17.          BufDatasetStudies.AppendRecord([
  18.                AvoidNull(Study.Value.Items[0].Value),
  19.                AvoidNull(Study.Value.Items[1].Value),
  20.                AvoidNull(Study.Value.Items[2].Value),
  21.                AvoidNull(Study.Value.Items[3].Value),
  22.                AvoidNull(Study.Value.Items[4].Value),
  23.                AvoidNull(Study.Value.Items[5].Value),
  24.                AvoidNull(Study.Value.Items[6].Value),
  25.                AvoidNull(Study.Value.Items[7].Value),
  26.                AvoidNull(Study.Value.Items[8].Value),
  27.                AvoidNull(Study.Value.Items[9].Value),
  28.                AvoidNull(Study.Value.Items[10].Value),
  29.                AvoidNull(Study.Value.Items[11].Value),
  30.                AvoidNull(Study.Value.Items[12].Value)
  31.  
  32.          ]);
  33.  
  34.       end;
  35.  
  36.       DataSourceStudies.DataSet:= BufDatasetStudies;
  37.  
  38.     finally
  39.  
  40.       BufDatasetStudies.EnableControls;
  41.       //free
  42.       Json.FreeInstance;
  43.  
  44.     end;  
  45.  
  46.  

Thaddy the fielddefs are defined in the properties panel.


What else could be the error?

Have a good day

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: TDBGrid, TBuffDataset and JSON
« Reply #4 on: January 20, 2018, 09:36:31 am »
What do you see with showmessage(Study.Value.Items[0].Value) and showmessage(AvoidNull(Study.Value.Items[12].Value))?
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

balazsszekely

  • Guest
Re: TDBGrid, TBuffDataset and JSON
« Reply #5 on: January 20, 2018, 09:45:44 am »
@hakuna
First of all you need a post:
Code: Pascal  [Select][+][-]
  1.      //...
  2.      BufDatasetStudies.Post; //this line
  3.      DataSourceStudies.DataSet:= BufDatasetStudies;
  4.  
Secondly the field definitions are wrong. Please do the following:
   1. Double click the BufDataset, select all , then click the "-" button
   2. Press the "Create New field and..."(third from left), select FieldType = Data, enter a name, then select a propert type: string, integer, float, date whatever
   3. Go to grid columns, then press the "Add fields" button
   4. From code, add the values like this:
Code: Pascal  [Select][+][-]
  1. with BufDatasetStudies do
  2. begin
  3.     Append;
  4.     IDStudy.AsString := AvoidNull(Study.Value.Items[0].Value);
  5.     RegistrationDate.AsDateTime:= AvoidNull(Study.Value.Items[1].Value);
  6.     //...
  7.     Age.AsInteger :=  AvoidNull(Study.Value.Items[4].Value);
  8.     Post;
  9. end;

PS: If it's not working just attach a test project + a demo json. Some forum users lately likes to play the guessing game. We don't.
PS1: Did you code in c, c# in the past? How you typed those field declaration suggest that you came from a c background.
« Last Edit: January 20, 2018, 09:47:34 am by GetMem »

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: TDBGrid, TBuffDataset and JSON
« Reply #6 on: January 20, 2018, 02:50:54 pm »
Appendrecord does post by it self. You don't have to do it manually.
AFAIK see the structure field the fields are filled in allready (see second picture).

I'm very curious if the json is filled.
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

hakuna

  • New member
  • *
  • Posts: 7
  • Taking Lazarus seriously
Re: TDBGrid, TBuffDataset and JSON
« Reply #7 on: January 20, 2018, 06:45:54 pm »
What do you see with showmessage(Study.Value.Items[0].Value) and showmessage(AvoidNull(Study.Value.Items[12].Value))?


mangakissa thanks you again. With showmessage(Study.Value.Items[0].Value) and showmessage(AvoidNull(Study.Value.Items[12].Value)) I can see the data from JSON.

AvoidNull check if the parameter it receives is null and return it as an empty string, this is the code:

Code: Pascal  [Select][+][-]
  1.  
  2. function TFormInicio.AvoidNull(Value: Variant ): String;
  3. begin
  4.    if (Value <> Null) then
  5.       AvoidNull:= Value
  6.    else
  7.       AvoidNull:= '';
  8. end;
  9.  
  10.  

hakuna

  • New member
  • *
  • Posts: 7
  • Taking Lazarus seriously
Re: TDBGrid, TBuffDataset and JSON
« Reply #8 on: January 20, 2018, 06:48:41 pm »
Appendrecord does post by it self. You don't have to do it manually.
AFAIK see the structure field the fields are filled in allready (see second picture).

I'm very curious if the json is filled.


Yes, the json is filled, in a first attempt I used a TStringGrid directly with the json and the data is well displayed.

hakuna

  • New member
  • *
  • Posts: 7
  • Taking Lazarus seriously
Re: TDBGrid, TBuffDataset and JSON
« Reply #9 on: January 20, 2018, 08:05:52 pm »
@hakuna
First of all you need a post:
Code: Pascal  [Select][+][-]
  1.      //...
  2.      BufDatasetStudies.Post; //this line
  3.      DataSourceStudies.DataSet:= BufDatasetStudies;
  4.  
Secondly the field definitions are wrong. Please do the following:
   1. Double click the BufDataset, select all , then click the "-" button
   2. Press the "Create New field and..."(third from left), select FieldType = Data, enter a name, then select a propert type: string, integer, float, date whatever
   3. Go to grid columns, then press the "Add fields" button
   4. From code, add the values like this:
Code: Pascal  [Select][+][-]
  1. with BufDatasetStudies do
  2. begin
  3.     Append;
  4.     IDStudy.AsString := AvoidNull(Study.Value.Items[0].Value);
  5.     RegistrationDate.AsDateTime:= AvoidNull(Study.Value.Items[1].Value);
  6.     //...
  7.     Age.AsInteger :=  AvoidNull(Study.Value.Items[4].Value);
  8.     Post;
  9. end;

PS: If it's not working just attach a test project + a demo json. Some forum users lately likes to play the guessing game. We don't.
PS1: Did you code in c, c# in the past? How you typed those field declaration suggest that you came from a c background.


GetMem the code is FPC and Lazarus from scratch ;)  although I have worked other projects with C and C#. Here is an example, my environment is Lazarus 1.8.0 for win32 and FPC 3.0.4:

 http://www.mediafire.com/file/8pi6f3vbaf76ulj/test.zip

As mangakissa  says , in theory according to the documentation of AppendRecord the function makes the post, this is the link:

https://www.freepascal.org/docs-html/fcl/db/tdataset.appendrecord.html


Thanks for the help

balazsszekely

  • Guest
Re: TDBGrid, TBuffDataset and JSON
« Reply #10 on: January 20, 2018, 08:25:26 pm »
@hakuna
Please test attached project, don't forget to copy libeay32.dll and ssleay32.dll back to the project folder. The attachment cannot be larger then 250 KB so I had to remove the dlls.

hakuna

  • New member
  • *
  • Posts: 7
  • Taking Lazarus seriously
Re: TDBGrid, TBuffDataset and JSON
« Reply #11 on: January 20, 2018, 10:44:11 pm »
@hakuna
Please test attached project, don't forget to copy libeay32.dll and ssleay32.dll back to the project folder. The attachment cannot be larger then 250 KB so I had to remove the dlls.


Thanks you GetMem your code rocks  8-) .Now I understand what you were referring to when defining the fields. I was using TBufDataset.FieldDefs  instead of using TBufDataset.Fields. Correcting this now with the AppendRecord  function also works.

I'm not sure if this is what Thaddy was trying to tell me too but thank you all for the support.

In case this can help someone else attached two screenshots in the correct and incorrect way at least for this example.

 

TinyPortal © 2005-2018