CurrentData as JSON as parameter in xDataService

[Karlheinz_Munz]

Hello,

I like to send the data from a TxDataWebDataSet in a Service to the xDataServer.

      JSVal: JSValue;
begin
   JSVal:=DataSet_NewCompany.CurrentData;
   TVc_XDataWebClient.RawInvoke('ITV_CreateNewCustService.Insert_Company_Test',[JSVal], @OnResponse);

The Service Implemenation looks like:

unit TV_CreateNewCustServiceImplementation;

interface

uses
   XData.Server.Module,
   XData.Service.Common,
   TV_CreateNewCustService,
   System.JSON,
   System.SysUtils,
   WEBLib.Dialogs,
   FireDAC.Comp.Client,
   VCL.Dialogs,
   xData.Web.Client,
   JS;

type
  [ServiceImplementation]
  TTV_CreateNewCustService = class(TInterfacedObject, ITV_CreateNewCustService)
  private
    function Insert_Company_Test(JSVal: JSValue): integer;
  end;


implementation

uses TTV_ConnectModuleUnit;

function TTV_CreateNewCustService.Insert_Company_Test(JSVal: JSValue): integer;
var   Query: TFDQuery;
      JSObj: TJSObject;
begin
   JSObj:=TJSJSON.parseObject(JS.toString(JSVal));
   Query:= TFDQuery.Create(nil);
   Query.Connection:=MySQL.MySQL_Cnnctn;
   Try
      Result:=0;
      Query.SQL.Clear;
      Query.SQL.Add('insert into company '+
                        '(company_name, anschrift_1, anschrift_2, plz, ort, telefon_ohne_dw, durchwahl, ust_id, sprache_id, land_id) '+
                        'values '+
                        '(:company_name, :anschrift_1, :anschrift_2, :plz, :ort, :telefon_ohne_dw, :durchwahl, :ust_id, :sprache_id, :land_id); '+
                        'select last_insert_id() as ID ');
      Query.ParamByName('company_name').AsString:=    string(JSObj['company_name']);
      Query.ParamByName('anschrift_1').AsString:=     string(JSObj['anschrift_1']);
      Query.ParamByName('anschrift_2').AsString:=     string(JSObj['anschrift_2']);
      Query.ParamByName('plz').AsString:=             string(JSObj['plz']);
      Query.ParamByName('ort').AsString:=             string(JSObj['ort']);
      Query.ParamByName('telefon_ohne_dw').AsString:= string(JSObj['telefon_ohne_dw']);
      Query.ParamByName('durchwahl').AsString:=       string(JSObj['durchwahl']);
      Query.ParamByName('ust_id').AsString:=          string(JSObj['ust_id']);
      Query.ParamByName('sprache_id').AsInteger:=     integer(JSObj['sprache_id']);
      Query.ParamByName('land_id').AsInteger:=        integer(JSObj['land_id']);

      Query.Open;
      Result:= Query.FieldByName('id').AsInteger;
   Finally
      Query.Free;
   End;

But when I try to compile the serverapp I get the message:

Erste Gelegenheit für Exception bei $756146D2. Exception-Klasse EUnsupportedActionParamType mit Meldung 'Cannot define action "Insert_Company_Test": Invalid type "JSValue" for param "JSVal"'. Prozess TheVisit_Server.exe (8960)

I am looking for a possiblity to send the parameters for the service in an oject or value. Is that possible on that way?

Many thanks
Patrick

[wlandgraf (Wagner Landgraf)]

JS unit and JSValue types are specific to TMS Web Core. In XData, you can simply use TJSONValue, or even TJSONObject if you know users will always send a JSON object.

[Karlheinz_Munz]

Hi Wagner,

many thanks for answering. I changed it to TJSONObject, but the error is the same...

Service_Unit:

unit TV_CreateNewCustService;

interface

uses
  XData.Service.Common,
  System.JSON;

type
  [ServiceContract]
  ITV_CreateNewCustService = interface(IInvokable)
    ['{42224191-058E-477B-87A9-C16D0B034074}']
    // By default, any service operation responds to (is invoked by) a POST request from the client.
    [HTTPGET] function Insert_Company_Test(JSObj: TJSONObject): integer;
  end;

implementation

initialization
  RegisterServiceType(TypeInfo(ITV_CreateNewCustService));

end.

Service_Implementation

unit TV_CreateNewCustServiceImplementation;

interface

uses
   XData.Server.Module,
   XData.Service.Common,
   TV_CreateNewCustService,
   System.JSON,
   System.SysUtils,
   WEBLib.Dialogs,
   FireDAC.Comp.Client,
   VCL.Dialogs;

