Version 1.18
  - SQL statements are now cached by default - in some cases, it will increase
    individual reading or writing speed by a factor of 4x
  - introducing new ISQLDBStatement interface, used by SQL statement cache
  - avoid syntax error for some engines which do not accept an ending ';' in
    SQL statements




  - added TSQLDBConnectionProperties.ForcedSchemaName optional property
  - added TSQLDBConnectionProperties.SQLGetIndex() and GetIndexes() methods
    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)
................................................................................
    // - by now, only SynDBOracle unit implements an array bind
    procedure BindArray(Param: Integer; const Values: array of RawUTF8); overload;
    {/ Execute a prepared SQL statement
     - parameters marked as ? should have been already bound with Bind*() functions
     - should raise an Exception on any error
     - after execution, you can access any returned data via ISQLDBRows methods }
    procedure ExecutePrepared;


  end;

  {$M+} { published properties to be logged as JSON }

  TSQLDBConnection = class;

  /// abstract class used to set Database-related properties
................................................................................
    // connection pool, and allow automatic reconnection
    procedure ClearConnectionPool; virtual;
    /// create a new thread-safe statement
    // - this method will call ThreadSafeConnection.NewStatement 
    function NewThreadSafeStatement: TSQLDBStatement;
    /// create a new thread-safe statement from an internal cache (if any)
    // - will call ThreadSafeConnection.NewStatementPrepared
    // - this method should return nil in case of error, or a prepared statement
    // instance in case of success



    function NewThreadSafeStatementPrepared(const aSQL: RawUTF8;
       ExpectResults: Boolean): ISQLDBStatement; overload;
    /// create a new thread-safe statement from an internal cache (if any)
    // - this method will call the NewThreadSafeStatementPrepared method
    // - here Args[] array does not refer to bound parameters, but to values
    // to be changed within SQLFormat in place of '%' characters (this method
    // will call FormatUTF8() internaly); parameters will be bound directly
    // on the returned TSQLDBStatement instance



    function NewThreadSafeStatementPrepared(SQLFormat: PUTF8Char;
      const Args: array of const; ExpectResults: Boolean): ISQLDBStatement; overload;
    /// execute a SQL query, returning a statement interface instance to retrieve
    // the result rows
    // - will call NewThreadSafeStatement method to retrieve a thread-safe
    // statement instance, then run the corresponding Execute() method

    // - returns an ISQLDBRows to access any resulting rows (if
    // ExpectResults is TRUE), and provide basic garbage collection, as such:
    // ! procedure WriteFamily(const aName: RawUTF8);
    // ! var I: ISQLDBRows;
    // ! begin
    // !   I := MyConnProps.Execute('select * from table where name=?',[aName]);
    // !   while I.Step do
    // !     writeln(I['FirstName'],' ',DateToStr(I['BirthDate']));
    // ! end;
................................................................................
  // - more than one TSQLDBConnection instance can be run for the same
  // TSQLDBConnectionProperties
  TSQLDBConnection = class
  private
    function GetInTransaction: boolean;
  protected
    fProperties: TSQLDBConnectionProperties;

    fErrorMessage: string;
    fInfoMessage: string;
    fTransactionCount: integer;
    fServerTimeStampOffset: TDateTime;
    fCache: TRawUTF8ListHashed;
    function GetServerTimeStamp: TTimeLog; virtual;
    /// raise an exception
    procedure CheckConnection;
................................................................................
    // - the caller should free the instance after use
    function NewStatement: TSQLDBStatement; virtual; abstract;
    /// initialize a new SQL query statement for the given connection
    // - 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;
................................................................................
    // - 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
................................................................................
    // - default implementation will return the executable time, i.e. Iso8601Now
    property ServerTimeStamp: TTimeLog read GetServerTimeStamp;
  published { to be logged as JSON }
    /// the associated database properties
    property Properties: TSQLDBConnectionProperties read fProperties;
    /// returns TRUE if the connection was set
    property Connected: boolean read IsConnected;
    /// some information message, as retrieved during execution
    property LastErrorMessage: string read fErrorMessage;


    /// some information message, as retrieved during execution
    property InfoMessage: string read fInfoMessage;
  end;

  /// generic abstract class to implement a prepared SQL query
  // - inherited classes should implement the DB-specific connection in its
  // overriden methods, especially Bind*(), Prepare(), ExecutePrepared, Step()
