{*******************************************************}
{       軟件名稱: --通用--                              }
{       單元名稱: uDOADataAccess.pas                    }
{       中文名稱: DOA數(shù)據(jù)訪問類                         }
{       單元描述: Oracle數(shù)據(jù)訪問類                      }
{       創(chuàng)    建: SamonHua                              }
{       創(chuàng)建日期: 2007-12-18                            }
{       修    改: 參見VSS記錄                           }
{       版權所有 (C)2002-2007 深圳壹平臺信息技術有限公司}
{*******************************************************}
unit uDOADataAccess;

interface

uses
  SysUtils, Classes, Variants, DB, DBClient, OracleData, Oracle, Provider,
  uDataCopy, uCommon, uDataAccess;

type
  TDOADataAccess = class(TDataAccess)
  private
    FOracleSession: TOracleSession;
    FOracleQuery: TOracleDataSet;
    FOracleProc: TOracleDataSet;
    FOracleExecQuery: TOracleDataSet;
    FOracleUpdateQuery: TOracleDataSet;
    function GetOracleSession: TOracleSession;
    function GetOracleQuery: TOracleDataSet;
    function GetOracleProc: TOracleDataSet;
    function GetOracleExecQuery: TOracleDataSet;
    function GetOracleUpdateQuery: TOracleDataSet;
    property OracleSession: TOracleSession read GetOracleSession;
    property OracleQuery: TOracleDataSet read GetOracleQuery;
    property OracleProc: TOracleDataSet read GetOracleProc;
    property OracleExecQuery: TOracleDataSet read GetOracleExecQuery;
    property OracleUpdateQuery: TOracleDataSet read GetOracleUpdateQuery;
  protected
    function GetQueryDataSet: TDataSet; override;
    function GetUpdateDataSet: TDataSet; override;
    function DoQueryData(ASQL: string): OleVariant; override;
  public
    function GetID(const TableName: string): string; override;
    function UpdateData(Data: OleVariant; const TableNameOrSQL, KeyFields: string): Boolean; override;
    function ExcuteSQL(const ASQL: string): Boolean; override;
    function GetBlobContent(const TableName, KeyFieldName, KeyFieldValue, BlobFieldName: string;
      BlobFieldContent: TStream): boolean; override;
    function UpdateBlobContent(const TableName, KeyFieldName, KeyFieldValue, BlobFieldName: string;
      BlobFieldContent: TStream): boolean; override;
    function BeginTrans: integer; override;
    procedure CommitTrans; override;
    procedure RollbackTrans; override;
    function InTransaction: boolean; override;
  end;

implementation

{ TDOADataAccess }

function TDOADataAccess.BeginTrans: integer;
begin
  result := 0;
end;

procedure TDOADataAccess.CommitTrans;
begin
  inherited;
  OracleSession.Commit;
end;

function TDOADataAccess.DoQueryData(ASQL: string): OleVariant;
begin
  with OracleQuery do
  begin
    SQL.Text := ASQL;
    try
      Open;
      Result := QueryDataProvider.Data;
      Close;
    except
      raise;
    end;
  end;
end;

function TDOADataAccess.ExcuteSQL(const ASQL: string): Boolean;
begin
  Result := false;
  with OracleExecQuery do
  try
    SQL.Text := ASQL;
    ExecSQL;
    Result := True;
    if Session.InTransaction then
      Session.Commit;
  except
    on e: exception do
    begin
      Session.Rollback;
      raise Exception.CreateFmt('執(zhí)行SQL出錯，錯誤：%s', [e.Message]);
    end;
  end;
end;

function TDOADataAccess.GetID(const TableName: string): string;
begin
  with OracleProc do
  try
    SQL.Clear;
    SQL.Add('begin');
    SQL.Add('Get_id(:TABLENAME,:ID,:KeyField) ;');
    SQL.Add('end;');

    DeleteVariables;
    DeclareVariable('TABLENAME', otString);
    DeclareVariable('KeyField', otString);
    DeclareVariable('ID', otString);

    SetVariable('TABLENAME', trim(TableName));
    SetVariable('KeyField', ' ');
    SetVariable('ID', trim('0'));

    ExecSQL;
    Result := GetVariable('ID');
  except
    on e: Exception do
      raise Exception.CreateFmt('生成自動主鍵值失敗，錯誤：%s', [TableName]);
  end;
end;

