unit QExport4SQL;

{$I QExport4VerCtrl.inc}

interface

uses
  {$IFDEF VCL16}
    System.Classes,
  {$ELSE}
    Classes,
  {$ENDIF}
  QExport4, QExport4Types, QExport4IniFiles;

type
  TQExportScriptType = (qstAnsi, qstDB2, qstIB, qstMS, qstMy, qstOra, qstPg);

  TGetTableDefinition = procedure(Sender: TObject; var DDL: QEString) of object;

  TQExport4SQL = class(TQExport4FormatTextSQL)
  private
    FCommitAfterScript: Boolean;
    FCreateTable: Boolean;
    FIdentityInsert: Boolean;
    FStatementTerm: char;
    FCommitRecCount: Integer;
    FCommitStatement: QEString;
    FTableName: QEString;
    FColumnString: QEString;
    FCustomScriptType: TQExportScriptType;
    FOpenTran: Boolean;
    FIBDialect: Integer;
    FOnGetTableDefinition: TGetTableDefinition;
    FMultipleInsert: Boolean;
    FIsContinueScript: Boolean;
    FBinaryAsHex: Boolean;

    function GetNullValue: QEString;
    procedure SetNullValue(const Value: QEString);
    function GetFormatValues: Boolean;
    procedure SetFormatValues(const Value: Boolean);

    function FormatIdent(const Ident: QEString): QEString;
    function GetDefaultVersion: Variant;
    function ColumnToIdentString(Column: TQExportColumn): QEString;
    function GetScriptTerminator: string;
    function GetCommitStatement: QEString;
    function GetInsertStatement: QEString;
    function GetMultipleInsertLine: QEString;
    function GetStartTranStatementIfNeed: QEString;
    procedure WriteCreateTableStatement;
    procedure WriteBeginMultipleInsert;
    procedure WriteEndMultipleInsert;
    function GetMultipleInsertSeparator: qeString;
    function GetMultipleInsertBeginOfLine: qeString;
    function GetMultipleInsertEndOfLine: qeString;
  protected
    procedure BeginExport; override;
    function GetColData(ColValue: QEString;
      Column: TQExportColumn): QEString; override;
    function GetDataRow: QEString; override;
    procedure WriteDataRow; override;
    procedure EndExport; override;
    procedure Loaded; override;
    procedure SaveProperties(IniFile: TQIniFile); override;
    procedure LoadProperties(IniFile: TQIniFile); override;
  public
    constructor Create(AOwner: TComponent); override;
  published
    property TableName: QEString read FTableName write FTableName;
    property CommitRecCount: Integer read FCommitRecCount
      write FCommitRecCount default 0;
    property CommitAfterScript: Boolean read FCommitAfterScript
      write FCommitAfterScript default False;
    property CommitStatement: QEString read GetCommitStatement write FCommitStatement;
    property CreateTable: Boolean read FCreateTable
      write FCreateTable default False;
    property IdentityInsert: Boolean read FIdentityInsert
      write FIdentityInsert default False;  // for SQL Server
    property StatementTerm: char read FStatementTerm
      write FStatementTerm default ';';
    property NullValue: QEString read GetNullValue write SetNullValue;
    property FormatValues: Boolean read GetFormatValues
      write SetFormatValues default False;
    property CustomScriptType: TQExportScriptType read FCustomScriptType
      write FCustomScriptType default qstAnsi;
    property IBDialect: Integer read FIBDialect write FIBDialect;
    property MultipleInsert: Boolean read FMultipleInsert
      write FMultipleInsert default False;
    property BinaryAsHex: Boolean read FBinaryAsHex write FBinaryAsHex;

    property OnGetTableDefinition: TGetTableDefinition read FOnGetTableDefinition
      write FOnGetTableDefinition;
  end;

//function QuoteIdent(const Name: QEString; LeftQuote, RightQuote: QEChar): QEString; overload;
//function QuoteIdent(const Name: QEString; Quote: QEChar): QEString; overload;

implementation

uses
  {$IFDEF VCL16}
    System.SysUtils,
    Data.DBConsts,
    Winapi.Windows,
    Data.DB,
    System.StrUtils,
  {$ELSE}
    SysUtils,
    DBConsts,
    {$IFDEF VCL9}
      Windows,
    {$ENDIF}
    DB,
    {$IFDEF VCL6}
      StrUtils,
    {$ENDIF}
  {$ENDIF}
  QExport4Common;

const
  LF = #13#10;
  _NSDlm = '.';