................................................................................
    fSQL: RawUTF8;
    fExpectResults: boolean;
    fParamCount: integer;
    fColumnCount: integer;
    fTotalRowsRetrieved: Integer;
    fCurrentRow: Integer;
    function GetSQLWithInlinedParams: RawUTF8;
    /// default implementation returns 0
    function GetUpdateCount: integer; virtual;
    /// raise an exception if Col is out of range according to fColumnCount
    procedure CheckCol(Col: integer); {$ifdef HASINLINE}inline;{$endif}
    {/ will set a Int64/Double/Currency/TDateTime/RawUTF8/TBlobData Dest variable
      from a given column value
     - internal conversion will use a temporary Variant and ColumnToVariant method
     - expects Dest to be of the exact type (e.g. Int64, not Integer) }
    function ColumnToTypedValue(Col: integer; DestType: TSQLDBFieldType; var Dest): TSQLDBFieldType;
................................................................................
    // - BLOB field value is saved as Base64, in the '"\uFFF0base64encodedbinary"'
    // format and contains true BLOB data
    // - if ReturnedRowCount points to an integer variable, it will be filled with
    // the number of row data returned (excluding field names)
    // - similar to corresponding TSQLRequest.Execute method in SynSQLite3 unit
    function FetchAllAsJSON(Expanded: boolean; ReturnedRowCount: PPtrInt=nil): RawUTF8;





    /// the associated database connection
    property Connection: TSQLDBConnection read fConnection;
    /// the prepared SQL statement, as supplied to Prepare() method
    property SQL: RawUTF8 read fSQL;
    /// the prepared SQL statement, with all '?' changed into the supplied
    // parameter values
    property SQLWithInlinedParams: RawUTF8 read GetSQLWithInlinedParams;
................................................................................
    /// the current row after Execute call, corresponding to Column*() methods
    // - contains 0 in case of no (more) available data, or a number >=1
    property CurrentRow: Integer read fCurrentRow;
    /// the total number of data rows retrieved by this instance
    // - is not reset when there is no more row of available data (Step returns
    // false), or when Step() is called with SeekFirst=true
    property TotalRowsRetrieved: Integer read fTotalRowsRetrieved;
    /// gets a number of updates made by latest executed statement
    property UpdateCount: Integer read GetUpdateCount;
  end;

  /// abstract connection created from TSQLDBConnectionProperties
  // - this overriden class will defined an hidden thread ID, to ensure
  // that one connection will be create per thread
  // - e.g. OleDB, ODBC and Oracle connections will inherit from this class
  TSQLDBConnectionThreadSafe = class(TSQLDBConnection)
................................................................................
    if fServerTimeStampOffset=0 then
      fServerTimeStampOffset := 0.0001; // request server only once
  end;
  PIso8601(@result)^.From(Current+fServerTimeStampOffset);
end;

function TSQLDBConnection.NewStatementPrepared(const aSQL: RawUTF8;
  ExpectResults: Boolean): ISQLDBStatement;
var Stmt: TSQLDBStatement;
    ToCache: boolean;
    ndx: integer;
begin
  ToCache := fProperties.IsCachable(Pointer(aSQL));
  if ToCache and (fCache<>nil) then begin
    ndx := fCache.IndexOf(aSQL);
................................................................................
      if fCache=nil then
        fCache := TRawUTF8ListHashed.Create(true);
      fCache.AddObject(aSQL,Stmt);
      Stmt._AddRef;
    end;
    result := Stmt;
  except
    on Exception do begin
      Stmt.Free;




      result := nil;
    end;
  end;
end;  

procedure TSQLDBConnection.Rollback;
begin
................................................................................
   fMainConnection.Free;
  inherited;
end;

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
    Execute(aSQL,false,Params);

    result := UpdateCount;
  finally
    Free;
  end;
end;

function TSQLDBConnectionProperties.GetMainConnection: TSQLDBConnection;
................................................................................

function TSQLDBConnectionProperties.NewThreadSafeStatement: TSQLDBStatement;
begin
  result := ThreadSafeConnection.NewStatement;
end;

function TSQLDBConnectionProperties.NewThreadSafeStatementPrepared(
  const aSQL: RawUTF8; ExpectResults: Boolean): ISQLDBStatement;
begin
  result := ThreadSafeConnection.NewStatementPrepared(aSQL,ExpectResults);
end;

function TSQLDBConnectionProperties.NewThreadSafeStatementPrepared(
  SQLFormat: PUTF8Char; const Args: array of const; ExpectResults: Boolean): ISQLDBStatement;
begin
  result := NewThreadSafeStatementPrepared(FormatUTF8(SQLFormat,Args),ExpectResults);
