How to dramatically reduce code conversion work when moving from Tier 2 to Tier 3.

 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;