Synopse mORMot Framework API Reference

SynDB.pas unit

Purpose: Abstract database direct access classes
- this unit is a part of the freeware Synopse framework, licensed under a MPL/GPL/LGPL tri-license; version 1.18

1.1. Units used in the SynDB unit

Unit Name	Description
SynCommons	Common functions used by most Synopse projects
SynLog	Logging functions used by Synopse projects
SynTable	Filter/database/cache/buffer/security/search/multithread/OS features

1.2. SynDB class hierarchy

SynDB class hierarchy

1.3. Objects implemented in the SynDB unit

Objects	Description
ESQLDBException	Generic Exception type, as used by the SynDB unit
ESQLDBRemote	Exception raised during remote connection process
ESQLQueryException	Generic Exception type raised by the TQuery class
ISQLDBRows	Generic interface to access a SQL query result rows
ISQLDBStatement	Generic interface to bind to prepared SQL query
TQuery	Class mapping VCL DB TQuery for direct database process
TQueryValue	Pseudo-class handling a TQuery bound parameter or column value
TSQLDBColumnCreate	Used to define how a column to be created
TSQLDBColumnDefine	Used to define a field/column layout in a table schema
TSQLDBColumnProperty	Used to define a field/column layout
TSQLDBConnection	Abstract connection created from TSQLDBConnectionProperties
TSQLDBConnectionProperties	Abstract class used to set Database-related properties
TSQLDBConnectionPropertiesThreadSafe	Connection properties which will implement an internal Thread-Safe connection pool
TSQLDBConnectionThreadSafe	Abstract connection created from TSQLDBConnectionProperties
TSQLDBDefinitionLimitClause	Defines the LIMIT clause to be inserted for a given SQL syntax
TSQLDBIndexDefine	Used to describe extended Index definition of a table schema
TSQLDBLib	Access to a native library
TSQLDBParam	A structure used to store a standard binding parameter
TSQLDBProcColumnDefine	Used to define a parameter/column layout in a stored procedure schema
TSQLDBProxyConnection	Implements an abstract proxy-like virtual connection to a DB engine
TSQLDBProxyConnectionCommandExecute	Structure to embedd all needed parameters to execute a SQL statement
TSQLDBProxyConnectionPropertiesAbstract	Implements a proxy-like virtual connection statement to a DB engine
TSQLDBProxyConnectionProtocol	Server-side implementation of a proxy connection to any SynDB engine
TSQLDBProxyStatement	Implements a proxy-like virtual connection statement to a DB engine
TSQLDBProxyStatementAbstract	Implements a proxy-like virtual connection statement to a DB engine
TSQLDBProxyStatementRandomAccess	Implements a virtual statement with direct data access
TSQLDBRemoteConnectionPropertiesAbstract	Client-side implementation of a remote connection to any SynDB engine
TSQLDBRemoteConnectionPropertiesTest	Fake proxy class for testing the remote connection to any SynDB engine
TSQLDBRemoteConnectionProtocol	Server-side implementation of a remote connection to any SynDB engine
TSQLDBRowVariantType	A custom variant type used to have direct access to a result row content
TSQLDBStatement	Generic abstract class to implement a prepared SQL query
TSQLDBStatementWithParams	Generic abstract class handling prepared statements with binding
TSQLDBStatementWithParamsAndColumns	Generic abstract class handling prepared statements with binding and column description

1.3.1. TSQLDBColumnDefine

TSQLDBColumnDefine = packed record

Used to define a field/column layout in a table schema
- for TSQLDBConnectionProperties.SQLCreate to describe the new table
- for TSQLDBConnectionProperties.GetFields to retrieve the table layout

ColumnIndexed: boolean;

Specify if column is indexed

ColumnLength: PtrInt;

The Column default width (in chars or bytes) of ftUTF8 or ftBlob
- can be set to value <0 for CLOB or BLOB column type, i.e. for a value without any maximal length

ColumnName: RawUTF8;

The Column name

ColumnPrecision: PtrInt;

The Column data precision
- used e.g. for numerical values

ColumnScale: PtrInt;

The Column data scale
- used e.g. for numerical values
- may be -1 if the metadata SQL statement returned NULL

ColumnType: TSQLDBFieldType;

The Column type, as recognized by our SynDB classes
- should not be ftUnknown nor ftNull

ColumnTypeNative: RawUTF8;

The Column type, as retrieved from the database provider
- returned as plain text by GetFields method, to be used e.g. by TSQLDBConnectionProperties.GetFieldDefinitions method
- SQLCreate will check for this value to override the default type

1.3.2. TSQLDBIndexDefine

TSQLDBIndexDefine = packed record

Used to describe extended Index definition of a table schema

Filter: RawUTF8;

Expression for the subset of rows included in the filtered index
- only set for MS SQL - not retrieved for other DB types yet

IncludedColumns: RawUTF8;

Comma separaded list of a nonkey column added to the index by using the CREATE INDEX INCLUDE clause
- only set for MS SQL - not retrieved for other DB types yet

IndexName: RawUTF8;

Name of the index

IsPrimaryKey: boolean;

If Index is part of a PRIMARY KEY constraint
- only set for MS SQL - not retrieved for other DB types yet

IsUnique: boolean;

If Index is unique

IsUniqueConstraint: boolean;

If Index is part of a UNIQUE constraint
- only set for MS SQL - not retrieved for other DB types yet

KeyColumns: RawUTF8;

Comma separated list of indexed column names, in order of their definition

TypeDesc: RawUTF8;

Description of the index type
- for MS SQL possible values are:

 HEAP | CLUSTERED | NONCLUSTERED | XML |SPATIAL

- for Oracle:

 NORMAL | BITMAP | FUNCTION-BASED NORMAL | FUNCTION-BASED BITMAP | DOMAIN

see @http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_1069.htm

1.3.3. TSQLDBProcColumnDefine

TSQLDBProcColumnDefine = packed record

Used to define a parameter/column layout in a stored procedure schema
- for TSQLDBConnectionProperties.GetProcedureParameters to retrieve the stored procedure parameters
- can be extended according to https://msdn.microsoft.com/en-us/library/ms711701(v=vs.85).aspx

ColumnLength: PtrInt;

The Column default width (in chars or bytes) of ftUTF8 or ftBlob
- can be set to value <0 for CLOB or BLOB column type, i.e. for a value without any maximal length

ColumnName: RawUTF8;

The Column name

ColumnParamType: TSQLDBParamInOutType;

Defines the procedure column as a parameter or a result set column

ColumnPrecision: PtrInt;

The Column data precision
- used e.g. for numerical values

ColumnScale: PtrInt;

The Column data scale
- used e.g. for numerical values
- may be -1 if the metadata SQL statement returned NULL

ColumnType: TSQLDBFieldType;

The Column type, as recognized by our SynDB classes
- should not be ftUnknown nor ftNull

ColumnTypeNative: RawUTF8;

The Column type, as retrieved from the database provider
- used e.g. by TSQLDBConnectionProperties.GetProcedureParameters method

1.3.4. TSQLDBColumnProperty

TSQLDBColumnProperty = packed record

Used to define a field/column layout
- for TSQLDBConnectionProperties.SQLCreate to describe the table
- for T*Statement.Execute/Column*() methods to map the IRowSet content

ColumnAttr: PtrUInt;

A general purpose integer value
- for SQLCreate: default width (in WideChars or Bytes) of ftUTF8 or ftBlob; if set to 0, a CLOB or BLOB column type will be created - note that UTF-8 encoding is expected when calculating the maximum column byte size for the CREATE TABLE statement (e.g. for Oracle 1333=4000/3 is used)
- for TOleDBStatement: the offset of this column in the IRowSet data, starting with a DBSTATUSENUM, the data, then its length (for inlined sftUTF8 and sftBlob only)
- for TSQLDBOracleStatement: contains an offset to this column values inside fRowBuffer[] internal buffer
- for TSQLDBDatasetStatement: maps TField pointer value
- for TSQLDBPostgresStatement: contains the column type OID

ColumnDataSize: integer;

May contain the current column size for not FIXEDLENGTH_SQLDBFIELDTYPE
- for SynDBODBC: size (in bytes) in corresponding fColData[]
- TSQLDBProxyStatement: the actual maximum column size

ColumnDataState: TSQLDBStatementGetCol;

May contain the current status of the column value
- for SynDBODBC: state of the latest SQLGetData() call

ColumnName: RawUTF8;

The Column name

ColumnNonNullable: boolean;

Set if the Column must exists (i.e. should not be null)

ColumnType: TSQLDBFieldType;

The Column type, used for storage
- for SQLCreate: should not be ftUnknown nor ftNull
- for TOleDBStatement: should not be ftUnknown
- for SynDBOracle: never ftUnknown, may be ftNull (for SQLT_RSET)

ColumnUnique: boolean;

Set if the Column shall have unique value (add the corresponding constraint)

ColumnValueDBCharSet: integer;

Optional character set encoding for ftUTF8 columns
- for SQLT_STR/SQLT_CLOB (SynDBOracle): equals to the OCI char set

ColumnValueDBForm: byte;

Driver-specific encoding information
- for SynDBOracle: used to store the ftUTF8 column encoding, i.e. for SQLT_CLOB, equals either to SQLCS_NCHAR or SQLCS_IMPLICIT

ColumnValueDBSize: cardinal;

Expected column data size
- for TSQLDBOracleStatement/TOleDBStatement/TODBCStatement: used to store one column size (in bytes)

ColumnValueDBType: smallint;

Internal DB column data type
- for TSQLDBOracleStatement: used to store the DefineByPos() TypeCode, can be SQLT_STR/SQLT_CLOB, SQLT_FLT, SQLT_INT, SQLT_DAT, SQLT_BLOB, SQLT_BIN and SQLT_RSET
- for TSQLDBODBCStatement: used to store the DataType as returned by ODBC.DescribeColW() - use private ODBC_TYPE_TO[ColumnType] to retrieve the marshalled type used during column retrieval
- for TSQLDBFirebirdStatement: used to store XSQLVAR.sqltype
- for TSQLDBDatasetStatement: indicates the TField class type, i.e. 0=TField, 1=TLargeIntField, 2=TWideStringField

ColumnValueInlined: boolean;

Set if the Column data is inlined within the main rows buffer
- for TOleDBStatement: set if column was NOT defined as DBTYPE_BYREF which is the most common case, when column data < 4 KB
- for TSQLDBOracleStatement: FALSE if column is an array of POCILobLocator (SQLT_CLOB/SQLT_BLOB) or POCIStmt (SQLT_RSET)
- for TSQLDBODBCStatement: FALSE if bigger than 255 WideChar (ftUTF8) or 255 bytes (ftBlob)

1.3.5. TSQLDBColumnCreate

TSQLDBColumnCreate = record

Used to define how a column to be created

DBType: TSQLDBFieldType;

The data type
- here, ftUnknown is used for Int32 values, ftInt64 for Int64 values, as expected by TSQLDBFieldTypeDefinition

Name: RawUTF8;

The column name

NonNullable: boolean;

If the column should be non null

PrimaryKey: boolean;

If the column is the ID primary key

Unique: boolean;

If the column should be unique

Width: cardinal;

The width, e.g. for VARCHAR() types

1.3.6. TSQLDBRowVariantType

TSQLDBRowVariantType = class(TSynInvokeableVariantType)

A custom variant type used to have direct access to a result row content
- use ISQLDBRows.RowData method to retrieve such a Variant

1.3.7. ISQLDBRows

ISQLDBRows = interface(IInterface)

Generic interface to access a SQL query result rows
- not all TSQLDBStatement methods are available, but only those to retrieve data from a statement result: the purpose of this interface is to make easy access to result rows, not provide all available features - therefore you only have access to the Step() and Column*() methods

function ColumnBlob(Col: integer): RawByteString; overload;

Return a Column as a blob value of the current Row, first Col is 0

function ColumnBlob(const ColName: RawUTF8): RawByteString; overload;

Return a Column as a blob value of the current Row, from a supplied column name

function ColumnBlobBytes(Col: integer): TBytes; overload;

Return a Column as a blob value of the current Row, first Col is 0

function ColumnBlobBytes(const ColName: RawUTF8): TBytes; overload;

Return a Column as a blob value of the current Row, from a supplied column name

function ColumnCount: integer;

The column/field count of the current Row

function ColumnCurrency(Col: integer): currency; overload;

Return a Column currency value of the current Row, first Col is 0

function ColumnCurrency(const ColName: RawUTF8): currency; overload;

Return a Column currency value of the current Row, from a supplied column name

function ColumnCursor(const ColName: RawUTF8): ISQLDBRows; overload;

Return a special CURSOR Column content as a SynDB result set
- Cursors are not handled internally by mORMot, but some databases (e.g. Oracle) usually use such structures to get data from strored procedures
- such columns are mapped as ftNull internally - so this method is the only one giving access to the data rows

function ColumnCursor(Col: integer): ISQLDBRows; overload;

Return a special CURSOR Column content as a SynDB result set
- Cursors are not handled internally by mORMot, but some databases (e.g. Oracle) usually use such structures to get data from stored procedures
- such columns are mapped as ftNull internally - so this method is the only one giving access to the data rows
- see also BoundCursor() if you want to access a CURSOR out parameter

function ColumnDateTime(const ColName: RawUTF8): TDateTime; overload;

Return a Column floating point value of the current Row, from a supplied column name

function ColumnDateTime(Col: integer): TDateTime; overload;

Return a Column floating point value of the current Row, first Col is 0

function ColumnDouble(const ColName: RawUTF8): double; overload;

Return a Column floating point value of the current Row, from a supplied column name

function ColumnDouble(Col: integer): double; overload;

Return a Column floating point value of the current Row, first Col is 0

function ColumnIndex(const aColumnName: RawUTF8): integer;

Returns the Column index of a given Column name
- Columns numeration (i.e. Col value) starts with 0
- returns -1 if the Column name is not found (via case insensitive search)

function ColumnInt(const ColName: RawUTF8): Int64; overload;

Return a Column integer value of the current Row, from a supplied column name

function ColumnInt(Col: integer): Int64; overload;

Return a Column integer value of the current Row, first Col is 0

function ColumnName(Col: integer): RawUTF8;

The Column name of the current Row
- Columns numeration (i.e. Col value) starts with 0
- it's up to the implementation to ensure than all column names are unique

function ColumnNull(Col: integer): boolean;

Returns TRUE if the column contains NULL

function ColumnString(const ColName: RawUTF8): string; overload;

Return a Column text value as generic VCL string of the current Row, from a supplied column name

function ColumnString(Col: integer): string; overload;

Return a Column text value as generic VCL string of the current Row, first Col is 0

function ColumnTimestamp(Col: integer): TTimeLog; overload;

Return a column date and time value of the current Row, first Col is 0

function ColumnTimestamp(const ColName: RawUTF8): TTimeLog; overload;

Return a column date and time value of the current Row, from a supplied column name

function ColumnToVariant(Col: integer; var Value: Variant): TSQLDBFieldType; overload;

Return a Column as a variant, first Col is 0
- this default implementation will call Column*() method above
- a ftUTF8 TEXT content will be mapped into a generic WideString variant for pre-Unicode version of Delphi, and a generic UnicodeString (=string) since Delphi 2009: you may not loose any data during charset conversion
- a ftBlob BLOB content will be mapped into a TBlobData AnsiString variant

function ColumnType(Col: integer; FieldSize: PInteger=nil): TSQLDBFieldType;

The Column type of the current Row
- FieldSize can be set to store the size in chars of a ftUTF8 column (0 means BLOB kind of TEXT column)

function ColumnUTF8(const ColName: RawUTF8): RawUTF8; overload;

Return a Column UTF-8 encoded text value of the current Row, from a supplied column name

function ColumnUTF8(Col: integer): RawUTF8; overload;

Return a Column UTF-8 encoded text value of the current Row, first Col is 0

function ColumnVariant(Col: integer): Variant; overload;

Return a Column as a variant
- a ftUTF8 TEXT content will be mapped into a generic WideString variant for pre-Unicode version of Delphi, and a generic UnicodeString (=string) since Delphi 2009: you may not loose any data during charset conversion
- a ftBlob BLOB content will be mapped into a TBlobData AnsiString variant

function ColumnVariant(const ColName: RawUTF8): Variant; overload;

Return a Column as a variant, from a supplied column name

function FetchAllAsJSON(Expanded: boolean; ReturnedRowCount: PPtrInt=nil): RawUTF8;