end;
................................................................................

procedure TSQLDBStatement.Execute(SQLFormat: PUTF8Char;
  ExpectResults: Boolean; const Args, Params: array of const);
begin
  Execute(FormatUTF8(SQLFormat,Args),ExpectResults,Params);
end;

function TSQLDBStatement.GetUpdateCount: integer;
begin
  result := 0;
end;

function TSQLDBStatement.ColumnString(Col: integer): string;
begin
  Result := UTF8ToString(ColumnUTF8(Col));

  Version 1.18
  - SQL statements are now cached by default - in some cases, it will increase
    individual reading or writing speed by a factor of 4x
  - introducing new ISQLDBStatement interface, used by SQL statement cache
  - avoid syntax error for some engines which do not accept an ending ';' in
    SQL statements
  - added RaiseExceptionOnError: boolean=false optional parameter to
    TSQLDBConnection.NewStatementPrepared() method
  - added TSQLDBConnection.LastErrorMessage and LastErrorException properties,
    to retrieve the error when NewStatementPrepared() returned nil
  - added TSQLDBConnectionProperties.ForcedSchemaName optional property
  - added TSQLDBConnectionProperties.SQLGetIndex() and GetIndexes() methods
    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)
................................................................................
    // - by now, only SynDBOracle unit implements an array bind
    procedure BindArray(Param: Integer; const Values: array of RawUTF8); overload;
    {/ Execute a prepared SQL statement
     - parameters marked as ? should have been already bound with Bind*() functions
     - should raise an Exception on any error
     - after execution, you can access any returned data via ISQLDBRows methods }
    procedure ExecutePrepared;
    /// gets a number of updates made by latest executed statement
    function UpdateCount: Integer;
  end;

  {$M+} { published properties to be logged as JSON }

  TSQLDBConnection = class;

  /// abstract class used to set Database-related properties
................................................................................
    // connection pool, and allow automatic reconnection
    procedure ClearConnectionPool; virtual;
    /// create a new thread-safe statement
    // - this method will call ThreadSafeConnection.NewStatement 
    function NewThreadSafeStatement: TSQLDBStatement;
    /// create a new thread-safe statement from an internal cache (if any)
    // - will call ThreadSafeConnection.NewStatementPrepared
    // - this method should return a prepared statement instance on success
    // - on error, returns nil and you can check Connnection.LastErrorMessage /
    // Connection.LastErrorException to retrieve corresponding error information
    // (if RaiseExceptionOnError is left to default FALSE value, otherwise, it will
    // raise an exception)
    function NewThreadSafeStatementPrepared(const aSQL: RawUTF8;
       ExpectResults: Boolean; RaiseExceptionOnError: Boolean=false): ISQLDBStatement; overload;
    /// create a new thread-safe statement from an internal cache (if any)
    // - this method will call the overloaded NewThreadSafeStatementPrepared method
    // - here Args[] array does not refer to bound parameters, but to values
    // to be changed within SQLFormat in place of '%' characters (this method
    // will call FormatUTF8() internaly); parameters will be bound directly
    // on the returned TSQLDBStatement instance
    // - this method should return a prepared statement instance on success
    // - on error, returns nil and you can check Connnection.LastErrorMessage /
    // Connection.LastErrorException to retrieve correspnding error information
    function NewThreadSafeStatementPrepared(SQLFormat: PUTF8Char;
      const Args: array of const; ExpectResults: Boolean): ISQLDBStatement; overload;
    /// execute a SQL query, returning a statement interface instance to retrieve
    // the result rows
    // - will call NewThreadSafeStatement method to retrieve a thread-safe
    // statement instance, then run the corresponding Execute() method
    // - raise an exception on error
    // - returns an ISQLDBRows to access any resulting rows (if ExpectResults is
    // TRUE), and provide basic garbage collection, as such:
    // ! procedure WriteFamily(const aName: RawUTF8);
    // ! var I: ISQLDBRows;
    // ! begin
    // !   I := MyConnProps.Execute('select * from table where name=?',[aName]);
    // !   while I.Step do
    // !     writeln(I['FirstName'],' ',DateToStr(I['BirthDate']));
    // ! end;
................................................................................
  // - more than one TSQLDBConnection instance can be run for the same
  // TSQLDBConnectionProperties
  TSQLDBConnection = class
  private
    function GetInTransaction: boolean;
  protected
    fProperties: TSQLDBConnectionProperties;
    fErrorException: ExceptClass;
    fErrorMessage: RawUTF8;
    fInfoMessage: string;
    fTransactionCount: integer;
    fServerTimeStampOffset: TDateTime;
    fCache: TRawUTF8ListHashed;
    function GetServerTimeStamp: TTimeLog; virtual;
    /// raise an exception
    procedure CheckConnection;