function QuoteIdent(const Name: QEString; LeftQuote, RightQuote: QEChar): QEString; overload;

  function GetQuoted(const Text: QEString; const CheckQuote: Boolean): QEString;
  begin
    Result := LeftQuote + Text + RightQuote;
    if CheckQuote and (Text <> '') then
      if (Text[1] = LeftQuote) and (Text[Length(Text)] = RightQuote) then
        Result := Text;
  end;
  
var
  I, J: Integer;
begin
  Result := '';
  if (Name = '') or (Name[1] = LeftQuote) then
  begin
    Result := Name; //alex c
    Exit;
  end;

  I := 1;
  repeat
    J := PosEx('.', Name, I);
    if J = 0 then
      J := Length(Name) + 1;
    if I < J then
      if Result = '' then
        Result := GetQuoted(Copy(Name, I, J - I), True)
      else
        Result := Result + '.' + GetQuoted(Copy(Name, I, J - I), True);
    I := J + 1;
  until
    I > Length(Name);
end;

function QuoteIdent(const Name: QEString; Quote: QEChar): QEString; overload;
begin
  Result := QuoteIdent(Name, Quote, Quote);
end;

function QEQuotedStr(const S: QEString; Quote: QEChar): QEString;
var
  I: Integer;
begin
  Result := S;
  for I := Length(Result) downto 1 do
    if Result[I] = '''' then Insert('''', Result, I);
  Result := '''' + Result + '''';
end;

function Get_Name(const FullName: QEString; const NSDlm: QEString = '.'): QEString;
var
  I, J: Integer;
begin
  //J := Pos('(', FullName);
  J := -1;
  I := Pos(NSDlm, FullName);
  if ((J <= 0) or (I < J)) and (I > 0) and (I + Length(NSDlm) <= Length(FullName)) then
    Result := Copy(FullName, I + Length(NSDlm), Length(FullName))
  else
    Result := FullName;
end;

function Get_NameSpace(const FullName: QEString; const NSDlm: QEString = '.'): QEString;
var
  I, J: Integer;
begin
  J := Pos('(', FullName);
  I := Pos(NSDlm, FullName);
  if ((J <= 0) or (I < J)) and (I > 0) and (I + Length(NSDlm) <= Length(FullName)) then
    Result := Copy(FullName, 1, I - 1)
  else
    Result := '';
end;

function FormatMyIdent(const Value: QEString; Version: Variant): QEString;
var
  Len: Integer;
begin
  Len := Length(Value);
  if (Value = '') or (Version < 32306) or ((Len > 1) and
    (Value[1] = '`') and (Value[Len] = '`')) then
    Result := Value
  else begin
    SetLength(Result, Len+2);
    Result := '`' + Value + '`';
  end;
end;

function FormatPgIdent(const FullName: QEString; DelCatalogNameSpace: Boolean =
    False): QEString;
var
  NS, Nm: QEString;
begin
  NS := Get_NameSpace(FullName);
  Nm := Get_Name(FullName);
  if DelCatalogNameSpace and (AnsiCompareText(NS, 'pg_catalog') = 0) then
    NS := '';
  {if (Nm <> '') and (Nm[1] in ['a'..'z', 'A'..'Z', '_', '0'..'9']) then
    Nm := '"' + Nm + '"';}
  if (Nm <> '') and (Nm[1] <> '"') then
    Nm := '"' + Nm + '"';
  if NS <> '' then
    if NS[1] <> '"' then
      Result := Format('"%s"' + _NSDlm + '%s', [NS, Nm])
    else
      Result := NS + _NSDlm + Nm
  else
    Result := Nm;
end;

function QExportFieldTypeToPg(FieldType: TFieldType; Size: Integer): QEString;
begin
  case FieldType of
    ftSmallint: Result := 'SMALLINT';
    ftInteger, ftWord, ftAutoInc, ftReference: Result := 'INTEGER';
    ftLargeint: Result := 'BIGINT';
    ftBoolean: Result := 'BOOLEAN';
    ftFloat: Result := 'DOUBLE PRECISION';
    //ftCurrency, ftBCD, ftFMTBcd: Result := 'MONEY'; // depricated and has low scale
    ftCurrency, ftBCD{$IFDEF VCL6}, ftFMTBcd{$ENDIF}: Result := 'NUMERIC(20,4)';
    ftDate: Result := 'DATE';
    ftTime: Result := 'TIME';
    ftDateTime{$IFDEF VCL6}, ftTimeStamp{$ENDIF}: Result := 'TIMESTAMP';
    ftParadoxOle, ftDBaseOle, ftTypedBinary, ftCursor, ftBytes, ftVarBytes,
    ftBlob, ftArray, ftOraBlob, ftDataSet:
      Result := 'BYTEA';
    ftWideString, ftMemo, ftFmtMemo, ftGraphic, ftADT, ftOraClob, ftVariant,
    ftInterface, ftIDispatch, ftGuid:
      Result := 'TEXT';
  else
    if (Size > 0) and (Size < 255) then
      Result := Format('VARCHAR(%d)', [Size])
    else
      Result := 'TEXT';
  end;
  Assert(Result <> '');
