api.clientdataset.pas

api.clientdataset.pas

unit api.clientdataset;
// clientdataset CRUD
// cxg 2024-12-6

interface

uses
  // mormot------
  mormot.core.variants,
  // system---------
  Classes, SysUtils,
  // my--------
  db.firedac, db.firedacpool,
  keyValue.serialize, sys.global;

type
  Tcds = record
    // 查询
    function select(ctxt: Tcontext): cardinal;
    // 保存
    function save(ctxt: Tcontext): cardinal;
    function execsql(ctxt: Tcontext): cardinal;
  end;

implementation

function Tcds.execsql(ctxt: Tcontext): cardinal;
var
  ser, ser2: TSerialize;
  pool: TDBPool;
  db: TDB;
begin
  ser := TSerialize.Create;
  ser2 := TSerialize.Create;
  try
    try
      ser.unMarshal(ctxt.InContent);
      pool := GetDBPool(ser.asStr['dbid']);
      db := pool.Lock;
      db.execsql(ser.asStr['sql']);
      ser2.asBool['ok'] := true;
      ctxt.OutContent := ser2.marshal3;
    except
      on E: Exception do
      begin
        ser2.asBool['ok'] := false;
        ser2.asStr['message'] := E.Message;
        ctxt.OutContent := ser2.marshal3;
        writelog('api.clientdataset.execsql()' + E.Message);
      end;
    end;
  finally
    pool.Unlock(db);
    ser.free;
    ser2.free;
  end;
end;

function Tcds.save(ctxt: Tcontext): cardinal;
var
  ser, ser2: TSerialize;
  pool: TDBPool;
  db: TDB;
  i, err: Integer;
  needFlds: string;
begin
  ser := TSerialize.Create;
  ser2 := TSerialize.Create;
  try
    try
      ser.unMarshal(ctxt.InContent);
      pool := GetDBPool(ser.asStr['dbid']);
      db := pool.Lock;
      db.startTrans;
      for i := 1 to ser.asByte['count'] do
      begin
        db.qry.Close;
        db.qry.SQL.Clear;
        //有的字段不要保存
        needFlds := db.getNeedFields(ser.asStr['tablename' + i.ToString], ser.asStr['nonfields' + i.ToString]);
        db.qry.SQL.Text := 'select ' + needFlds + ' from ' + ser.asStr['tablename' + i.ToString] + ' where 1=2';
        db.dsp.ApplyUpdates(ser.asVariant['delta' + i.ToString], 0, err);
        if err > 0 then
        begin
          db.rollbackTrans;
          ser2.asBool['ok'] := false;
          ctxt.OutContent := ser2.marshal3;
        end;
      end;
      db.commitTrans;
      ctxt.OutContent := ser2.marshal3;
      Result := 200;
    except
      on E: Exception do
      begin
        db.rollbackTrans;
        ser2.asBool['ok'] := false;
        ser2.asStr['message'] := E.Message;
        ctxt.OutContent := ser2.marshal3;
        writelog('api.clientdataset.save()' + E.Message);
      end;
    end;
  finally
    pool.Unlock(db);
    ser.free;
    ser2.free;
  end;
end;

function Tcds.select(ctxt: Tcontext): cardinal;
var
  ser, ser2: TSerialize;
  pool: TDBPool;
  db: TDB;
  i: Integer;
begin
  ser := TSerialize.Create;
  ser2 := TSerialize.Create;
  try
    try
      ser.unMarshal(ctxt.InContent);
      pool := GetDBPool(ser.asStr['dbid']);
      db := pool.Lock;
      for i := 1 to ser.asByte['count'] do
        ser2.asVariant['ds' + i.ToString] := db.select2(ser.asStr['sql' + i.ToString]);
      ser2.asBool['ok'] := True;
      ctxt.OutContent := ser2.marshal3;
      Result := 200;
    except
      on E: Exception do
      begin
        ser2.asBool['ok'] := false;
        ser2.asStr['message'] := E.Message;
        ctxt.OutContent := ser2.marshal3;
        writelog('api.clientdataset.select()' + E.Message);
      end;
    end;
  finally
    pool.Unlock(db);
    ser.free;
    ser2.free;
  end;
end;

var
  _: Tcds;

initialization

_router.Post('/cds/select', _.select);
_router.Post('/cds/save', _.save);
_router.Post('/cds/execsql', _.execsql);

end.