if SQL<>'' then
              SQL := ' where '+SQL;
            SQL := aProps.SQLSelectAll(Table,Fields,ExcludeTypes)+SQL;
            Rows := aProps.NewThreadSafeStatement;
            try
              Rows.Execute(SQL,true);
              Table := StringReplaceAll(StringReplaceAll(Table,' ',''),'-','');
              inc(result,RowsToSQLite3(Conn,Table,Rows,true));
            finally
              Rows.Free;
            end;
          end;
        except
          on E: Exception do
            ShowMessage(E.Message,true);

            if SQL<>'' then
              SQL := ' where '+SQL;
            SQL := aProps.SQLSelectAll(Table,Fields,ExcludeTypes)+SQL;
            Rows := aProps.NewThreadSafeStatement;
            try
              Rows.Execute(SQL,true);
              Table := StringReplaceAll(StringReplaceAll(Table,' ',''),'-','');
              inc(result,Conn.NewTableFromRows(Table,Rows,true));
            finally
              Rows.Free;
            end;
          end;
        except
          on E: Exception do
            ShowMessage(E.Message,true);

    to retrieve advanced information about database indexes (e.g. for indexes
    created after multiple columns)
  - added TSQLDBConnectionProperties.SQLTableName() method
  - added TSQLDBConnectionPropertiesThreadSafe.ForceOnlyOneSharedConnection
    property to by-pass internal thread-pool (e.g. for embedded engines)
  - added dFirebird kind of database in TSQLDBDefinition enumerate, and added
    associated SQL requests to retrieve metadata


  - TSQLDBConnectionProperties.SQLSelectAll() now handles spaces in table names
  - TSQLDBStatement.GetParamValueAsText() will truncate to a given number of
    chars the returned text
  - added missing ColumnToVarData() method to ISQLDBRows interface
  - now trim any spaces when retrieving database schema text values
  - fixed ticket [4c68975022] about broken SQL statement when logging active

................................................................................
    // - this default implementation will call the NewStatement method, and
    // implement handle statement caching is UseCache=true - in this case,
    // the TSQLDBStatement.Reset method shall have been overriden to allow
    // binding and execution of the very same prepared statement
    // - this method should return nil in case of error, or a prepared statement
    // instance in case of success
    function NewStatementPrepared(const aSQL: RawUTF8;
      ExpectResults: Boolean): ISQLDBStatement; virtual; 
    /// begin a Transaction for this connection
    // - this default implementation will check and set TransactionCount
    procedure StartTransaction; virtual; 
    /// commit changes of a Transaction for this connection
    // - StartTransaction method must have been called before
    // - this default implementation will check and set TransactionCount
    procedure Commit; virtual;
    /// discard changes of a Transaction for this connection
    // - StartTransaction method must have been called before
    // - this default implementation will check and set TransactionCount
    procedure Rollback; virtual;








    /// number of nested StartTransaction calls
    // - equals 0 if no transaction is active
    property TransactionCount: integer read fTransactionCount;
    /// TRUE if StartTransaction has been called
    // - check if TransactionCount>0
    property InTransaction: boolean read GetInTransaction;
    /// the current Date and Time, as retrieved from the server
................................................................................
    finally
      Disconnect;
    end;
  except
    on E: Exception do
      SynDBLog.Add.Log(sllError,E);
  end;

end;

function TSQLDBConnection.GetInTransaction: boolean;
begin
  result := TransactionCount>0;
end;

................................................................................

procedure TSQLDBConnection.StartTransaction;
begin
  CheckConnection;
  inc(fTransactionCount);
end;



















































{ TSQLDBConnectionProperties }

constructor TSQLDBConnectionProperties.Create(const aServerName, aDatabaseName,
  aUserID, aPassWord: RawUTF8);
var aDBMS: TSQLDBDefinition;
begin
................................................................................

function TSQLDBConnectionProperties.Execute(const aSQL: RawUTF8;
  const Params: array of const
  {$ifndef LVCL}{$ifndef DELPHI5OROLDER}; RowsVariant: PVariant=nil{$endif}{$endif}): ISQLDBRows;