end;

function QExportFieldTypeToMy(FieldType: TFieldType; Size: Integer): QEString;
begin
  case FieldType of
    ftInteger, ftWord, ftAutoInc:
      Result := 'INTEGER';
    ftSmallint:
      Result := 'SMALLINT';
    ftLargeint:
      Result := 'BIGINT';
    ftFloat:
      Result := 'DOUBLE';
    ftBoolean:
      Result := 'BOOLEAN';
    ftCurrency, ftBCD{$IFDEF VCL6}, ftFMTBcd{$ENDIF}:
      Result := 'FLOAT(15,2)';
    ftDate:
      Result := 'DATE';
    ftTime:
      Result := 'TIME';
    ftDateTime{$IFDEF VCL6}, ftTimeStamp{$ENDIF}:
      Result := 'DATETIME';
    ftBlob, ftMemo, ftGraphic, ftFmtMemo, ftParadoxOle, ftDBaseOle, ftOraBlob, ftOraClob, ftVariant:
      Result := 'LONGBLOB';
    else
    if (Size > 0) and (Size < 2048) then
        Result := Format('VARCHAR(%d)', [Size])
      else
        Result := 'TEXT';
  end;
end;

function QExportFieldTypeToMS(FieldType: TFieldType; Size: Integer): QEString;
begin
  case FieldType of
    ftBoolean:
      Result := 'CHAR(1)';
    ftInteger, ftWord, ftAutoInc:
      Result := 'INT';
    ftSmallint:
      Result := 'SMALLINT';
    ftLargeInt:
      Result := 'BIGINT';
    ftFloat:
      Result := 'FLOAT';
    ftCurrency, ftBCD{$IFDEF VCL6}, ftFMTBcd{$ENDIF}:
      Result := 'NUMERIC(15,2)';
    ftDate, ftTime{$IFDEF VCL6}, ftDateTime{$ENDIF}:
      Result := 'DATETIME';
    ftOraClob, ftMemo, ftFmtMemo:
      Result := 'TEXT';
    ftOraBlob, ftBlob, ftGraphic:
      Result := 'IMAGE';
    ftGuid:
      Result := Format('NCHAR(%d)', [Size]);
    ftWideString:
      begin
        if Size <= 8000 then
          Result := Format('NVARCHAR(%d)', [Size])
        else
          Result := 'NTEXT';
      end;
    ftBytes:
      Result := Format('BINARY(%d)', [Size]);
    ftVarBytes:
      Result := Format('VARBINARY(%d)', [Size]);
    {$IFDEF VCL6}ftTimeStamp:
      Result := 'TIMESTAMP';{$ENDIF}
    ftVariant:
      Result := 'SQL_VARIANT';
    else
    if (Size > 0) and (Size < 255) then
        Result := Format('VARCHAR(%d)', [Size])
      else
        Result := 'TEXT';
  end;
end;

function QExportFieldTypeToIB(FieldType: TFieldType; Size: Integer; SQLDialect: Integer): QEString;
begin
  case FieldType of
    ftBoolean:
      Result := 'BOOLEAN';
    ftLargeInt, ftInteger,
    ftWord, ftAutoInc:
      Result := 'INTEGER';
    ftSmallint:
      Result := 'SMALLINT';
    ftFloat:
      Result := 'DOUBLE PRECISION';
    ftCurrency, ftBCD{$IFDEF VCL6}, ftFMTBcd{$ENDIF}:
      Result := 'NUMERIC (15,2)';
    ftDate:
      Result := 'DATE';
    ftTime:
      if SQLDialect = 3 then
        Result := 'TIME'
      else
        Result := 'DATE';
    ftDateTime:
      if SQLDialect = 3 then
        Result := 'TIMESTAMP'
      else
        Result := 'DATE';
    ftBlob, ftMemo, ftGraphic, ftFmtMemo, ftParadoxOle, ftDBaseOle,
    ftOraBlob, ftOraClob, ftBytes, ftVarBytes, ftVariant:
      Result := 'BLOB';
  else
    if (Size > 0) and (Size < 255) then
      Result := Format('VARCHAR(%d)', [Size])
    else
      Result := 'BLOB';
  end;
end;

