RAD Server Academy Course – lecture 7 extract – Returning JSON using JSONValue and JSONWriter.

interface

uses
  System.SysUtils, System.Classes, System.JSON,
  EMS.Services, EMS.ResourceAPI, EMS.ResourceTypes, FireDAC.Stan.Intf,
  FireDAC.Stan.Option, FireDAC.Stan.Error, FireDAC.UI.Intf, FireDAC.Phys.Intf,
  FireDAC.Stan.Def, FireDAC.Stan.Pool, FireDAC.Stan.Async, FireDAC.Phys,
  FireDAC.Phys.IB, FireDAC.Phys.IBDef, FireDAC.ConsoleUI.Wait,
  FireDAC.Stan.Param, FireDAC.DatS, FireDAC.DApt.Intf, FireDAC.DApt,
  FireDAC.Stan.StorageJSON, Data.DB, FireDAC.Comp.DataSet, FireDAC.Comp.Client,
  FireDAC.Comp.BatchMove.JSON, FireDAC.Comp.BatchMove,
  FireDAC.Comp.BatchMove.DataSet;

type
  [ResourceName('FireDAC')]
  TFireDACResource1 = class(TDataModule)
    DatabaseConnection: TFDConnection;
    EmployeeQuery: TFDQuery;
    FDStanStorageJSONLink1: TFDStanStorageJSONLink;
    EmployeeQueryEMP_NO: TSmallintField;
    EmployeeQueryFIRST_NAME: TStringField;
    EmployeeQueryLAST_NAME: TStringField;
    EmployeeQueryPHONE_EXT: TStringField;
    EmployeeQueryHIRE_DATE: TSQLTimeStampField;
    EmployeeQueryDEPT_NO: TStringField;
    EmployeeQueryJOB_CODE: TStringField;
    EmployeeQueryJOB_GRADE: TSmallintField;
    EmployeeQueryJOB_COUNTRY: TStringField;
    EmployeeQuerySALARY: TFMTBCDField;
    EmployeeQueryFULL_NAME: TStringField;
  published
    procedure Get(const AContext: TendpointContext;
	const ARequest: TEndpointRequest; const AResponse: TEndpointResponse);
    [ResourceSuffix('{item}')]
    procedure GetItem(const AContext: TendpointContext;
	const ARequest: TEndpointRequest; const AResponse: TEndpointResponse);
  end;

implementation

{%CLASSGROUP 'System.Classes.TPersistent'}

uses
  System.JSON.Types;

{$R *.dfm}

procedure TFireDACResource1.Get(const AContext: TendpointContext;
	const ARequest: TEndpointRequest; const AResponse: TEndpointResponse);
var
  RowCount,FieldIndex : integer;
begin
  EmployeeQuery.Close();
  EmployeeQuery.SQL.Clear();
  EmployeeQuery.SQL.Add('select * from employee');
  EmployeeQuery.Open();

  // set the JSONWriter formatting to indented
  AResponse.Body.JSONWriter.Formatting := TJSONFormatting.Indented;

  // using JSONWriter to craft custom JSON return for all rows
  AResponse.Body.JSONWriter.WriteStartArray;
  for RowCount := 0 to EmployeeQuery.RecordCount-1 do begin
    AResponse.Body.JSONWriter.WriteStartObject;

    for FieldIndex := 0 to EmployeeQuery.FieldCount-1 do begin
      Aresponse.Body.JSONWriter.WritePropertyName(
		EmployeeQuery.Fields[FieldIndex].FieldName);
 	Aresponse.Body.JSONWriter.WriteValue(
	  EmployeeQuery.FieldByName(
	    EmployeeQuery.Fields[FieldIndex].FieldName).AsString)
    end;
    AResponse.Body.JSONWriter.WriteEndObject;
    EmployeeQuery.Next
  end;
  AResponse.Body.JSONWriter.WriteEndArray;
end;

procedure TFireDACResource1.GetItem(const AContext: TEndpointContext; const ARequest: TEndpointRequest; const AResponse: TEndpointResponse);
var
  FieldIndex : integer;
  LItem : string;
