JSONArray as String to DataBase

BIZ TMS XData
1

Hello,

I am struggling by sending my JSON Data to the database...

from the Client I get the JSArray String like that:

'[{"BNumber":"200026","Status":"print_label_ok","Date":"2021.04.16","Time":"12:42:22","TNumber":"0036527107258","VDienstL":"(Standard-Versand)"},
{"BNumber":"200588","Status":"print_label_ok","Date":"2021.04.20","Time":"12:40:10","TNumber":"003404342965","VDienstL":"(Standard-Versand)"}]'

I want to bring this two rows into my MySQL-Database. But I am failling:

function TDBService.Write_TNumber(JS_ArrayString: string): boolean;
var JS_Array: TJSONArray;
JS_Object: TJSONObject;
JS_Value: TJSONValue;
Row_Value: TJSONValue;
Row_Item: TJSONValue;
Insert_String: string;
ID: integer;
begin
JS_Object := TJSJSON.parseObject(JS_ArrayString);
Try
JS_Array := TJSArray.Create;
Try
JS_Array := TJSONObject.ParseJSONValue(JS_ArrayString) as TJSONArray;
ID:=0;
for JS_Value in JS_Array do begin
Insert_String := Insert_String +'(';
Row_Value := (JS_Value as TJSONObject).GetValue('Test');
if Row_Value is TJSONArray then begin
for Row_Item in TJSONArray(Row_Value) do begin
Row_Item.TryGetValue('bnumber', ID);
Insert_String := Insert_String + Row_Item.Value +', ';
Row_Item.TryGetValue('pront_status', ID);
Insert_String := Insert_String + Row_Item.Value +', ';
Row_Item.TryGetValue('ship_date', ID);
Insert_String := Insert_String + Row_Item.Value +', ';
Row_Item.TryGetValue('ship_time', ID);
Insert_String := Insert_String + Row_Item.Value +', ';
Row_Item.TryGetValue('tnumber', ID);
Insert_String := Insert_String + Row_Item.Value +', ';
Row_Item.TryGetValue('vdienstl', ID);
Insert_String := Insert_String + Row_Item.Value ;
end;
Insert_String := Insert_String +'), ';
end;
INC(ID);
end;
Finally
JS_Array.Free;
End;
Except
Result:= false;
End;
end;

But this way does not work. How can I bring the values from the JS_ArrayString into my DataBase / Insert-Statement?

Many thanks
Patrick

2

I found a way that I can fix it, but I am not sure, if it is the best way...

type
TRows = array of array of string;
TCols = array of string;

procedure TForm1.AdvGlowButton1Click(Sender: TObject);
var JSON: string;
ClientItem: TJSONValue;
ClientList: TJSONArray;
Rows: TRows;
Cols: TCols;
I, J: Integer;
SQL_Text: string;
Value_String: string;
begin
JSON := Memo_JSstring.Lines.Text;
SetLength(Cols, 6);
SetLength(Rows, Length(Cols),0);
Cols[0] := 'BNumber';
Cols[1] := 'Print_Status';
Cols[2] := 'Ship_Date';
Cols[3] := 'Ship_Time';
Cols[4] := 'TNumber';
Cols[5] := 'VDienstL';
ClientList := TJSONArray.Create;
try
ClientList := TJSONObject.ParseJSONValue(JSON) as TJSONArray;
I := 0;
for ClientItem in ClientList do begin
SetLength(Rows, Length(Cols), Length(Rows[0])+1);
for J := 0 to Length(Cols) -1 do begin
Rows[J,I] := ClientItem.GetValue(Cols[J]);
end;
INC(I);
end;

for I := 0 to Length(Rows[0]) -1 do begin
if I=0 then begin
Value_String := '';
end
else begin
Value_String := Value_String + ', ';
end;
Value_String := Value_String+ '('+
QuotedStr(Rows[0,I])+', '+
QuotedStr(Rows[1,I])+', '+
QuotedStr(Rows[2,I])+', '+
QuotedStr(Rows[3,I])+', '+
QuotedStr(Rows[4,I])+', '+
QuotedStr(Rows[5,I])+') ';
end;

 SQL_Text := 'INSERT INTO versand (bnumber, print_status, ship_date, ship_time, tnumber, vdienstl) '+
             'VALUES '+Value_String;

finally
ClientList.Free;
end;
end;

Patrick

3

Hi, another way

type
  TMyObject = class
  private
    FBNumber: string;
    FStatus: string;
    FDate: string;
    FTime: string;
    FTNumber: string;
    FVDienstL: string;
 public
   property BNumber: string read FBNumber;
   property Status: string read FStatus;
   property Date: string read FDate;
   property Time: string read FTime;
   property TNumber: string read FTNumber;
   property VDienstL: string read FVDienstL;
 end;

 (..)

implementation

uses
  Bcl.Json.Deserializer;

procedure XXXXXX;
var
  AJsonString: string;
  AItem: TMyObject;
  AList: TList<TMyObject>;
  ADeserializer: TJsonDeserializer;
begin
  AJsonString := (..);
  ADeserializer := TJsonDeserializer.Create;
  try
    AList := ADeserializer.Read<TObjectList<TMyObject>>(AJsonString);
    try
      for AItem in AList do
      begin
         // use AItem to build your SQL (ideally, use a parameterized query)
      end;
    finally
      AList.Free;
    end;
  finally
    ADeserializer.Free;
  end;
end;

HTH,

1 Like
4

BTW, you can make TMyObject an Aurelius entity and not have to deal with SQL at all.

      for AItem in AList do
      begin
         ObjectManager.Save(AItem);
      end;

Regards,

1 Like
5

@Hiby_Patrick please use the preformatted text option to format your code, not block quote. This button: image

It's very confusing to read your code like that. Your second code seem to leak memory, you don't have to create a TJSONArray in advance just to discard it after a ParseObject is called.