function QExportFieldTypeToOra(FieldType: TFieldType; Size: Integer): QEString;
begin
  case FieldType of
    ftWideString:
    begin
      if (Size > 0) and (Size <= 2000) then
        Result := 'NVARCHAR2(' + IntToStr(Size) + ')'
      else
        Result := 'BLOB';
    end;
    ftSmallint, ftInteger, ftWord, ftAutoInc, ftLargeint, ftFloat, ftCurrency,
    ftBCD{$IFDEF VCL6}, ftFMTBcd{$ENDIF}: Result := 'NUMBER';
    ftBoolean: Result := 'CHAR(1)';
    ftDate: Result := 'DATE';
    ftTime, ftDateTime{$IFDEF VCL6}, ftTimeStamp{$ENDIF}: Result := 'TIMESTAMP';
    ftParadoxOle, ftArray, ftOraBlob, ftOraClob, ftDBaseOle, ftGraphic,
    ftBytes, ftVarBytes, ftBlob, ftTypedBinary, ftFmtMemo: Result := 'BLOB';
    ftMemo, ftVariant: Result := 'CLOB';
  else
    if (Size > 0) and (Size < 255) then
      Result := Format('VARCHAR(%d)', [Size])
    else
      Result := 'BLOB';
  end;
end;

function QExportFieldTypeToDB2(FieldType: TFieldType; Size: Integer): QEString;
begin
  case FieldType of
    ftSmallint: Result := 'SMALLINT';
    ftInteger, ftWord, ftAutoInc: Result := 'INTEGER';
    ftBoolean: Result := 'BOOLEAN';
    ftFloat: Result := 'FLOAT'; {DOUBLE}
    ftCurrency, ftBCD{$IFDEF VCL6}, ftFMTBcd{$ENDIF}: Result := 'NUMERIC(24,4)';    // cr3884
    ftDate: Result := 'DATE';
    ftTime: Result := 'TIME';
    ftDateTime{$IFDEF VCL6}, ftTimeStamp{$ENDIF}: Result := 'TIMESTAMP';
    ftParadoxOle, ftArray, ftOraBlob, ftOraClob, ftDBaseOle, ftGraphic, ftBytes,
    ftVarBytes, ftBlob, ftTypedBinary, ftFmtMemo, ftWideString: Result := 'BLOB';
    ftMemo, ftVariant: Result := 'CLOB';
    ftLargeint: Result := 'BIGINT';
  else
    if (Size > 0) and (Size < 255) then
      Result := Format('VARCHAR(%d)', [Size])
    else
      Result := 'BLOB';
  end;
end;

function QExportFieldTypeToAnsi(FieldType: TFieldType; Size: Integer): QEString;
begin
  case FieldType of
    ftSmallint: Result := 'SMALLINT';
    ftInteger, ftWord, ftAutoInc, ftLargeint: Result := 'INTEGER';
    ftBoolean: Result := 'BOOLEAN';
    ftFloat: Result := 'FLOAT'; 
    ftCurrency, ftBCD{$IFDEF VCL6}, ftFMTBcd{$ENDIF}: Result := 'DOUBLE PRECISION';
    ftDate: Result := 'DATE';
    ftTime: Result := 'TIME';
    ftDateTime{$IFDEF VCL6}, ftTimeStamp{$ENDIF}: Result := 'TIMESTAMP';
    ftParadoxOle, ftArray, ftOraBlob, ftOraClob, ftDBaseOle,
    ftGraphic, ftBytes, ftVarBytes, ftBlob, ftTypedBinary,
    ftFmtMemo, ftMemo, ftVariant: Result := 'TEXT';
  else
    if (Size > 0) and (Size < 255) then
      Result := Format('VARCHAR(%d)', [Size])
    else
      Result := 'TEXT';
  end;
end;

{ TQExport4SQL }

constructor TQExport4SQL.Create(AOwner: TComponent);
begin
  inherited;
  FCommitRecCount := 0;
  FCommitAfterScript := False;
  FCommitStatement := S_Commit;
  FCreateTable := False;
  FIdentityInsert := False;
  FStatementTerm := ';';
  FCustomScriptType := qstAnsi;
  FIBDialect := 3;
  FOpenTran := False;
  FMultipleInsert := False;
  FBinaryAsHex := False;
end;

procedure TQExport4SQL.WriteCreateTableStatement;
var
  CreateTableStatement: qeString;
  i: Integer;