begin
  LItem := ARequest.Params.Values['item'];
  EmployeeQuery.Close();
  EmployeeQuery.SQL.Clear();
  EmployeeQuery.SQL.Add('select * from employee where EMP_NO = '+LItem);
  EmployeeQuery.Open();

  // set the JSONWriter formatting to indented
  AResponse.Body.JSONWriter.Formatting := TJSONFormatting.Indented;

  // using JSONWriter to craft custom JSON return for the selected employee row
  AResponse.Body.JSONWriter.WriteStartObject;
  for FieldIndex := 0 to EmployeeQuery.FieldCount-1 do begin
    Aresponse.Body.JSONWriter.WritePropertyName(
	EmployeeQuery.Fields[FieldIndex].FieldName);
    Aresponse.Body.JSONWriter.WriteValue(
	EmployeeQuery.FieldByName(
	  EmployeeQuery.Fields[FieldIndex].FieldName).AsString)
  end;
end;

procedure Register;
begin
  RegisterResource(TypeInfo(TFireDACResource1));
end;

//---------------------------------------------------------------------------
#pragma hdrstop

#include "DMUnit.h"
#include <memory>
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma classgroup "System.Classes.TPersistent"
#pragma resource "*.dfm"
//---------------------------------------------------------------------------
__fastcall TFireDACResource1::TFireDACResource1(TComponent* Owner)
	: TDataModule(Owner)
{
}

void TFireDACResource1::Get(TEndpointContext* Acontext,
		TEndpointRequest* Arequest, TEndpointResponse* AResponse)
{
	EmployeeQuery->Close();
	EmployeeQuery->SQL->Clear();
	EmployeeQuery->SQL->Add("select * from employee");
	EmployeeQuery->Open();
	// set the JSONWriter formatting to indented
	AResponse->Body->JSONWriter->Formatting = TJsonFormatting::Indented;
	// using JSONWriter to craft custom JSON return for all rows
	AResponse->Body->JSONWriter->WriteStartArray();
	for (int RowCount = 0; RowCount < EmployeeQuery->RecordCount; RowCount++) {
		AResponse->Body->JSONWriter->WriteStartObject();
		for (int FieldIndex = 0; FieldIndex < EmployeeQuery->FieldCount;
				 FieldIndex++) {
			AResponse->Body->JSONWriter->WritePropertyName(
				EmployeeQuery->Fields->Fields[FieldIndex]->FieldName);
			AResponse->Body->JSONWriter->WriteValue(
				EmployeeQuery->FieldByName(
					EmployeeQuery->Fields->Fields[FieldIndex]
						->FieldName)->AsString);
	}
	AResponse->Body->JSONWriter->WriteEndObject();
	EmployeeQuery->Next();
  }
  AResponse->Body->JSONWriter->WriteEndArray();
}

void TFireDACResource1::GetItem(TEndpointContext* Acontext,
		TEndpointRequest* ARequest, TEndpointResponse* AResponse)
{
	String item;
	item = ARequest->Params->Values["item"];
	EmployeeQuery->Close();
	EmployeeQuery->SQL->Clear();
	EmployeeQuery->SQL->Add("select * from employee where EMP_NO = "+item);
	EmployeeQuery->Open();
	// set the JSONWriter formatting to indented
	AResponse->Body->JSONWriter->Formatting = TJsonFormatting::Indented;
	// using JSONWriter to craft custom JSON for the selected employee row
	for (int FieldIndex = 0; FieldIndex < EmployeeQuery->FieldCount;
			 FieldIndex++) {
	  AResponse->Body->JSONWriter->WritePropertyName(
		EmployeeQuery->Fields->Fields[FieldIndex]->FieldName);
	  AResponse->Body->JSONWriter->WriteValue(
		EmployeeQuery->FieldByName(
			EmployeeQuery->Fields->Fields[FieldIndex]
				->FieldName)->AsString);
	}
}

static void Register()
{
       	std::auto_ptr<TEMSResourceAttributes> attributes(
			new TEMSResourceAttributes());
       	attributes->ResourceName = "FireDAC";
       	attributes->ResourceSuffix["GetItem"] = "{item}";
       	RegisterResource(__typeinfo(TFireDACResource1), attributes.release());
}

#pragma startup Register 32