Class Sqlserver

SQLServer driver.

Namespace: Cake\Database\Driver

Constants summary

int|null

MAX_ALIAS_LENGTH

128

Properties summary

$_autoQuoting protected

bool

Indicates whether or not the driver is doing automatic identifier quoting for all queries
$_baseConfig protected

array

Base configuration settings for Sqlserver driver
$_config protected

array

Configuration data.
$_connection protected

\PDO

Instance of PDO.
$_endQuote protected

string

String used to end a database identifier quoting to make it safe
$_startQuote protected

string

String used to start a database identifier quoting to make it safe

Method Summary

__construct() public

Constructor
__debugInfo() public

Returns an array that can be used to describe the internal state of this object.
__destruct() public

Destructor
_connect() protected

Establishes a connection to the database server
_deleteQueryTranslator() protected

Apply translation steps to delete queries.
_expressionTranslators() protected

Returns a dictionary of expressions to be transformed when compiling a Query to SQL. Array keys are method names to be called in this class
_insertQueryTranslator() protected

Apply translation steps to insert queries.
_pagingSubquery() protected

Generate a paging subquery for older versions of SQLserver.
_removeAliasesFromConditions() protected

Removes aliases from the WHERE clause of a query.
_selectQueryTranslator() protected

Modify the limit/offset to TSQL
_transformDistinct() protected

Returns the passed query after rewriting the DISTINCT clause, so that drivers that do not support the "ON" part can provide the actual way it should be done
_transformFunctionExpression() protected

Receives a FunctionExpression and changes it so that it conforms to this SQL dialect.
_transformTupleComparison() protected

Receives a TupleExpression and changes it so that it conforms to this SQL dialect.
_updateQueryTranslator() protected

Apply translation steps to update queries.
beginTransaction() public

Starts a transaction.
commitTransaction() public

Commits a transaction.
compileQuery() public

Transforms the passed query to this Driver's dialect and returns an instance of the transformed query and the full compiled SQL string.
connect() public

Establishes a connection to the database server.
disableAutoQuoting() public

Disable auto quoting of identifiers in queries.
disableForeignKeySQL() public

Get the SQL for disabling foreign keys.
disconnect() public

Disconnects from database server.
enableAutoQuoting() public

Sets whether or not this driver should automatically quote identifiers in queries.
enableForeignKeySQL() public

Get the SQL for enabling foreign keys.
enabled() public

Returns whether PHP is able to use this driver for connecting to database
getConnection() public

Get the internal PDO connection instance.
getMaxAliasLength() public

Returns the maximum alias length allowed.
isAutoQuotingEnabled() public

Returns whether or not this driver should automatically quote identifiers in queries.
isConnected() public

Checks whether or not the driver is connected.
lastInsertId() public

Returns last id generated for a table or sequence in database.
newCompiler() public

Returns an instance of a QueryCompiler.
newTableSchema() public

Constructs new TableSchema.
prepare() public

Prepares a sql statement to be executed
queryTranslator() public

Returns a callable function that will be used to transform a passed Query object.
quote() public

Returns a value in a safe representation to be used in a query string
quoteIdentifier() public

Quotes a database identifier (a column name, table name, etc..) to be used safely in queries without the risk of using reserved words
releaseSavePointSQL() public

Returns a SQL snippet for releasing a previously created save point
rollbackSavePointSQL() public

Returns a SQL snippet for rollbacking a previously created save point
rollbackTransaction() public

Rollbacks a transaction.
savePointSQL() public

Returns a SQL snippet for creating a new transaction savepoint
schema() public

Returns the schema name that's being used.
schemaDialect() public

Get the schema dialect.
schemaValue() public

Escapes values for use in schema definitions.
setConnection() public

Set the internal PDO connection instance.
supportsDynamicConstraints() public

Returns whether the driver supports adding or dropping constraints to already created tables.
supportsQuoting() public

Checks if the driver supports quoting, as PDO_ODBC does not support it.
supportsSavePoints() public

Returns whether this driver supports save points for nested transactions.
version() protected

Get the version of SQLserver we are connected to.

Method Detail

__construct() public

__construct(array $config)

Constructor

Parameters

array $config optional: The configuration for the driver.

Throws

InvalidArgumentException

__debugInfo() public

__debugInfo()

Returns an array that can be used to describe the internal state of this object.

Returns

array

__destruct() public

__destruct()

Destructor

_connect() protected

_connect(string $dsn, array $config)

Establishes a connection to the database server

Parameters

string $dsn: A Driver-specific PDO-DSN
array $config: configuration to be used for creating connection

Returns

bool

true on success

_deleteQueryTranslator() protected

_deleteQueryTranslator(\Cake\Database\Query $query)

Apply translation steps to delete queries.

Chops out aliases on delete query conditions as most database dialects do not support aliases in delete queries. This also removes aliases in table names as they frequently don't work either.

We are intentionally not supporting deletes with joins as they have even poorer support.

Parameters

\Cake\Database\Query $query: The query to translate

