DataSnap 多层返回数据集分析FireDAC JSON

时间:2022-08-27 05:40:03

采用服务器返回数据,一种是返回字符串数据例如JSON,跨平台跨语言,任何语言调用都支持兼容,类似WEBService。

第二种是紧密结合c++builder语言,传输DataSet,可以是ClientDataSet,也可以是FDMemTable,或TDataSet,这类好处是DataSet本身包含了很多属性,记录集的状态Insert/Modify/Delete,在服务端可以调用

不同的方法进行数据处理,客户端也只需要把dataset对象赋值就展示到dbgrid里了。

序列化。

FDMemTable1有saveToFile(sfJson),虽然是json格式,但不能跨语言交流,只能fdMem.LoadFromFile才可解析。所以用这个json字符串是不通用的,只能RAD delphi/c++使用。

http://localhost:8083/datasnap/rest/TServerMethods1/GetDepartmentNamesJSON

一、跨平台纯字符串

Tokyo 10.2.2有了TFDBatchMoveJSONWriter

https://community.embarcadero.com/blogs/entry/dataset-mapping-to-json-for-javascript-client-support-in-rad-studio-10-2-2

对返回的数据增删改查。对应的方法就是Add/Delete/Update/Query,客户端调用此方法就OK了。

数据集转换依赖于TDBXReader、TDBXCommand、SQLConnection1。不能随意转换dataset为json。有个第三方的库可以转。

Serever

String GetPersonAll()

{

return "";字符串形式的JSON或XML格式

aReader:=aCommand.ExecuteQuery;
Result:=TDBXJSONTools.TableToJSON(aReader,10,True).ToString;

}

Client

String DataSTR=srv->GetPersonAll();

对字符串解析JSON或XML,以DataSet展示就可以。

function TServerMethods1.GetData(SQL: String): String;
var
aCommand: TDBXCommand;
aReader: TDBXReader;
begin
Result := '';
aCommand := SQLConnection1.DBXConnection.CreateCommand; // 指定 SQLConnection1 连接数据库
try
aCommand.Text := SQL; // 指定SQL语句
aReader := aCommand.ExecuteQuery;
Result := TDBXJSONTools.TableToJSON(aReader, , True).ToString;
finally
aCommand.Free;
end;
end;

用第三方库SuperJson也可以。

二、DataSet

http://blog.csdn.net/ddqqyy/article/details/6982164 利用TDBXDataSetReader实例化,传输的是TDBXReader

http://blog.csdn.net/ddqqyy/article/details/6174525 讲的是返回dataset ClientDataSet1.Delta,TDataSetProvider,TSqlServerMethod,还用到了OleVariant

#include <Data.DBXCDSReaders.hpp>

static void __fastcall CopyReaderToClientDataSet(Data::Dbxcommon::TDBXReader* Reader, Datasnap::Dbclient::TClientDataSet* Dataset);
 static Datasnap::Dbclient::TClientDataSet* __fastcall ToClientDataSet(TComponent* AOwner, Data::Dbxcommon::TDBXReader* Reader, bool AOwnsInstance);

C++中这些返回指针,怎么释放一直没想清楚。

三、返回ClientDataSet1的OleVariant

int TServerMethods1::GetTableDataSet(String atableName, OleVariant adata)
{
   ClientDataSet1->Open();
   adata = this->ClientDataSet1->Data;
   return this->ClientDataSet1->RecordCount;

}

DataSetProvider1也有Data属性。

OleVariant Data

DataSetProvider1->Data;

只能用ClientDataSet1,其他的数据集都没有data属性,有data属性的FDQuery类型也不是OleVariant 。

ClientDataSet:OleVariant Data

FDQuery:_di_IFDDataSetReference Data

四、返回TDBXReader

TDBXReader  可以自己释放内存,所以不必担心内心释放泄露的问题了。

#include "Data.DBXCDSReaders.hpp"

TDBXReader* TServerMethods1::GetDSReader(String asql)
{
TDBXCommand *cmd;
TDBXReader *reader;
TClientDataSet *cds;
cmd = SQLConnection1.DBXConnection.CreateCommand;
cmd->Text = asql;
reader = cmd->ExecuteQuery();
return reader; //or
cds = TDBXClientDataSetReader::ToClientDataSet(NULL, reader, true);
return new TDBXClientDataSetReader(cds, true); }