................................................................................
    // - the caller should free the instance after use
    function NewStatement: TSQLDBStatement; virtual; abstract;
    /// initialize a new SQL query statement for the given connection
    // - 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 a prepared statement instance on success
    // - on error, if RaiseExceptionOnError=false (by default), it returns nil
    // and you can check LastErrorMessage and LastErrorException properties to
    // retrieve correspnding error information
    // - on error, if RaiseExceptionOnError=true, an exception is raised
    function NewStatementPrepared(const aSQL: RawUTF8;
      ExpectResults: Boolean; RaiseExceptionOnError: Boolean=false): 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;
................................................................................
    // - 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
    // - will raise an Exception in case of error
    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
................................................................................
    // - default implementation will return the executable time, i.e. Iso8601Now
    property ServerTimeStamp: TTimeLog read GetServerTimeStamp;
  published { to be logged as JSON }
    /// the associated database properties
    property Properties: TSQLDBConnectionProperties read fProperties;
    /// returns TRUE if the connection was set
    property Connected: boolean read IsConnected;
    /// some error message, e.g. during execution of NewStatementPrepared
    property LastErrorMessage: RawUTF8 read fErrorMessage;
    /// some error exception, e.g. during execution of NewStatementPrepared
    property LastErrorException: ExceptClass read fErrorException;
    /// some information message, as retrieved during execution
    property InfoMessage: string read fInfoMessage;
  end;

  /// generic abstract class to implement a prepared SQL query
  // - inherited classes should implement the DB-specific connection in its
  // overriden methods, especially Bind*(), Prepare(), ExecutePrepared, Step()
................................................................................
    fSQL: RawUTF8;
    fExpectResults: boolean;
    fParamCount: integer;
    fColumnCount: integer;
    fTotalRowsRetrieved: Integer;
    fCurrentRow: Integer;
    function GetSQLWithInlinedParams: RawUTF8;


    /// raise an exception if Col is out of range according to fColumnCount
    procedure CheckCol(Col: integer); {$ifdef HASINLINE}inline;{$endif}
    {/ will set a Int64/Double/Currency/TDateTime/RawUTF8/TBlobData Dest variable
      from a given column value
     - internal conversion will use a temporary Variant and ColumnToVariant method
     - expects Dest to be of the exact type (e.g. Int64, not Integer) }
    function ColumnToTypedValue(Col: integer; DestType: TSQLDBFieldType; var Dest): TSQLDBFieldType;
................................................................................
    // - BLOB field value is saved as Base64, in the '"\uFFF0base64encodedbinary"'
    // format and contains true BLOB data
    // - if ReturnedRowCount points to an integer variable, it will be filled with
    // the number of row data returned (excluding field names)
    // - similar to corresponding TSQLRequest.Execute method in SynSQLite3 unit
    function FetchAllAsJSON(Expanded: boolean; ReturnedRowCount: PPtrInt=nil): RawUTF8;

    /// gets a number of updates made by latest executed statement
    // - default implementation returns 0
    function UpdateCount: integer; virtual;

    /// the associated database connection
    property Connection: TSQLDBConnection read fConnection;
    /// the prepared SQL statement, as supplied to Prepare() method
    property SQL: RawUTF8 read fSQL;
    /// the prepared SQL statement, with all '?' changed into the supplied
    // parameter values
    property SQLWithInlinedParams: RawUTF8 read GetSQLWithInlinedParams;
................................................................................
    /// the current row after Execute call, corresponding to Column*() methods
    // - contains 0 in case of no (more) available data, or a number >=1
    property CurrentRow: Integer read fCurrentRow;
    /// the total number of data rows retrieved by this instance
    // - is not reset when there is no more row of available data (Step returns
    // false), or when Step() is called with SeekFirst=true
    property TotalRowsRetrieved: Integer read fTotalRowsRetrieved;


  end;

  /// abstract connection created from TSQLDBConnectionProperties
  // - this overriden class will defined an hidden thread ID, to ensure
  // that one connection will be create per thread
  // - e.g. OleDB, ODBC and Oracle connections will inherit from this class
  TSQLDBConnectionThreadSafe = class(TSQLDBConnection)
