When converting a 2-tier project to 3-tier, you often have to move the SQL queries used in the 2-tier app to the 3-tier middleware server, which increases the workload and makes the project on the server huge.
In this case, there is a way to send the SQL statement itself as a String parameter without moving the query statement used in the second-tier project to the server.
In this case, the middleware server receives the sql string, processes it, and makes the result visible to the client app.
Depending on the SQL query statement, the column names retrieved from the database or the field names replaced with AS in the SQL query statement are all different, so you can output the field names like below.
for i := 0 to FDQueryI.FieldCount - 1 do
JsubObj.AddPair( FDQueryI.Fields[ i ].FullName, FDQueryI.Fields[ i ].AsString );
In some cases, you may know the field names of the queried results in the database, but FireDAC can show the field names of the queried results in the same way as the sample source, so you can use it.
The sample source outputs the result as Json, so you can utilize Rad server or Datasnap Rest method.
function TRTestResource1.QueryText_sql( sqlText : string ) : String;
var
JTopObj, JsubObj : TJSONObject;
JArr : TJSONArray;
JPair : TJSONPair;
i : integer;
begin
JTopObj := TJSONObject.Create;
try
FDConnection1.Open;
try
FDQueryI.Close;
FDQueryI.SQL.Clear;
FDQueryI.SQL.Add( sqlText );
FDQueryI.Open;
FDQueryI.First;
JArr := TJSONArray.Create;
while Not FDQueryI.EOF do
begin
JsubObj := TJSONObject.Create;
for i := 0 to FDQueryI.FieldCount - 1 do
JsubObj.AddPair( FDQueryI.Fields[ i ].FullName, FDQueryI.Fields[ i ].AsString ); // 조회 결과값의 필드명과 데이터를 같이 출력 하는 방법
JArr.AddElement( JsubObj );
FDQueryI.Next;
end;
JPair := TJSONPair.Create( 'Items', JArr );
JTopObj.AddPair( 'Count', TJSONNumber.Create( FDQueryI.RecordCount ) );
JTopObj.AddPair( JPair );
except
on e: Exception do begin
result := 'Error';
Exit;
end;
end;
finally
FDConnection1.Close;
result := JTopObj.ToString; // 결과값 전달.
JTopObj.Free;
end;
end;