type
  [ServiceImplementation]
  TTV_CreateNewCustService = class(TInterfacedObject, ITV_CreateNewCustService)
  private
    function Insert_Company_Test(JSObj: TJSONObject): integer;
  end;


implementation

uses TTV_ConnectModuleUnit;

function TTV_CreateNewCustService.Insert_Company_Test(JSObj: TJSONObject): integer;
var   Query: TFDQuery;
begin
   Query:= TFDQuery.Create(nil);
   Query.Connection:=MySQL.MySQL_Cnnctn;
   Try
      Result:=0;
      Query.SQL.Clear;
      Query.SQL.Add('insert into company '+
                        '(company_name, anschrift_1, anschrift_2, plz, ort, telefon_ohne_dw, durchwahl, ust_id, sprache_id, land_id) '+
                        'values '+
                        '(:company_name, :anschrift_1, :anschrift_2, :plz, :ort, :telefon_ohne_dw, :durchwahl, :ust_id, :sprache_id, :land_id); '+
                    'select last_insert_id() as ID ');

      Query.ParamByName('company_name').AsString:=    string(JSObj.Values['company_name']);
      Query.ParamByName('anschrift_1').AsString:=     string(JSObj.Values['anschrift_1']);
      Query.ParamByName('anschrift_2').AsString:=     string(JSObj.Values['anschrift_2']);
      Query.ParamByName('plz').AsString:=             string(JSObj.Values['plz']);
      Query.ParamByName('ort').AsString:=             string(JSObj.Values['ort']);
      Query.ParamByName('telefon_ohne_dw').AsString:= string(JSObj.Values['telefon_ohne_dw']);
      Query.ParamByName('durchwahl').AsString:=       string(JSObj.Values['durchwahl']);
      Query.ParamByName('ust_id').AsString:=          string(JSObj.Values['ust_id']);
      Query.ParamByName('sprache_id').AsInteger:=     integer(JSObj.Values['sprache_id']);
      Query.ParamByName('land_id').AsInteger:=        integer(JSObj.Values['land_id']);

      Query.Open;
      Result:= Query.FieldByName('id').AsInteger;
   Finally
      Query.Free;
   End;
end;


initialization
  RegisterServiceType(TTV_CreateNewCustService);
end.

And the exception message calls:

 'Cannot define action "Insert_Company_Test": Type "(undefined)" for param "JSObj" cannot be bound from URI'. Prozess TheVisit_Server.exe (9964)

What does that mean and how can I fix it?

Many thanks
Patrick

[wlandgraf (Wagner Landgraf)]

That's not the same error, that's a different one.

It means you cannot receive a JSON content in a HTTP GET request. Change your [HTTPGET] attribute to a different one ([HttpPost] or [HttpPut]). Or just remove it, XData will then use POST as the default method.

[Karlheinz_Munz]

Hi Wagner,

I think I could solve the issue. Additional to your support I bought the book TMS WebCore...

That is the Save-Procedure in WebCore where I send a JSObject to the server:

procedure TDataModule_Client.Save_NewCompany(AOnDataRecieved: TDataRecievedCallback);
   procedure OnResponse(Response: TXDataClientResponse);
   var   companyID: integer;
         JSObj: TJSObject;
   begin
      JSObj:=TJSJSON.parseObject(Response.ResponseText);
      DataSet_NewCompanycompany_id.AsInteger:= StrToInt(String(JSObj['value']));

      if Assigned(AOnDataRecieved) then begin
         AOnDataRecieved();
      end;
   end;

var   JSVal: JSValue;
      JSObj: TJSObject;
begin
   JSVal:=DataSet_NewCompany.CurrentData;
   JSObj:=toObject(JSVal);
   TVc_XDataWebClient.RawInvoke('ITV_CreateNewCustService.Insert_Company_Test',[JSObj], @OnResponse);