Client

reader= CallProxy.GetDSReader();

TDBXClientDataSetReader::CopyReaderToClientDataSet(reader,cds);
cds->Open();

五、用ClientDataSet

服务端:

fdquery

DataSetProvider1.DataSet=fdquery;

DataSetProvider1.Option.poAllowCommandText=true;

客户端

SQLConnection1

TDSProviderConnection.sqlconnection=SQLConnection1

TDSProviderConnection.serverclassname =tServerMethods1

ClientDataSet.RemoteServer=DSProviderConnection1

ClientDataSet.providername =DataSetProvider1

ClientDataSet.open();

ok!

六、返回TFDJSONDataSets

TFDJSONDataSets可以返回多个数据集。这个是以后的趋势!REST Client,但是这个不能跨语言,客户端只能用RAD的TFDJSONDataSetsReader解析。但依然不能跨语言,C#,java无法解析。
服务的没有释放TFDJSONDataSets总感觉有内存泄漏,调用1000次测试一下。
返回字符串没有内存泄漏。 server
function TServerMethods1.GetJSONData: TFDJSONDataSets;
begin
Result := TFDJSONDataSets.Create;
if not FDMemTable1.Active then
FDMemTable1.LoadFromFile('../../customer.fds')
else
FDMemTable1.Active := False;
TFDJSONDataSetsWriter.ListAdd(Result, FDMemTable1);
end;

Client

var
DSList: TFDJSONDataSets;
begin
FDMemTable1.Close;
DSList := ClientModule1.ServerMethods1Client.GetJSONData;
FDMemTable1.AppendData(
TFDJSONDataSetsReader.GetListValue(DSList, ));
FDMemTable1.Open;
end;

or serverProxy eg

FUnMarshal := TDSRestCommand(FGetDepartmentNamesCommand.Parameters[0].ConnectionHandler).GetJSONUnMarshaler;
 Result := TFDJSONDataSets(FUnMarshal.UnMarshal(FGetDepartmentNamesCommand.Parameters[0].Value.GetJSONValue(True)));

客户端 也可以  FDMemTableDepartments.Data := TFDJSONDataSetsReader.GetListValue(LDataSetList, 0);

七、返回TJSONArray

未实行

八、返回FireDAC TJSONObject

此方法虽然返回了TJSONObject,但依然不能跨语言,C#,java无法解析。

DataSet2JsonObject

DataSetsToJSONObject
从DataSet到TJsonObject
http://community.embarcadero.com/blogs/entry/cbuilder-xe6-multitier-database-app-with-firedac-json-reflection-40330
TJSONObject * TServerMethods1::GetDepartmentEmployeesJSON     ( System::UnicodeString AID )
{ FDQueryDepartmentEmployees->Active = false;
FDQueryDepartment->Active = false;
FDQueryDepartment->Params->operator[ ]( )->Value = AID;
FDQueryDepartmentEmployees->Params->operator[ ]( )->Value = AID;
// Create dataset list
TFDJSONDataSets * ds = new TFDJSONDataSets( );
// Add departments dataset
TFDJSONDataSetsWriter::ListAdd( ds, sDepartment, FDQueryDepartment );
// Add employees dataset
TFDJSONDataSetsWriter::ListAdd( ds, sEmployees,
FDQueryDepartmentEmployees );
TJSONObject * obj = new TJSONObject( );
TFDJSONInterceptor::DataSetsToJSONObject( ds, obj );
return obj;
}

服务的没有释放TFDJSONDataSets总感觉有内存泄漏,调用1000次测试一下。
返回字符串没有内存泄漏。

TFDJSONDataSets

