Class Sqlserver

SQLServer driver.

Namespace: Cake\Database\Driver

Constants

int|null

MAX_ALIAS_LENGTH ¶
```
128
```
array<int>

RETRY_ERROR_CODES ¶
```
[40613]
```
class-string<Cake\Database\Statement\Statement>

STATEMENT_CLASS ¶
```
SqlserverStatement::class
```

Property Summary

$_autoQuoting protected

bool

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

array<string, mixed>

Base configuration settings for Sqlserver driver
$_config protected

array<string, mixed>

Configuration data.
$_endQuote protected

string

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

Cake\Database\Schema\SchemaDialect

The schema dialect for this driver
$_startQuote protected

string

String used to start a database identifier quoting to make it safe
$_version protected

string|null

The server version
$connectRetries protected

int

The last number of connection retry attempts.
$logger protected

?LoggerInterface

The logger instance.
$pdo protected

PDO|null

Instance of PDO.
$quoter protected

Cake\Database\IdentifierQuoter|null

Identifier quoter

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
_deleteQueryTranslator() protected

Apply translation steps to delete queries.
_expressionTranslators() protected

Returns an associative array of methods that will transform Expression objects to conform with the specific SQL dialect. Keys are class names and values a method 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

Apply translation steps to select queries.
_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.
config() public

Get the configuration data used to create the driver.
connect() public

Establishes a connection to the database server.
createLogger() protected

Create logger instance.
createPdo() protected

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 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
exec() public

Execute the SQL query using the internal PDO instance.
execute() public

Executes a query using $params for interpolating values and $types as a hint for each those params.
executeStatement() protected

Execute the statement and log the query string.
getLogger() public

Get the logger instance.
getMaxAliasLength() public

Returns the maximum alias length allowed.
getPdo() protected

Get the PDO connection instance.
getRole() public

Returns the connection role this driver performs.
inTransaction() public

Returns whether a transaction is active for connection.
isAutoQuotingEnabled() public

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

Checks whether the driver is connected.
lastInsertId() public

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

Logs a message or query using the configured logger object.
newCompiler() public
newTableSchema() public

Constructs new TableSchema.
prepare() public

Prepares a sql statement to be executed.
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
quoter() public

Get identifier quoter instance.
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.
run() public

Executes the provided query after compiling it for the specific driver dialect and returns the executed Statement object.
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.
setLogger() public

Sets a logger.
supports() public

Returns whether the driver supports the feature.
transformQuery() protected

Transform the query to accommodate any specificities of the SQL dialect in use.
version() public

Returns connected server version.

Method Detail

__construct() ¶ public

__construct(array<string, mixed> $config = [])

Constructor

Parameters

array<string, mixed> $config optional: The configuration for the driver.

Throws

InvalidArgumentException

__debugInfo() ¶ public

__debugInfo(): array<string, mixed>

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

Returns

array<string, mixed>

__destruct() ¶ public

__destruct()

Destructor

_deleteQueryTranslator() ¶ protected

_deleteQueryTranslator(Cake\Database\Query\DeleteQuery $query): Cake\Database\Query\DeleteQuery

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\DeleteQuery $query: The query to translate

Returns

Cake\Database\Query\DeleteQuery

_expressionTranslators() ¶ protected

_expressionTranslators(): array<class-string, string>

Returns an associative array of methods that will transform Expression objects to conform with the specific SQL dialect. Keys are class names and values a method in this class.

Returns

array<class-string, string>

_insertQueryTranslator() ¶ protected

_insertQueryTranslator(Cake\Database\Query\InsertQuery $query): Cake\Database\Query\InsertQuery

Apply translation steps to insert queries.

Parameters

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

Returns

Cake\Database\Query\InsertQuery

_pagingSubquery() ¶ protected

_pagingSubquery(Cake\Database\Query\SelectQuery<mixed> $original, int|null $limit, int|null $offset): Cake\Database\Query\SelectQuery<mixed>

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\SelectQuery<mixed> $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\SelectQuery<mixed>

_removeAliasesFromConditions() ¶ protected

_removeAliasesFromConditions(Cake\Database\Query\UpdateQuery|Cake\Database\Query\DeleteQuery $query): Cake\Database\Query\UpdateQuery|Cake\Database\Query\DeleteQuery

Removes aliases from the WHERE clause of a query.

Parameters

Cake\Database\Query\UpdateQuery|Cake\Database\Query\DeleteQuery $query: The query to process.

Returns

Cake\Database\Query\UpdateQuery|Cake\Database\Query\DeleteQuery

Throws

Cake\Database\Exception\DatabaseException
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\SelectQuery<mixed> $query): Cake\Database\Query\SelectQuery<mixed>

Apply translation steps to select queries.

Parameters

Cake\Database\Query\SelectQuery<mixed> $query

Returns

Cake\Database\Query\SelectQuery<mixed>

_transformDistinct() ¶ protected

_transformDistinct(Cake\Database\Query\SelectQuery<mixed> $query): Cake\Database\Query\SelectQuery<mixed>

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\SelectQuery<mixed> $query

Returns

Cake\Database\Query\SelectQuery<mixed>