var Query: TSQLDBStatement;
begin
  Query := NewThreadSafeStatement;

  Query.Execute(aSQL,true,Params);
{$ifndef LVCL}
{$ifndef DELPHI5OROLDER}
  if RowsVariant<>nil then
    RowsVariant^ := Query.RowData;
{$endif}
{$endif}
  result := Query;






end;

function TSQLDBConnectionProperties.ExecuteNoResult(const aSQL: RawUTF8;
  const Params: array of const): integer;
begin
  with NewThreadSafeStatement do
  try

    to retrieve advanced information about database indexes (e.g. for indexes
    created after multiple columns)
  - added TSQLDBConnectionProperties.SQLTableName() method
  - added TSQLDBConnectionPropertiesThreadSafe.ForceOnlyOneSharedConnection
    property to by-pass internal thread-pool (e.g. for embedded engines)
  - added dFirebird kind of database in TSQLDBDefinition enumerate, and added
    associated SQL requests to retrieve metadata
  - added TSQLDBConnection.NewTableFromRows() method to dump a SQL statement
    result into a new table of any database (may be used for replication)
  - TSQLDBConnectionProperties.SQLSelectAll() now handles spaces in table names
  - TSQLDBStatement.GetParamValueAsText() will truncate to a given number of
    chars the returned text
  - added missing ColumnToVarData() method to ISQLDBRows interface
  - now trim any spaces when retrieving database schema text values
  - fixed ticket [4c68975022] about broken SQL statement when logging active

................................................................................
    // - this default implementation will call the NewStatement method, and
    // implement handle statement caching is UseCache=true - in this case,
    // the TSQLDBStatement.Reset method shall have been overriden to allow
    // binding and execution of the very same prepared statement
    // - this method should return nil in case of error, or a prepared statement
    // instance in case of success
    function NewStatementPrepared(const aSQL: RawUTF8;
      ExpectResults: Boolean): ISQLDBStatement; virtual;
    /// begin a Transaction for this connection
    // - this default implementation will check and set TransactionCount
    procedure StartTransaction; virtual;
    /// commit changes of a Transaction for this connection
    // - StartTransaction method must have been called before
    // - this default implementation will check and set TransactionCount
    procedure Commit; virtual;
    /// discard changes of a Transaction for this connection
    // - StartTransaction method must have been called before
    // - this default implementation will check and set TransactionCount
    procedure Rollback; virtual;

    /// direct export of a DB statement rows into a new table of this database
    // - the corresponding table will be created within the current connection,
    // if it does not exist
    // - INSERTs will be nested within a transaction if WithinTransaction is TRUE
    function NewTableFromRows(const TableName: RawUTF8;
      Rows: TSQLDBStatement; WithinTransaction: boolean): integer;

    /// number of nested StartTransaction calls
    // - equals 0 if no transaction is active
    property TransactionCount: integer read fTransactionCount;
    /// TRUE if StartTransaction has been called
    // - check if TransactionCount>0
    property InTransaction: boolean read GetInTransaction;
    /// the current Date and Time, as retrieved from the server
................................................................................
    finally
      Disconnect;
    end;
  except
    on E: Exception do
      SynDBLog.Add.Log(sllError,E);
  end;
  inherited;
end;

function TSQLDBConnection.GetInTransaction: boolean;
begin
  result := TransactionCount>0;
end;

................................................................................

procedure TSQLDBConnection.StartTransaction;
begin
  CheckConnection;
  inc(fTransactionCount);
end;

function TSQLDBConnection.NewTableFromRows(const TableName: RawUTF8;
  Rows: TSQLDBStatement; WithinTransaction: boolean): integer;
var Fields: TSQLDBColumnPropertyDynArray;
    aTableName, SQL: RawUTF8;
    Tables: TRawUTF8DynArray;
    Ins: TSQLDBStatement;
