Permalink
Find file Copy path
60246c6 Oct 26, 2018
4 contributors

Users who have contributed to this file

@synopse @pavelmash @dominikcz @ASiwon
8734 lines (8193 sloc) 369 KB
/// 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
unit SynDB;
{
This file is part of Synopse framework.
Synopse framework. Copyright (C) 2018 Arnaud Bouchez
Synopse Informatique - https://synopse.info
*** BEGIN LICENSE BLOCK *****
Version: MPL 1.1/GPL 2.0/LGPL 2.1
The contents of this file are subject to the Mozilla Public License Version
1.1 (the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.mozilla.org/MPL
Software distributed under the License is distributed on an "AS IS" basis,
WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
for the specific language governing rights and limitations under the License.
The Original Code is Synopse mORMot framework.
The Initial Developer of the Original Code is Arnaud Bouchez.
Portions created by the Initial Developer are Copyright (C) 2018
the Initial Developer. All Rights Reserved.
Contributor(s):
- Adam Siwon (asiwon)
- Alexander (volax)
- Alfred Glaenzer (alf)
- delphinium
- dominikcz
- Esteban Martin (EMartin)
- Joe (at jokusoftware)
- Maciej Izak (hnb)
Alternatively, the contents of this file may be used under the terms of
either the GNU General Public License Version 2 or later (the "GPL"), or
the GNU Lesser General Public License Version 2.1 or later (the "LGPL"),
in which case the provisions of the GPL or the LGPL are applicable instead
of those above. If you wish to allow use of your version of this file only
under the terms of either the GPL or the LGPL, and not to allow others to
use your version of this file under the terms of the MPL, indicate your
decision by deleting the provisions above and replace them with the notice
and other provisions required by the GPL or the LGPL. If you do not delete
the provisions above, a recipient may use your version of this file under
the terms of any one of the MPL, the GPL or the LGPL.
***** END LICENSE BLOCK *****
Version 1.14
- first public release, corresponding to SQLite3 Framework 1.14
Version 1.15
- SynDB unit extracted from previous SynOleDB.pas
- TQueryValue.As* methods now handle NULL column as 0 or ''
- added new TSQLDBRowVariantType custom variant type, allowing late binding
access to row columns (not for Delphi 5) - see RowData method
- fixed transaction handling in a safe abstract manner
- TSQLDBStatement class now expects a prepared statement behavior, therefore
TSQLDBStatementPrepared class has been merged into its parent class, and
inherited classes have been renamed TSQLDBStatementWithParams[AndColumns]
- new TSQLDBStatement.FetchAllAsJSON method for JSON retrieval as RawUTF8
- exposed FetchAllAsJSON method for ISQLDBRows interface
- made the code compatible with Delphi 5
- new TSQLDBConnectionProperties.SQLIso8601ToDate virtual method
- code refactoring for better metadata (database and table schemas) handling,
including GetTableNames, GetFields, GetFieldDefinitions and GetForeignKey
methods - will work with OleDB metadata and direct Oracle sys.all_* tables
- new TSQLDBConnectionProperties.SQLCreate/SQLAddColumn/SQLAddIndex virtual
methods (SQLCreate and SQLAddColumn will use the new protected SQLFieldCreate
virtual method to retrieve the SQL field definition from protected
fSQLCreateField[Max] properties) - as a result, SQL statement generation as
requested for mORMot is now much more generic than previously
- new overloaded TSQLDBStatement.Execute() method, able to mix % and ?
parameters in the SQL statement
- new TSQLDBStatement.BindNull() method
- new TSQLDBConnectionProperties.NewThreadSafeStatementPrepared and
TSQLDBConnection.NewStatementPrepared methods, able to be overridden to
implement a SQL statement caching (used e.g. for SynDBSQLite3)
- new TSQLDBConnection.ServerTimestamp property, which will return the
external database Server current date and time as TTimeLog/Int64 value
(current implementation handle Oracle, MSSQL and MySQL database engines -
with SQLite3, this will be the local PC time, just as for other DB engines)
- new overloaded TSQLDBStatement.Bind() method, which can bind an array
of const (i.e. an open list of Delphi arguments) to a statement
- new overloaded TSQLDBStatement.Bind() and ColumnToVarData() methods, able
to bind or retrieve values from a TVarData/TVarDataDynArray (used e.g.
for direct access to/from SQLite3 virtual table in the SQLite3DB unit)
- new ColumnTimestamp method for TSQLDBStatement/ISQLDBRows, returning a
TTimeLog/Int64 value for a date/time column
Version 1.16
- both TSQLDBStatement.FetchAllToJSON and FetchAllAsJSON methods now return
the number of rows data fetched (excluding field names)
- new class method TSQLDBConnectionProperties.GetFieldDefinition()
- new method TSQLDBStatement.FetchAllToCSVValues() for fast to-file CSV export
- new TSQLDBStatement.ColumnsToSQLInsert() and BindFromRows() methods to allow
fast data conversion/export between databases
- new TSQLDBConnectionProperties.SQLSelectAll method to retrieve a SELECT
statement according to a DB column expected layout
- new TSQLDBConnectionProperties.ClearConnectionPool method (could be used
to recreate all connections in case of DB or network failure/timeout)
- fixed issue in TSQLDBConnection.GetServerTimestamp method
Version 1.17
- code refactoring to allow direct ODBC connection implementation
- fixed random issue in TSQLDBConnection.GetServerTimestamp method (using
wrongly TTimeLog direct arithmetic, therefore raising EncodeTime() errors)
- fixed issue about creating unexisting NCLOB instead of CLOB/NCLOB
- fixed TQuery implementation to match the expected original behavior
(e.g. SQL.Clear) - also undefined buggy Last method (use ORDER DESC instead)
- fixed issue in TQuery when executing requests with parameters
- fixed issues in TQuery when translated SQL from named parameters to
positioned (?) parameters, and escaping strings
- enhanced MySQL DBMS back-end compatibility
- TQuery will now accept reused parameters in the SQL statement (just like
the original class)
- added TQueryValue.AsLargeInt property alias for better compatibility
- enhanced TSQLDBStatement.BindVariant() to handle varBoolean value as integer,
and to avoid most temporary conversions to string
- enhanced TSQLDBStatement.Bind(Params: TVarDataDynArray) to handle varDate,
and modified TQueryValue in consequence
- enhanced TSQLDBStatement.Bind(const Params: array of const) to accept
BLOB content, when transmitted after BinToBase64WithMagic() conversion,
and TDateTime parameters via Date[Time]ToSQL() encoding
- declared TSQLDBConnectionProperties.GetMainConnection() method as virtual,
then override it for thread-safe connections - see ticket [65e24b2de4]
- now TSQLDBStatement.ColumnToVarData method will store '' when TDateTime value
is 0, or a pure date or a pure time if the value is defined as such, just as
expected by http://www.sqlite.org/lang_datefunc.html - i.e. SQLite3DB
- added FieldSize optional parameter to TSQLDBStatement.ColumnType() method
(used e.g. by SynDBVCL to provide the expected field size on TDataSet)
- added TSQLDBStatement.ColumnBlobBytes() methods to retrieve TBytes BLOBs
- added TSQLDBConnection.InTransaction property
- added TSQLDBConnectionProperties.EngineName property
- added TSQLDBConnectionProperties.DBMS property, and huge code refactoring
among all SynDB* units for generic handling of DBMS-specific properties
- added TSQLDBConnectionProperties.AdaptSQLLimitForEngineList for handling
the LIMIT # statement in a database-agnostic form
- added TSQLDBConnectionProperties.BatchSendingAbilities property to define
the CRUD modes available in batch sending (see e.g. Oracle's array bind,
or MS SQL bulk insert feature)
- added direct access to the columns description via new property
TSQLDBStatementWithParamsAndColumns.Columns
- added TSQLDBColumnProperty.ColumnUnique property (mainly for
TSQLDBConnectionProperties.SQLFieldCreate to create proper SQL)
- new TSQLDBStatement.BindArray*() methods, introducing array binding for
faster database batch modifications (only implemented in SynDBOracle by now)
Version 1.18
- SQL statements are now cached by default - in some cases, it will increase
individual reading or writing speed by a factor of 4x
- TSQLDBConnectionProperties.Create will set ForcedSchemaName := 'dbo'
("DataBase Owner") by default for dMSSQL kind of database engine
- introducing TSQLDBConnectionProperties DefinitionTo/DefinitionToJSON/
DefinitionToFile methods and CreateFrom*() class methods to persist the
database connection properties, and the associated class, as JSON
- new TSQLDBConnectionProperties/TSQLDBConnection.OnProcess event handlers
- new TSQLDBConnectionProperties.OnStatementInfo event handler
- added TSQLDBConnectionProperties.StoreVoidStringAsNull, which will be
set e.g. for MS SQL and Jet databases which do not allow by default to
store '' values, but expect NULL instead
- TSQLDBConnection.Connect will now trigger OnProcess(speReconnected) and
update the new TSQLDBConnection.TotalConnectionCount property
- TSQLDBConnection.Disconnect will now flush internal statement cache
- TQuery.Execute() is now able to try to re-connect once in case of failure
- fixed issue with bound parameter in TQuery.Execute() for Unicode Delphi
- introducing new ISQLDBStatement interface, used by SQL statement cache
- avoid syntax error for some engines which do not accept an ending ';' in
SQL statements
- added RaiseExceptionOnError: boolean=false optional parameter to
TSQLDBConnection.NewStatementPrepared() method
- fixed TSQLDBConnection.NewStatementPrepared() so that a prepared statement
currently in use (e.g. for a mORMot virtual table external query with two
similar JOINed clauses) will create up to 9 cache slots - see [736295149a9]
- added TSQLDBConnection.LastErrorMessage and LastErrorException properties,
to retrieve the error when NewStatementPrepared() returned nil
- new TSQLDBConnection.ServerDateTime property, which will return the
external database Server current date and time as TDateTime value
- added TSQLDBConnectionProperties.ConnectionTimeOutMinutes property to
allow automatic recreation of all connections after an idle period of
time, to avoid potential broken connection issues - see [f024266c08]
- added TSQLDBConnectionProperties.ForcedSchemaName optional property
- added TSQLDBConnectionProperties.DBMSEngineName property
- added TSQLDBConnectionProperties.SQLGetIndex() and GetIndexes() methods
to retrieve advanced information about database indexes (e.g. for indexes
created after multiple columns)
- added TSQLDBConnectionProperties.SQLTableName() method
- added TSQLDBConnectionProperties.SQLSplitTableName() and SQLFullTableName()
- now TSQLDBConnectionProperties.SQLAddIndex() will handle schema name and
will ensure that the generated identifier won't be too long
- added TSQLDBConnectionProperties.IsSQLKeyword() method for [7fbbd53966]
- added TSQLDBConnectionProperties.ExecuteInlined() overloaded methods
- added TSQLDBConnectionProperties.LoggedSQLMaxSize property to limit the
logged SQL content as requested by [0b6006e4f5]
- added published TSQLDBConnectionProperties.DatabaseNameSafe property, to
replace TSQLDBConnectionProperties.DatabaseName, triming any internal
TSQLDBConnectionProperties.Password value for safety
- ESQLDBException will now append the current SQL statement to its message,
if TSQLDBConnectionProperties.LogSQLStatementOnException is defined, as
requested by [ea07928ae9]
- added TSQLDBConnectionPropertiesThreadSafe.ForceOnlyOneSharedConnection
property to by-pass internal thread-pool (e.g. for embedded engines)
- enhanced TSQLDBConnectionPropertiesThreadSafe.ThreadSafeConnection speed
- introducing TSQLDBColumnCreate(DynArray) types used when creating columns,
allowing to create 32 bit integer fields (identified as ftUnknown) if needed
- declared all TSQLDBConnectionProperties.SQL*() methods as virtual
- TSQLDBConnectionProperties.SQLAddIndex() will now generate IF NOT EXISTS
statements, if the corresponding DBMS supports it (only SQLite3 AFAIK),
and handle MSSQL as expected (i.e. without 'dbo.' in INDEX name)
- additional aDescending parameter to TSQLDBConnectionProperties.SQLAddIndex()
- added TSQLDBConnectionProperties.OnBatchInsert property and the corresponding
MultipleValuesInsert() protected method to implement INSERT multiple VALUES
- added dFirebird, dNexusDB, dPostgreSQL and dDB2 kind of database in
TSQLDBDefinition, including associated SQL requests to retrieve metadata
- let TSQLDBConnectionProperties.SQLTableName() handle quoted table names
- added TSQLDBConnection.NewTableFromRows() method to dump a SQL statement
result into a new table of any database (may be used for replication)
- "rowCount": is added in TSQLDBStatement.FetchAllToJSON at the end of the
non-expanded JSON content, if needed - improves client parsing performance
- TSQLDBStatement.FetchAllToJSON will now add column names (in non-expanded
JSON format) if no data row is returned - just like TSQLRequest.Execute
- TSQLDBConnectionProperties.SQLSelectAll() now handles spaces in table names
- TSQLDBStatement.GetParamValueAsText() will truncate to a given number of
chars the returned text
- added ForceBlobAsNull property to ISQLDBStatement (used e.g. by SynDBExplorer)
- added RewindToFirst optional parameter to TSQLDBStatement.FetchAllAsJSON()
and FetchAllToJSON() methods (could be used e.g. for TQuery.FetchAllAsJSON)
- added new TSQLDBStatement.ExecutePreparedAndFetchAllAsJSON() method for
direct retrieval of JSON rows from a prepared statement
- added new TSQLDBStatement.PrepareInlined() methods (used by mORMotDB.pas)
- added direct result export into optimized binary content, via the new
TSQLDBStatement.FetchAllToBinary() method (used e.g. by TSQLDBProxyConnection)
- new TSQLDBProxyConnectionProperties, TSQLDBProxyConnection and
TSQLDBProxyStatement abstract classes for generic mean of connection
remoting (to be used e.g. for background thread or remote execution)
- replaced confusing TVarData by a new dedicated TSQLVar memory structure,
shared with mORMot and mORMotSQLite3 units (includes methods refactoring)
- TSQLDBFieldType is now defined in SynCommons, and used by TSQLVar and all
database-related process (i.e. in mORMot and SynDB units)
- added Bind(TSQLVar) overloaded method to ISQLDBStatement/TSQLDBStatement
- added optional BoundType parameter to BindNull() method since some providers
(e.g. OleDB during MULTI INSERT statements - see ticket [e8c211062e581])
expect the column type to be set in BoundType, even for NULL values
- TSQLDBStatement.Bind(const Params: array of const) will accept variant
values for BLOB, as requested by [64f7d840e1bf]
- added missing ColumnToSQLVar() method to ISQLDBRows interface
- exposed FetchAllToJSON method for ISQLDBRows interface
- added TSQLDBStatement.ColumnsToBinary() method
- method TSQLDBStatement.ColumnTypeNativeToDB() is now public, and will
recognize "uniqueidentifier" data type as ftUTF8
- added TSQLDBStatementWithParams.BindFromRows() method
- new TSQLDBProxyStatementRandomAccess class for in-memory browsing of data
retrieved via TSQLDBStatement.FetchAllToBinary()
- added TSQLDBConnectionPropertiesThreadSafe.ThreadingMode property instead
of limited boolean property ForceOnlyOneSharedConnection
- added generic ReplaceParamsByNames() function, which allows 'END;' at the
end of a statement to fulfill ticket [4a7da3c6a1]
- added TSQLDBConnection[Properties].OnProgress callback event handler
- now trim any spaces when retrieving database schema text values
- fixed ticket [4c68975022] about broken SQL statement when logging active
- fixed ticket [545fbe7579] about TSQLDBConnection.LastErrorMessage not reset
- fixed ticket [d465da9843] when guessing SQLite3 column type from its
affinity - see http://www.sqlite.org/datatype3.html
- exception during Commit should leave transaction state - see [ca035b8f0da]
- fixed potential GPF after TSQLDBConnectionProperties.ExecuteNoResult() method call
- fixed TSQLDBConnectionProperties.SQLGetField() returned value for dFirebird
- fixed TSQLDBConnectionProperties.ColumnTypeNativeToDB() for dFirebird
- fixed unnecessary limitation to 64 params for TSQLDBStatementWithParams
- TSQLDBStatement.Bind() will now handle a nil parameter to SQL null bound value
- TSQLDBStatement.ColumnToVariant() will now handle VariantStringAsWideString
- function ReplaceParamsByNames() won't generate any SQL keyword parameters
(e.g. :AS :OF :BY), to be compliant with Oracle OCI expectations
- added property RollbackOnDisconnect, set to TRUE by default, to ensure
any pending uncommitted transaction is roll-backed - see [dc64fe169b]
- added TSQLDBConnectionProperties.SharedTransaction() method to implement
nested transactions, as long as the same connection is re-used
- added TSQLDBConnectionProperties.GetIndexesAndSetFieldsColumnIndexed()
internal method, used by some overridden GetFields() implementations
- ensure a primary key column on SQlite3 is identified as indexed
- added support for getting stored procedure information: TSQLDBProcColumnDefine,
TSQLDBProcColumnDefineDynArray, TSQLDBConnectionProperties.GetProcedureParameters
and SQLGetParameter methods - by EMartin
- added Informix DBMS (dInformix), tested against Informix 11.70 by EMartin
- fixed misallocation of the parameter direction in GetProcedureParameters
- enhancement parsing stored procedure name MS SQL Server (e.g. dbo.procname;1) in SQLSplitProcedureName
- fixed typo SQL statement for getting Firebird stored procedure parameters in SQLGetParameter
- added GetProcedureNames and SQLGetProcedure for listing stored procedure names from current connection
- addes GetViewNames and SQLGetViewNames for listing view names from current connection
- bug fix getting stored procedure parameters on Firebird 3
- small refactoring in TSQLDBConnectionProperties.ExceptionIsAboutConnection
- added support for dInformix and dMSSQL in TSQLDBConnectionProperties.ExceptionIsAboutConnection
- added error codes in TSQLDBConnectionProperties.ExceptionIsAboutConnection for dOracle
- avoid GPI in TSQLDBConnection.GetLastErrorWasAboutConnection when fErrorMessage is empty
- added support for dMySQL in TSQLDBConnectionProperties.ExceptionIsAboutConnection
- added property stripSemicolon to strip last semicolon in query (default = true)
}
{$I Synopse.inc} // define HASINLINE USETYPEINFO CPU32 CPU64 OWNNORMTOUPPER
interface
/// if defined, a TQuery class will be defined to emulate the BDE TQuery class
{$define EMULATES_TQUERY}
/// if defined, a set of classes will be defined to implement remote access
{$define WITH_PROXY}
{$ifdef LVCL}
{$undef EMULATES_TQUERY}
{$endif}
uses
{$ifdef MSWINDOWS}
Windows,
{$else}
{$ifdef KYLIX3}
LibC,
Types,
SynKylix,
{$endif}
{$ifdef FPC}
SynFPCLinux,
{$endif}
{$endif}
{$ifdef FPC}
dynlibs,
{$endif}
{$ifdef ISDELPHIXE2}System.SysUtils,{$else}SysUtils,{$endif}
Classes,
{$ifndef LVCL}
Contnrs,
{$endif}
{$ifndef DELPHI5OROLDER}
Variants,
{$endif}
SynCommons,
SynTable, // for TSynTableStatement
SynLog;
{ -------------- TSQLDB* generic classes and types }
type
// NOTE: TSQLDBFieldType is defined in SynCommons.pas (used by TSQLVar)
/// 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
TSQLDBFieldTypeDefinition = array[TSQLDBFieldType] of RawUTF8;
/// 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)
TSQLDBParamInOutType =
(paramIn, paramOut, paramInOut);
/// 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
TSQLDBColumnDefine = packed record
/// the Column name
ColumnName: 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
ColumnTypeNative: RawUTF8;
/// 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
ColumnLength: PtrInt;
/// the Column data precision
// - used e.g. for numerical values
ColumnPrecision: PtrInt;
/// the Column data scale
// - used e.g. for numerical values
// - may be -1 if the metadata SQL statement returned NULL
ColumnScale: PtrInt;
/// the Column type, as recognized by our SynDB classes
// - should not be ftUnknown nor ftNull
ColumnType: TSQLDBFieldType;
/// specify if column is indexed
ColumnIndexed: boolean;
end;
/// used to define the column layout of a table schema
// - e.g. for TSQLDBConnectionProperties.GetFields
TSQLDBColumnDefineDynArray = array of TSQLDBColumnDefine;
/// used to describe extended Index definition of a table schema
TSQLDBIndexDefine = packed record
/// name of the index
IndexName: 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
TypeDesc: RawUTF8;
/// Expression for the subset of rows included in the filtered index
// - only set for MS SQL - not retrieved for other DB types yet
Filter: RawUTF8;
/// comma separated list of indexed column names, in order of their definition
KeyColumns: 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
IncludedColumns: RawUTF8;
/// if Index is unique
IsUnique: boolean;
/// if Index is part of a PRIMARY KEY constraint
// - only set for MS SQL - not retrieved for other DB types yet
IsPrimaryKey: boolean;
/// if Index is part of a UNIQUE constraint
// - only set for MS SQL - not retrieved for other DB types yet
IsUniqueConstraint: boolean;
end;
/// used to describe extended Index definition of a table schema
// - e.g. for TSQLDBConnectionProperties.GetIndexes
TSQLDBIndexDefineDynArray = array of TSQLDBIndexDefine;
/// 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
TSQLDBProcColumnDefine = packed record
/// the Column name
ColumnName: RawUTF8;
/// the Column type, as retrieved from the database provider
// - used e.g. by TSQLDBConnectionProperties.GetProcedureParameters method
ColumnTypeNative: RawUTF8;
/// 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
ColumnLength: PtrInt;
/// the Column data precision
// - used e.g. for numerical values
ColumnPrecision: PtrInt;
/// the Column data scale
// - used e.g. for numerical values
// - may be -1 if the metadata SQL statement returned NULL
ColumnScale: PtrInt;
/// the Column type, as recognized by our SynDB classes
// - should not be ftUnknown nor ftNull
ColumnType: TSQLDBFieldType;
/// defines the procedure column as a parameter or a result set column
ColumnParamType: TSQLDBParamInOutType;
end;
/// used to define the parameter/column layout of a stored procedure schema
// - e.g. for TSQLDBConnectionProperties.GetProcedureParameters
TSQLDBProcColumnDefineDynArray = array of TSQLDBProcColumnDefine;
/// possible column retrieval patterns
// - used by TSQLDBColumnProperty.ColumnValueState
TSQLDBStatementGetCol = (colNone, colNull, colWrongType, colDataFilled, colDataTruncated);
/// 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
TSQLDBColumnProperty = packed record
/// the Column name
ColumnName: RawUTF8;
/// 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
ColumnAttr: PtrUInt;
/// 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)
ColumnType: TSQLDBFieldType;
/// set if the Column must exists (i.e. should not be null)
ColumnNonNullable: boolean;
/// set if the Column shall have unique value (add the corresponding constraint)
ColumnUnique: 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)
ColumnValueInlined: boolean;
/// expected column data size
// - for TSQLDBOracleStatement/TOleDBStatement/TODBCStatement: used to store
// one column size (in bytes)
ColumnValueDBSize: cardinal;
/// optional character set encoding for ftUTF8 columns
// - for SQLT_STR/SQLT_CLOB (SynDBOracle): equals to the OCI char set
ColumnValueDBCharSet: integer;
/// 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
ColumnValueDBType: smallint;
/// 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
ColumnValueDBForm: byte;
/// may contain the current status of the column value
// - for SynDBODBC: state of the latest SQLGetData() call
ColumnDataState: TSQLDBStatementGetCol;
/// may contain the current column size for not FIXEDLENGTH_SQLDBFIELDTYPE
// - for SynDBODBC: size (in bytes) in corresponding fColData[]
// - TSQLDBProxyStatement: the actual maximum column size
ColumnDataSize: integer;
end;
PSQLDBColumnProperty = ^TSQLDBColumnProperty;
/// used to define a table/field column layout
TSQLDBColumnPropertyDynArray = array of TSQLDBColumnProperty;
/// used to define how a column to be created
TSQLDBColumnCreate = record
/// the data type
// - here, ftUnknown is used for Int32 values, ftInt64 for Int64 values,
// as expected by TSQLDBFieldTypeDefinition
DBType: TSQLDBFieldType;
/// the column name
Name: RawUTF8;
/// the width, e.g. for VARCHAR() types
Width: cardinal;
/// if the column should be unique
Unique: boolean;
/// if the column should be non null
NonNullable: boolean;
/// if the column is the ID primary key
PrimaryKey: boolean;
end;
/// used to define how a table is to be created
TSQLDBColumnCreateDynArray = array of TSQLDBColumnCreate;
/// identify a CRUD mode of a statement
TSQLDBStatementCRUD = (
cCreate, cRead, cUpdate, cDelete);
/// identify the CRUD modes of a statement
// - used e.g. for batch send abilities of a DB engine
TSQLDBStatementCRUDs = set of TSQLDBStatementCRUD;
/// the known database definitions
// - will be used e.g. for TSQLDBConnectionProperties.SQLFieldCreate(), or
// for OleDB/ODBC/ZDBC tuning according to the connected database engine
TSQLDBDefinition = (dUnknown, dDefault, dOracle, dMSSQL, dJet, dMySQL,
dSQLite, dFirebird, dNexusDB, dPostgreSQL, dDB2, dInformix);
/// set of the available database definitions
TSQLDBDefinitions = set of TSQLDBDefinition;
{$M+}
TSQLDBStatement = class;
{$M-}
{$ifndef LVCL}
{$ifndef DELPHI5OROLDER}
/// a custom variant type used to have direct access to a result row content
// - use ISQLDBRows.RowData method to retrieve such a Variant
TSQLDBRowVariantType = class(TSynInvokeableVariantType)
protected
procedure IntGet(var Dest: TVarData; const V: TVarData; Name: PAnsiChar); override;
procedure IntSet(const V, Value: TVarData; Name: PAnsiChar); override;
end;
{$endif}
{$endif}
/// 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
ISQLDBRows = interface
['{11291095-9C15-4984-9118-974F1926DB9F}']
/// 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
// ! while Step do // loop through all matching data rows
// ! assert(Copy(Customer.AccountNumber,1,8)='AW000001');
// ! end;
function Step(SeekFirst: boolean=false): boolean;
/// the column/field count of the current Row
function ColumnCount: integer;
/// 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 ColumnName(Col: integer): RawUTF8;
/// 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 ColumnIndex(const aColumnName: RawUTF8): integer;
/// 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 ColumnType(Col: integer; FieldSize: PInteger=nil): TSQLDBFieldType;
/// returns TRUE if the column contains NULL
function ColumnNull(Col: integer): boolean;
/// return a Column integer value of the current Row, first Col is 0
function ColumnInt(Col: integer): Int64; overload;
/// return a Column floating point value of the current Row, first Col is 0
function ColumnDouble(Col: integer): double; overload;
/// return a Column floating point value of the current Row, first Col is 0
function ColumnDateTime(Col: integer): TDateTime; overload;
/// return a column date and time value of the current Row, first Col is 0
function ColumnTimestamp(Col: integer): TTimeLog; overload;
/// return a Column currency value of the current Row, first Col is 0
function ColumnCurrency(Col: integer): currency; overload;
/// return a Column UTF-8 encoded text value of the current Row, first Col is 0
function ColumnUTF8(Col: integer): RawUTF8; overload;
/// return a Column text value as generic VCL string of the current Row, first Col is 0
function ColumnString(Col: integer): string; overload;
/// return a Column as a blob value of the current Row, first Col is 0
function ColumnBlob(Col: integer): RawByteString; overload;
/// return a Column as a blob value of the current Row, first Col is 0
function ColumnBlobBytes(Col: integer): TBytes; overload;
/// 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 ColumnToSQLVar(Col: Integer; var Value: TSQLVar;
var Temp: RawByteString);
{$ifndef LVCL}
/// 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(Col: integer): Variant; 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 ColumnToVariant(Col: integer; var Value: Variant): TSQLDBFieldType; overload;
{$endif}
/// 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 ColumnCursor(Col: integer): ISQLDBRows; overload;
/// return a Column integer value of the current Row, from a supplied column name
function ColumnInt(const ColName: RawUTF8): Int64; overload;
/// return a Column floating point value of the current Row, from a supplied column name
function ColumnDouble(const ColName: RawUTF8): double; overload;
/// return a Column floating point value of the current Row, from a supplied column name
function ColumnDateTime(const ColName: RawUTF8): TDateTime; overload;
/// return a column date and time value of the current Row, from a supplied column name
function ColumnTimestamp(const ColName: RawUTF8): TTimeLog; overload;
/// return a Column currency value of the current Row, from a supplied column name
function ColumnCurrency(const ColName: RawUTF8): currency; overload;
/// return a Column UTF-8 encoded text value of the current Row, from a supplied column name
function ColumnUTF8(const ColName: RawUTF8): RawUTF8; overload;
/// return a Column text value as generic VCL string of the current Row, from a supplied column name
function ColumnString(const ColName: RawUTF8): string; overload;
/// return a Column as a blob value of the current Row, from a supplied column name
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;
{$ifndef LVCL}
/// return a Column as a variant, from a supplied column name
function ColumnVariant(const ColName: RawUTF8): Variant; overload;
/// 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 GetColumnVariant(const ColName: RawUTF8): Variant;
/// 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(const ColName: RawUTF8): ISQLDBRows; overload;
/// 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']));
// ! 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
property Column[const ColName: RawUTF8]: Variant read GetColumnVariant; default;
{$ifndef DELPHI5OROLDER}
/// 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 := RowDaa;
// ! while Step do
// ! writeln(Row.FirstName,Row.BirthDate);
// ! end;
function RowData: Variant;
/// create a TDocVariant custom variant containing all columns values
// - will create a "fast" TDocVariant object instance with all fields
procedure RowDocVariant(out aDocument: variant;
aOptions: TDocVariantOptions=JSON_OPTIONS_FAST);
{$endif DELPHI5OROLDER}
{$endif LVCL}
/// return the associated statement instance
function Instance: TSQLDBStatement;
// 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
// - you can go back to the first row of data before creating the JSON, if
// RewindToFirst is TRUE (could be used e.g. for TQuery.FetchAllAsJSON)
// - if ReturnedRowCount points to an integer variable, it will be filled with
// the number of row data returned (excluding field names)
// - similar to corresponding TSQLRequest.Execute method in SynSQLite3 unit
function FetchAllAsJSON(Expanded: boolean; ReturnedRowCount: PPtrInt=nil;
RewindToFirst: boolean=false): RawUTF8;
// 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,..] }
// - 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
// - you can go back to the first row of data before creating the JSON, if
// RewindToFirst is TRUE (could be used e.g. for TQuery.FetchAllAsJSON)
// - returns the number of row data returned (excluding field names)
function FetchAllToJSON(JSON: TStream; Expanded: boolean;
RewindToFirst: boolean=false): PtrInt;
/// 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 FetchAllToBinary(Dest: TStream; MaxRowCount: cardinal=0;
DataRowPosition: PCardinalDynArray=nil): cardinal;
end;
/// 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()
ISQLDBStatement = interface(ISQLDBRows)
['{EC27B81C-BD57-47D4-9711-ACFA27B583D7}']
/// 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 BindNull(Param: Integer; IO: TSQLDBParamInOutType=paramIn;
BoundType: TSQLDBFieldType=ftNull);
/// bind an integer value to a parameter
// - the leftmost SQL parameter has an index of 1
procedure Bind(Param: Integer; Value: Int64;
IO: TSQLDBParamInOutType=paramIn); overload;
/// bind a double value to a parameter
// - the leftmost SQL parameter has an index of 1
procedure Bind(Param: Integer; Value: double;
IO: TSQLDBParamInOutType=paramIn); overload;
/// bind a TDateTime value to a parameter
// - the leftmost SQL parameter has an index of 1
procedure BindDateTime(Param: Integer; Value: TDateTime;
IO: TSQLDBParamInOutType=paramIn); overload;
/// bind a currency value to a parameter
// - the leftmost SQL parameter has an index of 1
procedure BindCurrency(Param: Integer; Value: currency;
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 buffer text (#0 ended) to a parameter
// - the leftmost SQL parameter has an index of 1
procedure BindTextP(Param: Integer; Value: PUTF8Char;
IO: TSQLDBParamInOutType=paramIn); overload;
/// bind a UTF-8 encoded string 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 BindTextW(Param: Integer; const Value: WideString;
IO: TSQLDBParamInOutType=paramIn); overload;
/// bind a Blob buffer to a parameter
// - the leftmost SQL parameter has an index of 1
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 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 BindVariant(Param: Integer; const Data: Variant; DataIsBlob: boolean;
IO: TSQLDBParamInOutType=paramIn);
/// bind one TSQLVar value
// - the leftmost SQL parameter has an index of 1
procedure Bind(Param: Integer; const Data: TSQLVar;
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; ParamType: TSQLDBFieldType; const Value: RawUTF8;
ValueAlreadyUnquoted: boolean; IO: TSQLDBParamInOutType=paramIn); overload;
/// 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
procedure Bind(const Params: array of const;
IO: TSQLDBParamInOutType=paramIn); overload;
/// 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 BindFromRows(const Fields: TSQLDBFieldTypeDynArray;
Rows: TSQLDBStatement);
/// 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 BindCursor(Param: integer);
/// 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 BoundCursor(Param: Integer): ISQLDBRows;
/// 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; ParamType: TSQLDBFieldType;
const Values: TRawUTF8DynArray; ValuesCount: integer); 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 Int64); 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 double); overload;
/// 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 BindArrayDateTime(Param: Integer; const Values: array of TDateTime);
/// 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 BindArrayCurrency(Param: Integer; const Values: array of currency);
/// 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; const Values: array of RawUTF8); overload;
{$ifndef LVCL}
/// 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 ParamToVariant(Param: Integer; var Value: Variant;
CheckIsOutParameter: boolean=true): TSQLDBFieldType;
{$endif}
/// execute a prepared SQL statement
// - parameters marked as ? should have been already bound with Bind*() functions
// - should raise an Exception on any error
// - after execution, you can access any returned data via ISQLDBRows methods
procedure ExecutePrepared;
// 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
procedure ExecutePreparedAndFetchAllAsJSON(Expanded: boolean; out JSON: RawUTF8);
function GetForceBlobAsNull: boolean;
procedure SetForceBlobAsNull(value: boolean);
/// 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 ForceBlobAsNull: boolean read GetForceBlobAsNull write SetForceBlobAsNull;
function GetForceDateWithMS: boolean;
procedure SetForceDateWithMS(value: boolean);
/// 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)
property ForceDateWithMS: boolean read GetForceDateWithMS write SetForceDateWithMS;
/// gets a number of updates made by latest executed statement
function UpdateCount: Integer;
end;
{$ifdef WITH_PROXY}
/// 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
TSQLDBProxyConnectionCommand = (
cGetToken,cGetDBMS,
cConnect, cDisconnect, cTryStartTransaction, cCommit, cRollback,
cServerTimestamp,
cGetFields, cGetIndexes, cGetTableNames, cGetForeignKeys,
cExecute, cExecuteToBinary, cExecuteToJSON, cExecuteToExpandedJSON,
cQuit, cExceptionRaised);
{$endif WITH_PROXY}
{$M+} { published properties to be logged as JSON }
TSQLDBConnection = class;
TSQLDBConnectionProperties = class;
{$M-}
/// where the LIMIT clause should be inserted for a given SQL syntax
// - used by TSQLDBDefinitionLimitClause and SQLLimitClause() method
TSQLDBDefinitionLimitPosition = (posNone, posWhere, posSelect, posAfter, posOuter);
/// defines the LIMIT clause to be inserted for a given SQL syntax
// - used by TSQLDBDefinitionLimitClause and SQLLimitClause() method
TSQLDBDefinitionLimitClause = record
Position: TSQLDBDefinitionLimitPosition;
InsertFmt: PUTF8Char;
end;
/// 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
TOnSQLDBProcessEvent = (
speConnected, speDisconnected,
speNonActive, speActive,
speConnectionLost, speReconnected,
speStartTransaction, speCommit, speRollback);
/// event handler called during all external DB process
// - event handler is specified by TSQLDBConnectionProperties.OnProcess or
// TSQLDBConnection.OnProperties properties
TOnSQLDBProcess = procedure(Sender: TSQLDBConnection; Event: TOnSQLDBProcessEvent) 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
TOnSQLDBInfo = procedure(Sender: TSQLDBStatement; const Msg: RawUTF8) of object;
/// actions implemented by TSQLDBConnectionProperties.SharedTransaction()
TSQLDBSharedTransactionAction = (transBegin,
transCommitWithoutException, transCommitWithException, transRollback);
/// 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"
TOnBatchInsert = procedure(Props: TSQLDBConnectionProperties;
const TableName: RawUTF8; const FieldNames: TRawUTF8DynArray;
const FieldTypes: TSQLDBFieldTypeArray; RowCount: integer;
const FieldValues: TRawUTF8DynArrayDynArray) of object;
/// bit set to identify columns, e.g. null columns
TSQLDBProxyStatementColumns = set of 0..255;
/// pointer to a bit set to identify columns, e.g. null columns
PSQLDBProxyStatementColumns = ^TSQLDBProxyStatementColumns;
/// 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)
TSQLDBConnectionPropertiesClass = class of TSQLDBConnectionProperties;
/// 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
TSQLDBConnectionProperties = class
protected
fServerName: RawUTF8;
fDatabaseName: RawUTF8;
fPassWord: RawUTF8;
fUserID: RawUTF8;
fForcedSchemaName: RawUTF8;
fMainConnection: TSQLDBConnection;
fBatchSendingAbilities: TSQLDBStatementCRUDs;
fBatchMaxSentAtOnce: integer;
fLoggedSQLMaxSize: integer;
fLogSQLStatementOnException: boolean;
fOnBatchInsert: TOnBatchInsert;
{$ifndef UNICODE}
fVariantWideString: boolean;
{$endif}
fUseCache: boolean;
fRollbackOnDisconnect: boolean;
fStoreVoidStringAsNull: boolean;
fForeignKeys: TSynNameValue;
fSQLCreateField: TSQLDBFieldTypeDefinition;
fSQLCreateFieldMax: cardinal;
fSQLGetServerTimestamp: RawUTF8;
fEngineName: RawUTF8;
fDBMS: TSQLDBDefinition;
fOnProcess: TOnSQLDBProcess;
fOnStatementInfo: TOnSQLDBInfo;
fConnectionTimeOutTicks: Int64;
fSharedTransactions: array of record
SessionID: cardinal;
RefCount: integer;
Connection: TSQLDBConnection;
end;
procedure SetConnectionTimeOutMinutes(minutes: cardinal);
function GetConnectionTimeOutMinutes: cardinal;
// this default implementation just returns the fDBMS value or dDefault
// (never returns dUnknwown)
function GetDBMS: TSQLDBDefinition; virtual;
function GetDBMSName: RawUTF8; virtual;
function GetForeignKeysData: RawByteString;
procedure SetForeignKeysData(const Value: RawByteString);
function FieldsFromList(const aFields: TSQLDBColumnDefineDynArray; aExcludeTypes: TSQLDBFieldTypes): RawUTF8;
function GetMainConnection: TSQLDBConnection; virtual;
function GetDatabaseNameSafe: RawUTF8; virtual;
/// any overriden TSQLDBConnectionProperties class should call it in the
// initialization section of its implementation unit to be recognized
class procedure RegisterClassNameForDefinition;
/// will be called at the end of constructor
// - this default implementation will do nothing
procedure SetInternalProperties; virtual;
/// Assign schema name to owner from ForceSchemaName or UserID or Database name
procedure SetSchemaNameToOwner(out Owner: RawUTF8); virtual;
/// SQL statement to get all field/column names for a specified Table
// - used by GetFieldDefinitions public method
// - should return a SQL "SELECT" statement with the field names as first
// column, a textual field type as 2nd column, then field length, then
// numeric precision and scale as 3rd, 4th and 5th columns, and the index
// count in 6th column
// - this default implementation just returns nothing
// - if this method is overridden, the ColumnTypeNativeToDB() method should
// also be overridden in order to allow conversion from native column
// type into the corresponding TSQLDBFieldType
function SQLGetField(const aTableName: RawUTF8): RawUTF8; virtual;
/// SQL statement to get advanced information about all indexes for a Table
// - should return a SQL "SELECT" statement with the index names as first
function SQLGetIndex(const aTableName: RawUTF8): RawUTF8; virtual;
/// SQL statement to get all parameter for a specified Stored Procedure
// - used by GetProcedureParameters public method
// - should return a SQL "SELECT" statement with the parameter names as first
// column, a textual field type as 2nd column, then parameter length as 3rd, then
// parameter direction as 4th
// - this default implementation just returns nothing
// - if this method is overridden, the ColumnTypeNativeToDB() method should
// also be overridden in order to allow conversion from native column
// type into the corresponding TSQLDBFieldType
function SQLGetParameter(const aProcName: RawUTF8): RawUTF8; virtual;
/// SQL statement to get all stored procedure names for current connection
// - used by GetProcedureNames public method
// - should return a SQL "SELECT" statement with the procedure names as unique column
// - this default implementation just returns nothing
// - if this method is overridden, the ColumnTypeNativeToDB() method should
// also be overridden in order to allow conversion from native column
// type into the corresponding TSQLDBFieldType
function SQLGetProcedure: RawUTF8; virtual;
/// SQL statement to get all table names
// - used by GetTableNames public method
// - should return a SQL "SELECT" statement with the table names as
// first column (any other columns will be ignored)
// - this default implementation just returns nothing
function SQLGetTableNames: RawUTF8; virtual;
/// SQL statement to get all view names
// - used by GetViewNames public method
// - should return a SQL "SELECT" statement with the view names as
// first column (any other columns will be ignored)
// - this default implementation just returns nothing
function SQLGetViewNames: RawUTF8; virtual;
/// should initialize fForeignKeys content with all foreign keys of this
// database
// - used by GetForeignKey method
procedure GetForeignKeys; virtual; abstract;
/// will use fSQLCreateField[Max] to create the SQL column definition
// - this default virtual implementation will handle properly all supported
// database engines, assuming aField.ColumnType as in TSQLDBFieldTypeDefinition
// - if the field is a primary key, aAddPrimaryKey may be modified to contain
// some text to be appended at the end of the ALTER/CREATE TABLE statement
function SQLFieldCreate(const aField: TSQLDBColumnCreate;
var aAddPrimaryKey: RawUTF8): RawUTF8; virtual;
/// wrapper around GetIndexes() + set Fields[].ColumnIndexed in consequence
// - used by some overridden versions of GetFields() method
procedure GetIndexesAndSetFieldsColumnIndexed(const aTableName: RawUTF8;
var Fields: TSQLDBColumnDefineDynArray);
/// check if the exception or its error message is about DB connection error
// - will be used by TSQLDBConnection.LastErrorWasAboutConnection method
// - default method will check for the 'conne' sub-string in the message text
// - should be overridden depending on the error message returned by the DB
function ExceptionIsAboutConnection(aClass: ExceptClass; const aMessage: RawUTF8): boolean; virtual;
/// generic method able to implement OnBatchInsert() with parameters
// - for MySQL, PostgreSQL, MSSQL2008, NexusDB or SQlite3, will execute
// (with parameters) the extended standard syntax:
// $ 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 Firebird, will run the corresponding EXECUTE BLOCK() statement
// with parameters - but Firebird sounds slower than without any parameter
// (as tested with ZDBC/ZEOS or UniDAC)
// - for Oracle, will run (with parameters for values):
// $ INSERT ALL
// $ INTO TableName(FieldNames[0],FieldNames[1]) VALUES (?,?)
// $ INTO TableName(FieldNames[0],FieldNames[1]) VALUES (?,?)
// $ INTO TableName(FieldNames[0],FieldNames[1]) VALUES (?,?)
// $ SELECT 1 FROM DUAL;
procedure MultipleValuesInsert(Props: TSQLDBConnectionProperties;
const TableName: RawUTF8; const FieldNames: TRawUTF8DynArray;
const FieldTypes: TSQLDBFieldTypeArray; RowCount: integer;
const FieldValues: TRawUTF8DynArrayDynArray);
/// Firebird-dedicated method able to implement OnBatchInsert()
// - will run an EXECUTE BLOCK statement without any parameters, but
// including inlined values - sounds to be faster on ZEOS/ZDBC!
procedure MultipleValuesInsertFirebird(Props: TSQLDBConnectionProperties;
const TableName: RawUTF8; const FieldNames: TRawUTF8DynArray;
const FieldTypes: TSQLDBFieldTypeArray; RowCount: integer;
const FieldValues: TRawUTF8DynArrayDynArray);
public
/// initialize the properties
// - children may optionaly handle the fact that no UserID or Password
// is supplied here, by displaying a corresponding Dialog box
constructor Create(const aServerName, aDatabaseName, aUserID, aPassWord: RawUTF8); virtual;
/// release related memory, and close MainConnection
destructor Destroy; override;
/// 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 DefinitionTo(Definition: TSynConnectionDefinition); 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
function DefinitionToJSON(Key: cardinal=0): RawUTF8; virtual;
/// 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 DefinitionToFile(const aJSONFile: TFileName; Key: cardinal=0);
/// create a new TSQLDBConnectionProperties instance from the stored values
class function CreateFrom(aDefinition: TSynConnectionDefinition): 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
class function CreateFromJSON(const aJSONDefinition: RawUTF8;
aKey: cardinal=0): TSQLDBConnectionProperties; virtual;
/// 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 CreateFromFile(const aJSONFile: TFileName;
aKey: cardinal=0): TSQLDBConnectionProperties;
/// retrieve the registered class from the aDefinition.Kind string
class function ClassFrom(aDefinition: TSynConnectionDefinition): TSQLDBConnectionPropertiesClass;
/// 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 NewConnection: 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)
function ThreadSafeConnection: TSQLDBConnection; 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 ClearConnectionPool; virtual;
/// 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 ConnectionTimeOutMinutes: cardinal
read GetConnectionTimeOutMinutes write SetConnectionTimeOutMinutes;
/// create a new thread-safe statement
// - this method will call ThreadSafeConnection.NewStatement
function NewThreadSafeStatement: TSQLDBStatement;
/// create a new thread-safe statement from an internal cache (if any)
// - will call ThreadSafeConnection.NewStatementPrepared
// - this method should return a prepared statement instance on success
// - on error, returns nil and you can check Connnection.LastErrorMessage /
// Connection.LastErrorException to retrieve corresponding error information
// (if RaiseExceptionOnError is left to default FALSE value, otherwise, it will
// raise an exception)
function NewThreadSafeStatementPrepared(const aSQL: RawUTF8;
ExpectResults: Boolean; RaiseExceptionOnError: Boolean=false): ISQLDBStatement; overload;
/// create a new thread-safe statement from an internal cache (if any)
// - this method will call the overloaded NewThreadSafeStatementPrepared method
// - here Args[] array does not refer to bound parameters, but to values
// to be changed within SQLFormat in place of '%' characters (this method
// will call FormatUTF8() internaly); parameters will be bound directly
// on the returned TSQLDBStatement instance
// - this method should return a prepared statement instance on success
// - on error, returns nil and you can check Connnection.LastErrorMessage /
// Connection.LastErrorException to retrieve correspnding error information
function NewThreadSafeStatementPrepared(const SQLFormat: RawUTF8;
const Args: array of const; 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 PrepareInlined(const aSQL: RawUTF8; 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 SQLFormat: RawUTF8; const Args: array of const;
ExpectResults: Boolean): ISQLDBStatement; overload;
/// 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']));
// ! 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
// ! while Step do
// ! writeln(R.FirstName,' ',DateToStr(R.BirthDate));
// ! end;
// - you can any BLOB field to be returned as null with the ForceBlobAsNull
// optional parameter
function Execute(const aSQL: RawUTF8; const Params: array of const
{$ifndef LVCL}{$ifndef DELPHI5OROLDER}; RowsVariant: PVariant=nil{$endif}{$endif};
ForceBlobAsNull: boolean=false): ISQLDBRows;
/// 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)
function ExecuteNoResult(const aSQL: RawUTF8; const Params: array of const): integer;
/// 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 ExecuteInlined(const aSQL: RawUTF8; 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 SQLFormat: RawUTF8; const Args: array of const;
ExpectResults: Boolean): ISQLDBRows; overload;
/// 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 SharedTransaction(SessionID: cardinal;
action: TSQLDBSharedTransactionAction): TSQLDBConnection; virtual;
/// convert a textual column data type, as retrieved e.g. from SQLGetField,
// into our internal primitive types
// - default implementation will always return ftUTF8
function ColumnTypeNativeToDB(const aNativeType: RawUTF8; aScale: integer): TSQLDBFieldType; 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 SQLCreate(const aTableName: RawUTF8;
const aFields: TSQLDBColumnCreateDynArray; aAddID: boolean): 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 SQLAddColumn(const aTableName: RawUTF8;
const aField: TSQLDBColumnCreate): 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 SQLAddIndex(const aTableName: RawUTF8;
const aFieldNames: array of RawUTF8; aUnique: boolean;
aDescending: boolean=false;
const aIndexName: RawUTF8=''): 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 SQLSelectAll(const aTableName: RawUTF8;
const aFields: TSQLDBColumnDefineDynArray; aExcludeTypes: TSQLDBFieldTypes): RawUTF8; virtual;
/// SQL statement to create the corresponding database
// - this default implementation will only handle dFirebird by now
function SQLCreateDatabase(const aDatabaseName: RawUTF8;
aDefaultPageSize: integer=0): 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 SQLIso8601ToDate(const Iso8601: RawUTF8): 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
procedure SQLSplitTableName(const aTableName: RawUTF8; out Owner, Table: 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 SQLSplitProcedureName(const aProcName: RawUTF8; out Owner, Package, ProcName: 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 SQLFullTableName(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 SQLTableName(const aTableName: RawUTF8): RawUTF8; 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 GetFields(const aTableName: RawUTF8; out Fields: TSQLDBColumnDefineDynArray); 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 GetIndexes(const aTableName: RawUTF8; out Indexes: TSQLDBIndexDefineDynArray); virtual;
/// 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 GetFieldDefinitions(const aTableName: RawUTF8;
out Fields: TRawUTF8DynArray; WithForeignKeys: boolean);
/// get one field/column definition as text
// - return column type as 'Name [Type Length Precision Scale]'
class function GetFieldDefinition(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;';
class function GetFieldORMDefinition(const Column: TSQLDBColumnDefine): RawUTF8;
/// check if the supplied text word is not a keyword for a given database engine
class function IsSQLKeyword(aDB: TSQLDBDefinition; aWord: RawUTF8): boolean; overload; virtual;
/// check if the supplied text word is not a keyword for the current database engine
// - just a wrapper around the overloaded class function
function IsSQLKeyword(aWord: RawUTF8): boolean; overload;
/// retrieve a list of stored procedure names from current connection
procedure GetProcedureNames(out Procedures: TRawUTF8DynArray); virtual;
/// retrieve procedure input/output parameter information
// - aProcName: stored procedure name to retrieve parameter infomation.
// - Parameters: parameter list info (name, datatype, direction, default)
procedure GetProcedureParameters(const aProcName: RawUTF8;
out Parameters: TSQLDBProcColumnDefineDynArray); virtual;
/// get all table names
// - this default implementation will use protected SQLGetTableNames virtual
// method to retrieve the table names
procedure GetTableNames(out Tables: TRawUTF8DynArray); virtual;
/// get all view names
// - this default implementation will use protected SQLGetViewNames virtual
// method to retrieve the view names
procedure GetViewNames(out Views: TRawUTF8DynArray); virtual;
/// 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 GetForeignKey(const aTableName, aColumnName: RawUTF8): RawUTF8;
/// 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 SQLLimitClause(AStmt: TSynTableStatement): TSQLDBDefinitionLimitClause; virtual;
/// determine if the SQL statement can be cached
// - used by TSQLDBConnection.NewStatementPrepared() for handling cache
function IsCachable(P: PUTF8Char): boolean; virtual;
/// return the database engine name, as computed from the class name
// - 'TSQLDBConnectionProperties' will be trimmed left side of the class name
class function EngineName: RawUTF8;
/// 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 MainConnection: TSQLDBConnection read GetMainConnection;
/// the associated User Password, as specified at creation
// - not published, for security reasons (may be serialized otherwise)
property PassWord: RawUTF8 read fPassWord;
/// 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 DatabaseName: RawUTF8 read fDatabaseName;
/// 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 ForeignKeysData: RawByteString
read GetForeignKeysData write SetForeignKeysData;
/// 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 OnProcess: TOnSQLDBProcess read fOnProcess write fOnProcess;
/// this event handler will be called when statements trigger some low-level
// information
property OnStatementInfo: TOnSQLDBInfo read fOnStatementInfo write fOnStatementInfo;
/// 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 OnBatchInsert: TOnBatchInsert read fOnBatchInsert write fOnBatchInsert;
published { to be logged as JSON - no UserID nor Password for security :) }
/// return the database engine name, as computed from the class name
// - 'TSQLDBConnectionProperties' will be trimmed left side of the class name
property Engine: RawUTF8 read fEngineName;
/// the associated server name, as specified at creation
property ServerName: RawUTF8 read fServerName;
/// the associated database name, safely trimmed from the password
// - this property would have any matching Password value content deleted
// before serialization, for security reasons
property DatabaseNameSafe: RawUTF8 read GetDatabaseNameSafe;
/// the associated User Identifier, as specified at creation
property UserID: RawUTF8 read fUserID;
/// the remote DBMS type, as stated by the inheriting class itself, or
// retrieved at connecton time (e.g. for ODBC)
property DBMS: TSQLDBDefinition read GetDBMS;
/// the remote DBMS type name, retrieved as text from the DBMS property
property DBMSEngineName: RawUTF8 read GetDBMSName;
/// the abilities of the database for batch sending
// - e.g. Oracle will handle array DML binds, or MS SQL bulk insert
property BatchSendingAbilities: TSQLDBStatementCRUDs read fBatchSendingAbilities;
/// 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 BatchMaxSentAtOnce: integer read fBatchMaxSentAtOnce write fBatchMaxSentAtOnce;
/// the maximum size, in bytes, of logged SQL statements
// - default 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 (could be set e.g. to 2048 to log up to 2KB per statement)
property LoggedSQLMaxSize: integer read fLoggedSQLMaxSize write fLoggedSQLMaxSize;
/// allow to log the SQL statement when any low-level ESQLDBException is raised
property LogSQLStatementOnException: boolean read fLogSQLStatementOnException
write fLogSQLStatementOnException;
/// 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 ForcedSchemaName: RawUTF8 read fForcedSchemaName write fForcedSchemaName;
/// 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 UseCache: boolean read fUseCache write fUseCache;
/// 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 RollbackOnDisconnect: Boolean read fRollbackOnDisconnect write fRollbackOnDisconnect;
/// 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 StoreVoidStringAsNull: Boolean read fStoreVoidStringAsNull write fStoreVoidStringAsNull;
{$ifndef UNICODE}
/// 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
property VariantStringAsWideString: boolean read fVariantWideString write fVariantWideString;
{$endif}
end;
{$ifdef WITH_PROXY}
/// 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
TSQLDBProxyConnectionProtocol = class
protected
fAuthenticate: TSynAuthentication;
fTransactionSessionID: integer;
fTransactionRetryTimeout: Int64;
fTransactionActiveTimeout: Int64;
fTransactionActiveAutoReleaseTicks: Int64;
fLock: TRTLCriticalSection;
function GetAuthenticate: TSynAuthentication;
/// default Handle*() will just return the incoming value
function HandleInput(const input: RawByteString): RawByteString; virtual;
function HandleOutput(const output: RawByteString): RawByteString; virtual;
/// default trial transaction
function TransactionStarted(connection: TSQLDBConnection;
sessionID: integer): boolean; virtual;
procedure TransactionEnd(sessionID: integer); virtual;
public
/// initialize a protocol, with a given authentication scheme
// - if no authentication is given, none will be processed
constructor Create(aAuthenticate: TSynAuthentication); reintroduce;
/// release associated authentication class
destructor Destroy; override;
/// the associated authentication information
// - you can manage users via AuthenticateUser/DisauthenticateUser methods
property Authenticate: TSynAuthentication read GetAuthenticate write fAuthenticate;
end;
/// 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.pas
TSQLDBRemoteConnectionProtocol = class(TSQLDBProxyConnectionProtocol)
protected
/// SynLZ decompression + digital signature + encryption
function HandleInput(const input: RawByteString): RawByteString; override;
/// SynLZ compression + digital signature + encryption
function HandleOutput(const output: RawByteString): RawByteString; override;
public
end;
/// specify the class of a proxy/remote connection to any SynDB engine
TSQLDBProxyConnectionProtocolClass = class of TSQLDBProxyConnectionProtocol;
{$endif WITH_PROXY}
/// abstract connection created from TSQLDBConnectionProperties
// - more than one TSQLDBConnection instance can be run for the same
// TSQLDBConnectionProperties
TSQLDBConnection = class
protected
fProperties: TSQLDBConnectionProperties;
fErrorException: ExceptClass;
fErrorMessage: RawUTF8;
fTransactionCount: integer;
fServerTimestampOffset: TDateTime;
fServerTimestampAtConnection: TDateTime;
fCache: TRawUTF8ListHashed;
fOnProcess: TOnSQLDBProcess;
fTotalConnectionCount: integer;
fInternalProcessActive: integer;
fRollbackOnDisconnect: Boolean;
fLastAccessTicks: Int64;
function IsOutdated: boolean; // do not make virtual
function GetInTransaction: boolean; virtual;
function GetServerTimestamp: TTimeLog;
function GetServerDateTime: TDateTime; virtual;
function GetLastErrorWasAboutConnection: boolean;
/// raise an exception if IsConnected returns false
procedure CheckConnection;
/// call OnProcess() call back event, if needed
procedure InternalProcess(Event: TOnSQLDBProcessEvent);
public
/// connect to a specified database engine
constructor Create(aProperties: TSQLDBConnectionProperties); virtual;
/// release memory and connection
destructor Destroy; override;
/// 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 Connect; 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 Disconnect; virtual;
/// return TRUE if Connect has been already successfully called
function IsConnected: boolean; virtual; abstract;
/// initialize a new SQL query statement for the given connection
// - the caller should free the instance after use
function NewStatement: TSQLDBStatement; virtual; abstract;
/// initialize a new SQL query statement for the given connection
// - this default implementation will call the NewStatement method, and
// implement handle statement caching is UseCache=true - in this case,
// the TSQLDBStatement.Reset method shall have been 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 correspnding error information
// - on error, if RaiseExceptionOnError=true, an exception is raised
function NewStatementPrepared(const aSQL: RawUTF8;
ExpectResults: Boolean; RaiseExceptionOnError: Boolean=false): ISQLDBStatement; virtual;
/// begin a Transaction for this connection
// - this default implementation will check and set TransactionCount
procedure StartTransaction; virtual;
/// commit changes of a Transaction for this connection
// - StartTransaction method must have been called before
// - this default implementation will check and set TransactionCount
procedure Commit; virtual;
/// discard changes of a Transaction for this connection
// - StartTransaction method must have been called before
// - this default implementation will check and set TransactionCount
procedure Rollback; virtual;
/// direct export of a DB statement rows into a new table of this database
// - the corresponding table will be created within the current connection,
// if it does not exist
// - 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
function NewTableFromRows(const TableName: RawUTF8;
Rows: TSQLDBStatement; WithinTransaction: boolean;
ColumnForcedTypes: TSQLDBFieldTypeDynArray=nil): integer;
{$ifdef WITH_PROXY}
/// 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 RemoteProcessMessage(const Input: RawByteString;
out Output: RawByteString; Protocol: TSQLDBProxyConnectionProtocol); virtual;
{$endif}
/// 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 timestamp in TTimeLog / TTimeLogBits /
// Int64 value
property ServerTimestamp: TTimeLog read GetServerTimestamp;
/// 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 ServerDateTime: TDateTime read GetServerDateTime;
/// 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 OnProcess: TOnSQLDBProcess read fOnProcess write fOnProcess;
published { to be logged as JSON }
/// returns TRUE if the connection was set
property Connected: boolean read IsConnected;
/// the time returned by the server when the connection occurred
property ServerTimestampAtConnection: TDateTime read fServerTimestampAtConnection;
/// number of sucessfull connections for this instance
// - can be greater than 1 in case of re-connection via Disconnect/Connect
property TotalConnectionCount: integer read fTotalConnectionCount;
/// number of nested StartTransaction calls
// - equals 0 if no transaction is active
property TransactionCount: integer read fTransactionCount;
/// TRUE if StartTransaction has been called
// - check if TransactionCount>0
property InTransaction: boolean read GetInTransaction;
/// 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 RollbackOnDisconnect: Boolean
read fRollbackOnDisconnect write fRollbackOnDisconnect;
/// some error message, e.g. during execution of NewStatementPrepared
property LastErrorMessage: RawUTF8 read fErrorMessage write fErrorMessage;
/// some error exception, e.g. during execution of NewStatementPrepared
property LastErrorException: ExceptClass read fErrorException;
/// 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 LastErrorWasAboutConnection: boolean read GetLastErrorWasAboutConnection;
/// the associated database properties
property Properties: TSQLDBConnectionProperties read fProperties;
end;
/// 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
TSQLDBStatement = class(TInterfacedObject, ISQLDBRows, ISQLDBStatement)
protected
fStripSemicolon: boolean;
fConnection: TSQLDBConnection;
fSQL: RawUTF8;
fExpectResults: boolean;
fParamCount: integer;
fColumnCount: integer;
fTotalRowsRetrieved: Integer;
fCurrentRow: Integer;
fSQLWithInlinedParams: RawUTF8;
fForceBlobAsNull: boolean;
fForceDateWithMS: boolean;
fDBMS: TSQLDBDefinition;
function GetSQLWithInlinedParams: RawUTF8;
function GetForceBlobAsNull: boolean;
procedure SetForceBlobAsNull(value: boolean);
function GetForceDateWithMS: boolean;
procedure SetForceDateWithMS(value: boolean);
/// raise an exception if Col is out of range according to fColumnCount
procedure CheckCol(Col: integer); {$ifdef HASINLINE}inline;{$endif}
/// will set a Int64/Double/Currency/TDateTime/RawUTF8/TBlobData Dest variable
// from a given column value
// - internal conversion will use a temporary Variant and ColumnToVariant method
// - expects Dest to be of the exact type (e.g. Int64, not Integer)
function ColumnToTypedValue(Col: integer; DestType: TSQLDBFieldType; var Dest): TSQLDBFieldType;
/// retrieve the inlined value of a given parameter, e.g. 1 or 'name'
// - use ParamToVariant() virtual method
// - optional MaxCharCount will truncate the text to a given number of chars
function GetParamValueAsText(Param, MaxCharCount: integer): RawUTF8; virtual;
/// append the inlined value of a given parameter
// - use GetParamValueAsText() method
// - optional MaxCharCount will truncate the text to a given number of chars
procedure AddParamValueAsText(Param: integer; Dest: TTextWriter;
MaxCharCount: integer); virtual;
{$ifndef LVCL}
/// return a Column as a variant
function GetColumnVariant(const ColName: RawUTF8): Variant;
{$endif}
/// return the associated statement instance for a ISQLDBRows interface
function Instance: TSQLDBStatement;
public
/// create a statement instance
constructor Create(aConnection: TSQLDBConnection); virtual;
/// 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 BindNull(Param: Integer; IO: TSQLDBParamInOutType=paramIn;
BoundType: TSQLDBFieldType=ftNull); virtual; abstract;
/// bind an integer value to a parameter
// - the leftmost SQL parameter has an index of 1
procedure Bind(Param: Integer; Value: Int64;
IO: TSQLDBParamInOutType=paramIn); overload; virtual; abstract;
/// bind a double value to a parameter
// - the leftmost SQL parameter has an index of 1
procedure Bind(Param: Integer; Value: double;
IO: TSQLDBParamInOutType=paramIn); overload; virtual; abstract;
/// bind a TDateTime value to a parameter
// - the leftmost SQL parameter has an index of 1
procedure BindDateTime(Param: Integer; Value: TDateTime;
IO: TSQLDBParamInOutType=paramIn); overload; virtual; abstract;
/// bind a currency value 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 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 buffer text (#0 ended) to a parameter
// - the leftmost SQL parameter has an index of 1
procedure BindTextP(Param: Integer; Value: PUTF8Char;
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 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 BindTextW(Param: Integer; const Value: WideString;
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; 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 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 BindVariant(Param: Integer; const Data: Variant; DataIsBlob: boolean;
IO: TSQLDBParamInOutType=paramIn); 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; const Data: TSQLVar;
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; ParamType: TSQLDBFieldType; const Value: RawUTF8;
ValueAlreadyUnquoted: boolean; 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(const Params: array of const;
IO: TSQLDBParamInOutType=paramIn); overload; virtual;
/// 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 BindFromRows(const Fields: TSQLDBFieldTypeDynArray;
Rows: TSQLDBStatement);
/// 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
// - this default method will raise an exception about unexpected behavior
procedure BindCursor(Param: integer); virtual;
/// 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
// - this default method will raise an exception about unexpected behavior
function BoundCursor(Param: Integer): ISQLDBRows; 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; ParamType: TSQLDBFieldType;
const Values: TRawUTF8DynArray; ValuesCount: integer); 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 Int64); 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; const Values: array of double); overload; 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 BindArrayDateTime(Param: Integer; const Values: array of TDateTime); 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 BindArrayCurrency(Param: Integer; const Values: array of currency); 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 BindArray(Param: Integer; const Values: array of RawUTF8); 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 Prepare(const aSQL: RawUTF8; ExpectResults: Boolean); overload; 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 ExecutePrepared; 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 Reset; virtual;
/// 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 Execute(const aSQL: RawUTF8; ExpectResults: Boolean); 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 aSQL: RawUTF8; ExpectResults: Boolean;
const 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 SQLFormat: RawUTF8; ExpectResults: Boolean;
const Args, Params: array of const); overload;
/// 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 ExecutePreparedAndFetchAllAsJSON(Expanded: boolean; out JSON: RawUTF8); virtual;
/// gets a number of updates made by latest executed statement
// - default implementation returns 0
function UpdateCount: integer; virtual;
{$ifndef LVCL}
/// 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 ParamToVariant(Param: Integer; var Value: Variant;
CheckIsOutParameter: boolean=true): TSQLDBFieldType; virtual;
{$endif}
/// 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');
// ! end;
// ! end;
function Step(SeekFirst: boolean=false): boolean; virtual; abstract;
/// the column/field count of the current Row
function ColumnCount: integer;
/// 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 ColumnName(Col: integer): RawUTF8; 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 ColumnIndex(const aColumnName: RawUTF8): integer; 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 ColumnType(Col: integer; FieldSize: PInteger=nil): TSQLDBFieldType; virtual; abstract;
/// returns TRUE if the column contains NULL
function ColumnNull(Col: integer): boolean; virtual; abstract;
/// return a Column integer value of the current Row, first Col is 0
function ColumnInt(Col: integer): Int64; overload; virtual; abstract;
/// return a Column floating point value of the current Row, first Col is 0
function ColumnDouble(Col: integer): double; overload; virtual; abstract;
/// return a Column date and time value of the current Row, first Col is 0
function ColumnDateTime(Col: integer): TDateTime; overload; virtual; abstract;
/// 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 ColumnTimestamp(Col: integer): TTimeLog; overload;
/// return a Column currency value of the current Row, first Col is 0
function ColumnCurrency(Col: integer): currency; overload; virtual; abstract;
/// return a Column UTF-8 encoded text value of the current Row, first Col is 0
function ColumnUTF8(Col: integer): RawUTF8; overload; virtual; abstract;
/// 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(Col: integer): string; overload; virtual;
/// return a Column as a blob value of the current Row, first Col is 0
function ColumnBlob(Col: integer): RawByteString; overload; virtual; abstract;
/// 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 ColumnBlobBytes(Col: integer): TBytes; overload; virtual;
{$ifndef LVCL}
/// 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(Col: integer): Variant; 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 ColumnToVariant(Col: integer; var Value: Variant): TSQLDBFieldType; virtual;
{$endif}
/// 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 ColumnToSQLVar(Col: Integer; var Value: TSQLVar;
var Temp: RawByteString); virtual;
/// 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;
/// return a Column integer value of the current Row, from a supplied column name
function ColumnInt(const ColName: RawUTF8): Int64; overload;
/// return a Column floating point value of the current Row, from a supplied column name
function ColumnDouble(const ColName: RawUTF8): double; overload;
/// return a Column date and time value of the current Row, from a supplied column name
function ColumnDateTime(const ColName: RawUTF8): TDateTime; 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(const ColName: RawUTF8): TTimeLog; overload;
/// return a Column currency value of the current Row, from a supplied column name
function ColumnCurrency(const ColName: RawUTF8): currency; overload;
/// return a Column UTF-8 encoded text value of the current Row, from a supplied column name
function ColumnUTF8(const ColName: RawUTF8): RawUTF8; overload;
/// return a Column text value as generic VCL string of the current Row, from a supplied column name
function ColumnString(const ColName: RawUTF8): string; overload;
/// return a Column as a blob value of the current Row, from a supplied column name
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;
{$ifndef LVCL}
/// return a Column as a variant, from a supplied column name
function ColumnVariant(const ColName: RawUTF8): Variant; overload;
{$ifndef DELPHI5OROLDER}
/// 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);
// ! end;
function RowData: Variant; virtual;
/// create a TDocVariant custom variant containing all columns values
// - will create a "fast" TDocVariant object instance with all fields
procedure RowDocVariant(out aDocument: variant;
aOptions: TDocVariantOptions=JSON_OPTIONS_FAST); virtual;
{$endif}
{$endif}
/// 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(const ColName: RawUTF8): ISQLDBRows; overload;
/// 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 ColumnsToJSON(WR: TJSONWriter); 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 ColumnsToSQLInsert(const TableName: RawUTF8;
var Fields: TSQLDBColumnCreateDynArray): RawUTF8; virtual;
// 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,..] }
// - 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
// - you can go back to the first row of data before creating the JSON, if
// RewindToFirst is TRUE (could be used e.g. for TQuery.FetchAllAsJSON)
// - 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 FetchAllToJSON(JSON: TStream; Expanded: boolean;
RewindToFirst: boolean=false): 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 FetchAllToCSVValues(Dest: TStream; Tab: boolean; CommaSep: AnsiChar=',';
AddBOM: boolean=true): PtrInt;
// 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)
// - you can go back to the first row of data before creating the JSON, if
// RewindToFirst is TRUE (could be used e.g. for TQuery.FetchAllAsJSON)
// - similar to corresponding TSQLRequest.Execute method in SynSQLite3 unit
function FetchAllAsJSON(Expanded: boolean; ReturnedRowCount: PPtrInt=nil;
RewindToFirst: boolean=false): RawUTF8;
/// 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 FetchAllToBinary(Dest: TStream; MaxRowCount: cardinal=0;
DataRowPosition: PCardinalDynArray=nil): cardinal; 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 ColumnsToBinary(W: TFileBufferWriter;
const Null: TSQLDBProxyStatementColumns;
const ColTypes: TSQLDBFieldTypeDynArray); virtual;
published
/// the prepared SQL statement, as supplied to Prepare() method
property SQL: RawUTF8 read fSQL;
/// the prepared SQL statement, with all '?' changed into the supplied
// parameter values
property SQLWithInlinedParams: RawUTF8 read GetSQLWithInlinedParams;
/// the current row after Execute call, corresponding to Column*() methods
// - contains 0 in case of no (more) available data, or a number >=1
property CurrentRow: Integer read fCurrentRow;
/// the total number of data rows retrieved by this instance
// - is not reset when there is no more row of available data (Step returns
// false), or when Step() is called with SeekFirst=true
property TotalRowsRetrieved: Integer read fTotalRowsRetrieved;
/// the associated database connection
property Connection: TSQLDBConnection read fConnection;
/// strip last semicolon in query
// - expectation may vary, depending on the SQL statement and the engine
// - default is true
property StripSemicolon: boolean read fStripSemicolon write fStripSemicolon;
end;
/// 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
TSQLDBConnectionThreadSafe = class(TSQLDBConnection)
protected
fThreadID: TThreadID;
end;
/// 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
TSQLDBConnectionPropertiesThreadSafeThreadingMode = (
tmThreadPool, tmMainConnection, tmBackgroundThread);
/// connection properties which will implement an internal Thread-Safe
// connection pool
TSQLDBConnectionPropertiesThreadSafe = class(TSQLDBConnectionProperties)
protected
fConnectionPool: TObjectList;
fLatestConnectionRetrievedInPool: integer;
fConnectionCS: TRTLCriticalSection;
fThreadingMode: TSQLDBConnectionPropertiesThreadSafeThreadingMode;
/// returns -1 if none was defined yet
function CurrentThreadConnectionIndex: Integer;
/// overridden method to properly handle multi-thread
function GetMainConnection: TSQLDBConnection; override;
public
/// initialize the properties
// - this overridden method will initialize the internal per-thread connection pool
constructor Create(const aServerName, aDatabaseName, aUserID, aPassWord: RawUTF8); override;
/// release related memory, and all per-thread connections
destructor Destroy; override;
/// get a thread-safe connection
// - this overridden implementation will define a per-thread TSQLDBConnection
// connection pool, via an internal pool
function ThreadSafeConnection: TSQLDBConnection; 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 ClearConnectionPool; override;
/// 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
procedure EndCurrentThread; virtual;
/// 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
property ThreadingMode: TSQLDBConnectionPropertiesThreadSafeThreadingMode
read fThreadingMode write fThreadingMode;
end;
/// 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
TSQLDBParam = packed record
/// 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
VData: RawByteString;
/// 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)
VArray: TRawUTF8DynArray;
/// the column/parameter Value type
VType: TSQLDBFieldType;
/// define if parameter can be retrieved after a stored procedure execution
VInOut: TSQLDBParamInOutType;
/// used e.g. by TSQLDBOracleStatement
VDBType: word;
/// storage used for ftInt64, ftDouble, ftDate and ftCurrency value
VInt64: Int64;
end;
PSQLDBParam = ^TSQLDBParam;
/// dynamic array used to store standard binding parameters
// - used e.g. by TSQLDBStatementWithParams (and its
// inherited TSQLDBOracleStatement)
TSQLDBParamDynArray = array of TSQLDBParam;
/// generic abstract class handling prepared statements with binding
// - will provide protected fields and methods for handling standard
// TSQLDBParam parameters
TSQLDBStatementWithParams = class(TSQLDBStatement)
protected
fParams: TSQLDBParamDynArray;
fParam: TDynArray;
fParamsArrayCount: integer;
function CheckParam(Param: Integer; NewType: TSQLDBFieldType;
IO: TSQLDBParamInOutType): PSQLDBParam; overload;
function CheckParam(Param: Integer; NewType: TSQLDBFieldType;
IO: TSQLDBParamInOutType; ArrayCount: integer): PSQLDBParam; overload;
/// append the inlined value of a given parameter
// - faster overridden method
procedure AddParamValueAsText(Param: integer; Dest: TTextWriter;
MaxCharCount: integer); override;
public
/// create a statement instance
// - this overridden version will initialize the internal fParam* fields
constructor Create(aConnection: TSQLDBConnection); 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 BindNull(Param: Integer; IO: TSQLDBParamInOutType=paramIn;
BoundType: TSQLDBFieldType=ftNull); override;
/// bind an integer 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 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: double;
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 BindDateTime(Param: Integer; Value: TDateTime;
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 BindCurrency(Param: Integer; Value: currency;
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 BindTextU(Param: Integer; const Value: RawUTF8;
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 BindTextP(Param: Integer; Value: PUTF8Char;
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 BindTextS(Param: Integer; const Value: string;
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 BindTextW(Param: Integer; const Value: WideString;
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; 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 M
procedure BindBlob(Param: Integer; const Data: RawByteString;
IO: TSQLDBParamInOutType=paramIn); 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 BindArray(Param: Integer; ParamType: TSQLDBFieldType;
const Values: TRawUTF8DynArray; ValuesCount: integer); 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 Int64); 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 double); overload; 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 BindArrayDateTime(Param: Integer; const Values: array of TDateTime); 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 BindArrayCurrency(Param: Integer; const Values: array of currency); 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; const Values: array of RawUTF8); overload; override;
/// 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 BindArrayRowPrepare(const aParamTypes: array of TSQLDBFieldType;
aExpectedMinimalRowCount: integer=0);
/// 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 BindArrayRow(const aValues: array of const);
/// 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 BindFromRows(Rows: TSQLDBStatement); virtual;
{$ifndef LVCL}
/// 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
function ParamToVariant(Param: Integer; var Value: Variant;
CheckIsOutParameter: boolean=true): TSQLDBFieldType; override;
{$endif}
/// Reset the previous prepared statement
// - this overridden implementation will just do reset the internal fParams[]
procedure Reset; override;
end;
/// 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
TSQLDBStatementWithParamsAndColumns = class(TSQLDBStatementWithParams)
protected
fColumns: TSQLDBColumnPropertyDynArray;
fColumn: TDynArrayHashed;
public
/// create a statement instance
// - this overridden version will initialize the internal fColumn* fields
constructor Create(aConnection: TSQLDBConnection); 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 ColumnName(Col: integer): RawUTF8; 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 ColumnIndex(const aColumnName: RawUTF8): integer; 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
function ColumnType(Col: integer; FieldSize: PInteger=nil): TSQLDBFieldType; override;
/// direct access to the columns description
// - gives more details than the default ColumnType() function
property Columns: TSQLDBColumnPropertyDynArray read fColumns;
end;
/// generic Exception type, as used by the SynDB unit
ESQLDBException = class(ESynException)
protected
fStatement: TSQLDBStatement;
public
/// 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
constructor CreateUTF8(const Format: RawUTF8; const Args: array of const);
published
/// associated TSQLDBStatement instance, if supplied as first parameter
property Statement: TSQLDBStatement read fStatement;
end;
{$ifdef WITH_PROXY}
/// exception raised during remote connection process
ESQLDBRemote = class(ESQLDBException);
/// 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
TSQLDBProxyConnectionCommandExecute = packed record
/// the associated SQL statement
SQL: RawUTF8;
/// input parameters
// - trunked to the exact number of parameters
Params: TSQLDBParamDynArray;
/// if input parameters expected BindArray() process
ArrayCount: integer;
/// 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
Force: set of (fBlobAsNull, fDateWithMS, fNoUpdateCount);
end;
/// implements a proxy-like virtual connection statement to a DB engine
// - will generate TSQLDBProxyConnection kind of connection
TSQLDBProxyConnectionPropertiesAbstract = class(TSQLDBConnectionProperties)
protected
fHandleConnection: boolean;
fProtocol: TSQLDBProxyConnectionProtocol;
fCurrentSession: integer;
fStartTransactionTimeOut: Int64;
/// abstract process of internal commands
// - one rough unique method is used, in order to make easier several
// implementation schemes and reduce data marshalling as much as possible
// - should raise an exception on error
// - returns the session ID (if any)
function Process(Command: TSQLDBProxyConnectionCommand;
const Input; var Output): integer; virtual; abstract;
/// calls Process(cGetToken) + Process(cGetDBMS)
// - override this method and set fProtocol before calling inherited
procedure SetInternalProperties; override;
/// calls Process(cGetForeignKeys,self,fForeignKeys)
procedure GetForeignKeys; override;
public
/// will notify for proxy disconnection
destructor Destroy; override;
/// create a new TSQLDBProxyConnection instance
// - the caller is responsible of freeing this instance
function NewConnection: TSQLDBConnection; override;
/// retrieve the column/field layout of a specified table
// - calls Process(cGetFields,aTableName,Fields)
procedure GetFields(const aTableName: RawUTF8; out Fields: TSQLDBColumnDefineDynArray); override;
/// retrieve the advanced indexed information of a specified Table
// - calls Process(cGetIndexes,aTableName,Indexes)
procedure GetIndexes(const aTableName: RawUTF8; out Indexes: TSQLDBIndexDefineDynArray); override;
/// get all table names
// - this default implementation will use protected SQLGetTableNames virtual
// - calls Process(cGetTableNames,self,Tables)
procedure GetTableNames(out Tables: TRawUTF8DynArray); override;
/// determine if the SQL statement can be cached
// - always returns false, to force a new fake statement to be created
function IsCachable(P: PUTF8Char): boolean; override;
published
/// 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 HandleConnection: boolean read fHandleConnection write fHandleConnection;
/// 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
property StartTransactionTimeOut: Int64
read fStartTransactionTimeOut write fStartTransactionTimeOut;
end;
/// 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
TSQLDBProxyConnection = class(TSQLDBConnection)
protected
fConnected: boolean;
fProxy: TSQLDBProxyConnectionPropertiesAbstract;
function GetServerDateTime: TDateTime; override;
public
/// connect to a specified database engine
constructor Create(aProperties: TSQLDBConnectionProperties); override;
/// connect to the specified database
procedure Connect; override;
/// stop connection to the specified database
procedure Disconnect; override;
/// return TRUE if Connect has been already successfully called
function IsConnected: boolean; override;
/// initialize a new SQL query statement for the given connection
function NewStatement: TSQLDBStatement; override;
/// begin a Transaction for this connection
procedure StartTransaction; override;
/// commit changes of a Transaction for this connection
procedure Commit; override;
/// discard changes of a Transaction for this connection
procedure Rollback; override;
end;
/// 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()
TSQLDBProxyStatementAbstract = class(TSQLDBStatementWithParamsAndColumns)
protected
fDataRowCount: integer;
fDataRowReaderOrigin, fDataRowReader: PByte;
fDataRowNullSize: cardinal;
fDataCurrentRowNullLen: cardinal;
fDataCurrentRowNull: TSQLDBProxyStatementColumns;
fDataCurrentRowIndex: integer;
fDataCurrentRowValues: array of pointer;
fDataCurrentRowValuesStart: pointer;
fDataCurrentRowValuesSize: Cardinal;
// per-row column type (SQLite3 only) e.g. select coalesce(column,0) from ..
fDataCurrentRowColTypes: array of TSQLDBFieldType;
function IntColumnType(Col: integer; out Data: PByte): TSQLDBFieldType;
{$ifdef HASINLINE}inline;{$endif}
procedure IntHeaderProcess(Data: PByte; DataLen: integer);
procedure IntFillDataCurrent(var Reader: PByte; IgnoreColumnDataSize: boolean);
public
/// the Column type of the current Row
function ColumnType(Col: integer; FieldSize: PInteger=nil): TSQLDBFieldType; override;
/// returns TRUE if the column contains NULL
function ColumnNull(Col: integer): boolean; override;
/// return a Column integer value of the current Row, first Col is 0
function ColumnInt(Col: integer): Int64; 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 ColumnDateTime(Col: integer): TDateTime; 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 ColumnCurrency(Col: integer): currency; override;
/// return a Column UTF-8 encoded text value of the current Row, first Col is 0
function ColumnUTF8(Col: integer): RawUTF8; override;
/// return a Column text value as generic VCL string of the current Row, first Col is 0
function ColumnString(Col: integer): string; override;
/// return a Column as a blob value of the current Row, first Col is 0
function ColumnBlob(Col: integer): RawByteString; override;
/// return all columns values into JSON content
procedure ColumnsToJSON(WR: TJSONWriter); override;
/// 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 ColumnData(Col: integer): pointer;
/// 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 ColumnsToBinary(W: TFileBufferWriter;
const Null: TSQLDBProxyStatementColumns;
const ColTypes: TSQLDBFieldTypeDynArray); override;
/// read-only access to the number of data rows stored
property DataRowCount: integer read fDataRowCount;
end;
/// 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)
TSQLDBProxyStatement = class(TSQLDBProxyStatementAbstract)
protected
fDataInternalCopy: RawByteString;
fUpdateCount: integer;
fForceNoUpdateCount: boolean;
procedure ParamsToCommand(var Input: TSQLDBProxyConnectionCommandExecute);
public
/// 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 ExecutePrepared; override;
/// 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 overridden implementation will use JSON for transmission, and
// binary encoding only for parameters (to avoid unneeded conversions, e.g.
// when called from mORMotDB.pas)
procedure ExecutePreparedAndFetchAllAsJSON(Expanded: boolean; out JSON: RawUTF8); 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 FetchAllToBinary(Dest: TStream; MaxRowCount: cardinal=0;
DataRowPosition: PCardinalDynArray=nil): cardinal; override;
/// gets a number of updates made by latest executed statement
// - this overriden method will return the integer value returned by
// cExecute command
function UpdateCount: integer; override;
/// force no UpdateCount method call on server side
// - may be needed to reduce server load, if this information is not needed
property ForceNoUpdateCount: boolean read fForceNoUpdateCount write fForceNoUpdateCount;
/// after a statement has been prepared via Prepare() + ExecutePrepared() or
// Execute(), this method must be called one or more times to evaluate it
function Step(SeekFirst: boolean=false): boolean; override;
/// Reset the previous prepared statement
// - always raise an ESQLDBException, since this method is not to be allowed
procedure Reset; override;
end;
/// 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.pas unit
// - abstract class which should override its protected ProcessMessage() method
// e.g. by TSQLDBRemoteConnectionPropertiesTest or
TSQLDBRemoteConnectionPropertiesAbstract = class(TSQLDBProxyConnectionPropertiesAbstract)
protected
/// will build and interpret binary messages to be served with ProcessMessage
// - would raise an exception in case of error, even on the server side
function Process(Command: TSQLDBProxyConnectionCommand;
const Input; var Output): integer; override;
/// abstract method to override for the expected transmission protocol
// - could raise an exception on transmission error
procedure ProcessMessage(const Input: RawByteString; out Output: RawByteString);
virtual; abstract;
end;
/// fake proxy class for testing the remote connection to any SynDB engine
// - resulting overhead due to our binary messaging: unnoticeable :)
TSQLDBRemoteConnectionPropertiesTest = class(TSQLDBRemoteConnectionPropertiesAbstract)
protected
fProps: TSQLDBConnectionProperties;
// this overriden method will just call fProps.RemoteProcessMessage()
procedure ProcessMessage(const Input: RawByteString; out Output: RawByteString); override;
public
/// 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
constructor Create(aProps: TSQLDBConnectionProperties;
const aUserID,aPassword: RawUTF8; aProtocol: TSQLDBProxyConnectionProtocolClass); reintroduce;
end;
/// 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
TSQLDBProxyStatementRandomAccess = class(TSQLDBProxyStatementAbstract)
protected
fRowData: TCardinalDynArray;
public
/// 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)
constructor Create(Data: PByte; DataLen: integer;
DataRowPosition: PCardinalDynArray=nil; IgnoreColumnDataSize: boolean=false); reintroduce;
/// Execute a prepared SQL statement
// - this unexpected overridden method will raise a ESQLDBException
procedure ExecutePrepared; override;
/// Change cursor position to the next available row
// - this unexpected overridden method will raise a ESQLDBException
function Step(SeekFirst: boolean=false): boolean; override;
/// 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 GotoRow(Index: integer; RaiseExceptionOnWrongIndex: Boolean=false): boolean;
end;
{$endif WITH_PROXY}
const
/// TSQLDBFieldType kind of columns which have a fixed width
FIXEDLENGTH_SQLDBFIELDTYPE = [ftInt64, ftDouble, ftCurrency, ftDate];
/// conversion matrix from TSQLDBFieldType into variant type
MAP_FIELDTYPE2VARTYPE: array[TSQLDBFieldType] of Word = (
varEmpty, varNull, varInt64, varDouble, varCurrency, varDate,
varSynUnicode, varString);
// ftUnknown, ftNull, ftInt64, ftDouble, ftCurrency, ftDate, ftUTF8, ftBlob
/// function helper logging some column truncation information text
procedure LogTruncatedColumn(const Col: TSQLDBColumnProperty);
/// retrieve a table name without any left schema
// - e.g. TrimLeftSchema('SCHEMA.TABLENAME')='TABLENAME'
function TrimLeftSchema(const TableName: RawUTF8): RawUTF8;
/// 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
function ReplaceParamsByNames(const aSQL: RawUTF8; var aNewSQL: RawUTF8): integer;
{ -------------- native connection interfaces, without OleDB }
type
/// 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
TSQLDBLib = class
protected
fHandle: {$ifdef FPC}TLibHandle{$else}HMODULE{$endif};
public
/// release associated memory and linked library
destructor Destroy; override;
/// the associated library handle
property Handle: {$ifdef FPC}TLibHandle{$else}HMODULE{$endif} read fHandle write fHandle;
end;
{$ifdef EMULATES_TQUERY}
{ -------------- TQuery TField TParam emulation classes and types }
type
/// generic Exception type raised by the TQuery class
ESQLQueryException = class(ESynException)
public
constructor CreateFromError(aMessage: string; aConnection: TSQLDBConnection);
end;
/// generic type used by TQuery / TQueryValue for BLOBs fields
TBlobData = RawByteString;
/// represent the use of parameters on queries or stored procedures
// - same enumeration as with the standard DB unit from VCL
TParamType = (ptUnknown, ptInput, ptOutput, ptInputOutput, ptResult);
TQuery = class;
/// 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
{$ifdef UNICODE}TQueryValue = record{$else}TQueryValue = object{$endif}
private
/// fName should be the first property, i.e. the searched hashed value
fName: string;
fValue: Variant;
fValueBlob: boolean;
fParamType: TParamType;
// =-1 if empty, =0 if eof, >=1 if cursor on row data
fRowIndex: integer;
fColumnIndex: integer;
fQuery: TQuery;
procedure CheckExists;
procedure CheckValue;
function GetIsNull: boolean;
function GetDouble: double;
function GetString: string;
function GetAsWideString: SynUnicode;
function GetCurrency: Currency;
function GetDateTime: TDateTime;
function GetVariant: Variant;
function GetInteger: integer;
function GetInt64: Int64;
function GetBlob: TBlobData;
function GetAsBytes: TBytes;
function GetBoolean: Boolean;
procedure SetDouble(const aValue: double);
procedure SetString(const aValue: string);
procedure SetAsWideString(const aValue: SynUnicode);
procedure SetCurrency(const aValue: Currency);
procedure SetDateTime(const aValue: TDateTime);
procedure SetVariant(const aValue: Variant);
procedure SetInteger(const aValue: integer);
procedure SetInt64(const aValue: Int64);
procedure SetBlob(const aValue: TBlobData);
procedure SetAsBytes(const Value: TBytes);
procedure SetBoolean(const aValue: Boolean);
procedure SetBound(const aValue: Boolean);
public
/// set the column value to null
procedure Clear;
/// the associated (field) name
property FieldName: string read fName;
/// the associated (parameter) name
property Name: string read fName;
/// parameter type for queries or stored procedures
property ParamType: TParamType read fParamType write fParamType;
/// returns TRUE if the stored Value is null
property IsNull: Boolean read GetIsNull;
/// just do nothing - here for compatibility reasons with Clear + Bound := true
property Bound: Boolean write SetBound;
/// access the Value as Integer
property AsInteger: integer read GetInteger write SetInteger;
/// 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 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 AsLargeInt: Int64 read GetInt64 write SetInt64;
/// access the Value as boolean
property AsBoolean: Boolean read GetBoolean write SetBoolean;
/// 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 AsString: string read GetString write SetString;
/// 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 AsWideString: SynUnicode read GetAsWideString write SetAsWideString;
/// 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 AsBlob: TBlobData read GetBlob write SetBlob;
/// 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 AsBytes: TBytes read GetAsBytes write SetAsBytes;
/// access the Value as double
property AsFloat: double read GetDouble write SetDouble;
/// access the Value as TDateTime
property AsDateTime: TDateTime read GetDateTime write SetDateTime;
/// access the Value as TDate
property AsDate: TDateTime read GetDateTime write SetDateTime;
/// access the Value as TTime
property AsTime: TDateTime read GetDateTime write SetDateTime;
/// access the Value as Currency
// - avoid any rounding conversion, as with AsFloat
property AsCurrency: Currency read GetCurrency write SetCurrency;
/// access the Value as Variant
property AsVariant: Variant read GetVariant write SetVariant;
end;
/// a dynamic array of TQuery bound parameters or column values
// - TQuery will use TDynArrayHashed for fast search
TQueryValueDynArray = array of TQueryValue;
/// pointer to TQuery bound parameter or column value
PQueryValue = ^TQueryValue;
/// pointer mapping the VCL DB TField class
// - to be used e.g. with code using local TField instances in a loop
TField = PQueryValue;
/// pointer mapping the VCL DB TParam class
// - to be used e.g. with code using local TParam instances
TParam = PQueryValue;
/// 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.pas 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...)
TQuery = class
protected
fSQL: TStringList;
fPrepared: ISQLDBStatement;
fRowIndex: Integer;
fConnection: TSQLDBConnection;
fParams: TQueryValueDynArray;
fResults: TQueryValueDynArray;
fResult: TDynArrayHashed;
fResultCount: integer;
fParam: TDynArrayHashed;
fParamCount: Integer;
function GetIsEmpty: Boolean;
function GetActive: Boolean;
function GetFieldCount: integer;
function GetParamCount: integer;
function GetField(aIndex: integer): TField;
function GetParam(aIndex: integer): TParam;
function GetEof: boolean;
function GetBof: Boolean;
function GetRecordCount: integer;
function GetSQLAsText: string;
procedure OnSQLChange(Sender: TObject);
/// prepare and execute the SQL query
procedure Execute(ExpectResults: Boolean);
public
/// initialize a query for the associated database connection
constructor Create(aConnection: TSQLDBConnection);
/// release internal memory and statements
destructor Destroy; override;
/// a do-nothing method, just available for compatibility purpose
procedure Prepare;
/// 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 Open;
/// 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 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
// - this method will return the number of updated rows (i.e.
// PreparedSQLDBStatement.UpdateCount)
function ExecSQLAndReturnUpdateCount: integer;
/// after a successfull Open, will get the first row of results
procedure First;
/// after successfull Open and First, go the the next row of results
procedure Next;
/// 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 Close;
/// 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
function ParamByName(const aParamName: string; CreateIfNotExisting: boolean=true): TParam;
/// 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 FieldByName(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 FindField(const aFieldName: string): TField;
/// the associated database connection
property Connection: TSQLDBConnection read fConnection;
/// 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 SQL: TStringList read fSQL;
/// the SQL statement with inlined bound parameters
property SQLAsText: string read GetSQLAsText;
/// equals true if there is some rows pending
property Eof: Boolean read GetEof;
/// equals true if on first row
property Bof: Boolean read GetBof;
/// 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 RecordCount: integer read GetRecordCount;
/// equals true if there is no row returned
property IsEmpty: Boolean read GetIsEmpty;
/// equals true if the query is opened
property Active: Boolean read GetActive;
/// the number of columns in the current opened SQL query row
property FieldCount: integer read GetFieldCount;
/// the number of bound parameters in the current SQL statement
property ParamCount: integer read GetParamCount;
/// 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 Fields[aIndex: integer]: TField read GetField;
/// retrieve a bound parameters in the current SQL statement
// - will return nil in case of error, e.g. out of range index
property Params[aIndex: integer]: TParam read GetParam;
/// non VCL property to access the internal SynDB prepared statement
// - is nil if the TQuery is not prepared (e.g. after Close)
property PreparedSQLDBStatement: ISQLDBStatement read fPrepared;
end;
{$endif EMULATES_TQUERY}
var
/// the TSynLog class used for logging for all our SynDB related units
// - you may override it with TSQLLog, if available from mORMot.pas
// - since not all exceptions are handled specificaly by this unit, you
// may better use a common TSynLog class for the whole application or module
SynDBLog: TSynLogClass=TSynLog;
{ -------------- Database specific classes - shared by several SynDB units }
const
/// 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
DB_FIELDS: array[TSQLDBDefinition] of TSQLDBFieldTypeDefinition = (
// ftUnknown=int32, ftNull=UTF8, ftInt64, ftDouble, ftCurrency, ftDate, ftUTF8, ftBlob
// dUnknown
(' INT',' NVARCHAR(%)',' BIGINT',' DOUBLE',' NUMERIC(19,4)',' TIMESTAMP',
' CLOB',' BLOB'),
// dDefault
(' INT',' NVARCHAR(%)',' BIGINT',' DOUBLE',' NUMERIC(19,4)',' TIMESTAMP',
' CLOB',' BLOB'),
// dOracle
(' NUMBER(22,0)',' NVARCHAR2(%)',' NUMBER(22,0)',' BINARY_DOUBLE',' NUMBER(19,4)',
' DATE',' NCLOB',' BLOB'),
// NCLOB (National Character Large Object) is an Oracle data type that can hold
// up to 4 GB of character data. It's similar to a CLOB, but characters are
// stored in a NLS or multibyte national character set (like NVARCHAR2)
// dMSSQL
(' int',' nvarchar(%)',' bigint',' float',' money',' datetime',' nvarchar(max)',
' varbinary(max)'),
// dJet
(' Long',' VarChar(%)',' Decimal(19,0)',' Double',' Currency',' DateTime',
' LongText',' LongBinary'),
// dMySQL
(' int',' varchar(%) character set UTF8',' bigint',' double',' decimal(19,4)',
' datetime',' mediumtext character set UTF8',' mediumblob'),
// dSQLite
(' INTEGER',' TEXT',' INTEGER',' FLOAT',' FLOAT',' TEXT',' TEXT',' BLOB'),
// dFirebird
(' 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'),
// about BLOB: http://www.ibphoenix.com/resources/documents/general/doc_54
// dNexusDB
(' INTEGER',' NVARCHAR(%)',' LARGEINT',' REAL',' MONEY',' DATETIME',' NCLOB',' BLOB'),
// VARCHAR(%) CODEPAGE 65001 just did not work well with Delphi<2009
// dPostgreSQL
(' INTEGER',' TEXT',' BIGINT',' DOUBLE PRECISION',' NUMERIC(19,4)',
' TIMESTAMP',' TEXT',' BYTEA'),
// like SQLite3, we will create TEXT column instead of VARCHAR(%), as stated
// by http://www.postgresql.org/docs/current/static/datatype-character.html
// dDB2 (for CCSID Unicode tables)
(' int',' varchar(%)',' bigint',' real',' decimal(19,4)',' timestamp',' clob', ' blob'),
{ note: bigint needs 9.1 and up }
// dInformix
(' 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()
// - SQLite3 doesn't expect any field length, neither PostgreSQL, so set to 0
DB_FIELDSMAX: array[TSQLDBDefinition] of cardinal = (
1000, 1000, 1333, { =4000/3 since WideChar is up to 3 bytes in UTF-8 }
4000, 255, 4000, 0, 32760, 32767, 0, 32700, 32700);
/// the known SQL statement to retrieve the server date and time
DB_SERVERTIME: array[TSQLDBDefinition] of RawUTF8 = (
'','', // return local server time by default
'select sysdate from dual',
'select GETDATE()',
'', // Jet is local -> return local time
'SELECT NOW()',
'', // SQlite is local -> return local time
'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'
);
const
/// 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()
DB_SQLLIMITCLAUSE: array[TSQLDBDefinition] of TSQLDBDefinitionLimitClause = (
(Position: posNone; InsertFmt:nil), { dUnknown }
(Position: posNone; InsertFmt:nil), { dDefault }
(Position: posWhere; InsertFmt:'rownum<=%'), { dOracle }
(Position: posSelect; InsertFmt:'top(%) '), { dMSSQL }
(Position: posSelect; InsertFmt:'top % '), { dJet }
(Position: posAfter; InsertFmt:' limit %'), { dMySQL }
(Position: posAfter; InsertFmt:' limit %'), { dSQLite }
(Position: posSelect; InsertFmt:'first % '), { dFirebird }
(Position: posSelect; InsertFmt:'top % '), { dNexusDB }
(Position: posAfter; InsertFmt:' limit %'), { dPostgreSQL }
(Position: posAfter; InsertFmt:' fetch first % rows only'), { dDB2 }
(Position: posAfter; InsertFmt:' first % ')); { dInformix }
/// the known database engines handling CREATE INDEX IF NOT EXISTS statement
DB_HANDLECREATEINDEXIFNOTEXISTS = [dSQLite];
/// 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
DB_HANDLEINDEXONBLOBS = [dSQLite,dPostgreSQL];
/// where the DESC clause shall be used for a CREATE INDEX statement
// - only identified syntax exception is for FireBird
DB_SQLDESENDINGINDEXPOS: array[TSQLDBDefinition] of
(posWithColumn, posGlobalBefore) = (
posWithColumn, posWithColumn, posWithColumn, posWithColumn, posWithColumn,
posWithColumn, posWithColumn, posGlobalBefore, posWithColumn, posWithColumn,
posWithColumn, posWithColumn);
/// the SQL text corresponding to the identified WHERE operators for a SELECT
DB_SQLOPERATOR: array[opEqualTo..opLike] of RawUTF8 = (
'=','<>','<','<=','>','>=',' in ',' is null',' is not null',' like ');
/// retrieve the text of a given Database SQL dialect enumeration
// - see also TSQLDBConnectionProperties.GetDBMSName() method
function ToText(DBMS: TSQLDBDefinition): PShortString; overload;
/// retrieve the text of a given Database field type enumeration
// - see also TSQLDBFieldTypeToString() function
function ToText(Field: TSQLDBFieldType): PShortString; overload;
/// retrieve the ready-to-be displayed text of a given Database field
// type enumeration
function TSQLDBFieldTypeToString(aType: TSQLDBFieldType): string;
{$ifdef WITH_PROXY}
/// retrieve the ready-to-be displayed text of proxy commands implemented by
// TSQLDBProxyConnectionProperties.Process()
function ToText(cmd: TSQLDBProxyConnectionCommand): PShortString; overload;
{$endif}
implementation
function ToText(DBMS: TSQLDBDefinition): PShortString;
begin
result := GetEnumName(TypeInfo(TSQLDBDefinition),ord(DBMS));