Returns

\Cake\Database\Query

The modified query

_expressionTranslators() protected

_expressionTranslators()

Returns a dictionary of expressions to be transformed when compiling a Query to SQL. Array keys are method names to be called in this class

Returns

array

_insertQueryTranslator() protected

_insertQueryTranslator(\Cake\Database\Query $query)

Apply translation steps to insert queries.

Parameters

\Cake\Database\Query $query: The query to translate

Returns

\Cake\Database\Query

The modified query

_pagingSubquery() protected

_pagingSubquery(\Cake\Database\Query $original, ?int $limit, ?int $offset)

Generate a paging subquery for older versions of SQLserver.

Prior to SQLServer 2012 there was no equivalent to LIMIT OFFSET, so a subquery must be used.

Parameters

\Cake\Database\Query $original: The query to wrap in a subquery.
int|null $limit: The number of rows to fetch.
int|null $offset: The number of rows to offset.

Returns

\Cake\Database\Query

Modified query object.

_removeAliasesFromConditions() protected

_removeAliasesFromConditions(\Cake\Database\Query $query)

Removes aliases from the WHERE clause of a query.

Parameters

\Cake\Database\Query $query: The query to process.

Returns

\Cake\Database\Query

The modified query.

Throws

RuntimeException
In case the processed query contains any joins, as removing aliases from the conditions can break references to the joined tables.

_selectQueryTranslator() protected

_selectQueryTranslator(\Cake\Database\Query $query)

Modify the limit/offset to TSQL

Parameters

\Cake\Database\Query $query: The query to translate

Returns

\Cake\Database\Query

The modified query

_transformDistinct() protected

_transformDistinct(\Cake\Database\Query $original)

Returns the passed query after rewriting the DISTINCT clause, so that drivers that do not support the "ON" part can provide the actual way it should be done

Parameters

\Cake\Database\Query $original: The query to be transformed

Returns

\Cake\Database\Query

_transformFunctionExpression() protected

_transformFunctionExpression(\Cake\Database\Expression\FunctionExpression $expression)

Receives a FunctionExpression and changes it so that it conforms to this SQL dialect.

Parameters

\Cake\Database\Expression\FunctionExpression $expression: The function expression to convert to TSQL.

_transformTupleComparison() protected

_transformTupleComparison(\Cake\Database\Expression\TupleComparison $expression, \Cake\Database\Query $query)

Receives a TupleExpression and changes it so that it conforms to this SQL dialect.

It transforms expressions looking like '(a, b) IN ((c, d), (e, f))' into an equivalent expression of the form '((a = c) AND (b = d)) OR ((a = e) AND (b = f))'.

It can also transform transform expressions where the right hand side is a query selecting the same amount of columns as the elements in the left hand side of the expression:

(a, b) IN (SELECT c, d FROM a_table) is transformed into

1 = (SELECT 1 FROM a_table WHERE (a = c) AND (b = d))

Parameters

\Cake\Database\Expression\TupleComparison $expression: The expression to transform
\Cake\Database\Query $query: The query to update.

_updateQueryTranslator() protected

_updateQueryTranslator(\Cake\Database\Query $query)

Apply translation steps to update queries.

Chops out aliases on update query conditions as not all database dialects do support aliases in update queries.

Just like for delete queries, joins are currently not supported for update queries.

Parameters

\Cake\Database\Query $query: The query to translate

Returns

\Cake\Database\Query

The modified query

beginTransaction() public

beginTransaction()

Starts a transaction.

Returns

bool

True on success, false otherwise.

commitTransaction() public

commitTransaction()

Commits a transaction.

Returns

bool

True on success, false otherwise.

compileQuery() public

compileQuery(\Cake\Database\Query $query, \Cake\Database\ValueBinder $generator)

Transforms the passed query to this Driver's dialect and returns an instance of the transformed query and the full compiled SQL string.

Parameters

\Cake\Database\Query $query: The query to compile.
\Cake\Database\ValueBinder $generator: The value binder to use.

Returns

array

containing 2 entries. The first entity is the transformed query and the second one the compiled SQL.

connect() public

connect()

Establishes a connection to the database server.