begin
  result := 0;
  if (self=nil) or (Rows=nil) or (Rows.ColumnCount=0) then
    exit;
  if (PosEx('.',TableName)=0) and (PosEx(' ',TableName)=0)  then
    aTablename := TableName else
    aTableName := '`'+TableName+'`';
  if WithinTransaction then
    StartTransaction; // MUCH faster within a transaction
  try
    Ins := nil;
    try
      while Rows.Step do begin
        // init when first row of data is available
        if Ins=nil then begin
          SQL := Rows.ColumnsToSQLInsert(aTableName,Fields);
          Properties.GetTableNames(Tables);
          if FindRawUTF8(Tables,TableName,false)<0 then
            with Properties do
              ExecuteNoResult(SQLCreate(aTableName,Fields),[]);
          Ins := NewStatement;
          Ins.Prepare(SQL,false);
        end;
        // write row data
        Ins.BindFromRows(Fields,Rows);
        Ins.ExecutePrepared;
        Ins.Reset;
        inc(result);
      end;
      if WithinTransaction then
        Commit;
    finally
      Ins.Free;
    end;
  except
    on Exception do begin
      if WithinTransaction then
        Rollback;
      raise;
    end;
  end;
end;


{ TSQLDBConnectionProperties }

constructor TSQLDBConnectionProperties.Create(const aServerName, aDatabaseName,
  aUserID, aPassWord: RawUTF8);
var aDBMS: TSQLDBDefinition;
begin
................................................................................

function TSQLDBConnectionProperties.Execute(const aSQL: RawUTF8;
  const Params: array of const
  {$ifndef LVCL}{$ifndef DELPHI5OROLDER}; RowsVariant: PVariant=nil{$endif}{$endif}): ISQLDBRows;
var Query: TSQLDBStatement;
begin
  Query := NewThreadSafeStatement;
  try
    Query.Execute(aSQL,true,Params);
    {$ifndef LVCL}
    {$ifndef DELPHI5OROLDER}
    if RowsVariant<>nil then
      RowsVariant^ := Query.RowData;
    {$endif}
    {$endif}
    result := Query;
  except
    on Exception do begin
      Query.Free;
      raise;
    end;
  end;
end;

function TSQLDBConnectionProperties.ExecuteNoResult(const aSQL: RawUTF8;
  const Params: array of const): integer;
begin
  with NewThreadSafeStatement do
  try

  - added TSQLDBSQLite3Connection.Synchronous property
  - code refactoring, especially about error handling and ODBC integration

  Version 1.18
  - statement cache refactoring: cache logic is now at SynDB unit level
  - fixed ticket [4c68975022] about broken SQL statement when logging active
  - added TSQLDBSQLite3ConnectionProperties.SQLTableName() overriden method



}

{$I Synopse.inc} // define HASINLINE USETYPEINFO CPU32 CPU64 OWNNORMTOUPPER

interface

................................................................................
     - will use WR.Expand to guess the expected output format
     - fast overriden implementation with no temporary variable
     - BLOB field value is saved as Base64, in the '"\uFFF0base64encodedbinary"
       format and contains true BLOB data }
    procedure ColumnsToJSON(WR: TJSONWriter); override;
  end;

/// direct export of a DB statement rows into a SQLite3 database
// - the corresponding table will be created within the specified DB connection
// - transaction will be created if WithinTransaction is set to TRUE
function RowsToSQLite3(Connection: TSQLDBSQLite3Connection; const TableName: RawUTF8;
  Rows: TSQLDBStatement; WithinTransaction: boolean=true): integer; overload;

/// direct export of a DB statement rows into a SQLite3 database
// - the corresponding table will be created within the specified DB file
function RowsToSQLite3(const Dest: TFileName; const TableName: RawUTF8;
  Rows: TSQLDBStatement; UseMormotCollations: boolean): integer; overload;


implementation

function RowsToSQLite3(Connection: TSQLDBSQLite3Connection; const TableName: RawUTF8;
  Rows: TSQLDBStatement; WithinTransaction: boolean): integer;
var Fields: TSQLDBColumnPropertyDynArray;
    aTableName, SQL: RawUTF8;
    Ins: TSQLDBStatement;