function TDOADataAccess.GetOracleExecQuery: TOracleDataSet;
begin
  if not Assigned(FOracleExecQuery) then
  begin
    FOracleExecQuery := TOracleDataSet.Create(Self);
    FOracleExecQuery.Session := OracleSession;
  end;
  Result := FOracleExecQuery;
end;

function TDOADataAccess.GetOracleProc: TOracleDataSet;
begin
  if not Assigned(FOracleProc) then
  begin
    FOracleProc := TOracleDataSet.Create(Self);
    FOracleProc.Session := OracleSession;
  end;
  Result := FOracleProc;
end;

function TDOADataAccess.GetOracleQuery: TOracleDataSet;
begin
  if not Assigned(FOracleQuery) then
  begin
    FOracleQuery := TOracleDataSet.Create(Self);
    FOracleQuery.Session := OracleSession;
  end;
  Result := FOracleQuery;
end;

function TDOADataAccess.GetOracleSession: TOracleSession;
var
  strConnectionString: string;
begin
  if not Assigned(FOracleSession) then
  begin
    if DBName = '' then
      DBName := DBHost;
    FOracleSession := TOracleSession.Create(Self);
    FOracleSession.BytesPerCharacter := bcAutoDetect;
    FOracleSession.Pooling := spInternal;
    FOracleSession.LogonDatabase := DBName;
    FOracleSession.LogonUsername := DBUserName;
    FOracleSession.LogonPassword := DBPassword;
    FOracleSession.Connected := true;
  end;
  Result := FOracleSession;
end;

function TDOADataAccess.GetOracleUpdateQuery: TOracleDataSet;
begin
  if not Assigned(FOracleUpdateQuery) then
  begin
    FOracleUpdateQuery := TOracleDataSet.Create(Self);
    FOracleUpdateQuery.Session := OracleSession;
  end;
  Result := FOracleUpdateQuery;
end;

function TDOADataAccess.GetQueryDataSet: TDataSet;
begin
  Result := OracleQuery;
end;

function TDOADataAccess.GetUpdateDataSet: TDataSet;
begin
  result := OracleUpdateQuery;
end;

function TDOADataAccess.InTransaction: boolean;
begin
  Result := OracleSession.InTransaction;
end;

function TDOADataAccess.GetBlobContent(const TableName, KeyFieldName,
  KeyFieldValue, BlobFieldName: string;
  BlobFieldContent: TStream): boolean;
var
  tmpCDS: TClientDataSet;
  strSQL, strKeyFieldList: string;
  i: integer;
begin
  Result := false;
  tmpCDS := TClientDataSet.Create(nil);
  try
    if Pos(';', KeyFieldName) > 0 then
    begin
      strKeyFieldList := '1 = 1';
      for i := 0 to SubStrCount(KeyFieldName) do
        strKeyFieldList := strKeyFieldList
          + Format(' and %s = %s', [CopySubStr(KeyFieldName, i), QuotedStr(CopySubStr(KeyFieldValue, i))]);
    end
    else
      strKeyFieldList := Format('%s = %s', [KeyFieldName, QuotedStr(KeyFieldValue)]);
    strSQL := Format('select %s from %s where %s',
      [BlobFieldName, TableName, strKeyFieldList]);
    tmpCDS.Data := GetData(strSQL);
    if tmpCDS.IsEmpty then
      exit;
    BlobFieldContent.Position := 0;
    TBlobField(tmpCDS.Fields[0]).SaveToStream(BlobFieldContent);
    Result := True;
  finally
    tmpCDS.Free;
  end;
end;

procedure TDOADataAccess.RollbackTrans;
begin
  inherited;
  OracleSession.Rollback;
end;

function TDOADataAccess.UpdateBlobContent(const TableName, KeyFieldName,
  KeyFieldValue, BlobFieldName: string;
  BlobFieldContent: TStream): boolean;
var
  tmpCDS: TClientDataSet;
  strSQL, strKeyFieldList: string;
  i: integer;