end;```

On server side that is the function to the database:

function TTV_CreateNewCustService.Insert_Company_Test(JSObj: TJSONObject): integer;
var Query: TFDQuery;
begin
Query:= TFDQuery.Create(nil);

Query.Connection:=MySQL.MySQL_Cnnctn;
Try
Result:=0;
Query.SQL.Clear;
Query.SQL.Add('insert into company '+
'(company_name, anschrift_1, anschrift_2, plz, ort, telefon_ohne_dw, durchwahl, ust_id, sprache_id, land_id) '+
'values '+
'(:company_name, :anschrift_1, :anschrift_2, :plz, :ort, :telefon_ohne_dw, :durchwahl, :ust_id, :sprache_id, :land_id); '+
'select last_insert_id() as ID ');

  Query.ParamByName('company_name').AsString:=    JSONstringQMARK(JSObj.GetValue('company_name').ToString);
  Query.ParamByName('anschrift_1').AsString:=     JSONstringQMARK(JSObj.GetValue('anschrift_1').ToString);
  Query.ParamByName('anschrift_2').AsString:=     JSONstringQMARK(JSObj.GetValue('anschrift_2').ToString);
  Query.ParamByName('plz').AsString:=             JSONstringQMARK(JSObj.GetValue('plz').ToString);
  Query.ParamByName('ort').AsString:=             JSONstringQMARK(JSObj.GetValue('ort').ToString);
  Query.ParamByName('telefon_ohne_dw').AsString:= JSONstringQMARK(JSObj.GetValue('telefon_ohne_dw').ToString);
  Query.ParamByName('durchwahl').AsString:=       JSONstringQMARK(JSObj.GetValue('durchwahl').ToString);
  Query.ParamByName('ust_id').AsString:=          JSONstringQMARK(JSObj.GetValue('ust_id').ToString);
  Query.ParamByName('sprache_id').AsInteger:=     JSONintegerNULL(JSObj.GetValue('sprache_id').ToString);
  Query.ParamByName('land_id').AsInteger:=        JSONintegerNULL(JSObj.GetValue('land_id').ToString);

  Query.Open;
  Result:= Query.FieldByName('id').AsInteger;

Finally
Query.Free;
End;
end;

I had the problem that null-values caused an exception. For that I wrote the function:

function JSONintegerNULL (JSstring: string): integer;
begin
if JSstring='null' then
Result:=0
else
Result:= StrToInt(JSONstringQMARK(JSstring));
end;```

And the string-values had a quotation mark at the start and the end, so I defined tah function:

function JSONstringQMARK (JSstring: string): string;
begin
   Result:=Copy(JSstring,1,Length(JSstring)-1);
   Result:=Copy(Result,2,Length(Result));
end;

Are these both function necessary or is there another possibility?

Many thanks
Patrick

[wlandgraf (Wagner Landgraf)]

You can alternatively use

TJSONString(JSObj.GetValue('land_id')).Value

This will return an empty string in case of null values.
Note that you can also use strong-typed classes with XData, i.e., instead of receiving a TJSONObject type, you can receive for example a TMyParam type, that will have fields with same name as your JSON properties:

type 
  TMyParam = class
    company_name: string;
    anschrift_1: string;
    // and so on
  end;

XData will convert the JSON to the object for you. More info:

Parameter Binding

Entity and Object Representation

[Karlheinz_Munz]

You are right! That's works much better!

function TTV_CreateNewCustService.Insert_Company(JSObj: TJSONObject): integer;
var   Query: TFDQuery;
begin
   Query:= TFDQuery.Create(nil);
   Query.Connection:=MySQL.MySQL_Cnnctn;
   Try
      Result:=0;
      Query.SQL.Clear;
      Query.SQL.Add('insert into company '+
                        '(company_name, anschrift_1, anschrift_2, plz, ort, telefon_ohne_dw, durchwahl, ust_id, sprache_id, land_id) '+
                    'values '+
                        '(:company_name, :anschrift_1, :anschrift_2, :plz, :ort, :telefon_ohne_dw, :durchwahl, :ust_id, :sprache_id, :land_id); '+
                    'select last_insert_id() as ID ');

      Query.ParamByName('company_name').AsString:=    TJSONString(JSObj.GetValue('company_name')).Value;
      Query.ParamByName('anschrift_1').AsString:=     TJSONString(JSObj.GetValue('anschrift_1')).Value;
      Query.ParamByName('anschrift_2').AsString:=     TJSONString(JSObj.GetValue('anschrift_2')).Value;
      Query.ParamByName('plz').AsString:=             TJSONString(JSObj.GetValue('plz')).Value;
      Query.ParamByName('ort').AsString:=             TJSONString(JSObj.GetValue('ort')).Value;
      Query.ParamByName('telefon_ohne_dw').AsString:= TJSONString(JSObj.GetValue('telefon_ohne_dw')).Value;
      Query.ParamByName('durchwahl').AsString:=       TJSONString(JSObj.GetValue('durchwahl')).Value;
      Query.ParamByName('ust_id').AsString:=          TJSONString(JSObj.GetValue('ust_id')).Value;
      Query.ParamByName('sprache_id').AsInteger:=     StrToIntDef(TJSONString(JSObj.GetValue('sprache_id')).Value,0);
      Query.ParamByName('land_id').AsInteger:=        StrToIntDef(TJSONString(JSObj.GetValue('land_id')).Value,0);

      Query.Open;
      Result:= Query.FieldByName('id').AsInteger;
   Finally
      Query.Free;
   End;
end;

Many thanks
Patrick