................................................................................
    if fServerTimeStampOffset=0 then
      fServerTimeStampOffset := 0.0001; // request server only once
  end;
  PIso8601(@result)^.From(Current+fServerTimeStampOffset);
end;

function TSQLDBConnection.NewStatementPrepared(const aSQL: RawUTF8;
  ExpectResults: Boolean; RaiseExceptionOnError: Boolean=false): ISQLDBStatement;
var Stmt: TSQLDBStatement;
    ToCache: boolean;
    ndx: integer;
begin
  ToCache := fProperties.IsCachable(Pointer(aSQL));
  if ToCache and (fCache<>nil) then begin
    ndx := fCache.IndexOf(aSQL);
................................................................................
      if fCache=nil then
        fCache := TRawUTF8ListHashed.Create(true);
      fCache.AddObject(aSQL,Stmt);
      Stmt._AddRef;
    end;
    result := Stmt;
  except
    on E: Exception do begin
      Stmt.Free;
      if RaiseExceptionOnError then
        raise;
      StringToUTF8(E.Message,fErrorMessage);
      fErrorException := PPointer(E)^;
      result := nil;
    end;
  end;
end;  

procedure TSQLDBConnection.Rollback;
begin
................................................................................
   fMainConnection.Free;
  inherited;
end;

function TSQLDBConnectionProperties.Execute(const aSQL: RawUTF8;
  const Params: array of const
  {$ifndef LVCL}{$ifndef DELPHI5OROLDER}; RowsVariant: PVariant=nil{$endif}{$endif}): ISQLDBRows;
var Stmt: ISQLDBStatement;
begin
  Stmt := NewThreadSafeStatementPrepared(aSQL,true,true);
  Stmt.Bind(Params);
  Stmt.ExecutePrepared;
  result := Stmt;
  {$ifndef LVCL}
  {$ifndef DELPHI5OROLDER}
  if RowsVariant<>nil then
    RowsVariant^ := result.RowData;
  {$endif}
  {$endif}







end;

function TSQLDBConnectionProperties.ExecuteNoResult(const aSQL: RawUTF8;
  const Params: array of const): integer;
begin
  with NewThreadSafeStatementPrepared(aSQL,false,true) do
  try
    Bind(Params);
    ExecutePrepared;
    result := UpdateCount;
  finally
    Free;
  end;
end;

function TSQLDBConnectionProperties.GetMainConnection: TSQLDBConnection;
................................................................................

function TSQLDBConnectionProperties.NewThreadSafeStatement: TSQLDBStatement;
begin
  result := ThreadSafeConnection.NewStatement;
end;

function TSQLDBConnectionProperties.NewThreadSafeStatementPrepared(
  const aSQL: RawUTF8; ExpectResults: Boolean; RaiseExceptionOnError: Boolean=false): ISQLDBStatement;
begin
  result := ThreadSafeConnection.NewStatementPrepared(aSQL,ExpectResults,RaiseExceptionOnError);
end;

function TSQLDBConnectionProperties.NewThreadSafeStatementPrepared(
  SQLFormat: PUTF8Char; const Args: array of const; ExpectResults: Boolean): ISQLDBStatement;
begin
  result := NewThreadSafeStatementPrepared(FormatUTF8(SQLFormat,Args),ExpectResults);
end;
................................................................................

procedure TSQLDBStatement.Execute(SQLFormat: PUTF8Char;
  ExpectResults: Boolean; const Args, Params: array of const);
begin
  Execute(FormatUTF8(SQLFormat,Args),ExpectResults,Params);
end;

function TSQLDBStatement.UpdateCount: integer;
begin
  result := 0;
end;