begin
  Result := false;
  tmpCDS := TClientDataSet.Create(nil);
  try
    if Pos(';', KeyFieldName) > 0 then
    begin
      strKeyFieldList := '1 = 1';
      for i := 0 to SubStrCount(KeyFieldName) do
        strKeyFieldList := strKeyFieldList
          + Format(' and %s = %s', [CopySubStr(KeyFieldName, i), QuotedStr(CopySubStr(KeyFieldValue, i))]);
    end
    else
      strKeyFieldList := Format('%s = %s', [KeyFieldName, QuotedStr(KeyFieldValue)]);
    strSQL := Format('select %s from %s where %s',
      [BlobFieldName, TableName, strKeyFieldList]);
    tmpCDS.Data := GetData(strSQL);
    if tmpCDS.IsEmpty then
      exit;
    BlobFieldContent.Position := 0;
    tmpCDS.Edit;
    TBlobField(tmpCDS.Fields[0]).LoadFromStream(BlobFieldContent);
    tmpCDS.Post;
    Result := UpdateData(tmpCDS.Delta, TableName, KeyFieldName);
  finally
    tmpCDS.Free;
  end;
end;

function TDOADataAccess.UpdateData(Data: OleVariant; const TableNameOrSQL,
  KeyFields: string): Boolean;
var
  strTableName, strKeyFields: string;
  i: integer;
  tmpField: TField;
  blnAutoTrans: boolean;
  setProviderFlags: TProviderFlags;
begin
  result := false;
  if VarIsNull(Data) or VarIsEmpty(Data) then
  begin
    Result := True;
    exit;
  end;
  if not VarIsArray(Data) then
    raise Exception.Create('傳入的數(shù)據(jù)集數(shù)據(jù)格式非法');
  blnAutoTrans := not InTransaction;
  if blnAutoTrans then
    BeginTrans;
  try
    OracleUpdateQuery.Close;
    //UpdateDataProvider.DataSet := nil;//避免賦Data時出錯
    UpdateDataProvider.UpdateMode := upWhereAll;
    //簡單方式獲取表名
    if CompareText(Copy(Trim(TableNameOrSQL), 1, 6), 'select') = 0 then
    begin
      OracleUpdateQuery.SQL.Text := TableNameOrSQL;
      strTableName := Trim(Copy(TableNameOrSQL, pos('FROM', UpperCase(TableNameOrSQL)) + 4, MaxInt));
      if Pos(' ', strTableName) > 0 then
        strTableName := Copy(strTableName, 1, pos(' ', strTableName) - 1);
    end
    //建議采用傳表名的方式
    else
    begin
      OracleUpdateQuery.SQL.Text := Format('select * from %s where 1 = 2', [TableNameOrSQL]);
      strTableName := TableNameOrSQL;
    end;
    OracleUpdateQuery.Open;
    if not CheckSimpleTableUpdate(UpdateDataCDS, Data, KeyFields) then
      UpdateDataCDS.Data := Data;
    if UpdateDataCDS.ChangeCount = 0 then
    begin
      Result := True;
      exit;
    end;
    //設置字段更新方式
    if KeyFields <> '' then
    begin
      strKeyFields := StringReplace(KeyFields, ',', ';', [rfReplaceAll]);
      for i := 0 to UpdateDataCDS.FieldCount - 1 do
      begin
        //pfInUpdate, pfInWhere, pfInKey, pfHidden
        if ParamExists(strKeyFields, UpdateDataCDS.Fields[i].FieldName, true) then
          UpdateDataCDS.Fields[i].ProviderFlags := [pfInUpdate, pfInWhere, pfInKey]//OracleUpdateQuery.Fields[i].ProviderFlags + [pfInKey]
        else
          UpdateDataCDS.Fields[i].ProviderFlags := [pfInUpdate];//OracleUpdateQuery.Fields[i].ProviderFlags - [pfInWhere, pfInKey];
        OracleUpdateQuery.FieldByName(UpdateDataCDS.Fields[i].FieldName).ProviderFlags := UpdateDataCDS.Fields[i].ProviderFlags;
      end;
    end;
    try
      UpdateDataProvider.DataSet := OracleUpdateQuery;
      UpdateDataProvider.Options := UpdateDataProvider.Options +
        [poAllowCommandText, poCascadeUpdates];
      if KeyFields <> '' then
        UpdateDataProvider.UpdateMode := upWhereKeyOnly;
      result := UpdateDataCDS.ApplyUpdates(0) = 0;//更新數(shù)據(jù)至數(shù)據(jù)庫中
      if blnAutoTrans then
        CommitTrans;
    except
      on e: Exception do
      begin
        if blnAutoTrans then
          RollbackTrans;
        raise Exception.CreateFmt('數(shù)據(jù)集 %s 更新數(shù)據(jù)失敗，錯誤：%s', [TableNameOrSQL, e.Message]);
      end;
    end;
  finally
    OracleUpdateQuery.Close;
    UpdateDataCDS.Close;
  end;
end;

end.