C++Builder
http://blogs.embarcadero.com/pawelglowacki/2014/06/04/40330/ Delphi
http://blogs.embarcadero.com/fernandorizzato/index.php/2014/07/21/multi-tier-com-delphi-xe6-e-firedac-json-reflection/ http://www.cnblogs.com/hnxxcxg/p/4007876.html
http://www.cnblogs.com/hnxxcxg/p/4008789.html
http://www.kzx123.com/?p=105
http://blog.marcocantu.com/blog/delphi_xe5_update2_datasnap_firedac.html
D:\Users\Public\Documents\Embarcadero\Studio\15.0\Samples\Object Pascal\DataSnap\FireDACJSONReflect  TFDJSONDataSets *dset;
 TFDJSONDataSetsReader * dsread;
 TFDJSONDataSetsWriter::ListAdd(dset, FDMemTable1);//FDQuery1 TFDJSONDataSets 是DataSet的集合,数据集的集合,可以持有多个DataSet。
FDQuery1,FDMemTable1其实也可以有多个数据集,也算是数据集的集合,取下一个数据集FDQuery1.NextRecordSet

FireDAC默认是不支持多结果集返回的, 需要手动设置 FetchOptions.AutoClose := False;

下面sql返回2个数据集。
FDQuery1.SQL.Text := 'select * from orders; select * from customers'; Data.FireDACJSONReflect.hpp
返回值可以是 TFDJSONDataSets,TJSONObject*,TFDJSONDeltas
通过 TFDJSONInterceptor::DataSetsToJSONObject()把TFDJSONDataSets转为TFDJSONDataSets。
 static bool __fastcall DataSetsToJSONObject(TFDJSONDataSetsBase* const ADataSets, System::Json::TJSONObject* const AJSONObject);
static bool __fastcall JSONObjectToDataSets(System::Json::TJSONObject* const AJSONObject, TFDJSONDataSetsBase* const ADataSets);
static int __fastcall ListApplyUpdates(TFDJSONDeltas* const ADeltaList, const System::UnicodeString AKey, Firedac::Comp::Client::TFDCustomCommand* const ASelectCommand, TFDJSONErrors* const AErrors = (TFDJSONErrors*)(0x0))/* overload */;
static Firedac::Comp::Client::TFDAdaptedDataSet* __fastcall GetListValueByName(TFDJSONDataSets* const ADataList, const System::UnicodeString AName);
void __fastcall TForm2::FormCreate(TObject *Sender)
{ //Server dataSet > JSONObj
TFDJSONDataSets *dss;
TFDJSONDataSetsWriter::ListAdd(dss, FDMemTable1);
TFDJSONDataSetsWriter::ListAdd(dss, "employ", FDMemTable1); TJSONObject *jsobj = new TJSONObject();
TFDJSONInterceptor::DataSetsToJSONObject(dss, jsobj); //Client JSONObject > dataSet
std::auto_ptr<TFDJSONDataSets>LDataSets(new TFDJSONDataSets());
TFDJSONInterceptor::JSONObjectToDataSets(jsobj, LDataSets.get());
FDMemTable1->Active = false;
TFDAdaptedDataSet * LDataSet = TFDJSONDataSetsReader::GetListValue(LDataSets.get(), );
FDMemTable1->AppendData(*LDataSet); // client updateData
TFDJSONDeltas *LDeltas;
TFDJSONDeltasWriter::ListAdd(LDeltas, "a", FDMemTable1);
TFDJSONInterceptor::DataSetsToJSONObject(LDeltas, jsobj);
// dm->ServerMethods1Client->ApplyChangesDepartmentEmployeesJSON(LJSONObject); //Server updata DataBase Server
TFDJSONInterceptor::JSONObjectToDataSets(jsobj, LDeltas);
TFDJSONErrors *errs;
TFDJSONDeltasApplyUpdates::ListApplyUpdates(LDeltas, "a", FDQuery1->Command, errs) }
更新还没实验好
  TFDAdaptedDataSet * LDataSet =  TFDJSONDataSetsReader::GetListValueByName(LDataSets.get(), sDepartment);
  // Update UI
  FDMemTableDepartment->Active = False;
  FDMemTableDepartment->AppendData(*LDataSet); fdstoreproc 执行返回数据
FDStoredProc1.Close;
FDStoredProc1.Unprepare;
FDStoredProc1.StoredProcName := 'TServerMethods1.QueryData';
FDStoredProc1.Prepare;
FDStoredProc1.ParamByName('sql').Value := 'select * from MyTable';
FDStoredProc1.open;
FDMemTable1.Close;
FDMemTable1.Data := FDStoredProc1.Data
#include "Data.DBXCommon.hpp"
TDBXCommand *FFindDataSetCommand;