_transformFunctionExpression() ¶ protected

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

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.

Returns

void

_transformTupleComparison() ¶ protected

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

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.

Returns

void

_updateQueryTranslator() ¶ protected

_updateQueryTranslator(Cake\Database\Query\UpdateQuery $query): Cake\Database\Query\UpdateQuery

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\UpdateQuery $query: The query to translate

Returns

Cake\Database\Query\UpdateQuery

beginTransaction() ¶ public

beginTransaction(): bool

Starts a transaction.

Returns

bool

commitTransaction() ¶ public

commitTransaction(): bool

Commits a transaction.

Returns

bool

compileQuery() ¶ public

compileQuery(Cake\Database\Query $query, Cake\Database\ValueBinder $binder): string

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 $binder: The value binder to use.

Returns

string

config() ¶ public

config(): array<string, mixed>

Get the configuration data used to create the driver.

Returns

array<string, mixed>

connect() ¶ public

connect(): void

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

void

Throws

InvalidArgumentException
if an unsupported setting is in the driver config

createLogger() ¶ protected

createLogger(string|null $className): Psr\Log\LoggerInterface

Create logger instance.

Parameters

string|null $className: Logger's class name

Returns

Psr\Log\LoggerInterface

createPdo() ¶ protected

createPdo(string $dsn, array<string, mixed> $config): PDO

Establishes a connection to the database server

Parameters

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

Returns

PDO

disableAutoQuoting() ¶ public

disableAutoQuoting(): $this

Disable auto quoting of identifiers in queries.

Returns

$this

disableForeignKeySQL() ¶ public

disableForeignKeySQL(): string

Get the SQL for disabling foreign keys.

Returns

string

disconnect() ¶ public

disconnect(): void

Disconnects from database server.

Returns

void

enableAutoQuoting() ¶ public

enableAutoQuoting(bool $enable = true): $this

Sets whether this driver should automatically quote identifiers in queries.

Parameters

bool $enable optional: Whether to enable auto quoting

Returns

$this

enableForeignKeySQL() ¶ public

enableForeignKeySQL(): string

Get the SQL for enabling foreign keys.

Returns

string

enabled() ¶ public

enabled(): bool

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

Returns

bool

exec() ¶ public

exec(string $sql): int|false

Execute the SQL query using the internal PDO instance.

Parameters

string $sql: SQL query.

Returns

int|false

execute() ¶ public

execute(string $sql, array $params = [], array $types = []): Cake\Database\StatementInterface

Executes a query using $params for interpolating values and $types as a hint for each those params.

Parameters

string $sql: SQL to be executed and interpolated with $params
array $params optional: List or associative array of params to be interpolated in $sql as values.
array $types optional: List or associative array of types to be used for casting values in query.

Returns

Cake\Database\StatementInterface

executeStatement() ¶ protected

executeStatement(Cake\Database\StatementInterface $statement, array|null $params = null): void

Execute the statement and log the query string.

Parameters

Cake\Database\StatementInterface $statement: Statement to execute.
array|null $params optional: List of values to be bound to query.

Returns

void

getLogger() ¶ public

getLogger(): Psr\Log\LoggerInterface|null

Get the logger instance.

Returns

Psr\Log\LoggerInterface|null

getMaxAliasLength() ¶ public

getMaxAliasLength(): int|null

Returns the maximum alias length allowed.

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

Returns

int|null

getPdo() ¶ protected

getPdo(): PDO

Get the PDO connection instance.

Returns

PDO

getRole() ¶ public

getRole(): string

Returns the connection role this driver performs.

Returns

string

inTransaction() ¶ public

inTransaction(): bool

Returns whether a transaction is active for connection.

Returns

bool

isAutoQuotingEnabled() ¶ public

isAutoQuotingEnabled(): bool

Returns whether this driver should automatically quote identifiers in queries.

Returns

bool

isConnected() ¶ public

isConnected(): bool

Checks whether the driver is connected.

Returns

bool

lastInsertId() ¶ public

lastInsertId(string|null $table = null): string

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.

Returns

string

log() ¶ public

log(Stringable|string $message, array $context = []): bool

Logs a message or query using the configured logger object.

Parameters

Stringable|string $message: Message string or query.
array $context optional: Logging context.

Returns

bool

newCompiler() ¶ public

newCompiler(): Cake\Database\SqlserverCompiler

Returns

Cake\Database\SqlserverCompiler

newTableSchema() ¶ public

newTableSchema(string $table, array<string, mixed> $columns = []): Cake\Database\Schema\TableSchemaInterface

Constructs new TableSchema.

Parameters

string $table: The table name.
array<string, mixed> $columns optional: The list of columns for the schema.

Returns

Cake\Database\Schema\TableSchemaInterface

prepare() ¶ public

prepare(Cake\Database\Query|string $query): Cake\Database\StatementInterface

Prepares a sql statement to be executed.

Parameters

Cake\Database\Query|string $query

Returns

Cake\Database\StatementInterface

quoteIdentifier() ¶ public

quoteIdentifier(string $identifier): string

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

quoter() ¶ public

