CODEWITHSUNDEEP
jsondelphi
niglng
niglng

Reputation: 99

JSON to Database table

Example JSON: 

[{"steward":"EMPL-0102","description":"Elish Guage","emplyear":"2001","emplmonth":"Nov","empl":"HOME","perhour":"50"},
 {"steward":"EMPL-9002","description":"Bush Harcourt","emplyear":"1990","emplmonth":"Nov","empl":"HOME","perhour":"50"}, 
 {"steward":"EMPL-0102","description":"John Long","emplyear":"2001","emplmonth":"Nov","empl":"OFFICE","perhour":"50"},
 {"steward":"EMPL-9002","description":"Wensel Gold","emplyear":"1990","emplmonth":"Nov","empl":"OFFICE","perhour":"50"}]

I need step-by-step workable Delphi snippet to translate JSON data received from PHP website to local database table. I have tried reading some documents but could not understand the proper implementation of my requirement.

I received the JSON data from my website and wish to parse this data into my local table.
I wish to asses the JSON record fields such as in table format (Column and rows).
I will really like to have it similar to 

FieldByName('field').AsString = JSONFieldByName('steward').AsString

then to the next JSON array record.

Upvotes: 0

Views: 10720

Answers (2)

Mason Wheeler
Mason Wheeler

Reputation: 84550

It's hard to get too specific without actual code and data to look at, but here's a general idea. This example uses the dwsJSON library from DWS, but the basic principles should work with other JSON implementations. It assumes that you have a JSON array made up of JSON objects, each of which contains only valid name/value pairs for your dataset.

procedure JsonToDataset(input: TdwsJSONArray; dataset: TDataset);
var
   i, j: integer;
   rec: TdwsJSONObject;
begin
   for i := 0 to input.ElementCount - 1 do
   begin
      rec := input.Elements[i] as TdwsJSONObject;
      dataset.Append;
      for j := 0 to rec.ElementCount - 1 do
         dataset[rec.names[j]] := rec.Values[j].value.AsVariant;
      dataset.Post;
   end;
   //at this point, do whatever you do to commit data in this particular dataset type
end;

Proper validation, error handling, etc is left as an exercise to the reader.

Upvotes: 5

Hari Babu Mandala
Hari Babu Mandala

Reputation: 295

The following code is more specific to your example. You can make use of JSON functionality used in this code, even though your requirement changes. In this array of string list contains (key, value) pairs. each string contains one record in the JSON object. You can refer using array index. 

procedure ParseJSONObject(jsonObject : TJSONObject);
var
  jsonArray     : TJSONArray;
  jsonArrSize,
  jsonListSize  : Integer;
  iLoopVar,
  iInnLoopVar   : Integer;
  recordList    : array of TStringList;
begin
  jsonArrSize := jsonObject.Size;
  SetLength(recordList, jsonArrSize);
  for iLoopVar := 0 to jsonArrSize - 1 do
  begin
    recordList[iLoopVar] := TStringList.Create;
    jsonArray := (jsonObject.Get(iLoopVar)).JsonValue as TJSONArray;
    jsonListSize := jsonArray.Size;
    for iInnLoopVar := 0 to jsonListSize - 1 do
    begin
      ParseJSONPair(jsonArray.Get(iInnLoopVar), recordList[iLoopVar]);
    end;
  end;
end;


procedure ParseJSONPair(ancestor: TJSONAncestor; var list : TStringList);
var
   jsonPair   : TJSONPair;
   jsonValue  : TJSONValue;
   jsonNumber : TJSONNumber;
   keyName    : String;
   keyvalue   : String;
begin
  if ancestor is TJSONPair then
  begin
    jsonPair := TJSONPair(ancestor);
    keyName := jsonPair.JsonString.ToString;
    if jsonPair.JsonValue is TJSONString then
      keyValue := jsonPair.JsonValue.ToString
    else if jsonPair.JsonValue is TJSONNumber then
    begin
      jsonNumber := jsonPair.JsonValue as TJSONNumber;
      keyvalue := IntToStr(jsonNumber.AsInt);
    end
    else if jsonPair.JsonValue is TJSONTrue then
      keyvalue := jsonPair.JsonValue.ToString
    else if jsonPair.JsonValue is TJSONFalse then
      keyvalue := jsonPair.JsonValue.ToString;
  end;
  list.Values[keyName] := keyvalue;
end;

Upvotes: 1

Related Questions