function TSQLDBStatement.ColumnString(Col: integer): string;
begin
  Result := UTF8ToString(ColumnUTF8(Col));

  protected
    fMain: TSQLDBFirebirdInternalStatement;
    fAutoCommit: TSQLDBFirebirdInternalStatement;
    fCurrent: ^TSQLDBFirebirdInternalStatement;
    fInput: TByteDynArray;
    fOutput: TByteDynArray;
    fStatus: TSQLDBFirebirdStatus;
    function GetUpdateCount: integer; override;
    function ReleaseMainStatementIfAny: boolean;
  public
    {{ create a Firebird statement instance, from an existing Firebird connection
     - the Execute method can be called once per TSQLDBFirebirdStatement instance,
       but you can use the Prepare once followed by several ExecutePrepared methods
     - if the supplied connection is not of TOleDBConnection type, will raise
       an exception }
................................................................................
    function ColumnBlob(Col: integer): RawByteString; override;
    {{ append all columns values of the current Row to a JSON stream
     - 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;

const
  FBLIBNAME: array[boolean] of TFileName =
  {$ifdef MSWINDOWS}
    ('gds32.dll','fbembed.dll');
  {$endif}
................................................................................
        Check(isc_rollback_transaction(fStatus,fAutoCommit.Transaction),fStatus);
    end;
  finally
    inherited Destroy;
  end;
end;

function TSQLDBFirebirdStatement.GetUpdateCount: integer;
begin
end;

procedure TSQLDBFirebirdStatement.Prepare(const aSQL: RawUTF8; ExpectResults: Boolean);
var Log: ISynLog;
    Conn: TSQLDBFirebirdConnection;
begin
................................................................................


{ TFirebirdLib }

procedure TFirebirdLib.Check(Status: ISCStatus; var PrivateStatus: TSQLDBFirebirdStatus);

  procedure RaiseEFirebirdException;
  var Code, len: integer;
      sql,tmp: array[byte] of AnsiChar;
      Vec: pointer;
      msg: AnsiString;
  begin
    Code := isc_sqlcode(PrivateStatus);
    isc_sql_interprete(Code,sql,sizeof(sql));
    Vec := @PrivateStatus;

  protected
    fMain: TSQLDBFirebirdInternalStatement;
    fAutoCommit: TSQLDBFirebirdInternalStatement;
    fCurrent: ^TSQLDBFirebirdInternalStatement;
    fInput: TByteDynArray;
    fOutput: TByteDynArray;
    fStatus: TSQLDBFirebirdStatus;

    function ReleaseMainStatementIfAny: boolean;
  public
    {{ create a Firebird statement instance, from an existing Firebird connection
     - the Execute method can be called once per TSQLDBFirebirdStatement instance,
       but you can use the Prepare once followed by several ExecutePrepared methods
     - if the supplied connection is not of TOleDBConnection type, will raise
       an exception }
................................................................................
    function ColumnBlob(Col: integer): RawByteString; override;
    {{ append all columns values of the current Row to a JSON stream
     - 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;
    /// returns the number of rows updated by the execution of this statement
    function UpdateCount: integer; override;
  end;

const
  FBLIBNAME: array[boolean] of TFileName =
  {$ifdef MSWINDOWS}
    ('gds32.dll','fbembed.dll');
  {$endif}
................................................................................
        Check(isc_rollback_transaction(fStatus,fAutoCommit.Transaction),fStatus);
    end;
  finally
    inherited Destroy;
  end;
end;

function TSQLDBFirebirdStatement.UpdateCount: integer;
begin
end;

procedure TSQLDBFirebirdStatement.Prepare(const aSQL: RawUTF8; ExpectResults: Boolean);
var Log: ISynLog;
    Conn: TSQLDBFirebirdConnection;
begin
................................................................................


{ TFirebirdLib }

procedure TFirebirdLib.Check(Status: ISCStatus; var PrivateStatus: TSQLDBFirebirdStatus);

  procedure RaiseEFirebirdException;
  var Code: integer;
      sql,tmp: array[byte] of AnsiChar;
      Vec: pointer;
      msg: AnsiString;
  begin
    Code := isc_sqlcode(PrivateStatus);
    isc_sql_interprete(Code,sql,sizeof(sql));
    Vec := @PrivateStatus;

    fColTmpLen: cardinal;
    fGetColIndicator: PtrInt; // as PtrInt=SqlLen
    fGetColStatus: SmallInt; // as SqlReturn
    fSQLW: RawUnicode;
    procedure AllocStatement;
    procedure BindColumns;
    function GetCol(Col: integer; ExpectedType: TSQLDBFieldType): TSQLDBStatementGetCol;
    function GetUpdateCount: integer; override;
  public
    {{ create a ODBC statement instance, from an existing ODBC connection
     - the Execute method can be called once per TODBCStatement instance,
       but you can use the Prepare once followed by several ExecutePrepared methods
     - if the supplied connection is not of TOleDBConnection type, will raise
       an exception }
    constructor Create(aConnection: TSQLDBConnection); override;
................................................................................
    function ColumnBlob(Col: integer): RawByteString; override;
    {{ append all columns values of the current Row to a JSON stream
     - 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;


implementation


{ -------------- ODBC library interfaces, constants and types }
................................................................................
      Check(CloseCursor(fStatement),SQL_HANDLE_STMT,fStatement);
    if fParamCount>0 then
      Check(FreeStmt(fStatement,SQL_RESET_PARAMS),SQL_HANDLE_STMT,fStatement);
  end;
  inherited Reset;
end;

function TODBCStatement.GetUpdateCount: integer;
var RowCount: SqlLen;
begin
  if (fStatement<>nil) and not fExpectResults then
    ODBC.Check(ODBC.RowCount(fStatement,RowCount),SQL_HANDLE_STMT,fStatement) else
    RowCount := 0;
  result := RowCount;
end;

    fColTmpLen: cardinal;
    fGetColIndicator: PtrInt; // as PtrInt=SqlLen
    fGetColStatus: SmallInt; // as SqlReturn
    fSQLW: RawUnicode;
    procedure AllocStatement;
    procedure BindColumns;
    function GetCol(Col: integer; ExpectedType: TSQLDBFieldType): TSQLDBStatementGetCol;

  public
    {{ create a ODBC statement instance, from an existing ODBC connection
     - the Execute method can be called once per TODBCStatement instance,
       but you can use the Prepare once followed by several ExecutePrepared methods
     - if the supplied connection is not of TOleDBConnection type, will raise
       an exception }
    constructor Create(aConnection: TSQLDBConnection); override;
................................................................................
    function ColumnBlob(Col: integer): RawByteString; override;
    {{ append all columns values of the current Row to a JSON stream
     - 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;
    /// returns the number of rows updated by the execution of this statement
    function UpdateCount: integer; override;
  end;


implementation


{ -------------- ODBC library interfaces, constants and types }
................................................................................
      Check(CloseCursor(fStatement),SQL_HANDLE_STMT,fStatement);
    if fParamCount>0 then
      Check(FreeStmt(fStatement,SQL_RESET_PARAMS),SQL_HANDLE_STMT,fStatement);
  end;
  inherited Reset;
end;

function TODBCStatement.UpdateCount: integer;
var RowCount: SqlLen;
begin
  if (fStatement<>nil) and not fExpectResults then
    ODBC.Check(ODBC.RowCount(fStatement,RowCount),SQL_HANDLE_STMT,fStatement) else
    RowCount := 0;
  result := RowCount;
end;

    // - the caller should free the instance after use
    function NewStatement: TSQLDBStatement; override;
    /// initialize a new SQL query statement for the given connection
    // - if UseCache=true, this overriden implementation will use server-side
    // Oracle statement cache - in this case, StatementCacheSize will define
    // how many statements are to be cached
    // - 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; override; 
    /// begin a Transaction for this connection
    // - current implementation do not support nested transaction with those
    // methods: exception will be raised in such case
    // - by default, TSQLDBOracleStatement works in AutoCommit mode, unless
    // StartTransaction is called
    procedure StartTransaction; override;
    /// commit changes of a Transaction for this connection
................................................................................
    fRowFetchedEnded: boolean;
    fRowBuffer: TByteDynArray;
    fInternalBufferSize: cardinal;
    fUseServerSideStatementCache: boolean;
{$ifndef DELPHI5OROLDER}
    fTimeElapsed: TPrecisionTimer;
{$endif}
    function GetUpdateCount: integer; override;
    procedure FreeHandles;
    procedure FetchTest(Status: integer);
    /// Col=0...fColumnCount-1
    function GetCol(Col: Integer; out Column: PSQLDBColumnProperty): pointer;
  public
    {{ create an OCI statement instance, from an existing OCI connection
     - the Execute method can be called once per TSQLDBOracleStatement instance,
................................................................................
    {{ append all columns values of the current Row to a JSON stream
     - will use WR.Expand to guess the expected output format
     - fast overriden implementation with no temporary variable (about 20%
       faster when run over high number of data rows) 
     - BLOB field value is saved as Base64, in the '"\uFFF0base64encodedbinary"
       format and contains true BLOB data }
    procedure ColumnsToJSON(WR: TJSONWriter); override;


  end;


implementation

{ TOracleDate }

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

function TSQLDBOracleConnection.NewStatement: TSQLDBStatement;
begin
  result := TSQLDBOracleStatement.Create(self);
end;

function TSQLDBOracleConnection.NewStatementPrepared(const aSQL: RawUTF8;
  ExpectResults: Boolean): ISQLDBStatement;
var Stmt: TSQLDBOracleStatement;
begin


  Stmt := TSQLDBOracleStatement.Create(self);
  if fProperties.IsCachable(pointer(aSQL)) then
    Stmt.fUseServerSideStatementCache := true;
  Stmt.Prepare(aSQL,ExpectResults);
  result := Stmt;










end;

procedure TSQLDBOracleConnection.Rollback;
begin
  inherited;
  if fTrans=nil then
    raise ESQLDBOracle.Create('Invalid RollBack call');
................................................................................
    // 0:OK, >0:untruncated length, -1:NULL, -2:truncated (length>32KB)
   -1: result := nil; // NULL
    0: exit;
    else LogTruncatedColumn(Column^);
  end;
end;

function TSQLDBOracleStatement.GetUpdateCount: integer;
begin
  result := 0;
  if fStatement<>nil then
    OCI.AttrGet(fStatement,OCI_HTYPE_STMT,@result,nil,OCI_ATTR_ROW_COUNT,fError);
end;

const

    // - the caller should free the instance after use
    function NewStatement: TSQLDBStatement; override;
    /// initialize a new SQL query statement for the given connection
    // - if UseCache=true, this overriden implementation will use server-side
    // Oracle statement cache - in this case, StatementCacheSize will define
    // how many statements are to be cached
    // - this method should return nil in case of error, or a prepared statement
    // instance in case of success (with default RaiseExceptionOnError=false)
    function NewStatementPrepared(const aSQL: RawUTF8;
      ExpectResults: Boolean; RaiseExceptionOnError: Boolean=false): ISQLDBStatement; override;
    /// begin a Transaction for this connection
    // - current implementation do not support nested transaction with those
    // methods: exception will be raised in such case
    // - by default, TSQLDBOracleStatement works in AutoCommit mode, unless
    // StartTransaction is called
    procedure StartTransaction; override;
    /// commit changes of a Transaction for this connection
................................................................................
    fRowFetchedEnded: boolean;
    fRowBuffer: TByteDynArray;
    fInternalBufferSize: cardinal;
    fUseServerSideStatementCache: boolean;
{$ifndef DELPHI5OROLDER}
    fTimeElapsed: TPrecisionTimer;
{$endif}

    procedure FreeHandles;
    procedure FetchTest(Status: integer);
    /// Col=0...fColumnCount-1
    function GetCol(Col: Integer; out Column: PSQLDBColumnProperty): pointer;
  public
    {{ create an OCI statement instance, from an existing OCI connection
     - the Execute method can be called once per TSQLDBOracleStatement instance,
................................................................................
    {{ append all columns values of the current Row to a JSON stream
     - will use WR.Expand to guess the expected output format
     - fast overriden implementation with no temporary variable (about 20%
       faster when run over high number of data rows) 
     - BLOB field value is saved as Base64, in the '"\uFFF0base64encodedbinary"
       format and contains true BLOB data }
    procedure ColumnsToJSON(WR: TJSONWriter); override;
    /// returns the number of rows updated by the execution of this statement
    function UpdateCount: integer; override;
  end;


implementation

{ TOracleDate }

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

function TSQLDBOracleConnection.NewStatement: TSQLDBStatement;
begin
  result := TSQLDBOracleStatement.Create(self);
end;

function TSQLDBOracleConnection.NewStatementPrepared(const aSQL: RawUTF8;
  ExpectResults: Boolean; RaiseExceptionOnError: Boolean=false): ISQLDBStatement;
var Stmt: TSQLDBOracleStatement;
begin
  Stmt := nil;
  try
    Stmt := TSQLDBOracleStatement.Create(self);
    if fProperties.IsCachable(pointer(aSQL)) then
      Stmt.fUseServerSideStatementCache := true;
    Stmt.Prepare(aSQL,ExpectResults);
    result := Stmt;
  except
    on E: Exception do begin
      Stmt.Free;
      if RaiseExceptionOnError then
        raise;
      fErrorException := PPointer(E)^;
      StringToUTF8(E.Message,fErrorMessage);
      result := nil;
    end;
  end;
end;

procedure TSQLDBOracleConnection.Rollback;
begin
  inherited;
  if fTrans=nil then
    raise ESQLDBOracle.Create('Invalid RollBack call');
................................................................................
    // 0:OK, >0:untruncated length, -1:NULL, -2:truncated (length>32KB)
   -1: result := nil; // NULL
    0: exit;
    else LogTruncatedColumn(Column^);
  end;
end;

function TSQLDBOracleStatement.UpdateCount: integer;
begin
  result := 0;
  if fStatement<>nil then
    OCI.AttrGet(fStatement,OCI_HTYPE_STMT,@result,nil,OCI_ATTR_ROW_COUNT,fError);
end;

const