quoter(): Cake\Database\IdentifierQuoter

Get identifier quoter instance.

Returns

Cake\Database\IdentifierQuoter

releaseSavePointSQL() ¶ public

releaseSavePointSQL(string|int $name): string

Returns a SQL snippet for releasing a previously created save point

Parameters

string|int $name

Returns

string

rollbackSavePointSQL() ¶ public

rollbackSavePointSQL(string|int $name): string

Returns a SQL snippet for rollbacking a previously created save point

Parameters

string|int $name

Returns

string

rollbackTransaction() ¶ public

rollbackTransaction(): bool

Rollbacks a transaction.

Returns

bool

run() ¶ public

run(Cake\Database\Query $query): Cake\Database\StatementInterface

Executes the provided query after compiling it for the specific driver dialect and returns the executed Statement object.

Parameters

Cake\Database\Query $query: The query to be executed.

Returns

Cake\Database\StatementInterface

savePointSQL() ¶ public

savePointSQL(string|int $name): string

Returns a SQL snippet for creating a new transaction savepoint

Parameters

string|int $name

Returns

string

schema() ¶ public

schema(): string

Returns the schema name that's being used.

Returns

string

schemaDialect() ¶ public

schemaDialect(): Cake\Database\Schema\SchemaDialect

Get the schema dialect.

Used by {@link \Cake\Database\Schema} package to reflect schema and generate schema.

If all the tables that use this Driver specify their own schemas, then this may return null.

Returns

Cake\Database\Schema\SchemaDialect

schemaValue() ¶ public

schemaValue(mixed $value): string

Escapes values for use in schema definitions.

Parameters

mixed $value: The value to escape.

Returns

string

setLogger() ¶ public

setLogger(LoggerInterface $logger): void

Sets a logger.

Parameters

LoggerInterface $logger

Returns

void

supports() ¶ public

supports(Cake\Database\DriverFeatureEnum $feature): bool

Returns whether the driver supports the feature.

Should return false for unknown features.

Parameters

Cake\Database\DriverFeatureEnum $feature

Returns

bool

transformQuery() ¶ protected

transformQuery(Cake\Database\Query $query): Cake\Database\Query

Transform the query to accommodate any specificities of the SQL dialect in use.

It will also quote the identifiers if auto quoting is enabled.

Parameters

Cake\Database\Query $query: Query to transform.

Returns

Cake\Database\Query

version() ¶ public

version(): string

Returns connected server version.

Returns

string

Property Detail

`$_autoQuoting` ¶ protected

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

Type

bool

`$_baseConfig` ¶ protected

Base configuration settings for Sqlserver driver

Type

array<string, mixed>

`$_config` ¶ protected

Configuration data.

Type

array<string, mixed>

`$_endQuote` ¶ protected

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

Type

string

`$_schemaDialect` ¶ protected

The schema dialect for this driver

Type

Cake\Database\Schema\SchemaDialect

`$_startQuote` ¶ protected

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

Type

string

`$_version` ¶ protected

The server version

Type

string|null

`$connectRetries` ¶ protected

The last number of connection retry attempts.

Type

int

`$logger` ¶ protected

The logger instance.

Type

?LoggerInterface

`$pdo` ¶ protected

Instance of PDO.

Type

PDO|null

`$quoter` ¶ protected

Identifier quoter

Type

Cake\Database\IdentifierQuoter|null

Namespaces

Class Sqlserver

Constants

Property Summary

Method Summary

__construct() public

__debugInfo() public

__destruct() public

_deleteQueryTranslator() protected

_expressionTranslators() protected

_insertQueryTranslator() protected

_pagingSubquery() protected

_removeAliasesFromConditions() protected

_selectQueryTranslator() protected

_transformDistinct() protected

_transformFunctionExpression() protected

_transformTupleComparison() protected

_updateQueryTranslator() protected

beginTransaction() public

commitTransaction() public

compileQuery() public

config() public

connect() public

createLogger() protected

createPdo() protected

disableAutoQuoting() public

disableForeignKeySQL() public

disconnect() public

enableAutoQuoting() public

enableForeignKeySQL() public

enabled() public

exec() public

execute() public

executeStatement() protected

getLogger() public

getMaxAliasLength() public

getPdo() protected

getRole() public

inTransaction() public

isAutoQuotingEnabled() public

isConnected() public

lastInsertId() public

log() public

newCompiler() public

newTableSchema() public

prepare() public

quoteIdentifier() public

quoter() public

releaseSavePointSQL() public

rollbackSavePointSQL() public

rollbackTransaction() public

run() public

savePointSQL() public

schema() public

schemaDialect() public

schemaValue() public

setLogger() public

supports() public

transformQuery() protected

version() public

Method Detail

__construct() ¶ public

Parameters

Throws

__debugInfo() ¶ public

Returns

__destruct() ¶ public

_deleteQueryTranslator() ¶ protected

Parameters

Returns

_expressionTranslators() ¶ protected

Returns

_insertQueryTranslator() ¶ protected

Parameters

Returns

_pagingSubquery() ¶ protected

Parameters

Returns

_removeAliasesFromConditions() ¶ protected

Parameters