Please note that the PDO::ATTR_PERSISTENT attribute is not supported by the SQL Server PHP PDO drivers. As a result you cannot use the persistent config option when connecting to a SQL Server (for more information see: https://github.com/Microsoft/msphpsql/issues/65).

Returns

bool

true on success

Throws

InvalidArgumentException
if an unsupported setting is in the driver config

disableAutoQuoting() public

disableAutoQuoting()

Disable auto quoting of identifiers in queries.

Returns

$this

disableForeignKeySQL() public

disableForeignKeySQL()

Get the SQL for disabling foreign keys.

Returns

string

disconnect() public

disconnect()

Disconnects from database server.

enableAutoQuoting() public

enableAutoQuoting(bool $enable)

Sets whether or not this driver should automatically quote identifiers in queries.

Parameters

bool $enable optional: Whether to enable auto quoting

Returns

$this

enableForeignKeySQL() public

enableForeignKeySQL()

Get the SQL for enabling foreign keys.

Returns

string

enabled() public

enabled()

Returns whether PHP is able to use this driver for connecting to database

Returns

bool

true if it is valid to use this driver

getConnection() public

getConnection()

Get the internal PDO connection instance.

Returns

\PDO

getMaxAliasLength() public

getMaxAliasLength()

Returns the maximum alias length allowed.

This can be different than the maximum identifier length for columns.

Returns

int|null

Maximum alias length or null if no limit

isAutoQuotingEnabled() public

isAutoQuotingEnabled()

Returns whether or not this driver should automatically quote identifiers in queries.

Returns

bool

isConnected() public

isConnected()

Checks whether or not the driver is connected.

Returns

bool

lastInsertId() public

lastInsertId(?string $table, ?string $column)

Returns last id generated for a table or sequence in database.

Parameters

string|null $table optional: table name or sequence to get last insert value from.
string|null $column optional: the name of the column representing the primary key.

Returns

string|int

newCompiler() public

newCompiler()

Returns an instance of a QueryCompiler.

Returns

\Cake\Database\SqlserverCompiler

newTableSchema() public

newTableSchema(string $table, array $columns)

Constructs new TableSchema.

Parameters

string $table: The table name.
array $columns optional: The list of columns for the schema.

Returns

\Cake\Database\Schema\TableSchema

prepare() public

prepare(mixed $query)

Prepares a sql statement to be executed

Parameters

string|\Cake\Database\Query $query: The query to prepare.

Returns

\Cake\Database\StatementInterface

queryTranslator() public

queryTranslator(string $type)

Returns a callable function that will be used to transform a passed Query object.

This function, in turn, will return an instance of a Query object that has been transformed to accommodate any specificities of the SQL dialect in use.

Parameters

string $type: the type of query to be transformed (select, insert, update, delete)

Returns

\Closure

quote() public

quote(mixed $value, mixed $type)

Returns a value in a safe representation to be used in a query string

Parameters

mixed $value: The value to quote.
int $type optional: Type to be used for determining kind of quoting to perform.

Returns

string

quoteIdentifier() public

quoteIdentifier(string $identifier)

Quotes a database identifier (a column name, table name, etc..) to be used safely in queries without the risk of using reserved words

Parameters

string $identifier: The identifier to quote.

Returns

string

releaseSavePointSQL() public

releaseSavePointSQL(mixed $name)

Returns a SQL snippet for releasing a previously created save point

Parameters

string|int $name: save point name

Returns

string

rollbackSavePointSQL() public

rollbackSavePointSQL(mixed $name)

Returns a SQL snippet for rollbacking a previously created save point

Parameters

string|int $name: save point name

Returns

string

rollbackTransaction() public

rollbackTransaction()

Rollbacks a transaction.

Returns

bool

True on success, false otherwise.

savePointSQL() public

savePointSQL(mixed $name)

Returns a SQL snippet for creating a new transaction savepoint

Parameters

string|int $name: save point name

Returns

string

schema() public

schema()

Returns the schema name that's being used.

Returns

string

schemaDialect() public

schemaDialect()

Get the schema dialect.

Used by Cake\Schema package to reflect schema and generate schema.

Returns

\Cake\Database\Schema\BaseSchema

schemaValue() public

schemaValue(mixed $value)

Escapes values for use in schema definitions.

Parameters

mixed $value: The value to escape.

Returns

string

String for use in schema definitions.

setConnection() public

setConnection(mixed $connection)

Set the internal PDO connection instance.

Parameters

\PDO $connection: PDO instance.

Returns

$this

supportsDynamicConstraints() public

supportsDynamicConstraints()

Returns whether the driver supports adding or dropping constraints to already created tables.

Returns

bool

True if driver supports dynamic constraints.

supportsQuoting() public

supportsQuoting()

Checks if the driver supports quoting, as PDO_ODBC does not support it.

Returns

bool

supportsSavePoints() public

supportsSavePoints()

Returns whether this driver supports save points for nested transactions.

Returns

bool

True if save points are supported, false otherwise.

version() protected

version()

Get the version of SQLserver we are connected to.

Returns

string

Property Detail

`$_autoQuoting` protected

Indicates whether or not the driver is doing automatic identifier quoting for all queries

Type

bool

`$_baseConfig` protected

Base configuration settings for Sqlserver driver

Type

array

`$_config` protected

Configuration data.

Type

array

`$_connection` protected

Instance of PDO.

Type

\PDO

`$_endQuote` protected

String used to end a database identifier quoting to make it safe

Type

string

`$_startQuote` protected

String used to start a database identifier quoting to make it safe

Type

string

© 2005–present The Cake Software Foundation, Inc.
Licensed under the MIT License.
CakePHP is a registered trademark of Cake Software Foundation, Inc.
We are not endorsed by or affiliated with CakePHP.
https://api.cakephp.org/4.0/class-Cake.Database.Driver.Sqlserver.html