begin
  CreateTableStatement := EmptyStr;
  if FTableName <> EmptyStr then
  begin
    CreateTableStatement := Format(S_CreateTable + '(',
      [FormatIdent(FTableName)]) + CRLF;
    for i := 0 to Columns.Count - 1 do
    begin
      CreateTableStatement := CreateTableStatement + Format('  %s  %s',
        [FormatIdent(Columns[i].Name), ColumnToIdentString(Columns[i])]);
      CreateTableStatement := CreateTableStatement + ',' + CRLF;
    end;
    Delete(CreateTableStatement, Length(CreateTableStatement) - 2, 3);
    CreateTableStatement := CreateTableStatement + ');' + CRLF;
    if FCustomScriptType = qstIB then
      CreateTableStatement := CreateTableStatement + GetCommitStatement + CRLF;
  end;
  if Assigned(FOnGetTableDefinition) then
    FOnGetTableDefinition(Self, CreateTableStatement);
  if CreateTableStatement <> EmptyStr then
  begin
    GetWriter.WriteLn(CreateTableStatement);
    GetWriter.EmptyLine;
  end;
end;

procedure TQExport4SQL.BeginExport;
var
  i: Integer;
begin
  inherited;
  {$IFDEF QE_UNICODE}
  GetWriter.WriteSignature;
  {$ENDIF}
  for i := 0 to Header.Count - 1 do
    GetWriter.WriteLn(Header[i]);

  if (FCustomScriptType in [qstPg, qstMS]) and FCommitAfterScript then
  begin
    GetWriter.WriteLn(GetStartTranStatementIfNeed);
    GetWriter.EmptyLine;
  end;

  if FCreateTable then
    WriteCreateTableStatement;

  if FIdentityInsert and (FTableName <> EmptyStr) and (FCustomScriptType = qstMS) then
  begin
    GetWriter.WriteLn(Format(S_IdentityInsertOn + GetScriptTerminator,
      [FormatIdent(FTableName)]));
    GetWriter.EmptyLine;
  end;

  //**************
  FColumnString := EmptyStr;
  for i := 0 to Columns.Count - 1 do
    FColumnString := FColumnString + FormatIdent(Columns[i].Name) + ',';

  if FColumnString[Length(FColumnString)] = ',' then
    Delete(FColumnString, Length(FColumnString), 1);

  if FMultipleInsert then
    WriteBeginMultipleInsert;
  FIsContinueScript := False;
end;

procedure TQExport4SQL.EndExport;
var
  i: Integer;
begin
  if FIdentityInsert and (FTableName <> EmptyStr) and (FCustomScriptType = qstMS) then
  begin
    GetWriter.WriteLn(Format(S_IdentityInsertOff + GetScriptTerminator,
      [FormatIdent(FTableName)]));
    GetWriter.EmptyLine;
  end;
    
  if FMultipleInsert then
    WriteEndMultipleInsert;

  if FCommitAfterScript and (CommitStatement <> EmptyStr) then
    GetWriter.WriteLn(CommitStatement);
    
  if FOpenTran then
    GetWriter.WriteLn(CommitStatement);

  for i := 0 to Footer.Count - 1 do
    GetWriter.WriteLn(Footer[i]);
  inherited;
end;

function TQExport4SQL.GetColData(ColValue: QEString;
  Column: TQExportColumn): QEString;
var
  Index, p: Integer;
const
  _null = 'null';