Return all rows content as a JSON string
- JSON data is retrieved with UTF-8 encoding
- if Expanded is true, JSON data is an array of objects, for direct use with any Ajax or .NET client:

 [ {"col1":val11,"col2":"val12"},{"col1":val21,... ]

- if Expanded is false, JSON data is serialized (used in TSQLTableJSON)

 { "FieldCount":1,"Values":["col1","col2",val11,"val12",val21,..] }

- 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 FetchAllToBinary(Dest: TStream; MaxRowCount: cardinal=0; DataRowPosition: PCardinalDynArray=nil): cardinal;

Append all rows content as binary stream
- will save the column types and name, then every data row in optimized binary format (faster and smaller than JSON)
- you can specify a LIMIT for the data extent (default 0 meaning all data)
- generates the format expected by TSQLDBProxyStatement

function FetchAllToJSON(JSON: TStream; Expanded: boolean): PtrInt;

Append all rows content as a JSON stream
- JSON data is added to the supplied TStream, with UTF-8 encoding
- if Expanded is true, JSON data is an array of objects, for direct use with any Ajax or .NET client:

 [ {"col1":val11,"col2":"val12"},{"col1":val21,... ]

- if Expanded is false, JSON data is serialized (used in TSQLTableJSON)

 { "FieldCount":1,"Values":["col1","col2",val11,"val12",val21,..] }

function GetColumnVariant(const ColName: RawUTF8): Variant;

Return a Column as a variant, from a supplied column name
- since a property getter can't be an overloaded method, we define one for the Column[] property

function Instance: TSQLDBStatement;

Return the associated statement instance

function RowData: Variant;

Create a TSQLDBRowVariantType able to access any field content via late binding
- i.e. you can use Data.Name to access the 'Name' column of the current row
- this Variant will point to the corresponding TSQLDBStatement instance, so it's not necessary to retrieve its value for each row; but once the associated ISQLDBRows instance is released, you won't be able to access its data - use RowDocVariant instead
- typical use is:

 var Row: Variant;
 (...)
  with MyConnProps.Execute('select * from table where name=?',[aName]) do begin
    Row := RowData;
    while Step do
      writeln(Row.FirstName,Row.BirthDate);
    ReleaseRows;
  end;

function Step(SeekFirst: boolean=false): boolean;

After a prepared statement has been prepared returning a ISQLDBRows interface, this method must be called one or more times to evaluate it
- you shall call this method before calling any Column*() methods
- return TRUE on success, with data ready to be retrieved by Column*()
- return FALSE if no more row is available (e.g. if the SQL statement is not a SELECT but an UPDATE or INSERT command)
- access the first or next row of data from the SQL Statement result: if SeekFirst is TRUE, will put the cursor on the first row of results, otherwise, it will fetch one row of data, to be called within a loop
- should raise an Exception on any error
- typical use may be:

 var Customer: Variant;
 begin
   with Props.Execute( 'select * from Sales.Customer where AccountNumber like ?',
       ['AW000001%'],@Customer) do begin
     while Step do //  loop through all matching data rows
       assert(Copy(Customer.AccountNumber,1,8)='AW000001');
     ReleaseRows;
   end;
 end;

procedure ColumnBlobFromStream(Col: integer; Stream: TStream); overload;

Write a blob Column into the Stream parameter
- expected to be used with 'SELECT .. FOR UPDATE' locking statements

procedure ColumnBlobFromStream(const ColName: RawUTF8; Stream: TStream); overload;

Write a blob Column into the Stream parameter

procedure ColumnBlobToStream(Col: integer; Stream: TStream); overload;

Read a blob Column into the Stream parameter

procedure ColumnBlobToStream(const ColName: RawUTF8; Stream: TStream); overload;

Read a blob Column into the Stream parameter

procedure ColumnToSQLVar(Col: Integer; var Value: TSQLVar; var Temp: RawByteString);

Return a Column as a TSQLVar value, first Col is 0
- the specified Temp variable will be used for temporary storage of svtUTF8/svtBlob values

procedure ReleaseRows;

Release cursor memory and resources once Step loop is finished
- this method call is optional, but is better be used if the ISQLDBRows statement from taken from cache, and returned a lot of content which may still be in client (and server) memory
- will also free all temporary memory used for optional logging

procedure RowDocVariant(out aDocument: variant; aOptions: TDocVariantOptions=JSON_OPTIONS_FAST);

Create a TDocVariant custom variant containing all columns values
- will create a "fast" TDocVariant object instance with all fields

property Column[const ColName: RawUTF8]: Variant read GetColumnVariant;

Return a Column as a variant
- this default property can be used to write simple code like this:

 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']));
   I.ReleaseRows;
 end;

- of course, using a variant and a column name will be a bit slower than direct access via the Column*() dedicated methods, but resulting code is fast in practice

1.3.8. ISQLDBStatement

ISQLDBStatement = interface(ISQLDBRows)

Generic interface to bind to prepared SQL query
- inherits from ISQLDBRows, so gives access to the result columns data
- not all TSQLDBStatement methods are available, but only those to bind parameters and retrieve data after execution
- reference counting mechanism of this interface will feature statement cache (if available) for NewThreadSafeStatementPrepared() or PrepareInlined()

function BoundCursor(Param: Integer): ISQLDBRows;

Return a special CURSOR parameter content as a SynDB result set
- this method is not about a column, but a parameter defined with BindCursor() before method execution
- Cursors are not handled internally by mORMot, but some databases (e.g. Oracle) usually use such structures to get data from strored procedures
- this method allow direct access to the data rows after execution

function ParamToVariant(Param: Integer; var Value: Variant; CheckIsOutParameter: boolean=true): TSQLDBFieldType;

Retrieve the parameter content, after SQL execution
- the leftmost SQL parameter has an index of 1
- to be used e.g. with stored procedures:

 query :=  'BEGIN TEST_PKG.DUMMY(?, ?, ?, ?, ?); END;';
 stmt := Props.NewThreadSafeStatementPrepared(query, false);
 stmt.Bind(1, in1, paramIn);
 stmt.BindTextU(2, in2, paramIn);
 stmt.BindTextU(3, in3, paramIn);
 stmt.BindTextS(4, '', paramOut); //  to be retrieved with out1: string
 stmt.Bind(5, 0, paramOut);       //  to be retrieved with out2: integer
 stmt.ExecutePrepared;
 stmt.ParamToVariant(4, out1, true);
 stmt.ParamToVariant(5, out2, true);

- the parameter should have been bound with IO=paramOut or IO=paramInOut if CheckIsOutParameter is TRUE

function UpdateCount: Integer;

Gets a number of updates made by latest executed statement

procedure Bind(Param: Integer; Value: Int64; IO: TSQLDBParamInOutType=paramIn); overload;

Bind an integer value to a parameter
- the leftmost SQL parameter has an index of 1

procedure Bind(Param: Integer; const Data: TSQLVar; IO: TSQLDBParamInOutType=paramIn); overload;

Bind one TSQLVar value
- the leftmost SQL parameter has an index of 1

procedure Bind(const Params: array of const; IO: TSQLDBParamInOutType=paramIn); overload;

procedure Bind(Param: Integer; ParamType: TSQLDBFieldType; const Value: RawUTF8; ValueAlreadyUnquoted: boolean; IO: TSQLDBParamInOutType=paramIn); overload;

Bind one RawUTF8 encoded value
- the leftmost SQL parameter has an index of 1
- the value should match the BindArray() format, i.e. be stored as in SQL (i.e. number, 'quoted string', 'YYYY-MM-DD hh:mm:ss', null)

procedure Bind(Param: Integer; Value: double; IO: TSQLDBParamInOutType=paramIn); overload;

Bind a double value to a parameter
- the leftmost SQL parameter has an index of 1

procedure BindArray(Param: Integer; const Values: array of double); overload;

Bind an array of double values to a parameter
- the leftmost SQL parameter has an index of 1
- this default implementation will raise an exception if the engine does not support array binding

procedure BindArray(Param: Integer; const Values: array of Int64); overload;

Bind an array of integer values to a parameter
- the leftmost SQL parameter has an index of 1
- this default implementation will raise an exception if the engine does not support array binding

procedure BindArray(Param: Integer; const Values: array of RawUTF8); overload;

Bind an array of RawUTF8 values to a parameter
- the leftmost SQL parameter has an index of 1
- values are stored as in SQL (i.e. 'quoted string')
- this default implementation will raise an exception if the engine does not support array binding

procedure BindArray(Param: Integer; ParamType: TSQLDBFieldType; const Values: TRawUTF8DynArray; ValuesCount: integer); overload;

Bind an array of values to a parameter
- the leftmost SQL parameter has an index of 1
- values are stored as in SQL (i.e. number, 'quoted string', 'YYYY-MM-DD hh:mm:ss', null)
- this default implementation will raise an exception if the engine does not support array binding

procedure BindArrayCurrency(Param: Integer; const Values: array of currency);

Bind an array of currency values to a parameter
- the leftmost SQL parameter has an index of 1
- this default implementation will raise an exception if the engine does not support array binding

procedure BindArrayDateTime(Param: Integer; const Values: array of TDateTime);

procedure BindBlob(Param: Integer; Data: pointer; Size: integer; IO: TSQLDBParamInOutType=paramIn); overload;

Bind a Blob buffer to a parameter
- the leftmost SQL parameter has an index of 1

procedure BindBlob(Param: Integer; const Data: RawByteString; IO: TSQLDBParamInOutType=paramIn); overload;

Bind a Blob buffer to a parameter
- the leftmost SQL parameter has an index of 1

procedure BindCurrency(Param: Integer; Value: currency; IO: TSQLDBParamInOutType=paramIn); overload;

Bind a currency value to a parameter
- the leftmost SQL parameter has an index of 1

procedure BindCursor(Param: integer);

Bind a special CURSOR parameter to be returned as a SynDB result set
- Cursors are not handled internally by mORMot, but some databases (e.g. Oracle) usually use such structures to get data from strored procedures
- such parameters are mapped as ftUnknown
- use BoundCursor() method to retrieve the corresponding ISQLDBRows after execution of the statement

procedure BindDateTime(Param: Integer; Value: TDateTime; IO: TSQLDBParamInOutType=paramIn); overload;

Bind a TDateTime value to a parameter
- the leftmost SQL parameter has an index of 1

procedure BindFromRows(const Fields: TSQLDBFieldTypeDynArray; Rows: TSQLDBStatement);

Bind an array of fields from an existing SQL statement
- can be used e.g. after ColumnsToSQLInsert() method call for fast data conversion between tables

procedure BindNull(Param: Integer; IO: TSQLDBParamInOutType=paramIn; BoundType: TSQLDBFieldType=ftNull);

Bind a NULL value to a parameter
- the leftmost SQL parameter has an index of 1
- some providers (e.g. OleDB during MULTI INSERT statements) expect the proper column type to be set in BoundType, even for NULL values

procedure BindTextP(Param: Integer; Value: PUTF8Char; IO: TSQLDBParamInOutType=paramIn); overload;

Bind a UTF-8 encoded buffer text (#0 ended) to a parameter
- the leftmost SQL parameter has an index of 1

procedure BindTextS(Param: Integer; const Value: string; IO: TSQLDBParamInOutType=paramIn); overload;

Bind a UTF-8 encoded string to a parameter
- the leftmost SQL parameter has an index of 1

procedure BindTextU(Param: Integer; const Value: RawUTF8; IO: TSQLDBParamInOutType=paramIn); overload;

Bind a UTF-8 encoded string to a parameter
- the leftmost SQL parameter has an index of 1

procedure BindTextW(Param: Integer; const Value: WideString; IO: TSQLDBParamInOutType=paramIn); overload;

Bind a UTF-8 encoded string to a parameter
- the leftmost SQL parameter has an index of 1

procedure BindVariant(Param: Integer; const Data: Variant; DataIsBlob: boolean; IO: TSQLDBParamInOutType=paramIn);

Bind a Variant value to a parameter
- the leftmost SQL parameter has an index of 1
- will call all virtual Bind*() methods from the Data type
- if DataIsBlob is TRUE, will call BindBlob(RawByteString(Data)) instead of BindTextW(WideString(Variant)) - used e.g. by TQuery.AsBlob/AsBytes

procedure ExecutePrepared;

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 ExecutePreparedAndFetchAllAsJSON(Expanded: boolean; out JSON: RawUTF8);

Execute a prepared SQL statement and return all rows content as a JSON string
- JSON data is retrieved with UTF-8 encoding
- if Expanded is true, JSON data is an array of objects, for direct use with any Ajax or .NET client:

 [ {"col1":val11,"col2":"val12"},{"col1":val21,... ]

- if Expanded is false, JSON data is serialized (used in TSQLTableJSON)

 { "FieldCount":1,"Values":["col1","col2",val11,"val12",val21,..] }

- BLOB field value is saved as Base64, in the '"\uFFF0base64encodedbinary"' format and contains true BLOB data

property ForceBlobAsNull: boolean read GetForceBlobAsNull write SetForceBlobAsNull;

If set, any BLOB field won't be retrieved, and forced to be null
- this may be used to speed up fetching the results for SQL requests with * statements

property ForceDateWithMS: boolean read GetForceDateWithMS write SetForceDateWithMS;

If set, any ftDate field will contain the milliseconds information when serialized into ISO-8601 text
- this setting is private to each statement, since may vary depending on data definition (e.g. ORM TDateTime/TDateTimeMS)

1.3.9. TSQLDBDefinitionLimitClause

TSQLDBDefinitionLimitClause = record

Defines the LIMIT clause to be inserted for a given SQL syntax
- used by TSQLDBDefinitionLimitClause and SQLLimitClause() method

1.3.10. TSQLDBConnectionProperties

TSQLDBConnectionProperties = class(TObject)

Abstract class used to set Database-related properties
- handle e.g. the Database server location and connection parameters (like UserID and password)
- should also provide some Database-specific generic SQL statement creation (e.g. how to create a Table), to be used e.g. by the mORMot layer
- this class level will handle a single "main connection" - you may inherit from TSQLDBConnectionThreadSafe to maintain one connection per thread

constructor Create(const aServerName, aDatabaseName, aUserID, aPassWord: RawUTF8); virtual;

Initialize the properties
- children may optionaly handle the fact that no UserID or Password is supplied here, by displaying a corresponding Dialog box

destructor Destroy; override;

Release related memory, and close MainConnection

class function ClassFrom(aDefinition: TSynConnectionDefinition): TSQLDBConnectionPropertiesClass;

Retrieve the registered class from the aDefinition.Kind string

function ColumnTypeNativeToDB(const aNativeType: RawUTF8; aScale: integer): TSQLDBFieldType; virtual;

Convert a textual column data type, as retrieved e.g. from SQLGetField, into our internal primitive types
- default implementation will always return ftUTF8

class function CreateFrom(aDefinition: TSynConnectionDefinition): TSQLDBConnectionProperties; virtual;

Create a new TSQLDBConnectionProperties instance from the stored values

class function CreateFromFile(const aJSONFile: TFileName; aKey: cardinal=0): TSQLDBConnectionProperties;

Create a new TSQLDBConnectionProperties instance from a JSON file
- as previously serialized with TSQLDBConnectionProperties.DefinitionToFile
- you can specify a custom Key, if the default is not safe enough for you

class function CreateFromJSON(const aJSONDefinition: RawUTF8; aKey: cardinal=0): TSQLDBConnectionProperties; virtual;

Create a new TSQLDBConnectionProperties instance from a JSON content
- as previously serialized with TSQLDBConnectionProperties.DefinitionToJSON
- you can specify a custom Key, if the default is not safe enough for you

function DefinitionToJSON(Key: cardinal=0): RawUTF8; virtual;

Save the properties into a JSON file
- you could use TSQLDBConnectionPropertiesDescription.CreateFromJSON() later on to instantiate the proper TSQLDBConnectionProperties class
- you can specify a custom Key, if the default is not enough for you

class function EngineName: RawUTF8;

Return the database engine name, as computed from the class name
- 'TSQLDBConnectionProperties' will be trimmed left side of the class name

function Execute(const aSQL: RawUTF8; const Params: array of const ; RowsVariant: PVariant=nil; ForceBlobAsNull: boolean=false): ISQLDBRows;

Execute a SQL query, returning a statement interface instance to retrieve the result rows corresponding to the supplied SELECT statement
- 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']));
   I.ReleaseRows;
 end;

- if RowsVariant is set, you can use it to row column access via late binding, as such:

 procedure WriteFamily(const aName: RawUTF8);
 var R: Variant;
 begin
   with MyConnProps.Execute('select * from table where name=?',[aName],@R) do begin
     while Step do
       writeln(R.FirstName,' ',DateToStr(R.BirthDate));
     ReleaseRows;
   end;
 end;

- you can any BLOB field to be returned as null with the ForceBlobAsNull optional parameter

function ExecuteInlined(const SQLFormat: RawUTF8; const Args: array of const; ExpectResults: Boolean): ISQLDBRows; overload;

Create, prepare, bound inlined parameters and execute a thread-safe statement
- overloaded method using FormatUTF8() and inlined parameters

function ExecuteInlined(const aSQL: RawUTF8; ExpectResults: Boolean): ISQLDBRows; overload;

Create, prepare, bound inlined parameters and execute a thread-safe statement
- this implementation will call the NewThreadSafeStatement virtual method, then bound inlined parameters as :(1234): and call its Execute method
- raise an exception on error

function ExecuteNoResult(const aSQL: RawUTF8; const Params: array of const): integer;

Execute a SQL query, without returning any rows
- can be used to launch INSERT, DELETE or UPDATE statement, e.g.
- will call NewThreadSafeStatement method to retrieve a thread-safe statement instance, then run the corresponding Execute() method
- return the number of modified rows, i.e. the ISQLDBStatement.UpdateCount value (or 0 if the DB driver does not supply this value)

class function GetFieldDefinition(const Column: TSQLDBColumnDefine): RawUTF8;

Get one field/column definition as text
- return column type as 'Name [Type Length Precision Scale]'

class function GetFieldORMDefinition(const Column: TSQLDBColumnDefine): RawUTF8;

Get one field/column definition as text, targeting a TSQLRecord published property
- return e.g. property type information as:

 'Name: RawUTF8 read fName write fName index 20;';

function GetForeignKey(const aTableName, aColumnName: RawUTF8): RawUTF8;

Retrieve a foreign key for a specified table and column
- first time it is called, it will retrieve all foreign keys from the remote database using virtual protected GetForeignKeys method into the protected fForeignKeys list: this may be slow, depending on the database access (more than 10 seconds waiting is possible)
- any further call will use this internal list, so response will be immediate
- the whole foreign key list is shared by all connections

function IsCachable(P: PUTF8Char): boolean; virtual;

Determine if the SQL statement can be cached
- used by TSQLDBConnection.NewStatementPrepared() for handling cache

function IsSQLKeyword(aWord: RawUTF8): boolean; overload;

Check if the supplied text word is not a keyword for the current database engine
- just a wrapper around the overloaded class function

class function IsSQLKeyword(aDB: TSQLDBDefinition; aWord: RawUTF8): boolean; overload; virtual;

Check if the supplied text word is not a keyword for a given database engine

function NewConnection: TSQLDBConnection; virtual;

Create a new connection
- call this method if the shared MainConnection is not enough (e.g. for multi-thread access)
- the caller is responsible of freeing this instance

function NewThreadSafeStatement: TSQLDBStatement;

Create a new thread-safe statement
- this method will call ThreadSafeConnection.NewStatement

function NewThreadSafeStatementPrepared(const SQLFormat: RawUTF8; const Args: array of const; 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 (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)
- 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 PrepareInlined(const SQLFormat: RawUTF8; const Args: array of const; ExpectResults: Boolean): ISQLDBStatement; overload;

Create, prepare and bound inlined parameters to a thread-safe statement
- overloaded method using FormatUTF8() and inlined parameters
- consider using ExecuteInlined() for direct execution

function PrepareInlined(const aSQL: RawUTF8; ExpectResults: Boolean): ISQLDBStatement; overload;

Create, prepare and bound inlined parameters to a thread-safe statement
- this implementation will call the NewThreadSafeStatement virtual method, then bound inlined parameters as :(1234): and return the resulting statement
- raise an exception on error
- consider using ExecuteInlined() for direct execution

function SharedTransaction(SessionID: cardinal; action: TSQLDBSharedTransactionAction): TSQLDBConnection; virtual;

Handle a transaction process common to all associated connections
- could be used to share a single transaction among several connections, or to run nested transactions even on DB engines which do not allow them
- will use a simple reference counting mechanism to allow nested transactions, identified by a session identifier
- will fail if the same connection is not used for the whole process, which would induce a potentially incorrect behavior
- returns the connection corresponding to the session, nil on error

function SQLAddColumn(const aTableName: RawUTF8; const aField: TSQLDBColumnCreate): RawUTF8; virtual;

Returns the SQL statement used to add a column to a Table
- should return the SQL "ALTER TABLE" statement needed to add a column to an existing table
- this default implementation will use internal fSQLCreateField and fSQLCreateFieldMax protected values, which contains by default the ANSI SQL Data Types and maximum 1000 inlined WideChars: inherited classes may change the default fSQLCreateField* content or override this method

function SQLAddIndex(const aTableName: RawUTF8; const aFieldNames: array of RawUTF8; aUnique: boolean; aDescending: boolean=false; const aIndexName: RawUTF8=''): RawUTF8; virtual;

Returns the SQL statement used to add an index to a Table
- should return the SQL "CREATE INDEX" statement needed to add an index to the specified column names of an existing table
- index will expect UNIQUE values in the specified columns, if Unique parameter is set to true
- this default implementation will return the standard SQL statement, i.e. 'CREATE [UNIQUE] INDEX index_name ON table_name (column_name[s])'

function SQLCreate(const aTableName: RawUTF8; const aFields: TSQLDBColumnCreateDynArray; aAddID: boolean): RawUTF8; virtual;

Returns the SQL statement used to create a Table
- should return the SQL "CREATE" statement needed to create a table with the specified field/column names and types
- if aAddID is TRUE, "ID Int64 PRIMARY KEY" column is added as first, and will expect the ORM to create an unique RowID value sent at INSERT (could use "select max(ID) from table" to retrieve the last value) - note that 'ID' is used instead of 'RowID' since it fails on Oracle e.g.
- this default implementation will use internal fSQLCreateField and fSQLCreateFieldMax protected values, which contains by default the ANSI SQL Data Types and maximum 1000 inlined WideChars: inherited classes may change the default fSQLCreateField* content or override this method

function SQLCreateDatabase(const aDatabaseName: RawUTF8; aDefaultPageSize: integer=0): RawUTF8; virtual;

SQL statement to create the corresponding database
- this default implementation will only handle dFirebird by now

function SQLDateToIso8601Quoted(DateTime: TDateTime): RawUTF8; virtual;

Convert a TDateTime into a ISO-8601 encoded time and date, as expected by the database provider
- e.g. SQLite3, DB2 and PostgreSQL will use non-standard ' ' instead of 'T'

function SQLFullTableName(const aTableName: RawUTF8): RawUTF8; virtual;

Return the fully qualified SQL table name
- will use ForcedSchemaName property (if applying), or return aTableName
- you can override this method to force the expected format

function SQLIso8601ToDate(const Iso8601: RawUTF8): RawUTF8; virtual;

Convert an ISO-8601 encoded time and date into a date appropriate to be pasted in the SQL request
- this default implementation will return the quoted ISO-8601 value, i.e. 'YYYY-MM-DDTHH:MM:SS' (as expected by Microsoft SQL server e.g.)
- returns to_date('....','YYYY-MM-DD HH24:MI:SS') for Oracle

function SQLLimitClause(AStmt: TSynTableStatement): TSQLDBDefinitionLimitClause; virtual;

Returns the information to adapt the LIMIT # clause in the SQL SELECT statement to a syntax matching the underlying DBMS
- e.g. TSQLRestStorageExternal.AdaptSQLForEngineList() calls this to let TSQLRestServer.URI by-pass virtual table mechanism

function SQLSelectAll(const aTableName: RawUTF8; const aFields: TSQLDBColumnDefineDynArray; aExcludeTypes: TSQLDBFieldTypes): RawUTF8; virtual;

Used to compute a SELECT statement for the given fields
- should return the SQL "SELECT ... FROM ..." statement to retrieve the specified column names of an existing table
- by default, all columns specified in aFields[] will be available: it will return "SELECT * FROM TableName"
- but if you specify a value in aExcludeTypes, it will compute the matching column names to ignore those kind of content (e.g. [stBlob] to save time and space)

function SQLTableName(const aTableName: RawUTF8): RawUTF8; virtual;

Return a SQL table name with quotes if necessary
- can be used e.g. with SELECT statements
- you can override this method to force the expected format

function ThreadSafeConnection: TSQLDBConnection; virtual;

Get a thread-safe connection
- this default implementation will return the MainConnection shared instance, so the provider should be thread-safe by itself
- TSQLDBConnectionPropertiesThreadSafe will implement a per-thread connection pool, via an internal TSQLDBConnection pool, per thread if necessary (e.g. for OleDB, which expect one TOleDBConnection instance per thread)

procedure ClearConnectionPool; virtual;

Release all existing connections
- can be called e.g. after a DB connection problem, to purge the connection pool, and allow automatic reconnection
- is called automatically if ConnectionTimeOutMinutes property is set
- warning: no connection shall still be used on the background (e.g. in multi-threaded applications), or some unexpected border effects may occur

procedure DefinitionTo(Definition: TSynConnectionDefinition); virtual;

Save the properties into a persistent storage object
- you can use TSQLDBConnectionPropertiesDescription.CreateFrom() later on to instantiate the proper TSQLDBConnectionProperties class
- current Definition.Key value will be used for the password encryption

procedure DefinitionToFile(const aJSONFile: TFileName; Key: cardinal=0);

Save the properties into a JSON file
- you could use TSQLDBConnectionPropertiesDescription.CreateFromFile() later on to instantiate the proper TSQLDBConnectionProperties class
- you can specify a custom Key, if the default is not enough for you

procedure GetFieldDefinitions(const aTableName: RawUTF8; out Fields: TRawUTF8DynArray; WithForeignKeys: boolean);

Get all field/column definition for a specified Table as text
- call the GetFields method and retrieve the column field name and type as 'Name [Type Length Precision Scale]'
- if WithForeignKeys is set, will add external foreign keys as '% tablename'

procedure GetFields(const aTableName: RawUTF8; out Fields: TSQLDBColumnDefineDynArray); virtual;

Retrieve the column/field layout of a specified table
- this default implementation will use protected SQLGetField virtual method to retrieve the field names and properties
- used e.g. by GetFieldDefinitions
- will call ColumnTypeNativeToDB protected virtual method to guess the each mORMot TSQLDBFieldType

procedure GetIndexes(const aTableName: RawUTF8; out Indexes: TSQLDBIndexDefineDynArray); virtual;

Retrieve the advanced indexed information of a specified Table
- this default implementation will use protected SQLGetIndex virtual method to retrieve the index names and properties
- currently only MS SQL and Oracle are supported

procedure GetProcedureNames(out Procedures: TRawUTF8DynArray); virtual;

Retrieve a list of stored procedure names from current connection

procedure GetProcedureParameters(const aProcName: RawUTF8; out Parameters: TSQLDBProcColumnDefineDynArray); virtual;

Retrieve procedure input/output parameter information
- aProcName: stored procedure name to retrieve parameter infomation.
- Parameters: parameter list info (name, datatype, direction, default)

procedure GetTableNames(out Tables: TRawUTF8DynArray); virtual;

Get all table names
- this default implementation will use protected SQLGetTableNames virtual method to retrieve the table names

procedure GetViewNames(out Views: TRawUTF8DynArray); virtual;

Get all view names
- this default implementation will use protected SQLGetViewNames virtual method to retrieve the view names

procedure SQLSplitProcedureName(const aProcName: RawUTF8; out Owner, Package, ProcName: RawUTF8); virtual;

Split a procedure name to its OWNER.PACKAGE.PROCEDURE full name (if applying)
- will use ForcedSchemaName property (if applying), or the OWNER. already available within the supplied table name

procedure SQLSplitTableName(const aTableName: RawUTF8; out Owner, Table: RawUTF8); virtual;

Split a table name to its OWNER.TABLE full name (if applying)
- will use ForcedSchemaName property (if applying), or the OWNER. already available within the supplied table name

property BatchMaxSentAtOnce: integer read fBatchMaxSentAtOnce write fBatchMaxSentAtOnce;

The maximum number of rows to be transmitted at once for batch sending
- e.g. Oracle handles array DML operation with iters <= 32767 at best
- if OnBatchInsert points to MultipleValuesInsert(), this value is ignored, and the maximum number of parameters is guessed per DBMS type

property BatchSendingAbilities: TSQLDBStatementCRUDs read fBatchSendingAbilities;

The abilities of the database for batch sending
- e.g. Oracle will handle array DML binds, or MS SQL bulk insert

property ConnectionTimeOutMinutes: cardinal read GetConnectionTimeOutMinutes write SetConnectionTimeOutMinutes;

Specify a maximum period of inactivity after which all connections will be flushed and recreated, to avoid potential broken connections issues
- in practice, recreating the connections after a while is safe and won't slow done the process - on the contrary, it may help reducing the consumpted resources, and stabilize long running n-Tier servers
- ThreadSafeConnection method will check for the last activity on this TSQLDBConnectionProperties instance, then call ClearConnectionPool to release all active connections if the idle time elapsed was too long
- warning: no connection shall still be used on the background (e.g. in multi-threaded applications), or some unexpected issues may occur - for instance, ensure that your mORMot ORM server runs all its statements in blocking mode for both read and write:

 aServer.AcquireExecutionMode[execORMGet] := am***;
 aServer.AcquireExecutionMode[execORMWrite] := am***;

here, safe blocking am*** modes are any mode but amUnlocked, i.e. either amLocked, amBackgroundThread or amMainThread

property DatabaseName: RawUTF8 read fDatabaseName;

The associated database name, as specified at creation
- not published, for security reasons (may be serialized otherwise)
- DatabaseNameSafe will be published, and delete any matching PasswordValue in DatabaseName

property DatabaseNameSafe: RawUTF8 read GetDatabaseNameSafe;

The associated database name, safely trimmed from the password
- would replace any matching Password value content from DatabaseName by '***' for security reasons, e.g. before serialization

property DateTimeFirstChar: AnsiChar read fDateTimeFirstChar write fDateTimeFirstChar;

Customize the ISO-8601 text format expected by the database provider
- is 'T' by default, as expected by the ISO-8601 standard
- will be changed e.g. for PostgreSQL, which expects ' ' instead
- as used by SQLDateToIso8601Quoted() and BindArray()

property DBMS: TSQLDBDefinition read GetDBMS;

The remote DBMS type, as stated by the inheriting class itself, or retrieved at connecton time (e.g. for ODBC)

property DBMSEngineName: RawUTF8 read GetDBMSName;

The remote DBMS type name, retrieved as text from the DBMS property

property Engine: RawUTF8 read fEngineName;

Return the database engine name, as computed from the class name
- 'TSQLDBConnectionProperties' will be trimmed left side of the class name

property ExecuteWhenConnected: TRawUTF8DynArray read fExecuteWhenConnected write fExecuteWhenConnected;

SQL statements what will be executed for each new connection usage scenarios examples:
- Oracle: force case-insensitive like

  ['ALTER SESSION SET NLS_COMP=LINGUISTIC', 'ALTER SESSION SET NLS_SORT=BINARY_CI']

- Postgres: disable notices and warnings

  ['SET client_min_messages to ERROR']

- SQLite3: turn foreign keys ON

  ['PRAGMA foreign_keys = ON']

property FilterTableViewSchemaName: boolean read fFilterTableViewSchemaName write fFilterTableViewSchemaName;

If GetTableNames/GetViewNames should only return the table names starting with 'ForcedSchemaName.' prefix

property ForcedSchemaName: RawUTF8 read fForcedSchemaName write fForcedSchemaName;

An optional Schema name to be used for SQLGetField() instead of UserID
- by default, UserID will be used as schema name, if none is specified (i.e. if table name is not set as SCHEMA.TABLE)
- depending on the DBMS identified, the class may also set automatically the default 'dbo' for MS SQL or 'public' for PostgreSQL
- you can set a custom schema to be used instead

property ForeignKeysData: RawByteString read GetForeignKeysData write SetForeignKeysData;

Can be used to store the fForeignKeys[] data in an external BLOB
- since GetForeignKeys can be (somewhat) slow, could save a lot of time

property LoggedSQLMaxSize: integer read fLoggedSQLMaxSize write fLoggedSQLMaxSize;

The maximum size, in bytes, of logged SQL statements
- setting 0 will log statement and parameters with no size limit
- setting -1 will log statement without any parameter value (just ?)
- setting any value >0 will log statement and parameters up to the number of bytes (default set to 2048 to log up to 2KB per statement)

property LogSQLStatementOnException: boolean read fLogSQLStatementOnException write fLogSQLStatementOnException;

Allow to log the SQL statement when any low-level ESQLDBException is raised

property MainConnection: TSQLDBConnection read GetMainConnection;

Return a shared connection, corresponding to the given database
- call the ThreadSafeConnection method instead e.g. for multi-thread access, or NewThreadSafeStatement for direct retrieval of a new statement

property OnBatchInsert: TOnBatchInsert read fOnBatchInsert write fOnBatchInsert;

You can define a callback method able to handle multiple INSERT
- may execute e.g. INSERT with multiple VALUES (like MySQL, MSSQL, NexusDB, PostgreSQL or SQlite3), as defined by MultipleValuesInsert() callback

property OnProcess: TOnSQLDBProcess read fOnProcess write fOnProcess;

This event handler will be called during all process
- can be used e.g. to change the desktop cursor, or be notified on connection/disconnection/reconnection
- you can override this property directly in the TSQLDBConnection

property OnStatementInfo: TOnSQLDBInfo read fOnStatementInfo write fOnStatementInfo;

This event handler will be called when statements trigger some low-level information

property PassWord: RawUTF8 read fPassWord;

The associated User Password, as specified at creation
- not published, for security reasons (may be serialized otherwise)

property ReconnectAfterConnectionError: boolean read fReconnectAfterConnectionError write fReconnectAfterConnectionError;

Intercept connection errors at statement preparation and try to reconnect
- i.e. detect TSQLDBConnection.LastErrorWasAboutConnection in TSQLDBConnection.NewStatementPrepared
- warning: no connection shall still be used on the background (e.g. in multi-threaded applications), or some unexpected issues may occur - see AcquireExecutionMode[] recommendations in ConnectionTimeOutMinutes

property RollbackOnDisconnect: Boolean read fRollbackOnDisconnect write fRollbackOnDisconnect;

Defines if TSQLDBConnection.Disconnect shall Rollback any pending transaction
- some engines executes a COMMIT when the client is disconnected, others do raise an exception: this parameter ensures that any pending transaction is roll-backed before disconnection
- is set to TRUE by default

property ServerName: RawUTF8 read fServerName;

The associated server name, as specified at creation

property StatementCacheReplicates: integer read fStatementCacheReplicates write fStatementCacheReplicates;

If UseCache is true, how many statement replicates can be generated if the cached ISQLDBStatement is already used
- such replication is normally not needed in a per-thread connection, unless ISQLDBStatement are not released as soon as possible
- above this limit, no cache will be made, and a dedicated single-time statement will be prepared
- default is 0 to cache statements once - but you may try to increase this value if you run identical SQL with long-standing ISQLDBStatement; or you can set -1 if you don't want the warning log to appear

property StatementMaxMemory: Int64 read fStatementMaxMemory write fStatementMaxMemory;

Maximum bytes allowed for FetchAllToJSON/FetchAllToBinary methods
- if a result set exceeds this limit, an ESQLDBException is raised
- default is 512 shl 20, i.e. 512MB which is very high
- avoid unexpected OutOfMemory errors when incorrect statement is run

property StoreVoidStringAsNull: Boolean read fStoreVoidStringAsNull write fStoreVoidStringAsNull;

Defines if '' string values are to be stored as SQL null
- by default, '' will be stored as ''
- but some DB engines (e.g. Jet or MS SQL) does not allow by default to store '' values, but expect NULL to be stored instead

property UseCache: boolean read fUseCache write fUseCache;

TRUE if an internal cache of SQL statement should be used
- cache will be accessed for NewStatementPrepared() method only, by returning ISQLDBStatement interface instances
- default value is TRUE for faster process (e.g. TTestSQLite3ExternalDB regression tests will be two times faster with statement caching)
- will cache only statements containing ? parameters or a SELECT with no WHERE clause within

property UserID: RawUTF8 read fUserID;

The associated User Identifier, as specified at creation

property VariantStringAsWideString: boolean read fVariantWideString write fVariantWideString;

Set to true to force all variant conversion to WideString instead of the default faster AnsiString, for pre-Unicode version of Delphi
- by default, the conversion to Variant will create an AnsiString kind of variant: for pre-Unicode Delphi, avoiding WideString/OleStr content will speed up the process a lot, if you are sure that the current charset matches the expected one (which is very likely)
- set this property to TRUE so that the conversion to Variant will create a WideString kind of variant, to avoid any character data loss: the access to the property will be slower, but you won't have any potential data loss
- starting with Delphi 2009, the TEXT content will be stored as an UnicodeString in the variant, so this property is not necessary
- the Variant conversion is mostly used for the TQuery wrapper, or for the ISQLDBRows.Column[] property or ISQLDBRows.ColumnVariant() method; this won't affect other Column*() methods, or JSON production

1.3.11. TSQLDBProxyConnectionProtocol

TSQLDBProxyConnectionProtocol = class(TObject)

Server-side implementation of a proxy connection to any SynDB engine
- this default implementation will send the data without compression, digital signature, nor encryption
- inherit from this class to customize the transmission layer content

constructor Create(aAuthenticate: TSynAuthenticationAbstract); reintroduce;

Initialize a protocol, with a given authentication scheme
- if no authentication is given, none will be processed

destructor Destroy; override;

Release associated authentication class

property Authenticate: TSynAuthenticationAbstract read GetAuthenticate write fAuthenticate;

The associated authentication information
- you can manage users via AuthenticateUser/DisauthenticateUser methods

1.3.12. TSQLDBRemoteConnectionProtocol

TSQLDBRemoteConnectionProtocol = class(TSQLDBProxyConnectionProtocol)

Server-side implementation of a remote connection to any SynDB engine
- implements digitally signed SynLZ-compressed binary message format, with simple symmetric encryption, as expected by SynDBRemote

1.3.13. TSQLDBConnection

TSQLDBConnection = class(TObject)

Abstract connection created from TSQLDBConnectionProperties
- more than one TSQLDBConnection instance can be run for the same TSQLDBConnectionProperties

constructor Create(aProperties: TSQLDBConnectionProperties); virtual;

Connect to a specified database engine

destructor Destroy; override;

Release memory and connection

function IsConnected: boolean; virtual; abstract;

Return TRUE if Connect has been already successfully called

function NewStatement: TSQLDBStatement; virtual; abstract;

Initialize a new SQL query statement for the given connection
- the caller should free the instance after use

function NewStatementPrepared(const aSQL: RawUTF8; ExpectResults: Boolean; RaiseExceptionOnError: Boolean=false; AllowReconnect: Boolean=true): ISQLDBStatement; virtual;

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 overridden to allow binding and execution of the very same prepared statement
- the same aSQL can cache up to 9 statements in this TSQLDBConnection
- 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 corresponding error information
- if TSQLDBConnectionProperties.ReconnectAfterConnectionError is set, any connection error will be trapped, unless AllowReconnect is false
- on error, if RaiseExceptionOnError=true, an exception is raised

function NewTableFromRows(const TableName: RawUTF8; Rows: TSQLDBStatement; WithinTransaction: boolean; ColumnForcedTypes: TSQLDBFieldTypeDynArray=nil): integer;

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
- if the column types are not set, they will be identified from the first row of data
- INSERTs will be nested within a transaction if WithinTransaction is TRUE
- will raise an Exception in case of error

procedure Commit; 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 Connect; virtual;

Connect to the specified database
- should raise an Exception on error
- this default implementation will notify OnProgress callback for sucessfull re-connection: it should be called in overridden methods AFTER actual connection process

procedure Disconnect; virtual;

Stop connection to the specified database
- should raise an Exception on error
- this default implementation will release all cached statements: so it should be called in overridden methods BEFORE actual disconnection

procedure RemoteProcessMessage(const Input: RawByteString; out Output: RawByteString; Protocol: TSQLDBProxyConnectionProtocol); virtual;

Server-side implementation of a remote connection to any SynDB engine
- follow the compressed binary message format expected by the TSQLDBRemoteConnectionPropertiesAbstract.ProcessMessage method
- any transmission protocol could call this method to execute the corresponding TSQLDBProxyConnectionCommand on the current connection

procedure Rollback; 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 StartTransaction; virtual;

Begin a Transaction for this connection
- this default implementation will check and set TransactionCount

property Connected: boolean read IsConnected;

Returns TRUE if the connection was set

property InTransaction: boolean read GetInTransaction;

TRUE if StartTransaction has been called
- check if TransactionCount>0

property LastErrorException: ExceptClass read fErrorException;

Some error exception, e.g. during execution of NewStatementPrepared

property LastErrorMessage: RawUTF8 read fErrorMessage write fErrorMessage;

Some error message, e.g. during execution of NewStatementPrepared

property LastErrorWasAboutConnection: boolean read GetLastErrorWasAboutConnection;

TRUE if last error is a broken connection, e.g. during execution of NewStatementPrepared
- i.e. LastErrorException/LastErrorMessage concerns the database connection
- will use TSQLDBConnectionProperties.ExceptionIsAboutConnection virtual method

property OnProcess: TOnSQLDBProcess read fOnProcess write fOnProcess;

This event handler will be called during all process
- can be used e.g. to change the desktop cursor
- by default, will follow TSQLDBConnectionProperties.OnProcess property

property Properties: TSQLDBConnectionProperties read fProperties;

The associated database properties

property RollbackOnDisconnect: Boolean read fRollbackOnDisconnect write fRollbackOnDisconnect;

Defines if Disconnect shall Rollback any pending transaction
- some engines executes a COMMIT when the client is disconnected, others do raise an exception: this parameter ensures that any pending transaction is roll-backed before disconnection
- is set to TRUE by default

property ServerDateTime: TDateTime read GetServerDateTime;

The current Date and Time, as retrieved from the server
- note that this value is the DB_SERVERTIME[] constant SQL value, so will most likely return a local time, not an UTC time
- this property will return the value as regular TDateTime

property ServerTimestamp: TTimeLog read GetServerTimestamp;

property ServerTimestampAtConnection: TDateTime read fServerTimestampAtConnection;

The time returned by the server when the connection occurred

property TotalConnectionCount: integer read fTotalConnectionCount;

Number of sucessfull connections for this instance
- can be greater than 1 in case of re-connection via Disconnect/Connect

property TransactionCount: integer read fTransactionCount;

Number of nested StartTransaction calls
- equals 0 if no transaction is active

1.3.14. TSQLDBStatement

TSQLDBStatement = class(TInterfacedObject)

Generic abstract class to implement a prepared SQL query
- inherited classes should implement the DB-specific connection in its overridden methods, especially Bind*(), Prepare(), ExecutePrepared, Step() and Column*() methods

constructor Create(aConnection: TSQLDBConnection); virtual;

Create a statement instance

function BoundCursor(Param: Integer): ISQLDBRows; virtual;

function ColumnBlob(Col: integer): RawByteString; overload; virtual; abstract;

Return a Column as a blob value of the current Row, first Col is 0

function ColumnBlob(const ColName: RawUTF8): RawByteString; overload;

Return a Column as a blob value of the current Row, from a supplied column name

function ColumnBlobBytes(const ColName: RawUTF8): TBytes; overload;

Return a Column as a blob value of the current Row, from a supplied column name

function ColumnBlobBytes(Col: integer): TBytes; overload; virtual;

Return a Column as a blob value of the current Row, first Col is 0
- this function will return the BLOB content as a TBytes
- this default virtual method will call ColumnBlob()

function ColumnCount: integer;

The column/field count of the current Row

function ColumnCurrency(const ColName: RawUTF8): currency; overload;

Return a Column currency value of the current Row, from a supplied column name

function ColumnCurrency(Col: integer): currency; overload; virtual; abstract;

Return a Column currency value of the current Row, first Col is 0

function ColumnCursor(const ColName: RawUTF8): ISQLDBRows; overload;

Return a special CURSOR Column content as a SynDB result set
- Cursors are not handled internally by mORMot, but some databases (e.g. Oracle) usually use such structures to get data from strored procedures
- such columns are mapped as ftNull internally - so this method is the only one giving access to the data rows
- this default method will raise an exception about unexpected behavior

function ColumnCursor(Col: integer): ISQLDBRows; overload; virtual;

function ColumnDateTime(const ColName: RawUTF8): TDateTime; overload;

Return a Column date and time value of the current Row, from a supplied column name

function ColumnDateTime(Col: integer): TDateTime; overload; virtual; abstract;

Return a Column date and time value of the current Row, first Col is 0

function ColumnDouble(const ColName: RawUTF8): double; overload;

Return a Column floating point value of the current Row, from a supplied column name

function ColumnDouble(Col: integer): double; overload; virtual; abstract;

Return a Column floating point value of the current Row, first Col is 0

function ColumnIndex(const aColumnName: RawUTF8): integer; virtual; abstract;

Returns the Column index of a given Column name
- Columns numeration (i.e. Col value) starts with 0
- returns -1 if the Column name is not found (via case insensitive search)

function ColumnInt(Col: integer): Int64; overload; virtual; abstract;

Return a Column integer value of the current Row, first Col is 0

function ColumnInt(const ColName: RawUTF8): Int64; overload;

Return a Column integer value of the current Row, from a supplied column name

function ColumnName(Col: integer): RawUTF8; virtual; abstract;

The Column name of the current Row
- Columns numeration (i.e. Col value) starts with 0
- it's up to the implementation to ensure than all column names are unique

function ColumnNull(Col: integer): boolean; virtual; abstract;

Returns TRUE if the column contains NULL

function ColumnsToSQLInsert(const TableName: RawUTF8; var Fields: TSQLDBColumnCreateDynArray): RawUTF8; virtual;

Compute the SQL INSERT statement corresponding to this columns row
- and populate the Fields[] array with columns information (type and name)
- if the current column value is NULL, will return ftNull: it is up to the caller to set the proper field type
- the SQL statement is prepared with bound parameters, e.g.

 insert into TableName (Col1,Col2) values (?,N)

- used e.g. to convert some data on the fly from one database to another, via the TSQLDBConnection.NewTableFromRows method

function ColumnString(Col: integer): string; overload; virtual;

Return a Column text value as generic VCL string of the current Row, first Col is 0
- this default implementation will call ColumnUTF8

function ColumnString(const ColName: RawUTF8): string; overload;

Return a Column text value as generic VCL string of the current Row, from a supplied column name

function ColumnTimestamp(const ColName: RawUTF8): TTimeLog; overload;

Return a column date and time value of the current Row, from a supplied column name
- call ColumnDateTime or ColumnUTF8 to convert into TTimeLogBits/Int64 time stamp from a TDateTime or text

function ColumnTimestamp(Col: integer): TTimeLog; overload;

Return a column date and time value of the current Row, first Col is 0
- call ColumnDateTime or ColumnUTF8 to convert into TTimeLogBits/Int64 time stamp from a TDateTime or text

function ColumnToVariant(Col: integer; var Value: Variant): TSQLDBFieldType; virtual;

Return a Column as a variant, first Col is 0
- this default implementation will call Column*() method above
- a ftUTF8 TEXT content will be mapped into a generic WideString variant for pre-Unicode version of Delphi, and a generic UnicodeString (=string) since Delphi 2009: you may not loose any data during charset conversion
- a ftBlob BLOB content will be mapped into a TBlobData AnsiString variant

function ColumnType(Col: integer; FieldSize: PInteger=nil): TSQLDBFieldType; virtual; abstract;

The Column type of the current Row
- FieldSize can be set to store the size in chars of a ftUTF8 column (0 means BLOB kind of TEXT column)

function ColumnUTF8(Col: integer): RawUTF8; overload; virtual; abstract;

Return a Column UTF-8 encoded text value of the current Row, first Col is 0

function ColumnUTF8(const ColName: RawUTF8): RawUTF8; overload;

Return a Column UTF-8 encoded text value of the current Row, from a supplied column name

function ColumnVariant(Col: integer): Variant; overload;

Return a Column as a variant, first Col is 0
- this default implementation will call ColumnToVariant() method
- a ftUTF8 TEXT content will be mapped into a generic WideString variant for pre-Unicode version of Delphi, and a generic UnicodeString (=string) since Delphi 2009: you may not loose any data during charset conversion
- a ftBlob BLOB content will be mapped into a TBlobData AnsiString variant

function ColumnVariant(const ColName: RawUTF8): Variant; overload;

Return a Column as a variant, from a supplied column name

function FetchAllAsJSON(Expanded: boolean; ReturnedRowCount: PPtrInt=nil): RawUTF8;

Return all rows content as a JSON string
- JSON data is retrieved with UTF-8 encoding
- if Expanded is true, JSON data is an array of objects, for direct use with any Ajax or .NET client:

 [ {"col1":val11,"col2":"val12"},{"col1":val21,... ]

- if Expanded is false, JSON data is serialized (used in TSQLTableJSON)

 { "FieldCount":1,"Values":["col1","col2",val11,"val12",val21,..] }

function FetchAllToBinary(Dest: TStream; MaxRowCount: cardinal=0; DataRowPosition: PCardinalDynArray=nil): cardinal; virtual;

function FetchAllToCSVValues(Dest: TStream; Tab: boolean; CommaSep: AnsiChar=','; AddBOM: boolean=true): PtrInt;

Append all rows content as a CSV stream
- CSV data is added to the supplied TStream, with UTF-8 encoding
- if Tab=TRUE, will use TAB instead of ',' between columns
- you can customize the ',' separator - use e.g. the global ListSeparator variable (from SysUtils) to reflect the current system definition (some country use ',' as decimal separator, for instance our "douce France")
- AddBOM will add a UTF-8 Byte Order Mark at the beginning of the content
- BLOB fields will be appended as "blob" with no data
- returns the number of row data returned

function FetchAllToJSON(JSON: TStream; Expanded: boolean): PtrInt;

 [ {"col1":val11,"col2":"val12"},{"col1":val21,... ]

- if Expanded is false, JSON data is serialized (used in TSQLTableJSON)

 { "FieldCount":1,"Values":["col1","col2",val11,"val12",val21,..] }

- BLOB field value is saved as Base64, in the '"\uFFF0base64encodedbinary"' format and contains true BLOB data
- similar to corresponding TSQLRequest.Execute method in SynSQLite3 unit
- returns the number of row data returned (excluding field names)
- warning: TSQLRestStorageExternal.EngineRetrieve in mORMotDB unit expects the Expanded=true format to return '[{...}]'#10

function ParamToVariant(Param: Integer; var Value: Variant; CheckIsOutParameter: boolean=true): TSQLDBFieldType; virtual;

Retrieve the parameter content, after SQL execution
- the leftmost SQL parameter has an index of 1
- to be used e.g. with stored procedures:

 query :=  'BEGIN TEST_PKG.DUMMY(?, ?, ?, ?, ?); END;';
 stmt := Props.NewThreadSafeStatementPrepared(query, false);
 stmt.Bind(1, in1, paramIn);
 stmt.BindTextU(2, in2, paramIn);
 stmt.BindTextU(3, in3, paramIn);
 stmt.BindTextS(4, '', paramOut); //  to be retrieved with out1: string
 stmt.Bind(5, 0, paramOut);       //  to be retrieved with out2: integer
 stmt.ExecutePrepared;
 stmt.ParamToVariant(4, out1, true);
 stmt.ParamToVariant(5, out2, true);

- the parameter should have been bound with IO=paramOut or IO=paramInOut if CheckIsOutParameter is TRUE
- this implementation just check that Param is correct: overridden method should fill Value content

function RowData: Variant; virtual;

Create a TSQLDBRowVariantType able to access any field content via late binding
- i.e. you can use Data.Name to access the 'Name' column of the current row
- this Variant will point to the corresponding TSQLDBStatement instance, so it's not necessary to retrieve its value for each row
- typical use is:

 var Row: Variant;
 (...)
  with MyConnProps.Execute('select * from table where name=?',[aName]) do begin
    Row := RowDaa;
    while Step do
      writeln(Row.FirstName,Row.BirthDate);
    ReleaseRows;
  end;

function Step(SeekFirst: boolean=false): boolean; virtual; abstract;

After a statement has been prepared via Prepare() + ExecutePrepared() or Execute(), this method must be called one or more times to evaluate it
- you shall call this method before calling any Column*() methods
- return TRUE on success, with data ready to be retrieved by Column*()
- return FALSE if no more row is available (e.g. if the SQL statement is not a SELECT but an UPDATE or INSERT command)
- access the first or next row of data from the SQL Statement result: if SeekFirst is TRUE, will put the cursor on the first row of results, otherwise, it will fetch one row of data, to be called within a loop
- should raise an Exception on any error
- typical use may be (see also e.g. the mORMotDB unit):

 var Query: ISQLDBStatement;
 begin
   Query := Props.NewThreadSafeStatementPrepared('select AccountNumber from Sales.Customer where AccountNumber like ?', ['AW000001%'],true);
   if Query<>nil then begin
     assert(SameTextU(Query.ColumnName(0),'AccountNumber'));
     while Query.Step do //  loop through all matching data rows
       assert(Copy(Query.ColumnUTF8(0),1,8)='AW000001');
     Query.ReleaseRows;
   end;
 end;

function UpdateCount: integer; virtual;

Gets a number of updates made by latest executed statement
- default implementation returns 0

procedure Bind(Param: Integer; Value: double; IO: TSQLDBParamInOutType=paramIn); overload; virtual; abstract;

Bind a double value to a parameter
- the leftmost SQL parameter has an index of 1

procedure Bind(const Params: array of const; IO: TSQLDBParamInOutType=paramIn); overload; virtual;

Bind an array of const values
- parameters marked as ? should be specified as method parameter in Params[]
- BLOB parameters can be bound with this method, when set after encoding via BinToBase64WithMagic() call
- TDateTime parameters can be bound with this method, when encoded via a DateToSQL() or DateTimeToSQL() call
- any variant parameter will be bound with BindVariant(i,VVariant^,true,IO) i.e. with DataIsBlob=true
- this default implementation will call corresponding Bind*() method

procedure Bind(Param: Integer; ParamType: TSQLDBFieldType; const Value: RawUTF8; ValueAlreadyUnquoted: boolean; IO: TSQLDBParamInOutType=paramIn); overload; virtual;

Bind one RawUTF8 encoded value
- the leftmost SQL parameter has an index of 1
- the value should match the BindArray() format, i.e. be stored as in SQL (i.e. number, 'quoted string', 'YYYY-MM-DD hh:mm:ss', null) - e.g. as computed by TJSONObjectDecoder.Decode()

procedure Bind(Param: Integer; const Data: TSQLVar; IO: TSQLDBParamInOutType=paramIn); overload; virtual;

Bind one TSQLVar value
- the leftmost SQL parameter has an index of 1
- this default implementation will call corresponding Bind*() method

procedure Bind(Param: Integer; Value: Int64; IO: TSQLDBParamInOutType=paramIn); overload; virtual; abstract;

Bind an integer value to a parameter
- the leftmost SQL parameter has an index of 1

procedure BindArray(Param: Integer; const Values: array of double); overload; virtual;

Bind an array of double values to a parameter
- the leftmost SQL parameter has an index of 1
- this default implementation will raise an exception if the engine does not support array binding

procedure BindArray(Param: Integer; ParamType: TSQLDBFieldType; const Values: TRawUTF8DynArray; ValuesCount: integer); overload; virtual;

Bind an array of values to a parameter
- the leftmost SQL parameter has an index of 1
- values are stored as in SQL (i.e. number, 'quoted string', 'YYYY-MM-DD hh:mm:ss', null)
- this default implementation will raise an exception if the engine does not support array binding

procedure BindArray(Param: Integer; const Values: array of Int64); overload; virtual;

Bind an array of integer values to a parameter
- the leftmost SQL parameter has an index of 1
- this default implementation will raise an exception if the engine does not support array binding

procedure BindArray(Param: Integer; const Values: array of RawUTF8); overload; virtual;

Bind an array of RawUTF8 values to a parameter
- the leftmost SQL parameter has an index of 1
- values are stored as in SQL (i.e. 'quoted string')
- this default implementation will raise an exception if the engine does not support array binding

procedure BindArrayCurrency(Param: Integer; const Values: array of currency); virtual;

Bind an array of currency values to a parameter
- the leftmost SQL parameter has an index of 1
- this default implementation will raise an exception if the engine does not support array binding

procedure BindArrayDateTime(Param: Integer; const Values: array of TDateTime); virtual;

Bind an array of TDateTime values to a parameter
- the leftmost SQL parameter has an index of 1
- values are stored as in SQL (i.e. 'YYYY-MM-DD hh:mm:ss')
- this default implementation will raise an exception if the engine does not support array binding

procedure BindBlob(Param: Integer; Data: pointer; Size: integer; IO: TSQLDBParamInOutType=paramIn); overload; virtual; abstract;

Bind a Blob buffer to a parameter
- the leftmost SQL parameter has an index of 1

procedure BindBlob(Param: Integer; const Data: RawByteString; IO: TSQLDBParamInOutType=paramIn); overload; virtual; abstract;

Bind a Blob buffer to a parameter
- the leftmost SQL parameter has an index of 1

procedure BindCurrency(Param: Integer; Value: currency; IO: TSQLDBParamInOutType=paramIn); overload; virtual; abstract;

Bind a currency value to a parameter
- the leftmost SQL parameter has an index of 1

procedure BindCursor(Param: integer); virtual;

procedure BindDateTime(Param: Integer; Value: TDateTime; IO: TSQLDBParamInOutType=paramIn); overload; virtual; abstract;

Bind a TDateTime value to a parameter
- the leftmost SQL parameter has an index of 1

procedure BindFromRows(const Fields: TSQLDBFieldTypeDynArray; Rows: TSQLDBStatement);

Bind an array of fields from an existing SQL statement
- can be used e.g. after ColumnsToSQLInsert() method call for fast data conversion between tables

procedure BindNull(Param: Integer; IO: TSQLDBParamInOutType=paramIn; BoundType: TSQLDBFieldType=ftNull); virtual; abstract;

Bind a NULL value to a parameter
- the leftmost SQL parameter has an index of 1
- some providers (e.g. OleDB during MULTI INSERT statements) expect the proper column type to be set in BoundType, even for NULL values

procedure BindTextP(Param: Integer; Value: PUTF8Char; IO: TSQLDBParamInOutType=paramIn); overload; virtual; abstract;

Bind a UTF-8 encoded buffer text (#0 ended) to a parameter
- the leftmost SQL parameter has an index of 1

procedure BindTextS(Param: Integer; const Value: string; IO: TSQLDBParamInOutType=paramIn); overload; virtual; abstract;

Bind a UTF-8 encoded string to a parameter
- the leftmost SQL parameter has an index of 1

procedure BindTextU(Param: Integer; const Value: RawUTF8; IO: TSQLDBParamInOutType=paramIn); overload; virtual; abstract;

Bind a UTF-8 encoded string to a parameter
- the leftmost SQL parameter has an index of 1

procedure BindTextW(Param: Integer; const Value: WideString; IO: TSQLDBParamInOutType=paramIn); overload; virtual; abstract;

Bind a UTF-8 encoded string to a parameter
- the leftmost SQL parameter has an index of 1

procedure BindVariant(Param: Integer; const Data: Variant; DataIsBlob: boolean; IO: TSQLDBParamInOutType=paramIn); virtual;

Bind a Variant value to a parameter
- the leftmost SQL parameter has an index of 1
- will call all virtual Bind*() methods from the Data type
- if DataIsBlob is TRUE, will call BindBlob(RawByteString(Data)) instead of BindTextW(WideString(Variant)) - used e.g. by TQuery.AsBlob/AsBytes

procedure ColumnBlobFromStream(const ColName: RawUTF8; Stream: TStream); overload;

Write a blob Column into the Stream parameter
- expected to be used with 'SELECT .. FOR UPDATE' locking statements

procedure ColumnBlobFromStream(Col: integer; Stream: TStream); overload; virtual;

Write a blob Column into the Stream parameter
- expected to be used with 'SELECT .. FOR UPDATE' locking statements
- default implementation will through an exception, since it is highly provider-specific; SynDBOracle e.g. implements it properly

procedure ColumnBlobToStream(const ColName: RawUTF8; Stream: TStream); overload;

Read a blob Column into the Stream parameter

procedure ColumnBlobToStream(Col: integer; Stream: TStream); overload; virtual;

Read a blob Column into the Stream parameter
- default implementation will just call ColumnBlob(), whereas some providers (like SynDBOracle) may implement direct support

procedure ColumnsToBinary(W: TFileBufferWriter; Null: pointer; const ColTypes: TSQLDBFieldTypeDynArray); virtual;

Append current row content as binary stream
- will save one data row in optimized binary format (if not in Null)
- virtual method called by FetchAllToBinary()
- follows the format expected by TSQLDBProxyStatement

procedure ColumnsToJSON(WR: TJSONWriter); virtual;

Append all columns values of the current Row to a JSON stream
- will use WR.Expand to guess the expected output format
- this default implementation will call Column*() methods above, but you should also implement a custom version with no temporary variable
- BLOB field value is saved as Base64, in the '"\uFFF0base64encodedbinary" format and contains true BLOB data (unless ForceBlobAsNull property was set)

procedure ColumnToSQLVar(Col: Integer; var Value: TSQLVar; var Temp: RawByteString); virtual;

Return a Column as a TSQLVar value, first Col is 0
- the specified Temp variable will be used for temporary storage of svtUTF8/svtBlob values

procedure Execute(const aSQL: RawUTF8; ExpectResults: Boolean; const Params: array of const); overload;

Prepare and Execute an UTF-8 encoded SQL statement
- parameters marked as ? should be specified as method parameter in Params[]
- BLOB parameters could not be bound with this method, but need an explicit call to BindBlob() method
- if ExpectResults is TRUE, then Step() and Column*() methods are available to retrieve the data rows
- should raise an Exception on any error
- this method will bind parameters, then call Excecute() virtual method

procedure Execute(const SQLFormat: RawUTF8; ExpectResults: Boolean; const Args, Params: array of const); overload;

Prepare and Execute an UTF-8 encoded SQL statement
- parameters marked as % will be replaced by Args[] value in the SQL text
- parameters marked as ? should be specified as method parameter in Params[]
- so could be used as such, mixing both % and ? parameters:

 Statement.Execute('SELECT % FROM % WHERE RowID=?',true,[FieldName,TableName],[ID])

- BLOB parameters could not be bound with this method, but need an explicit call to BindBlob() method
- if ExpectResults is TRUE, then Step() and Column*() methods are available to retrieve the data rows
- should raise an Exception on any error
- this method will bind parameters, then call Excecute() virtual method

procedure Execute(const aSQL: RawUTF8; ExpectResults: Boolean); overload;

Prepare and Execute an UTF-8 encoded SQL statement
- parameters marked as ? should have been already bound with Bind*() functions above
- if ExpectResults is TRUE, then Step() and Column*() methods are available to retrieve the data rows
- should raise an Exception on any error
- this method will call Prepare then ExecutePrepared methods

procedure ExecutePrepared; virtual;

Execute a prepared SQL statement
- parameters marked as ? should have been already bound with Bind*() functions
- should raise an Exception on any error
- this void default implementation will call set fConnection.fLastAccess

procedure ExecutePreparedAndFetchAllAsJSON(Expanded: boolean; out JSON: RawUTF8); virtual;

Execute a prepared SQL statement and return all rows content as a JSON string
- JSON data is retrieved with UTF-8 encoding
- if Expanded is true, JSON data is an array of objects, for direct use with any Ajax or .NET client:

 [ {"col1":val11,"col2":"val12"},{"col1":val21,... ]

- if Expanded is false, JSON data is serialized (used in TSQLTableJSON)

 { "FieldCount":1,"Values":["col1","col2",val11,"val12",val21,..] }

- BLOB field value is saved as Base64, in the '"\uFFF0base64encodedbinary"' format and contains true BLOB data
- this virtual implementation calls ExecutePrepared then FetchAllAsJSON()

procedure Prepare(const aSQL: RawUTF8; ExpectResults: Boolean); overload; virtual;

Prepare an UTF-8 encoded SQL statement
- parameters marked as ? will be bound later, before ExecutePrepared call
- if ExpectResults is TRUE, then Step() and Column*() methods are available to retrieve the data rows
- should raise an Exception on any error
- this default implementation will just store aSQL content and the ExpectResults parameter, and connect to the remote server is was not already connected

procedure ReleaseRows; virtual;

Release cursor memory and resources once Step loop is finished
- this method call is optional, but is better be used if the ISQLDBRows statement from taken from cache, and returned a lot of content which may still be in client (and server) memory
- override to free cursor memory when ISQLDBStatement is back in cache

procedure Reset; virtual;

Reset the previous prepared statement
- some drivers expect an explicit reset before binding parameters and executing the statement another time
- this default implementation will just do nothing

procedure RowDocVariant(out aDocument: variant; aOptions: TDocVariantOptions=JSON_OPTIONS_FAST); virtual;

Create a TDocVariant custom variant containing all columns values
- will create a "fast" TDocVariant object instance with all fields

property CacheIndex: integer read fCacheIndex;

Low-level access to the statement cache index, after a call to Prepare()
- contains >= 0 if the database supports prepared statement cache (Oracle, Postgres) and query plan is cached; contains -1 in other cases

property Connection: TSQLDBConnection read fConnection;

The associated database connection

property CurrentRow: Integer read fCurrentRow;

The current row after Execute/Step call, corresponding to Column*() methods
- contains 0 before initial Step call, or a number >=1 during data retrieval

property SQL: RawUTF8 read fSQL;

The prepared SQL statement, as supplied to Prepare() method

property SQLCurrent: RawUTF8 read GetSQLCurrent;

The prepared SQL statement, in its current state
- if statement is prepared, then equals SQLPrepared, otherwise, contains the raw SQL property content
- used internally by the implementation units, e.g. for errors logging

property SQLLogTimer: TPrecisionTimer read fSQLLogTimer;

Low-level access to the Timer used for last DB operation

property SQLPrepared: RawUTF8 read fSQLPrepared;

After a call to Prepare(), contains the query text to be passed to the DB
- depending on the DB, parameters placeholders are replaced by ?, :1, $1 etc
- this SQL is ready to be used in any DB tool, e.g. to check the real execution plan/timing

property SQLWithInlinedParams: RawUTF8 read GetSQLWithInlinedParams;

The prepared SQL statement, with all '?' changed into the supplied parameter values
- such statement query plan usually differ from a real execution plan for prepared statements with parameters - see SQLPrepared property instead

property StripSemicolon: boolean read fStripSemicolon write fStripSemicolon;

Strip last semicolon in query
- expectation may vary, depending on the SQL statement and the engine
- default is true

property TotalRowsRetrieved: Integer read fTotalRowsRetrieved;

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

1.3.15. TSQLDBConnectionThreadSafe

TSQLDBConnectionThreadSafe = class(TSQLDBConnection)

Abstract connection created from TSQLDBConnectionProperties
- this overridden 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

1.3.16. TSQLDBConnectionPropertiesThreadSafe

TSQLDBConnectionPropertiesThreadSafe = class(TSQLDBConnectionProperties)

Connection properties which will implement an internal Thread-Safe connection pool

constructor Create(const aServerName, aDatabaseName, aUserID, aPassWord: RawUTF8); override;

Initialize the properties
- this overridden method will initialize the internal per-thread connection pool

destructor Destroy; override;

Release related memory, and all per-thread connections

function ThreadSafeConnection: TSQLDBConnection; override;

Get a thread-safe connection
- this overridden implementation will define a per-thread TSQLDBConnection connection pool, via an internal pool

procedure ClearConnectionPool; override;

Release all existing connections
- this overridden implementation will release all per-thread TSQLDBConnection internal connection pool
- warning: no connection shall still be used on the background (e.g. in multi-threaded applications), or some unexpected border effects may occur

procedure EndCurrentThread; virtual;

You can call this method just before a thread is finished to ensure that the associated Connection will be released
- could be used e.g. in a try...finally block inside a TThread.Execute overridden method
- could be used e.g. to call CoUnInitialize from thread in which CoInitialize was made, for instance via a method defined as such:

 procedure TMyServer.OnHttpThreadTerminate(Sender: TObject);
 begin
   fMyConnectionProps.EndCurrentThread;
 end;

- this method shall be called from the thread about to be terminated: e.g. if you call it from the main thread, it may fail to release resources
- within the mORMot server, mORMotDB unit will call this method for every terminating thread created for TSQLRestServerNamedPipeResponse or TSQLHttpServer multi-thread process

property ThreadingMode: TSQLDBConnectionPropertiesThreadSafeThreadingMode read fThreadingMode write fThreadingMode;

Set this property if you want to disable the per-thread connection pool
- to be used e.g. in database embedded mode (SQLite3/FireBird), when multiple connections may break stability and decrease performance
- see TSQLDBConnectionPropertiesThreadSafeThreadingMode for the possible values

1.3.17. TSQLDBParam

TSQLDBParam = packed record

A structure used to store a standard binding parameter
- you can use your own internal representation of parameters (TOleDBStatement use its own TOleDBStatementParam type), but this type can be used to implement a generic parameter
- used e.g. by TSQLDBStatementWithParams as a dynamic array (and its inherited TSQLDBOracleStatement)
- don't change this structure, since it will be serialized as binary for TSQLDBProxyConnectionCommandExecute

VArray: TRawUTF8DynArray;

Storage used for bound array values
- number of items in array is stored in VInt64
- values are stored as in SQL (i.e. number, 'quoted string', 'YYYY-MM-DD hh:mm:ss', null)

VData: RawByteString;

Storage used for TEXT (ftUTF8) and BLOB (ftBlob) values
- ftBlob are stored as RawByteString
- ftUTF8 are stored as RawUTF8
- sometimes, may be ftInt64 or ftCurrency provided as SQLT_AVC text, or ftDate value converted to SQLT_TIMESTAMP

VDBType: word;

Used e.g. by TSQLDBOracleStatement

VInOut: TSQLDBParamInOutType;

Define if parameter can be retrieved after a stored procedure execution

VInt64: Int64;

Storage used for ftInt64, ftDouble, ftDate and ftCurrency value

VType: TSQLDBFieldType;

The column/parameter Value type

1.3.18. TSQLDBStatementWithParams

TSQLDBStatementWithParams = class(TSQLDBStatement)

Generic abstract class handling prepared statements with binding
- will provide protected fields and methods for handling standard TSQLDBParam parameters

constructor Create(aConnection: TSQLDBConnection); override;

Create a statement instance
- this overridden version will initialize the internal fParam* fields

function ParamToVariant(Param: Integer; var Value: Variant; CheckIsOutParameter: boolean=true): TSQLDBFieldType; override;

Retrieve the parameter content, after SQL execution
- the leftmost SQL parameter has an index of 1
- to be used e.g. with stored procedures
- this overridden function will retrieve the value stored in the protected fParams[] array: the ExecutePrepared method should have updated its content as exepcted

procedure Bind(Param: Integer; Value: double; IO: TSQLDBParamInOutType=paramIn); overload; override;

Bind a double value to a parameter
- the leftmost SQL parameter has an index of 1
- raise an Exception on any error

procedure Bind(Param: Integer; Value: Int64; IO: TSQLDBParamInOutType=paramIn); overload; override;

Bind an integer value to a parameter
- the leftmost SQL parameter has an index of 1
- raise an Exception on any error

procedure BindArray(Param: Integer; const Values: array of double); overload; override;

Bind an array of double values to a parameter
- the leftmost SQL parameter has an index of 1
- this default implementation will raise an exception if the engine does not support array binding
- this default implementation will call BindArray() after conversion into RawUTF8 items, stored in TSQLDBParam.VArray

procedure BindArray(Param: Integer; const Values: array of Int64); overload; override;

Bind an array of integer values to a parameter
- the leftmost SQL parameter has an index of 1
- this default implementation will call BindArray() after conversion into RawUTF8 items, stored in TSQLDBParam.VArray

procedure BindArray(Param: Integer; const Values: array of RawUTF8); overload; override;

Bind an array of RawUTF8 values to a parameter
- the leftmost SQL parameter has an index of 1
- values are stored as 'quoted string'
- this default implementation will raise an exception if the engine does not support array binding

procedure BindArray(Param: Integer; ParamType: TSQLDBFieldType; const Values: TRawUTF8DynArray; ValuesCount: integer); overload; override;

Bind an array of values to a parameter using OCI bind array feature
- the leftmost SQL parameter has an index of 1
- values are stored as in SQL (i.e. number, 'quoted string', 'YYYY-MM-DD hh:mm:ss', null)
- values are stored as in SQL (i.e. 'YYYY-MM-DD hh:mm:ss')

procedure BindArrayCurrency(Param: Integer; const Values: array of currency); override;

Bind an array of currency values to a parameter
- the leftmost SQL parameter has an index of 1
- this default implementation will raise an exception if the engine does not support array binding
- this default implementation will call BindArray() after conversion into RawUTF8 items, stored in TSQLDBParam.VArray

procedure BindArrayDateTime(Param: Integer; const Values: array of TDateTime); override;

Bind an array of TDateTime values to a parameter
- the leftmost SQL parameter has an index of 1
- values are stored as in SQL (i.e. 'YYYY-MM-DD hh:mm:ss')
- this default implementation will raise an exception if the engine does not support array binding
- this default implementation will call BindArray() after conversion into RawUTF8 items, stored in TSQLDBParam.VArray

procedure BindArrayRow(const aValues: array of const);

Bind a set of parameters for further array binding
- supplied parameters shall follow the BindArrayRowPrepare() supplied types (i.e. RawUTF8, Integer/Int64, double); you can also bind directly a TDateTime value if the corresponding binding has been defined as ftDate by BindArrayRowPrepare()

procedure BindArrayRowPrepare(const aParamTypes: array of TSQLDBFieldType; aExpectedMinimalRowCount: integer=0);

Start parameter array binding per-row process
- BindArray*() methods expect the data to be supplied "verticaly": this method allow-per row binding
- call this method, then BindArrayRow() with the corresponding values for one statement row, then Execute to send the query

procedure BindBlob(Param: Integer; Data: pointer; Size: integer; IO: TSQLDBParamInOutType=paramIn); overload; override;

Bind a Blob buffer to a parameter
- the leftmost SQL parameter has an index of 1
- raise an Exception on any error

procedure BindBlob(Param: Integer; const Data: RawByteString; IO: TSQLDBParamInOutType=paramIn); overload; override;

Bind a Blob buffer to a parameter
- the leftmost SQL parameter has an index of 1
- raise an Exception on any error M

procedure BindCurrency(Param: Integer; Value: currency; IO: TSQLDBParamInOutType=paramIn); overload; override;

Bind a currency value to a parameter
- the leftmost SQL parameter has an index of 1
- raise an Exception on any error

procedure BindDateTime(Param: Integer; Value: TDateTime; IO: TSQLDBParamInOutType=paramIn); overload; override;

Bind a TDateTime value to a parameter
- the leftmost SQL parameter has an index of 1
- raise an Exception on any error

procedure BindFromRows(Rows: TSQLDBStatement); virtual;

Bind an array of fields from an existing SQL statement for array binding
- supplied Rows columns shall follow the BindArrayRowPrepare() supplied types (i.e. RawUTF8, Integer/Int64, double, date)
- can be used e.g. after ColumnsToSQLInsert() method call for fast data conversion between tables

procedure BindNull(Param: Integer; IO: TSQLDBParamInOutType=paramIn; BoundType: TSQLDBFieldType=ftNull); override;

Bind a NULL value to a parameter
- the leftmost SQL parameter has an index of 1
- raise an Exception on any error
- some providers (only OleDB during MULTI INSERT statements, so never used in this class) expect the proper column type to be set in BoundType

procedure BindTextP(Param: Integer; Value: PUTF8Char; IO: TSQLDBParamInOutType=paramIn); overload; override;

Bind a UTF-8 encoded buffer text (#0 ended) to a parameter
- the leftmost SQL parameter has an index of 1

procedure BindTextS(Param: Integer; const Value: string; IO: TSQLDBParamInOutType=paramIn); overload; override;

Bind a VCL string to a parameter
- the leftmost SQL parameter has an index of 1
- raise an Exception on any error

procedure BindTextU(Param: Integer; const Value: RawUTF8; IO: TSQLDBParamInOutType=paramIn); overload; override;

Bind a UTF-8 encoded string to a parameter
- the leftmost SQL parameter has an index of 1
- raise an Exception on any error

procedure BindTextW(Param: Integer; const Value: WideString; IO: TSQLDBParamInOutType=paramIn); overload; override;

Bind an OLE WideString to a parameter
- the leftmost SQL parameter has an index of 1
- raise an Exception on any error }

procedure ReleaseRows; override;

Release used memory
- this overridden implementation will free the fParams[] members (e.g. VData) but not the parameters themselves

procedure Reset; override;

Reset the previous prepared statement
- this overridden implementation will just do reset the internal fParams[]

1.3.19. TSQLDBStatementWithParamsAndColumns

TSQLDBStatementWithParamsAndColumns = class(TSQLDBStatementWithParams)

Generic abstract class handling prepared statements with binding and column description
- will provide protected fields and methods for handling both TSQLDBParam parameters and standard TSQLDBColumnProperty column description

constructor Create(aConnection: TSQLDBConnection); override;

Create a statement instance
- this overridden version will initialize the internal fColumn* fields

function ColumnIndex(const aColumnName: RawUTF8): integer; override;

Returns the Column index of a given Column name
- Columns numeration (i.e. Col value) starts with 0
- returns -1 if the Column name is not found (via case insensitive search)

function ColumnName(Col: integer): RawUTF8; override;

Retrieve a column name of the current Row
- Columns numeration (i.e. Col value) starts with 0
- it's up to the implementation to ensure than all column names are unique

function ColumnType(Col: integer; FieldSize: PInteger=nil): TSQLDBFieldType; override;

The Column type of the current Row
- ftCurrency type should be handled specifically, for faster process and avoid any rounding issue, since currency is a standard OleDB type
- FieldSize can be set to store the size in chars of a ftUTF8 column (0 means BLOB kind of TEXT column) - this implementation will store fColumns[Col].ColumnValueDBSize if ColumnValueInlined=true

property Columns: TSQLDBColumnPropertyDynArray read fColumns;

Direct access to the columns description
- gives more details than the default ColumnType() function

1.3.20. ESQLDBException

ESQLDBException = class(ESynException)

Generic Exception type, as used by the SynDB unit

constructor CreateUTF8(const Format: RawUTF8; const Args: array of const);

Constructor which will use FormatUTF8() instead of Format()
- if the first Args[0] is a TSQLDBStatement class instance, the current SQL statement will be part of the exception message

property Statement: TSQLDBStatement read fStatement;

Associated TSQLDBStatement instance, if supplied as first parameter

1.3.21. ESQLDBRemote

ESQLDBRemote = class(ESQLDBException)

Exception raised during remote connection process

1.3.22. TSQLDBProxyConnectionCommandExecute

TSQLDBProxyConnectionCommandExecute = packed record

Structure to embedd all needed parameters to execute a SQL statement
- used for cExecute, cExecuteToBinary, cExecuteToJSON and cExecuteToExpandedJSON commands of TSQLDBProxyConnectionProperties.Process()
- set by TSQLDBProxyStatement.ParamsToCommand() protected method

ArrayCount: integer;

If input parameters expected BindArray() process

Force: set of (fBlobAsNull, fDateWithMS, fNoUpdateCount);

How server side would handle statement execution
- fBlobAsNull and fDateWithMS do match ForceBlobAsNull and ForceDateWithMS ISQLDBStatement properties
- fNoUpdateCount avoids to call ISQLDBStatement.UpdateCount method, e.g. for performance reasons

Params: TSQLDBParamDynArray;

Input parameters
- trunked to the exact number of parameters

SQL: RawUTF8;

The associated SQL statement

1.3.23. TSQLDBProxyConnectionPropertiesAbstract

TSQLDBProxyConnectionPropertiesAbstract = class(TSQLDBConnectionProperties)

Implements a proxy-like virtual connection statement to a DB engine
- will generate TSQLDBProxyConnection kind of connection

destructor Destroy; override;

Will notify for proxy disconnection

function IsCachable(P: PUTF8Char): boolean; override;

Determine if the SQL statement can be cached
- always returns false, to force a new fake statement to be created

function NewConnection: TSQLDBConnection; override;

Create a new TSQLDBProxyConnection instance
- the caller is responsible of freeing this instance

procedure GetFields(const aTableName: RawUTF8; out Fields: TSQLDBColumnDefineDynArray); override;

Retrieve the column/field layout of a specified table
- calls Process(cGetFields,aTableName,Fields)

procedure GetIndexes(const aTableName: RawUTF8; out Indexes: TSQLDBIndexDefineDynArray); override;

Retrieve the advanced indexed information of a specified Table
- calls Process(cGetIndexes,aTableName,Indexes)

procedure GetTableNames(out Tables: TRawUTF8DynArray); override;

Get all table names
- this default implementation will use protected SQLGetTableNames virtual
- calls Process(cGetTableNames,self,Tables)

property HandleConnection: boolean read fHandleConnection write fHandleConnection;

Connect and Disconnect won't really connect nor disconnect the remote connection
- you can set this property to TRUE if you expect the remote connection by in synch with the remote proxy connection (should not be used in most cases, unless you are sure you have only one single client at a time

property StartTransactionTimeOut: Int64 read fStartTransactionTimeOut write fStartTransactionTimeOut;

Milliseconds to way until StartTransaction is allowed by the server
- in the current implementation, there should be a single transaction at once on the server side: this is the time to try before reporting an ESQLDBRemote exception failure

1.3.24. TSQLDBProxyConnection

TSQLDBProxyConnection = class(TSQLDBConnection)

Implements an abstract proxy-like virtual connection to a DB engine
- can be used e.g. for remote access or execution in a background thread

constructor Create(aProperties: TSQLDBConnectionProperties); override;

Connect to a specified database engine

function IsConnected: boolean; override;

Return TRUE if Connect has been already successfully called

function NewStatement: TSQLDBStatement; override;

Initialize a new SQL query statement for the given connection

procedure Commit; override;

Commit changes of a Transaction for this connection

procedure Connect; override;

Connect to the specified database

procedure Disconnect; override;

Stop connection to the specified database

procedure Rollback; override;

Discard changes of a Transaction for this connection

procedure StartTransaction; override;

Begin a Transaction for this connection

1.3.25. TSQLDBProxyStatementAbstract

TSQLDBProxyStatementAbstract = class(TSQLDBStatementWithParamsAndColumns)

Implements a proxy-like virtual connection statement to a DB engine
- abstract class, with no corresponding kind of connection, but allowing access to the mapped data via Column*() methods
- will handle an internal binary buffer when the statement returned rows data, as generated by TSQLDBStatement.FetchAllToBinary()

function ColumnBlob(Col: integer): RawByteString; override;

Return a Column as a blob value of the current Row, first Col is 0

function ColumnCurrency(Col: integer): currency; override;

Return a Column currency value of the current Row, first Col is 0
- should retrieve directly the 64 bit Currency content, to avoid any rounding/conversion error from floating-point types

function ColumnData(Col: integer): pointer;

Direct access to the data buffer of the current row
- points to Double/Currency value, or variable-length Int64/UTF8/Blob
- points to nil if the column value is NULL

function ColumnDateTime(Col: integer): TDateTime; override;

Return a Column floating point value of the current Row, first Col is 0

function ColumnDouble(Col: integer): double; override;

Return a Column floating point value of the current Row, first Col is 0

function ColumnInt(Col: integer): Int64; override;

Return a Column integer value of the current Row, first Col is 0

function ColumnNull(Col: integer): boolean; override;

Returns TRUE if the column contains NULL

function ColumnString(Col: integer): string; override;

Return a Column text value as generic VCL string of the current Row, first Col is 0

function ColumnType(Col: integer; FieldSize: PInteger=nil): TSQLDBFieldType; override;

The Column type of the current Row

function ColumnUTF8(Col: integer): RawUTF8; override;

Return a Column UTF-8 encoded text value of the current Row, first Col is 0

procedure ColumnsToBinary(W: TFileBufferWriter; Null: pointer; const ColTypes: TSQLDBFieldTypeDynArray); override;

Append current row content as binary stream
- will save one data row in optimized binary format (if not in Null)
- virtual method called by FetchAllToBinary()
- follows the format expected by TSQLDBProxyStatement

procedure ColumnsToJSON(WR: TJSONWriter); override;

Return all columns values into JSON content

property DataRowCount: integer read fDataRowCount;

Read-only access to the number of data rows stored

1.3.26. TSQLDBProxyStatement

TSQLDBProxyStatement = class(TSQLDBProxyStatementAbstract)

Implements a proxy-like virtual connection statement to a DB engine
- is generated by TSQLDBProxyConnection kind of connection
- will use an internal binary buffer when the statement returned rows data, as generated by TSQLDBStatement.FetchAllToBinary() or JSON for ExecutePreparedAndFetchAllAsJSON() method (as expected by our ORM)

function FetchAllToBinary(Dest: TStream; MaxRowCount: cardinal=0; DataRowPosition: PCardinalDynArray=nil): cardinal; override;

Append all rows content as binary stream
- will save the column types and name, then every data row in optimized binary format (faster and smaller than JSON)
- you can specify a LIMIT for the data extent (default 0 meaning all data)
- generates the format expected by TSQLDBProxyStatement
- this overriden method will use the internal data copy of the binary buffer retrieved by ExecutePrepared, so would be almost immediate, and would allow e.g. direct consumption via our TSynSQLStatementDataSet
- note that DataRowPosition won't be set by this method: will be done e.g. in TSQLDBProxyStatementRandomAccess.Create

function Step(SeekFirst: boolean=false): boolean; override;

After a statement has been prepared via Prepare() + ExecutePrepared() or Execute(), this method must be called one or more times to evaluate it

function UpdateCount: integer; override;

Gets a number of updates made by latest executed statement
- this overriden method will return the integer value returned by cExecute command

procedure ExecutePrepared; override;

Execute a SQL statement
- for TSQLDBProxyStatement, preparation and execution are processed in one step, when this method is executed - as such, Prepare() won't call the remote process, but will just set fSQL
- this overridden implementation will use out optimized binary format as generated by TSQLDBStatement.FetchAllToBinary(), and not JSON

procedure ExecutePreparedAndFetchAllAsJSON(Expanded: boolean; out JSON: RawUTF8); override;

 [ {"col1":val11,"col2":"val12"},{"col1":val21,... ]

- if Expanded is false, JSON data is serialized (used in TSQLTableJSON)

 { "FieldCount":1,"Values":["col1","col2",val11,"val12",val21,..] }

- BLOB field value is saved as Base64, in the '"\uFFF0base64encodedbinary"' format and contains true BLOB data
- this overridden implementation will use JSON for transmission, and binary encoding only for parameters (to avoid unneeded conversions, e.g. when called from mORMotDB)

property ForceNoUpdateCount: boolean read fForceNoUpdateCount write fForceNoUpdateCount;

Force no UpdateCount method call on server side
- may be needed to reduce server load, if this information is not needed

1.3.27. TSQLDBRemoteConnectionPropertiesAbstract

TSQLDBRemoteConnectionPropertiesAbstract = class(TSQLDBProxyConnectionPropertiesAbstract)

Client-side implementation of a remote connection to any SynDB engine
- will compute binary compressed messages for the remote processing, ready to be served e.g. over HTTP via our SynDBRemote unit
- abstract class which should override its protected ProcessMessage() method e.g. by TSQLDBRemoteConnectionPropertiesTest or

1.3.28. TSQLDBRemoteConnectionPropertiesTest

TSQLDBRemoteConnectionPropertiesTest = class(TSQLDBRemoteConnectionPropertiesAbstract)

Fake proxy class for testing the remote connection to any SynDB engine
- resulting overhead due to our binary messaging: unnoticeable :)

constructor Create(aProps: TSQLDBConnectionProperties; const aUserID,aPassword: RawUTF8; aProtocol: TSQLDBProxyConnectionProtocolClass); reintroduce;

Create a test redirection to an existing local connection property
- you can specify a User/Password credential pair to also test the authentication via TSynAuthentication

1.3.29. TSQLDBProxyStatementRandomAccess

TSQLDBProxyStatementRandomAccess = class(TSQLDBProxyStatementAbstract)

Implements a virtual statement with direct data access
- is generated with no connection, but allows direct random access to any data row retrieved from TSQLDBStatement.FetchAllToBinary() binary data
- GotoRow() method allows direct access to a row data via Column*()
- is used e.g. by TSynSQLStatementDataSet of SynDBVCL unit

constructor Create(Data: PByte; DataLen: integer; DataRowPosition: PCardinalDynArray=nil; IgnoreColumnDataSize: boolean=false); reintroduce;

Initialize the internal structure from a given memory buffer
- by default, ColumnDataSize would be computed from the supplied data, unless you set IgnoreColumnDataSize=true to set the value to 0 (and force e.g. SynDBVCL TSynBinaryDataSet.InternalInitFieldDefs define the field as ftDefaultMemo)

function GotoRow(Index: integer; RaiseExceptionOnWrongIndex: Boolean=false): boolean;

Change the current data Row
- if Index<DataRowCount, returns TRUE and you can access to the data via regular Column*() methods
- can optionally raise an ESQLDBException if Index is not correct

function Step(SeekFirst: boolean=false): boolean; override;

Change cursor position to the next available row
- this unexpected overridden method will raise a ESQLDBException

procedure ExecutePrepared; override;

Execute a prepared SQL statement
- this unexpected overridden method will raise a ESQLDBException

1.3.30. TSQLDBLib

TSQLDBLib = class(TObject)

Access to a native library
- this generic class is to be used for any native connection using an external library
- is used e.g. in SynDBOracle by TSQLDBOracleLib to access the OCI library, or by SynDBODBC to access the ODBC library

destructor Destroy; override;

Release associated memory and linked library

property Handle: HMODULE read fHandle write fHandle;

The associated library handle

property LibraryPath: TFileName read fLibraryPath;

The loaded library path

1.3.31. ESQLQueryException

ESQLQueryException = class(ESynException)

Generic Exception type raised by the TQuery class

1.3.32. TQueryValue

TQueryValue = object(TObject)

Pseudo-class handling a TQuery bound parameter or column value
- will mimic both TField and TParam classes as defined in standard DB unit, by pointing both classes types to PQueryValue
- usage of an object instead of a class allow faster access via a dynamic array (and our TDynArrayHashed wrapper) for fast property name handling (via name hashing) and pre-allocation
- it is based on an internal Variant to store the parameter or column value

procedure Clear;

Set the column value to null

property AsBlob: TBlobData read GetBlob write SetBlob;

Access the BLOB Value as an AnsiString
- will work for all Delphi versions, including Unicode versions (i.e. since Delphi 2009)
- for a BLOB parameter or column, you should use AsBlob or AsBlob properties instead of AsString (this later won't work after Delphi 2007)

property AsBoolean: Boolean read GetBoolean write SetBoolean;

Access the Value as boolean

property AsBytes: TBytes read GetAsBytes write SetAsBytes;

Access the BLOB Value as array of byte (TBytes)
- will work for all Delphi versions, including Unicode versions (i.e. since Delphi 2009)
- for a BLOB parameter or column, you should use AsBlob or AsBlob properties instead of AsString (this later won't work after Delphi 2007)

property AsCurrency: Currency read GetCurrency write SetCurrency;

Access the Value as Currency
- avoid any rounding conversion, as with AsFloat

property AsDate: TDateTime read GetDateTime write SetDateTime;

Access the Value as TDate

property AsDateTime: TDateTime read GetDateTime write SetDateTime;

Access the Value as TDateTime

property AsFloat: double read GetDouble write SetDouble;

Access the Value as double

property AsInt64: Int64 read GetInt64 write SetInt64;

Access the Value as Int64
- note that under Delphi 5, Int64 is not handled: the Variant type only handle integer types, in this Delphi version :(

property AsInteger: integer read GetInteger write SetInteger;

Access the Value as Integer

property AsLargeInt: Int64 read GetInt64 write SetInt64;

Access the Value as Int64
- note that under Delphi 5, Int64 is not handled: the Variant type only handle integer types, in this Delphi version :(

property AsString: string read GetString write SetString;

Access the Value as String
- used in the VCL world for both TEXT and BLOB content (BLOB content will only work in pre-Unicode Delphi version, i.e. before Delphi 2009)

property AsTime: TDateTime read GetDateTime write SetDateTime;

Access the Value as TTime

property AsVariant: Variant read GetVariant write SetVariant;

Access the Value as Variant

property AsWideString: SynUnicode read GetAsWideString write SetAsWideString;

Access the Value as an unicode String
- will return a WideString before Delphi 2009, and an UnicodeString for Unicode versions of the compiler (i.e. our SynUnicode type)

property Bound: Boolean write SetBound;

Just do nothing - here for compatibility reasons with Clear + Bound := true

property FieldName: string read fName;

The associated (field) name

property IsNull: Boolean read GetIsNull;

Returns TRUE if the stored Value is null

property Name: string read fName;

The associated (parameter) name

property ParamType: TParamType read fParamType write fParamType;

Parameter type for queries or stored procedures

1.3.33. TQuery

TQuery = class(TObject)

Class mapping VCL DB TQuery for direct database process
- this class can mimic basic TQuery VCL methods, but won't need any BDE installed, and will be faster for field and parameters access than the standard TDataSet based implementation; in fact, OleDB replaces the BDE or the DBExpress layer, or access directly to the client library (e.g. for TSQLDBOracleConnectionProperties which calls oci.dll)
- it is able to run basic queries as such:

  Q := TQuery.Create(aSQLDBConnection);
  try
    Q.SQL.Clear; // optional
    Q.SQL.Add('select * from DOMAIN.TABLE');
    Q.SQL.Add('  WHERE ID_DETAIL=:detail;');
    Q.ParamByName('DETAIL').AsString := '123420020100000430015';
    Q.Open;
    Q.First;    // optional
    while not Q.Eof do begin
      assert(Q.FieldByName('id_detail').AsString='123420020100000430015');
      Q.Next;
    end;
    Q.Close;    // optional
  finally
    Q.Free;
  end;

- since there is no underlying TDataSet, you can't have read and write access, or use the visual DB components of the VCL: it's limited to direct emulation of low-level SQL as in the above code, with one-direction retrieval (e.g. the Edit, Post, Append, Cancel, Prior, Locate, Lookup methods do not exist within this class)
- use ToDataSet() function from SynDBVCL to create a TDataSet from such a TQuery instance, and link this request to visual DB components
- this class is Unicode-ready even before Delphi 2009 (via the TQueryValue AsWideString method), will natively handle Int64/TBytes field or parameter data, and will have less overhead than the standard DB components of the VCL
- you should better use TSQLDBStatement instead of this wrapper, but having such code-compatible TQuery replacement could make easier some existing code upgrade (e.g. to avoid deploying the deprecated BDE, generate smaller executable, access any database without paying a big fee, avoid rewriting a lot of existing code lines of a big application...)

constructor Create(aConnection: TSQLDBConnection);

Initialize a query for the associated database connection

destructor Destroy; override;

Release internal memory and statements

function ExecSQLAndReturnUpdateCount: integer;

Begin the SQL query, for a non SELECT statement
- will parse the entered SQL statement, and bind parameters
- the query will be released with a call to Close within this method
- this method will return the number of updated rows (i.e. PreparedSQLDBStatement.UpdateCount)

function FieldByName(const aFieldName: string): TField;

Retrieve a column value from the current opened SQL query row
- will raise an ESQLQueryException error in case of error, e.g. if no column name matchs the supplied name

function FindField(const aFieldName: string): TField;

Retrieve a column value from the current opened SQL query row
- will return nil in case of error, e.g. if no column name matchs the supplied name

function ParamByName(const aParamName: string; CreateIfNotExisting: boolean=true): TParam;

Access a SQL statement parameter, entered as :aParamName in the SQL
- if the requested parameter do not exist yet in the internal fParams list, AND if CreateIfNotExisting=true, a new TQueryValue instance will be created and registered

procedure Close;

End the SQL query
- will release the SQL statement, results and bound parameters
- the query should be released with a call to Close before reopen

procedure ExecSQL;

Begin the SQL query, for a non SELECT statement
- will parse the entered SQL statement, and bind parameters
- the query will be released with a call to Close within this method
- will return the number of updated rows (i.e. PreparedSQLDBStatement.UpdateCount)

procedure First;

After a successfull Open, will get the first row of results

procedure Next;

After successfull Open and First, go the the next row of results

procedure Open;

Begin the SQL query, for a SELECT statement
- will parse the entered SQL statement, and bind parameters
- will then execute the SELECT statement, ready to use First/Eof/Next methods, the returned rows being available via FieldByName methods

procedure Prepare;

A do-nothing method, just available for compatibility purpose

property Active: Boolean read GetActive;

Equals true if the query is opened

property Bof: Boolean read GetBof;

Equals true if on first row

property Connection: TSQLDBConnection read fConnection;

The associated database connection

property Eof: Boolean read GetEof;

Equals true if there is some rows pending

property FieldCount: integer read GetFieldCount;

The number of columns in the current opened SQL query row

property Fields[aIndex: integer]: TField read GetField;

Retrieve a column value from the current opened SQL query row
- will return nil in case of error, e.g. out of range index

property IsEmpty: Boolean read GetIsEmpty;

Equals true if there is no row returned

property ParamCount: integer read GetParamCount;

The number of bound parameters in the current SQL statement

property Params[aIndex: integer]: TParam read GetParam;

Retrieve a bound parameters in the current SQL statement
- will return nil in case of error, e.g. out of range index

property PreparedSQLDBStatement: ISQLDBStatement read fPrepared;

Non VCL property to access the internal SynDB prepared statement
- is nil if the TQuery is not prepared (e.g. after Close)

property RecordCount: integer read GetRecordCount;

Returns 0 if no record was retrievd, 1 if there was some records
- not the exact count: just here for compatibility purpose with code like if aQuery.RecordCount>0 then ...

property SQL: TStringList read fSQL;

The SQL statement to be executed
- statement will be prepared and executed via Open or ExecSQL methods
- SQL.Clear will force a call to the Close method (i.e. reset the query, just as with the default VCL implementation)

property SQLAsText: string read GetSQLAsText;

The SQL statement with inlined bound parameters

property Tag: PtrInt read fTag write fTag;

User-customizable number attached to this instance
- for compatibility with TComponent

1.4. Types implemented in the SynDB unit

1.4.1. PQueryValue

PQueryValue = ^TQueryValue;

Pointer to TQuery bound parameter or column value

1.4.2. TBlobData

TBlobData = RawByteString;

Generic type used by TQuery / TQueryValue for BLOBs fields

1.4.3. TField

TField = PQueryValue;

Pointer mapping the VCL DB TField class
- to be used e.g. with code using local TField instances in a loop

1.4.4. TOnBatchInsert

TOnBatchInsert = procedure(Props: TSQLDBConnectionProperties; const TableName: RawUTF8; const FieldNames: TRawUTF8DynArray; const FieldTypes: TSQLDBFieldTypeArray; RowCount: integer; const FieldValues: TRawUTF8DynArrayDynArray) of object;

Defines a callback signature able to handle multiple INSERT
- may execute e.g. for 2 fields and 3 data rows on a database engine implementing INSERT with multiple VALUES (like MySQL, PostgreSQL, NexusDB, MSSQL or SQlite3), as implemented by TSQLDBConnectionProperties.MultipleValuesInsert() :

 INSERT INTO TableName(FieldNames[0],FieldNames[1]) VALUES
   (FieldValues[0][0],FieldValues[1][0]),
   (FieldValues[0][1],FieldValues[1][1]),
   (FieldValues[0][2],FieldValues[1][2]);

- for other kind of DB which do not support multi values INSERT, may execute a dedicated driver command, like MSSQL "bulk insert" or Firebird "execute block"

1.4.5. TOnSQLDBInfo

TOnSQLDBInfo = procedure(Sender: TSQLDBStatement; const Msg: RawUTF8) of object;

Event handler called when the low-level driver send some warning information
- errors will trigger Exceptions, but sometimes the database driver returns some non critical information, which is logged and may be intercepted using the TSQLDBConnectionProperties.OnStatementInfo property
- may be used e.g. to track ORA-28001 or ORA-28002 about account expire
- is currently implemented by SynDBOracle, SynDBODBC and SynOleDB units

1.4.6. TOnSQLDBProcess

TOnSQLDBProcess = procedure(Sender: TSQLDBConnection; Event: TOnSQLDBProcessEvent) of object;

Event handler called during all external DB process
- event handler is specified by TSQLDBConnectionProperties.OnProcess or TSQLDBConnection.OnProperties properties

1.4.7. TOnSQLDBProcessEvent

TOnSQLDBProcessEvent = ( speConnected, speDisconnected, speNonActive, speActive, speConnectionLost, speReconnected, speStartTransaction, speCommit, speRollback );

Possible events notified to TOnSQLDBProcess callback method
- event handler is specified by TSQLDBConnectionProperties.OnProcess or TSQLDBConnection.OnProcess properties
- speConnected / speDisconnected will notify TSQLDBConnection.Connect and TSQLDBConnection.Disconnect calls
- speNonActive / speActive will be used to notify external DB blocking access, so can be used e.g. to change the mouse cursor shape (this trigger is re-entrant, i.e. it will be executed only once in case of nested calls)
- speReconnected will be called if TSQLDBConnection did successfully recover its database connection (on error, TQuery will call speConnectionLost): this event will be called by TSQLDBConnection.Connect after a regular speConnected notification
- speConnectionLost will be called by TQuery in case of broken connection, and if Disconnect/Reconnect did not restore it as expected (i.e. speReconnected)
- speStartTransaction / speCommit / speRollback will notify the corresponding TSQLDBConnection.StartTransaction, TSQLDBConnection.Commit and TSQLDBConnection.Rollback methods

1.4.8. TParam

TParam = PQueryValue;

Pointer mapping the VCL DB TParam class
- to be used e.g. with code using local TParam instances

1.4.9. TParamType

TParamType = ( ptUnknown, ptInput, ptOutput, ptInputOutput, ptResult );

Represent the use of parameters on queries or stored procedures
- same enumeration as with the standard DB unit from VCL

1.4.10. TQueryValueDynArray

TQueryValueDynArray = array of TQueryValue;

A dynamic array of TQuery bound parameters or column values
- TQuery will use TDynArrayHashed for fast search

1.4.11. TSQLDBColumnCreateDynArray

TSQLDBColumnCreateDynArray = array of TSQLDBColumnCreate;

Used to define how a table is to be created

1.4.12. TSQLDBColumnDefineDynArray

TSQLDBColumnDefineDynArray = array of TSQLDBColumnDefine;

Used to define the column layout of a table schema
- e.g. for TSQLDBConnectionProperties.GetFields

1.4.13. TSQLDBColumnPropertyDynArray

TSQLDBColumnPropertyDynArray = array of TSQLDBColumnProperty;

Used to define a table/field column layout

1.4.14. TSQLDBConnectionPropertiesClass

TSQLDBConnectionPropertiesClass = class of TSQLDBConnectionProperties;

Specify the class of TSQLDBConnectionProperties
- sometimes used to create connection properties instances, from a set of available classes (see e.g. SynDBExplorer or sample 16)

1.4.15. TSQLDBConnectionPropertiesThreadSafeThreadingMode

TSQLDBConnectionPropertiesThreadSafeThreadingMode = ( tmThreadPool, tmMainConnection, tmBackgroundThread );

Threading modes set to TSQLDBConnectionPropertiesThreadSafe.ThreadingMode
- default mode is to use a Thread Pool, i.e. one connection per thread
- or you can force to use the main connection
- or you can use a shared background thread process (not implemented yet)
- last two modes could be used for embedded databases (SQLite3/FireBird), when multiple connections may break stability, consume too much resources and/or decrease performance

1.4.16. TSQLDBDefinition

TSQLDBDefinition = ( dUnknown, dDefault, dOracle, dMSSQL, dJet, dMySQL, dSQLite, dFirebird, dNexusDB, dPostgreSQL, dDB2, dInformix );

The known database definitions
- will be used e.g. for TSQLDBConnectionProperties.SQLFieldCreate(), or for OleDB/ODBC/ZDBC tuning according to the connected database engine

1.4.17. TSQLDBDefinitionLimitPosition

TSQLDBDefinitionLimitPosition = ( posNone, posWhere, posSelect, posAfter, posOuter );

Where the LIMIT clause should be inserted for a given SQL syntax
- used by TSQLDBDefinitionLimitClause and SQLLimitClause() method

1.4.18. TSQLDBDefinitions

TSQLDBDefinitions = set of TSQLDBDefinition;

Set of the available database definitions

1.4.19. TSQLDBFieldTypeDefinition

TSQLDBFieldTypeDefinition = array[TSQLDBFieldType] of RawUTF8;

An array of RawUTF8, for each existing column type
- used e.g. by SQLCreate method
- ftUnknown maps int32 field (e.g. boolean), ftNull maps RawUTF8 index # field, ftUTF8 maps RawUTF8 blob field, other types map their default kind
- for UTF-8 text, ftUTF8 will define the BLOB field, whereas ftNull will expect to be formated with an expected field length in ColumnAttr
- the RowID definition will expect the ORM to create an unique identifier, and will use the ftInt64 type definition for this and send it with the INSERT statement (some databases, like Oracle, do not support standard's IDENTITY attribute) - see http://troels.arvin.dk/db/rdbms

1.4.20. TSQLDBIndexDefineDynArray

TSQLDBIndexDefineDynArray = array of TSQLDBIndexDefine;

Used to describe extended Index definition of a table schema
- e.g. for TSQLDBConnectionProperties.GetIndexes

1.4.21. TSQLDBParamDynArray

TSQLDBParamDynArray = array of TSQLDBParam;

Dynamic array used to store standard binding parameters
- used e.g. by TSQLDBStatementWithParams (and its inherited TSQLDBOracleStatement)

1.4.22. TSQLDBParamInOutType

TSQLDBParamInOutType = ( paramIn, paramOut, paramInOut );

The diverse type of bound parameters during a statement execution
- will be paramIn by default, which is the case 90% of time
- could be set to paramOut or paramInOut if must be refereshed after execution (for calling a stored procedure expecting such parameters)

1.4.23. TSQLDBProcColumnDefineDynArray

TSQLDBProcColumnDefineDynArray = array of TSQLDBProcColumnDefine;

Used to define the parameter/column layout of a stored procedure schema
- e.g. for TSQLDBConnectionProperties.GetProcedureParameters

1.4.24. TSQLDBProxyConnectionCommand

TSQLDBProxyConnectionCommand = ( cGetToken, cGetDBMS, cConnect, cDisconnect, cTryStartTransaction, cCommit, cRollback, cServerTimestamp, cGetFields, cGetIndexes, cGetTableNames, cGetForeignKeys, cExecute, cExecuteToBinary, cExecuteToJSON, cExecuteToExpandedJSON, cQuit, cExceptionRaised );

Proxy commands implemented by TSQLDBProxyConnectionProperties.Process()
- method signature expect "const Input" and "var Output" arguments
- Input is not used for cConnect, cDisconnect, cGetForeignKeys, cTryStartTransaction, cCommit, cRollback and cServerTimestamp
- Input is the TSQLDBProxyConnectionProperties instance for cInitialize
- Input is the RawUTF8 table name for most cGet* metadata commands
- Input is the SQL statement and associated bound parameters for cExecute, cExecuteToBinary, cExecuteToJSON, and cExecuteToExpandedJSON, encoded as TSQLDBProxyConnectionCommandExecute record
- Output is not used for cConnect, cDisconnect, cCommit, cRollback and cExecute
- Output is TSQLDBDefinition (i.e. DBMS type) for cInitialize
- Output is TTimeLog for cServerTimestamp
- Output is boolean for cTryStartTransaction
- Output is TSQLDBColumnDefineDynArray for cGetFields
- Output is TSQLDBIndexDefineDynArray for cGetIndexes
- Output is TSynNameValue (fForeignKeys) for cGetForeignKeys
- Output is TRawUTF8DynArray for cGetTableNames
- Output is RawByteString result data for cExecuteToBinary
- Output is UpdateCount: integer text for cExecute
- Output is RawUTF8 result data for cExecuteToJSON and cExecuteToExpandedJSON
- calls could be declared as such:

 Process(cGetToken,?,result: Int64);
 Process(cGetDBMS,User#1Hash: RawUTF8,fDBMS: TSQLDBDefinition);
 Process(cConnect,?,?);
 Process(cDisconnect,?,?);
 Process(cTryStartTransaction,?,started: boolean);
 Process(cCommit,?,?);
 Process(cRollback,?,?);
 Process(cServerTimestamp,?,result: TTimeLog);
 Process(cGetFields,aTableName: RawUTF8,Fields: TSQLDBColumnDefineDynArray);
 Process(cGetIndexes,aTableName: RawUTF8,Indexes: TSQLDBIndexDefineDynArray);
 Process(cGetTableNames,?,Tables: TRawUTF8DynArray);
 Process(cGetForeignKeys,?,fForeignKeys: TSynNameValue);
 Process(cExecute,Request: TSQLDBProxyConnectionCommandExecute,UpdateCount: integer);
 Process(cExecuteToBinary,Request: TSQLDBProxyConnectionCommandExecute,Data: RawByteString);
 Process(cExecuteToJSON,Request: TSQLDBProxyConnectionCommandExecute,JSON: RawUTF8);
 Process(cExecuteToExpandedJSON,Request: TSQLDBProxyConnectionCommandExecute,JSON: RawUTF8);

- cExceptionRaised is a pseudo-command, used only for sending an exception to the client in case of execution problem on the server side

1.4.25. TSQLDBProxyConnectionProtocolClass

TSQLDBProxyConnectionProtocolClass = class of TSQLDBProxyConnectionProtocol;

Specify the class of a proxy/remote connection to any SynDB engine

1.4.26. TSQLDBSharedTransactionAction

TSQLDBSharedTransactionAction = ( transBegin, transCommitWithoutException, transCommitWithException, transRollback );

Actions implemented by TSQLDBConnectionProperties.SharedTransaction()

1.4.27. TSQLDBStatementCRUD

TSQLDBStatementCRUD = ( cCreate, cRead, cUpdate, cDelete, cPostgreBulkArray );

Identify a CRUD mode of a statement
- in addition to CRUD states, cPostgreBulkArray would identify if the ORM should generate unnested/any bound array statements - currently only supported by SynDBPostgres for bulk insert/update/delete

1.4.28. TSQLDBStatementCRUDs

TSQLDBStatementCRUDs = set of TSQLDBStatementCRUD;

Identify the CRUD modes of a statement
- used e.g. for batch send abilities of a DB engine

1.4.29. TSQLDBStatementGetCol

TSQLDBStatementGetCol = ( colNone, colNull, colWrongType, colDataFilled, colDataTruncated );

Possible column retrieval patterns
- used by TSQLDBColumnProperty.ColumnValueState

1.5. Constants implemented in the SynDB unit

1.5.1. DB_FIELDS

DB_FIELDS: array[TSQLDBDefinition] of TSQLDBFieldTypeDefinition = ( (' INT',' NVARCHAR(%)',' BIGINT',' DOUBLE',' NUMERIC(19,4)',' TIMESTAMP', ' CLOB',' BLOB'), (' INT',' NVARCHAR(%)',' BIGINT',' DOUBLE',' NUMERIC(19,4)',' TIMESTAMP', ' CLOB',' BLOB'), (' NUMBER(22,0)',' NVARCHAR2(%)',' NUMBER(22,0)',' BINARY_DOUBLE',' NUMBER(19,4)', ' DATE',' NCLOB',' BLOB'), (' int',' nvarchar(%)',' bigint',' float',' money',' datetime',' nvarchar(max)', ' varbinary(max)'), (' Long',' VarChar(%)',' Decimal(19,0)',' Double',' Currency',' DateTime', ' LongText',' LongBinary'), (' int',' varchar(%) character set UTF8',' bigint',' double',' decimal(19,4)', ' datetime',' mediumtext character set UTF8',' mediumblob'), (' INTEGER',' TEXT',' INTEGER',' FLOAT',' FLOAT',' TEXT',' TEXT',' BLOB'), (' INTEGER',' VARCHAR(%) CHARACTER SET UTF8',' BIGINT',' FLOAT',' DECIMAL(18,4)', ' TIMESTAMP',' BLOB SUB_TYPE 1 SEGMENT SIZE 2000 CHARACTER SET UTF8', ' BLOB SUB_TYPE 0 SEGMENT SIZE 2000'), (' INTEGER',' NVARCHAR(%)',' LARGEINT',' REAL',' MONEY',' DATETIME',' NCLOB',' BLOB'), (' INTEGER',' TEXT',' BIGINT',' DOUBLE PRECISION',' NUMERIC(19,4)', ' TIMESTAMP',' TEXT',' BYTEA'), (' int',' varchar(%)',' bigint',' real',' decimal(19,4)',' timestamp',' clob', ' blob'), (' int',' lvarchar(%)',' bigint',' smallfloat',' decimal(19,4)', ' datetime year to fraction(3)',' clob', ' blob') );

The known column data types corresponding to our TSQLDBFieldType types
- will be used e.g. for TSQLDBConnectionProperties.SQLFieldCreate()
- see TSQLDBFieldTypeDefinition documentation to find out the mapping

1.5.2. DB_FIELDSMAX

DB_FIELDSMAX: array[TSQLDBDefinition] of cardinal = ( 1000, 1000, 1333, 4000, 255, 4000, 0, 32760, 32767, 0, 32700, 32700);

The known column data types corresponding to our TSQLDBFieldType types
- will be used e.g. for TSQLDBConnectionProperties.SQLFieldCreate()
- SQLite3 doesn't expect any field length, neither PostgreSQL, so set to 0

1.5.3. DB_HANDLECREATEINDEXIFNOTEXISTS

DB_HANDLECREATEINDEXIFNOTEXISTS = [dSQLite, dPostgreSQL];

The known database engines handling CREATE INDEX IF NOT EXISTS statement

1.5.4. DB_HANDLEINDEXONBLOBS

DB_HANDLEINDEXONBLOBS = [dSQLite,dPostgreSQL];

The known database engines handling CREATE INDEX on BLOB columns
- SQLite3 does not have any issue about indexing any column
- PostgreSQL is able to index TEXT columns, which are some kind of CLOB

1.5.5. DB_SERVERTIME

DB_SERVERTIME: array[TSQLDBDefinition] of RawUTF8 = ( '','', 'select sysdate from dual', 'select GETDATE()', '', 'SELECT NOW()', '', 'select current_timestamp from rdb$database', 'SELECT CURRENT_TIMESTAMP', 'SELECT LOCALTIMESTAMP', 'select current timestamp from sysibm.sysdummy1', 'select CURRENT YEAR TO FRACTION(3) from SYSTABLES where tabid = 1' );

The known SQL statement to retrieve the server date and time

1.5.6. DB_SQLDESENDINGINDEXPOS

DB_SQLDESENDINGINDEXPOS: array[TSQLDBDefinition] of (posWithColumn, posGlobalBefore) = ( posWithColumn, posWithColumn, posWithColumn, posWithColumn, posWithColumn, posWithColumn, posWithColumn, posGlobalBefore, posWithColumn, posWithColumn, posWithColumn, posWithColumn);

Where the DESC clause shall be used for a CREATE INDEX statement
- only identified syntax exception is for FireBird

1.5.7. DB_SQLLIMITCLAUSE

DB_SQLLIMITCLAUSE: array[TSQLDBDefinition] of TSQLDBDefinitionLimitClause = ( (Position: posNone; InsertFmt:nil), (Position: posNone; InsertFmt:nil), (Position: posWhere; InsertFmt:'rownum<=%'), (Position: posSelect; InsertFmt:'top(%) '), (Position: posSelect; InsertFmt:'top % '), (Position: posAfter; InsertFmt:' limit %'), (Position: posAfter; InsertFmt:' limit %'), (Position: posSelect; InsertFmt:'first % '), (Position: posSelect; InsertFmt:'top % '), (Position: posAfter; InsertFmt:' limit %'), (Position: posAfter; InsertFmt:' fetch first % rows only'), (Position: posAfter; InsertFmt:' first % '));

Return local server time by default Jet is local -> return local time SQlite is local -> return local time the known SQL syntax to limit the number of returned rows in a SELECT
- Positon indicates if should be included within the WHERE clause, at the beginning of the SQL statement, or at the end of the SQL statement
- InsertFmt will replace '%' with the maximum number of lines to be retrieved
- used by TSQLDBConnectionProperties.AdaptSQLLimitForEngineList()

1.5.8. DB_SQLOPERATOR

DB_SQLOPERATOR: array[opEqualTo..opLike] of RawUTF8 = ( '=','<>','<','<=','>','>=',' in ',' is null',' is not null',' like ');

The SQL text corresponding to the identified WHERE operators for a SELECT

1.5.9. FIXEDLENGTH_SQLDBFIELDTYPE

FIXEDLENGTH_SQLDBFIELDTYPE = [ftInt64, ftDouble, ftCurrency, ftDate];

TSQLDBFieldType kind of columns which have a fixed width

1.5.10. MAP_FIELDTYPE2VARTYPE

MAP_FIELDTYPE2VARTYPE: array[TSQLDBFieldType] of Word = ( varEmpty, varNull, varInt64, varDouble, varCurrency, varDate, varSynUnicode, varString);

Conversion matrix from TSQLDBFieldType into variant type

1.6. Functions or procedures implemented in the SynDB unit

Functions or procedures	Description
BoundArrayToJSONArray	Create a JSON array from an array of UTF-8 bound values
LogTruncatedColumn	FtUnknown, ftNull, ftInt64, ftDouble, ftCurrency, ftDate, ftUTF8, ftBlob function helper logging some column truncation information text
ReplaceParamsByNames	Replace all '?' in the SQL statement with named parameters like :AA :AB..
ReplaceParamsByNumbers	Replace all '?' in the SQL statement with indexed parameters like $1 $2 ...
ToText	Retrieve the text of a given Database SQL dialect enumeration
ToText	Retrieve the ready-to-be displayed text of proxy commands implemented by TSQLDBProxyConnectionProperties.Process()
ToText	Retrieve the text of a given Database field type enumeration
TrimLeftSchema	Retrieve a table name without any left schema
TSQLDBFieldTypeToString	Retrieve the ready-to-be displayed text of a given Database field type enumeration

1.6.1. BoundArrayToJSONArray

function BoundArrayToJSONArray(const Values: TRawUTF8DynArray): RawUTF8;

Create a JSON array from an array of UTF-8 bound values
- as generated during array binding, i.e. with quoted strings 'one','t"wo' -> '{"one","t\"wo"}' and 1,2,3 -> '{1,2,3}'
- as used e.g. by PostgreSQL library

1.6.2. LogTruncatedColumn

procedure LogTruncatedColumn(const Col: TSQLDBColumnProperty);

FtUnknown, ftNull, ftInt64, ftDouble, ftCurrency, ftDate, ftUTF8, ftBlob function helper logging some column truncation information text

1.6.3. ReplaceParamsByNames

function ReplaceParamsByNames(const aSQL: RawUTF8; var aNewSQL: RawUTF8; aStripSemicolon: boolean=true): integer;

Replace all '?' in the SQL statement with named parameters like :AA :AB..
- returns the number of ? parameters found within aSQL
- won't generate any SQL keyword parameters (e.g. :AS :OF :BY), to be compliant with Oracle OCI expectations
- any ending ';' character is deleted, unless aStripSemicolon is unset

1.6.4. ReplaceParamsByNumbers

function ReplaceParamsByNumbers(const aSQL: RawUTF8; var aNewSQL: RawUTF8; IndexChar: AnsiChar = '$'; AllowSemicolon: boolean = false): integer;

Replace all '?' in the SQL statement with indexed parameters like $1 $2 ...
- returns the number of ? parameters found within aSQL
- as used e.g. by PostgreSQL & Oracle (:1 :2) library
- if AllowSemicolon is false (by default), reject any statement with ; (Postgres do not allow ; inside prepared statement); it should be true for Oracle

1.6.5. ToText

function ToText(Field: TSQLDBFieldType): PShortString; overload;

Retrieve the text of a given Database field type enumeration
- see also TSQLDBFieldTypeToString() function

1.6.6. ToText

function ToText(cmd: TSQLDBProxyConnectionCommand): PShortString; overload;

Retrieve the ready-to-be displayed text of proxy commands implemented by TSQLDBProxyConnectionProperties.Process()

1.6.7. ToText

function ToText(DBMS: TSQLDBDefinition): PShortString; overload;

Retrieve the text of a given Database SQL dialect enumeration
- see also TSQLDBConnectionProperties.GetDBMSName() method

1.6.8. TrimLeftSchema

function TrimLeftSchema(const TableName: RawUTF8): RawUTF8;

Retrieve a table name without any left schema
- e.g. TrimLeftSchema('SCHEMA.TABLENAME')='TABLENAME'

1.6.9. TSQLDBFieldTypeToString

function TSQLDBFieldTypeToString(aType: TSQLDBFieldType): TShort16;

Retrieve the ready-to-be displayed text of a given Database field type enumeration

1.7. Variables implemented in the SynDB unit

1.7.1. SynDBLog

SynDBLog: TSynLogClass=TSynLog;

The TSynLog class used for logging for all our SynDB related units
- you may override it with TSQLLog, if available from mORMot
- since not all exceptions are handled specificaly by this unit, you may better use a common TSynLog class for the whole application or module