dbfunc.pas

/// <author>cxg 2020-9-29</author>

unit dbfunc;

interface

uses
  Classes, uunidacpool, serial, uunidac, ulog, SysUtils;

function query(const params: RawByteString): RawByteString;

function save(const params: RawByteString): RawByteString;

function execsql(const params: RawByteString): RawByteString;

function execsp(const params: RawByteString): RawByteString;

implementation

function query(const params: RawByteString): RawByteString;
var
  pool: tunidacpool;
  dm: Tunidac;
  requ, resp: TSerial;
  accountno, sql: string;
  tablenum, i: integer;
begin
  dm := nil;
  pool := nil;
  requ := TSerial.Create;
  resp := TSerial.Create;
  try
    try
      requ.loadFromRawByteString(params);
      accountno := requ.force('accountno').AsString;
      tablenum := requ.force('tablenum').AsInteger;
      pool := GetDBPool(accountno);
      dm := pool.Lock;
      dm.dsp.DataSet := dm.qry;
      for i := 1 to tablenum do
      begin
        sql := requ.force('sql'+inttostr(i)).AsString ;
        dm.qry.Close;
        dm.qry.sql.Clear;
        dm.qry.sql.Add(sql);
        resp.force('dataset' + inttostr(i)).AsVariant := dm.dsp.Data;
      end;
      dm.dsp.DataSet := nil;
      resp.force('return').AsBoolean := True;
      Result := resp.saveToRawByteString;
    except
      on e: Exception do
      begin
        resp.force('return').AsBoolean := False;
        resp.force('err').AsString := e.Message;
        Result := resp.saveToRawByteString;
        Log.WriteLog('dbfunc.query ' + e.Message);
      end;
    end;
  finally
    pool.unlock(dm);
    requ.Free;
    resp.Free;
  end;
end;

function save(const params: RawByteString): RawByteString;
var
  pool: tunidacpool;
  dm: Tunidac;
  requ, resp: TSerial;
  accountno, tablename, nosavefields: string;
  tablenum, i, err: integer;
  delta: OleVariant;
begin
  pool := nil;
  dm := nil;
  requ := TSerial.Create;
  resp := TSerial.Create;
  try
    try
      requ.loadFromRawByteString(params);
      tablenum := requ.force('tablenum').AsInteger;
      accountno := requ.force('accountno').AsString;
      pool := GetDBPool(accountno);
      dm := pool.Lock;
      dm.dsp.DataSet := dm.qry;
      if not dm.conn.InTransaction then
        dm.conn.StartTransaction; // 开启事务
      for i := 1 to tablenum do
      begin
        tablename := requ.force('tablename' + IntToStr(i)).AsString;
        nosavefields := requ.force('nosavefields' + IntToStr(i)).AsString;  //不要提交的字段 例：字段一,字段二
        dm.qry.Close;
        dm.qry.sql.Clear;
        dm.qry.sql.Add('select * from ' + tablename + ' where 1=2');
        delta := requ.force('delta' + IntToStr(i)).AsVariant;
        dm.dsp.ApplyUpdates(delta, 0, err);
        if err > 0 then
        begin
          dm.conn.Rollback; // 回滚事务
          resp.force('return').AsBoolean := False;
          resp.saveToRawByteString;
          Exit;                      //有一个表提交失败，就要中止
        end;
      end;
      dm.conn.Commit; // 提交事务
      dm.dsp.DataSet := nil;
      resp.force('return').AsBoolean := True;
      resp.saveToRawByteString;
    except
      on e: Exception do
      begin
        resp.force('return').AsBoolean := False;
        resp.force('err').AsString := e.Message;
        resp.saveToRawByteString;
        Log.WriteLog('dbfunc.save ' + e.Message);
      end;
    end;
  finally
    requ.Free;
    resp.Free;
    pool.Unlock(dm);
  end;
end;

function execsql(const params: RawByteString): RawByteString;
var
  requ: TSerial;
  pool: tunidacpool;
  dm: Tunidac;
  accountno, sql: string;
begin
  pool := nil;
  dm := nil;
  requ := TSerial.Create;
  try
    try
      requ.loadFromRawByteString(params);
      accountno := requ.force('accountno').AsString;
      sql := requ.force('sql').AsString;
      pool := GetDBPool(accountno);
      dm := pool.Lock;
      if not dm.conn.InTransaction then
        dm.conn.StartTransaction;  //开启事务
      dm.qry.Close;
      dm.qry.sql.Clear;
      dm.qry.sql.Add(sql);
      dm.qry.Execute;
      dm.conn.Commit;   //提交事务
      requ.clear;
      requ.force('return').AsBoolean := True;
      requ.saveToRawByteString;
    except
      on e: Exception do
      begin
        dm.conn.Rollback; //回滚事务
        requ.clear;
        requ.force('return').AsBoolean := False;
        requ.saveToRawByteString;
        Log.WriteLog('dbfunc.execsql ' + e.Message);
      end;
    end;
  finally
    requ.Free;
    pool.Unlock(dm);
  end;
end;

function execsp(const params: RawByteString): RawByteString;
var
  requ: TSerial;
  pool: tunidacpool;
  dm: Tunidac;
  accountno, spname, lparams: string;
  list: tstringlist;
  i: Integer;
begin
  pool := nil;
  dm := nil;
  requ := TSerial.Create;
  list := TStringList.Create;
  try
    try
      requ.loadFromRawByteString(params);
      accountno := requ.force('accountno').AsString;
      spname := requ.force('spname').AsString;
      lparams := requ.force('params').AsString;
      pool := GetDBPool(accountno);
      dm := pool.Lock;
      dm.dsp.DataSet := dm.sp;
      dm.sp.Close;
      dm.sp.Params.Clear;
      dm.sp.StoredProcName := spname;
      dm.sp.Prepare;
      if lparams > '' then // 不是所有的存储过程都有参数
      begin
        list.Delimiter := ';';
        list.DelimitedText := params;
        for i := 0 to list.count - 1 do
          dm.sp.FindParam(list.Names[i]).AsString := list.Values[list.Names[i]];
      end;
      dm.dsp.DataSet := nil;
      requ.clear;
      requ.force('return').AsBoolean := true;
      requ.force('dataset').AsVariant := dm.dsp.Data;
      requ.saveToRawByteString;
    except
      on e: Exception do
      begin
        requ.force('return').AsBoolean := False;
        requ.force('err').AsString := e.Message;
        requ.saveToRawByteString;
        Log.WriteLog('dbfunc.execsp ' + e.Message);
      end;
    end;
  finally
    requ.Free;
    list.Free;
    pool.Unlock(dm);
  end;
end;  

end.