内存释放FreeOnExecute有此函数貌似安全了。

FreeOnExecute registers the object indicated by Value, and frees it the next time the command is executed, closed, or freed.

 返回TDataSet*

TDataSet* __fastcall TServerMethods1Client::FindDataSet(System::UnicodeString sql)
{
if (FFindDataSetCommand == NULL)
{
FFindDataSetCommand = FDBXConnection->CreateCommand();
FFindDataSetCommand->CommandType = TDBXCommandTypes_DSServerMethod;
FFindDataSetCommand->Text = "TServerMethods1.FindDataSet";
FFindDataSetCommand->Prepare();
}
FFindDataSetCommand->Parameters->Parameter[]->Value->SetWideString(sql);
FFindDataSetCommand->ExecuteUpdate();
TDataSet* result = new TCustomSQLDataSet(NULL, FFindDataSetCommand->Parameters->Parameter[]->Value->GetDBXReader(False), True);
result->Open();
if (FInstanceOwner)
FFindDataSetCommand->FreeOnExecute(result);
return result;
}

 九、返回TJSONObject

server code

// Get a Department and all Employees in the department.  Return TJSONObject.
function TServerMethods1.GetDepartmentEmployeesJSON(const AID: string)
  : TJSONObject;
var
  LDataSets: TFDJSONDataSets;
begin
  LDataSets := GetDepartmentEmployees(AID);
  try
    Result := TJSONObject.Create;
    TFDJSONInterceptor.DataSetsToJSONObject(LDataSets, Result)
  finally
    LDataSets.Free;
  end;
end;

client code

TJSONObject* __fastcall TServerMethods1Client::FindDataSetJSObj(System::UnicodeString sql)
{
if (FFindDataSetJSObjCommand == NULL)
{
FFindDataSetJSObjCommand = FDBXConnection->CreateCommand();
FFindDataSetJSObjCommand->CommandType = TDBXCommandTypes_DSServerMethod;
FFindDataSetJSObjCommand->Text = "TServerMethods1.FindDataSetJSObj";
FFindDataSetJSObjCommand->Prepare();
}
FFindDataSetJSObjCommand->Parameters->Parameter[]->Value->SetWideString(sql);
FFindDataSetJSObjCommand->ExecuteUpdate();
TJSONObject* result = (TJSONObject*)FFindDataSetJSObjCommand->Parameters->Parameter[]->Value->GetJSONValue(FInstanceOwner);
return result;
}

返回Stream

Result := FFileDownloadCommand.Parameters[1].Value.GetStream(FInstanceOwner);

update json

// Update department and employees using deltas.  TJSONObject parameter.
procedure TServerMethods1.ApplyChangesDepartmentEmployeesJSON(const AJSONObject
  : TJSONObject);
var
  LDeltas: TFDJSONDeltas;
begin
  LDeltas := TFDJSONDeltas.Create;
  TFDJSONInterceptor.JSONObjectToDataSets(AJSONObject, LDeltas);
  ApplyChangesDepartmentEmployees(LDeltas);
end;