begin
  Index := Column.Index;
  if Columns.GetColumnIsNull(Index) then
  begin
    case FCustomScriptType of
      qstDB2, qstIB,
      qstMS, qstMy,
      qstOra, qstPg:
        Result := _null;
    else
      Result := Formats.NullString;
    end;
  end else
  begin
    Result := inherited GetColData(ColValue, Column);
    case Column.ColType of
      ectString, ectUnknown:
        if Column.IsBlob or Column.IsMemo or
          (Column.FieldType in [ftParadoxOle, ftArray, ftOraBlob, ftOraClob,
          ftDBaseOle, ftGraphic, ftBytes, ftVarBytes, ftBlob, ftTypedBinary, ftFmtMemo])
          or ((Column.FieldType = ftWideString) and (Column.Length > 2000))
          then
          begin
            if Column.IsMemo then
              Result := QEQuotedStr(Result, '''')
            else
            case FCustomScriptType of
              qstDB2: Result := BlobValueToDB2(Result);
              qstIB: Result := BlobValueToIB(Result);
              qstMS:
                {$IFDEF VCL10}
                //pav >> for nvarchar do not convert Result
                if Column.FieldType = ftWideMemo then
                  Result := QEQuotedStr(Result, '''')
                else
                {$ENDIF}
                  Result := BlobValueToMS(Result);
              qstMy: Result := BlobValueToMy(Result);
              qstOra: Result := BlobValueToOra(Result, Column.FieldType);
            qstPg: Result := BlobValueToPg(Result);
            else
              Result := QEQuotedStr(Result, '''');
            end
          end
          else
            Result := QEQuotedStr(Result, '''');
      ectDate,
      ectTime,
      ectDateTime:
      begin
        case FCustomScriptType of
//          qstMy, qstMS, qstDB2:
//            Result := QEQuotedStr(FormatDateTime('yyyy-mm-dd hh:mm:ss', Columns.GetColumnData(Index)), '''');
          qstORA:
            Result := 'TO_DATE(' + QEQuotedStr(FormatDateTime('dd.mm.yyyy hh:mm:ss', Columns.GetColumnData(Index)), '''') +
              ',' + QEQuotedStr('dd.mm.yyyy hh24:mi:ss', '''') + ')';
//          qstPg:
//            Result := QEQuotedStr(FormatDateTime('yyyy.mm.dd hh:mm:ss', Columns.GetColumnData(Index)), '''');
        else
          Result := QEQuotedStr(Result, '''');
        end;
      end;
      ectBoolean:
        if (AnsiCompareText(STextTrue, Trim(inherited GetColData(ColValue, Column))) = 0) or
           (AnsiCompareText(Formats.BooleanTrue, Trim(inherited GetColData(ColValue, Column))) = 0)
          then Result := Formats.BooleanTrue
          else Result := Formats.BooleanFalse;

      ectFloat,
      ectNumeric,
      ectCurrency,
      ectInteger,
      ectBigint: begin
        p := Pos(Formats.ThousandSeparator, Result);
        while p > 0 do
        begin
          Delete(Result, p, 1);
          p := Pos(Formats.ThousandSeparator, Result);
        end;
//        if SysUtils.DecimalSeparator <> '.' then
        if Formats.DecimalSeparator <> '.' then
        begin
          p := Pos(Formats.DecimalSeparator, Result);
          while p > 0 do
          begin
            Delete(Result, p, 1);
            Insert('.', Result, p);
            p := Pos(Formats.DecimalSeparator, Result);
          end;
        end;  
      end;
    end;
  end;
  Result := Result + ',';
end;

function TQExport4SQL.GetDataRow: QEString;
begin
  if FMultipleInsert then
    Result := GetMultipleInsertLine
  else
    Result := GetInsertStatement;
end;

procedure TQExport4SQL.WriteBeginMultipleInsert;
var
  SQLLine: qeString;
begin
  if FCustomScriptType = qstOra then
    SQLLine := 'INSERT ALL' + CRLF
  else
  begin
    SQLLine := Format(S_Insert + CRLF + '  (%s)' + CRLF,
      [FormatIdent(FTableName), FColumnString]);
    if  FCustomScriptType in [qstMS, qstMy, qstPg, qstAnsi, qstDB2] then
      SQLLine := SQLLine + S_Values + CRLF;
  end;
  GetWriter.Write(SQLLine);
end;

procedure TQExport4SQL.WriteDataRow;
begin
  GetWriter.Write(GetDataRow);
  if FMultipleInsert then
    Exit;
      
  GetWriter.EmptyLine;
  if (FCommitRecCount > 0) and (RecordCounter mod FCommitRecCount = 0) and
     (CommitStatement <> EmptyStr)  then
  begin
    GetWriter.WriteLn(CommitStatement);
    GetWriter.EmptyLine;
  end;

  if (FCustomScriptType in [qstPg, qstMS]) and FCommitAfterScript and (not FOpenTran) then
  begin
    GetWriter.WriteLn(GetStartTranStatementIfNeed);
    GetWriter.EmptyLine;
  end;
end;

procedure TQExport4SQL.WriteEndMultipleInsert;
begin
  if FCustomScriptType = qstAnsi then
    GetWriter.WriteLn(FStatementTerm)
  else
  begin
    if FCustomScriptType = qstOra then
      GetWriter.Write(CRLF + 'SELECT * FROM DUAL');
    GetWriter.WriteLn(GetScriptTerminator);
  end;
end;

function TQExport4SQL.GetNullValue: QEString;
begin
  Result := Formats.NullString;
end;

procedure TQExport4SQL.SetNullValue(const Value: QEString);
begin
  if Formats.NullString <> Value then
    Formats.NullString := Value;
end;

function TQExport4SQL.GetFormatValues: Boolean;
begin
  Result := Formats.KeepOriginalFormat;
end;

procedure TQExport4SQL.SaveProperties(IniFile: TQIniFile);
begin
  inherited;
  IniFile.WriteBool(S_SQL, S_SQL_CommitAfterScript, FCommitAfterScript);
  IniFile.WriteBool(S_SQL, S_SQL_CreateTable, FCreateTable);
  IniFile.WriteBool(S_SQL, S_SQL_IdentityInsert, FIdentityInsert);
  IniFile.WriteString(S_SQL, S_SQL_StatementTerm, FStatementTerm);
  IniFile.WriteInteger(S_SQL, S_SQL_CommitRecCount, FCommitRecCount);
  IniFile.WriteString(S_SQL, S_SQL_CommitStatement, FCommitStatement);
  IniFile.WriteString(S_SQL, S_SQL_TableName, FTableName);
  IniFile.WriteInteger(S_SQL, S_SQL_CustomScriptType, Integer(FCustomScriptType));
  IniFile.WriteInteger(S_SQL, S_SQL_IBDialect, FIBDialect);
  IniFile.WriteBool(S_SQL, S_SQL_MultipleInsert, FMultipleInsert);
end;

procedure TQExport4SQL.SetFormatValues(const Value: Boolean);
begin
  Formats.KeepOriginalFormat := Value;
end;

function TQExport4SQL.FormatIdent(const Ident: QEString): QEString;
var
  LeftQuote, RightQuote: QEChar;
begin
  LeftQuote := '"';
  RightQuote := '"';
  case FCustomScriptType of
    qstDB2, qstOra:
      Result := QuoteIdent(Ident, LeftQuote, RightQuote);
    qstIB:
      if IBDialect = 3 then
        Result := QuoteIdent(Ident, LeftQuote, RightQuote)
      else
        Result := Ident;
    qstMS:
    begin
      LeftQuote := '[';
      RightQuote := ']';
      Result := QuoteIdent(Ident, LeftQuote, RightQuote);
    end;
    qstMy:
      Result := FormatMyIdent(Ident, GetDefaultVersion);
    qstPg:
      Result := FormatPgIdent(Ident);
  else
    Result := QuoteIdent(Ident, LeftQuote, RightQuote);
  end
end;

function TQExport4SQL.GetDefaultVersion: Variant;
begin
  case FCustomScriptType of
    qstDB2: Result := 8020000;
    qstIB: Result := 2.0;
    qstMS: Result := 9;
    qstMy: Result := 50018;
    qstOra: Result := 9;
    qstPg: Result := 80100;
  else
    Result := 0;
  end;
end;

function TQExport4SQL.ColumnToIdentString(Column: TQExportColumn): QEString;

  function GetFieldType: TFieldType;
  begin
    if Self.ExportSource in [esCustom, esListView, esStringGrid] then
    begin
      case Column.ColType of
        ectString:
        begin
          if Column.IsBlob then
            Result := ftBlob
          else
          if Column.IsMemo then
            Result := ftMemo
          else
            Result := ftString;
        end;
        ectInteger:
          Result := ftInteger;
        {$IFNDEF VCL3}
        ectBigint:
          Result := ftLargeInt;{$ENDIF}
        ectBoolean:
          Result :=  ftBoolean;
        ectFloat:
          Result := ftFloat;
        ectNumeric:
          Result := ftBCD;
        ectCurrency:
          Result := ftCurrency;
        ectDate:
          Result := ftDate;
        ectTime:
          Result := ftTime;
        ectDateTime:
          Result := ftDateTime;
      else
        Result := ftUnknown;
      end;
    end else //if esDataSet or esDBGrid
      Result := Column.FieldType;
  end;

begin
  case FCustomScriptType of
    qstDB2:
      Result := QExportFieldTypeToDB2(GetFieldType, Column.Length);
    qstIB:
      Result := QExportFieldTypeToIB(GetFieldType, Column.Length, 3);
    qstMS:
      Result := QExportFieldTypeToMS(GetFieldType, Column.Length);
    qstMy:
      Result := QExportFieldTypeToMy(GetFieldType, Column.Length);
    qstOra:
      Result := QExportFieldTypeToOra(GetFieldType, Column.Length);
    qstPg:
      Result := QExportFieldTypeToPg(GetFieldType, Column.Length);
    qstAnsi:
      Result := QExportFieldTypeToAnsi(GetFieldType, Column.Length);
  else
    Result := '';
  end;
end;

function TQExport4SQL.GetScriptTerminator: string;
begin
  if FCustomScriptType = qstMS then
    Result := LF + 'GO'
  else
    Result := ';';
end;

function TQExport4SQL.GetCommitStatement: QEString;
begin
  if FCustomScriptType in [qstIB, qstOra] then
    Result := 'COMMIT WORK'
  else
  if FCustomScriptType <> qstAnsi then
    Result := 'COMMIT'
  else
    Result := FCommitStatement;

  if Result[Length(Result)] <> GetScriptTerminator then
    Result := Result + GetScriptTerminator;

  FOpenTran := False;
end;

function TQExport4SQL.GetInsertStatement: QEString;
begin
  Result := Format(S_Insert + CRLF + '  (%s)' + CRLF + S_Values,
    [FormatIdent(FTableName), FColumnString]);
  Result := Result + '(' + inherited GetDataRow;
  Delete(Result, Length(Result), 1);
  if FCustomScriptType = qstAnsi then
    Result := Result + ')' + FStatementTerm
  else
    Result := Result + ')' + GetScriptTerminator;
  Result := Result + CRLF;
end;

function TQExport4SQL.GetMultipleInsertBeginOfLine: qeString;
begin
  case FCustomScriptType of
    qstIB:
      Result := #32#32'SELECT'#32;
    qstMS, qstMy, qstPg, qstAnsi, qstDB2:
      Result := #32#32'(';
  end;
end;

function TQExport4SQL.GetMultipleInsertEndOfLine: qeString;
begin
  case FCustomScriptType of
    qstIB:
      Result := #32'FROM RDB$DATABASE';
    qstMS, qstMy, qstPg, qstAnsi, qstDB2:
      Result := ')';
  end;
end;

function TQExport4SQL.GetMultipleInsertLine: QEString;
var
  Values: qeString;
begin
  if FIsContinueScript then
    Result := GetMultipleInsertSeparator + CRLF;
  Values := inherited GetDataRow;
  Delete(Values, Length(Values), 1);

  if FCustomScriptType = qstOra then
    Result := Result + #32#32'INTO'#32 + FormatIdent(FTableName) + #32'(' +
      FColumnString + ')'#32 + S_Values + #32'(' + Values + ')'
  else
    Result := Result + GetMultipleInsertBeginOfLine + Values +
      GetMultipleInsertEndOfLine;
  FIsContinueScript := True;
end;

function TQExport4SQL.GetMultipleInsertSeparator: qeString;
begin
  case FCustomScriptType of
    qstIB:
      Result := #32'UNION ALL';
    qstMS, qstMy, qstPg, qstAnsi, qstDB2:
      Result := ',';
  end;
end;

function TQExport4SQL.GetStartTranStatementIfNeed: QEString;
begin
  if FCustomScriptType = qstMS then
    Result := 'BEGIN TRANSACTION'
  else if FCustomScriptType = qstPg then
    Result := 'BEGIN'
  else
    Result := '';
  if Result <> '' then
     Result := Result + GetScriptTerminator;

  FOpenTran := True;
end;

procedure TQExport4SQL.Loaded;
begin
  inherited;
  Formats.NullString := S_NULL_STRING;
  Formats.ThousandSeparator := #0;
  Formats.DecimalSeparator := '.';
  Formats.FloatFormat := Formats.IntegerFormat + Formats.DecimalSeparator +
    '########';
  Formats.CurrencyFormat := Formats.FloatFormat;
end;

procedure TQExport4SQL.LoadProperties(IniFile: TQIniFile);
var
  TempTerminator: String;
begin
  inherited;
  FCommitAfterScript := IniFile.ReadBool(S_SQL, S_SQL_CommitAfterScript,
    FCommitAfterScript);
  FCreateTable := IniFile.ReadBool(S_SQL, S_SQL_CreateTable, FCreateTable);
  FIdentityInsert := IniFile.ReadBool(S_SQL, S_SQL_IdentityInsert,
    FIdentityInsert);
  TempTerminator := IniFile.ReadString(S_SQL, S_SQL_StatementTerm,
    FStatementTerm);
  if Length(TempTerminator) > 0 then
    FStatementTerm := TempTerminator[1];
  FCommitRecCount := IniFile.ReadInteger(S_SQL, S_SQL_CommitRecCount,
    FCommitRecCount);
  FCommitStatement := IniFile.ReadString(S_SQL, S_SQL_CommitStatement,
    FCommitStatement);
  FTableName := IniFile.ReadString(S_SQL, S_SQL_TableName, FTableName);
  FCustomScriptType := TQExportScriptType(IniFile.ReadInteger(S_SQL,
    S_SQL_CustomScriptType, Integer(FCustomScriptType)));
  FIBDialect := IniFile.ReadInteger(S_SQL, S_SQL_IBDialect, FIBDialect);
  FMultipleInsert := IniFile.ReadBool(S_SQL, S_SQL_MultipleInsert,
    FMultipleInsert);
end;

end.