begin
  result := 0;
  if (Connection=nil) or (Rows=nil) or (Rows.ColumnCount=0) then
    exit;
  if (PosEx('.',TableName)=0) and (PosEx(' ',TableName)=0)  then
    aTablename := TableName else
    aTableName := '`'+TableName+'`';
  if WithinTransaction then
    Connection.StartTransaction; // MUCH faster within a transaction
  try
    Ins := nil;
    while Rows.Step do begin
      // init when first row of data is available
      if Ins=nil then begin
        SQL := Rows.ColumnsToSQLInsert(aTableName,Fields); // get layout
        Connection.DB.Execute(Connection.Properties.SQLCreate(aTableName,Fields)); // create table
        Ins := Connection.NewStatement;
        Ins.Prepare(SQL,false); // prepare INSERT
      end;
      // write row data
      Ins.BindFromRows(Fields,Rows);
      Ins.ExecutePrepared;
      Ins.Reset;
      inc(result);
    end;
    if WithinTransaction then
      Connection.Commit;
  except
    on Exception do begin
      if WithinTransaction then
        Connection.Rollback;
      raise;
    end;
  end;
end;

function RowsToSQLite3(const Dest: TFileName; const TableName: RawUTF8;
  Rows: TSQLDBStatement; UseMormotCollations: boolean): integer; overload;
var DB: TSQLDBSQLite3ConnectionProperties;
    Conn: TSQLDBSQLite3Connection;
begin
  result := 0;
................................................................................
    exit;
  // we do not call DeleteFile(Dest) since DB may be completed on purpose
  DB := TSQLDBSQLite3ConnectionProperties.Create(StringToUTF8(Dest),'','','');
  try
    DB.UseMormotCollations := UseMormotCollations;
    Conn := DB.MainConnection as TSQLDBSQLite3Connection;
    Conn.Connect;
    result := RowsToSQLite3(Conn,TableName,Rows,true);

  finally
    DB.Free;
  end;
end;

{ TSQLDBSQLite3ConnectionProperties }

  - added TSQLDBSQLite3Connection.Synchronous property
  - code refactoring, especially about error handling and ODBC integration

  Version 1.18
  - statement cache refactoring: cache logic is now at SynDB unit level
  - fixed ticket [4c68975022] about broken SQL statement when logging active
  - added TSQLDBSQLite3ConnectionProperties.SQLTableName() overriden method
  - overloaded function RowsToSQLite3() is now moved as generic
    TSQLDBConnection.NewTableFromRows() method

}

{$I Synopse.inc} // define HASINLINE USETYPEINFO CPU32 CPU64 OWNNORMTOUPPER

interface

................................................................................
     - will use WR.Expand to guess the expected output format
     - fast overriden implementation with no temporary variable
     - BLOB field value is saved as Base64, in the '"\uFFF0base64encodedbinary"
       format and contains true BLOB data }
    procedure ColumnsToJSON(WR: TJSONWriter); override;
  end;







/// direct export of a DB statement rows into a SQLite3 database
// - the corresponding table will be created within the specified DB file
function RowsToSQLite3(const Dest: TFileName; const TableName: RawUTF8;
  Rows: TSQLDBStatement; UseMormotCollations: boolean): integer; 


implementation










































function RowsToSQLite3(const Dest: TFileName; const TableName: RawUTF8;
  Rows: TSQLDBStatement; UseMormotCollations: boolean): integer; overload;
var DB: TSQLDBSQLite3ConnectionProperties;
    Conn: TSQLDBSQLite3Connection;
begin
  result := 0;
................................................................................
    exit;
  // we do not call DeleteFile(Dest) since DB may be completed on purpose
  DB := TSQLDBSQLite3ConnectionProperties.Create(StringToUTF8(Dest),'','','');
  try
    DB.UseMormotCollations := UseMormotCollations;
    Conn := DB.MainConnection as TSQLDBSQLite3Connection;
    Conn.Connect;
    result := Conn.NewTableFromRows(TableName,Rows,true);
    Conn.Disconnect;
  finally
    DB.Free;
  end;
end;

{ TSQLDBSQLite3ConnectionProperties }