Skip to content
This repository Explore
Features
Enterprise
Pricing flrizzato/FireDACJSONReflectionXE7 FireDACJSONReflectionXE7/ServerMethodsUnit1.pas
Git User on Oct first version contributors
lines ( sloc) 6.751 kB
//
// FireDACJSONReflect demo
// Copyright (c) 1995-2013 Embarcadero Technologies, Inc. // You may only use this software if you are an authorized licensee
// of Delphi, C++Builder or RAD Studio (Embarcadero Products).
// This software is considered a Redistributable as defined under
// the software license agreement that comes with the Embarcadero Products
// and is subject to that software license agreement.
//
unit ServerMethodsUnit1; interface uses System.SysUtils, System.Classes, Datasnap.DSServer, Datasnap.DSAuth,
FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.Param,
FireDAC.Stan.Error, FireDAC.DatS, FireDAC.Phys.Intf, FireDAC.DApt.Intf,
FireDAC.Stan.Async, FireDAC.DApt, FireDAC.UI.Intf, FireDAC.VCLUI.Wait,
FireDAC.Stan.Def, FireDAC.Stan.Pool, FireDAC.Phys, Data.DB,
FireDAC.Comp.Client, FireDAC.Phys.IBBase, FireDAC.Phys.IB, FireDAC.Comp.UI,
FireDAC.Comp.DataSet, Data.FireDACJSONReflect, System.JSON,
FireDAC.Stan.StorageBin, FireDAC.Stan.StorageJSON, FireDAC.Phys.IBDef; type
{$METHODINFO ON}
TServerMethods1 = class(TDataModule)
FDQueryDepartmentEmployees: TFDQuery;
FDQueryDepartment: TFDQuery;
FDQueryDepartmentNames: TFDQuery;
FDGUIxWaitCursor1: TFDGUIxWaitCursor;
FDPhysIBDriverLink1: TFDPhysIBDriverLink;
FDConnectionEMPLOYEE: TFDConnection;
FDStanStorageJSONLink1: TFDStanStorageJSONLink;
FDQueryDepartmentDEPT_NO: TStringField;
FDQueryDepartmentDEPARTMENT: TStringField;
FDQueryDepartmentHEAD_DEPT: TStringField;
FDQueryDepartmentMNGR_NO: TSmallintField;
FDQueryDepartmentBUDGET: TBCDField;
FDQueryDepartmentLOCATION: TStringField;
FDQueryDepartmentPHONE_NO: TStringField;
procedure DataModuleCreate(Sender: TObject);
public
{ Public declarations }
function EchoString(Value: string): string;
function ReverseString(Value: string): string; // Strongly typed methods
function GetDepartmentNames: TFDJSONDataSets;
function GetDepartmentEmployees(const AID: string): TFDJSONDataSets;
procedure ApplyChangesDepartmentEmployees(const ADeltaList: TFDJSONDeltas); // Equivalent TJSONObject methods (C++ compatible)
function GetDepartmentNamesJSON: TJSONObject;
function GetDepartmentEmployeesJSON(const AID: string): TJSONObject;
procedure ApplyChangesDepartmentEmployeesJSON(const AJSONObject
: TJSONObject); function FileDownload(sFileName: string): TStream;
procedure FileUpload(fStream: TStream);
end;
{$METHODINFO OFF} implementation {$R *.dfm} uses System.StrUtils, System.Generics.Collections; const
sDepartment = 'Department';
sEmployees = 'Employees';
sDataDir = 'c:\temp\'; // Get a Department and all Employees in the department. Result TFDJSONDataSets.
function TServerMethods1.GetDepartmentEmployees(const AID: string)
: TFDJSONDataSets;
begin
// Clear active so that query will reexecute
FDQueryDepartmentEmployees.Active := False;
FDQueryDepartment.Active := False;
FDQueryDepartment.Params[].Value := AID;
FDQueryDepartmentEmployees.Params[].Value := AID; // Create dataset list
Result := TFDJSONDataSets.Create;
// Add departments dataset
TFDJSONDataSetsWriter.ListAdd(Result, sDepartment, FDQueryDepartment);
// Add employees dataset
TFDJSONDataSetsWriter.ListAdd(Result, sEmployees, FDQueryDepartmentEmployees);
end; // Get a Department and all Employees in the department. Return TJSONObject.
function TServerMethods1.GetDepartmentEmployeesJSON(const AID: string)
: TJSONObject;
var
LDataSets: TFDJSONDataSets;
begin
LDataSets := GetDepartmentEmployees(AID);
try
Result := TJSONObject.Create;
TFDJSONInterceptor.DataSetsToJSONObject(LDataSets, Result)
finally
LDataSets.Free;
end;
end; // Update department and employees using deltas. TFDJSONDeltas parameter.
procedure TServerMethods1.ApplyChangesDepartmentEmployees(const ADeltaList
: TFDJSONDeltas);
var
LApply: IFDJSONDeltasApplyUpdates;
begin
// Create the apply object
LApply := TFDJSONDeltasApplyUpdates.Create(ADeltaList);
// Apply the department delta
LApply.ApplyUpdates(sDepartment, FDQueryDepartment.Command); if LApply.Errors.Count = then
// If no errors, apply the employee delta
LApply.ApplyUpdates(sEmployees, FDQueryDepartmentEmployees.Command);
if LApply.Errors.Count > then
// Raise an exception if any errors.
raise Exception.Create(LApply.Errors.Strings.Text);
end; // Update department and employees using deltas. TJSONObject parameter.
procedure TServerMethods1.ApplyChangesDepartmentEmployeesJSON(const AJSONObject
: TJSONObject);
var
LDeltas: TFDJSONDeltas;
begin
LDeltas := TFDJSONDeltas.Create;
TFDJSONInterceptor.JSONObjectToDataSets(AJSONObject, LDeltas);
ApplyChangesDepartmentEmployees(LDeltas);
end; procedure TServerMethods1.DataModuleCreate(Sender: TObject);
begin end; // Get all Departments. Result TFDJSONDataSets.
function TServerMethods1.GetDepartmentNames: TFDJSONDataSets;
begin
// Clear active so that query will reexecute
FDQueryDepartmentNames.Active := False; Result := TFDJSONDataSets.Create;
TFDJSONDataSetsWriter.ListAdd(Result, FDQueryDepartmentNames);
end; // Get all Departments. Result TFDJSONDataSets;
function TServerMethods1.GetDepartmentNamesJSON: TJSONObject;
var
LDataSets: TFDJSONDataSets;
begin
LDataSets := GetDepartmentNames;
try
Result := TJSONObject.Create;
TFDJSONInterceptor.DataSetsToJSONObject(LDataSets, Result);
finally
LDataSets.Free;
end;
end; function TServerMethods1.EchoString(Value: string): string;
begin
Result := Value;
end; function TServerMethods1.FileDownload(sFileName: string): TStream;
var
fFileStream: TFileStream;
begin
fFileStream := TFileStream.Create(sDataDir + sFileName, fmOpenRead);
Result := fFileStream;
end; procedure TServerMethods1.FileUpload(fStream: TStream);
var
fMemStream: TMemoryStream;
BytesRead: integer;
sFileName: string;
Buffer: PByte;
const
MaxBufSize = * ;
begin
sFileName := sDatadir + 'file-to-upload-copy.jpg';
DeleteFile(sFileName); if not FileExists(sFileName) then
begin
fMemStream := TMemoryStream.Create;
try
fStream.Seek(, TSeekOrigin.soBeginning);
fStream.Position := ; GetMem(Buffer, MaxBufSize);
repeat
BytesRead := fStream.Read(Buffer^, MaxBufSize);
if BytesRead > then
fMemStream.WriteBuffer(Buffer^, BytesRead);
until BytesRead < MaxBufSize;
fMemStream.Seek(, TSeekOrigin.soBeginning); fMemStream.SaveToFile(sFileName);
finally
fMemStream.Free;
end;
end;
end; function TServerMethods1.ReverseString(Value: string): string;
begin
Result := System.StrUtils.ReverseString(Value);
end; end. Status API Training Shop Blog About Pricing © GitHub, Inc. Terms Privacy Security Contact Help

Delphi DataSet与JSON互转

DataSetConverter4Delphi

https://github.com/ezequieljuliano/DataSetConverter4Delphi

用webmodule返回

https://community.embarcadero.com/blogs/entry/display-json-base64-images-created-in-c-builder-using-ext-js-in-browser

void __fastcall TWebModule_main::WebModule_mainWebActionItem1Action(TObject *Sender,
TWebRequest *Request, TWebResponse *Response, bool &Handled)
function TServerConnMonitorMethodsClient.ReverseString(Value: string): string;
begin
if FReverseStringCommand = nil then
begin
FReverseStringCommand := FDBXConnection.CreateCommand;
FReverseStringCommand.CommandType := TDBXCommandTypes.DSServerMethod;
FReverseStringCommand.Text := 'TServerConnMonitorMethods.ReverseString';
FReverseStringCommand.Prepare;
end;
FReverseStringCommand.Parameters[].Value.SetWideString(Value);
FReverseStringCommand.ExecuteUpdate;
Result := FReverseStringCommand.Parameters[].Value.GetWideString;
end;