Bitrix API

MssqlSqlHelper extends SqlHelper

Table of Contents

Methods

__construct()  : mixed
addDaysToDateTime()  : string
Returns function for adding days time interval to $from.
addSecondsToDateTime()  : string
Returns function for adding seconds time interval to $from.
castToChar()  : string
Returns CAST expression for converting field or expression into string
convertDateField()  : null|Date
convertDatetimeField()  : null|DateTime
convertFromDb()  : mixed
Returns $value converted to a type according to $field type.
convertFromDbDate()  : Date
convertFromDbDateTime()  : DateTime
convertFromDbFloat()  : float
convertFromDbInteger()  : int
convertFromDbString()  : string
convertFromDbText()  : string
convertToDb()  : string
Converts values to the string according to the column type to use it in a SQL query.
convertToDbBinary()  : string
Returns binary safe data representation.
convertToDbDate()  : string
Converts value to the string according to the data type to use it in a SQL query.
convertToDbDateTime()  : string
Converts value to the string according to the data type to use it in a SQL query.
convertToDbFloat()  : string
Converts value to the string according to the data type to use it in a SQL query.
convertToDbInteger()  : int
Converts value to the string according to the data type to use it in a SQL query.
convertToDbString()  : string
Converts value to the string according to the data type to use it in a SQL query.
convertToDbText()  : string
Converts value to the string according to the data type to use it in a SQL query.
formatDate()  : string
Returns database expression for converting $field value according the $format.
forSql()  : string
Escapes special characters in a string for use in an SQL statement.
getAliasLength()  : int
Returns maximum length of an alias in a select statement
getAscendingOrder()  : string
Returns ascending order specifier for ORDER BY clause.
getCharToDateFunction()  : string
Returns function for converting string value into datetime.
getColumnTypeByField()  : string
Returns a column type according to ScalarField object.
getConcatFunction()  : string
Returns function for concatenating database fields or expressions.
getConditionalAssignment()  : string
getConverter()  : false|callable
Returns callback to be called for a field value on fetch.
getCurrentDateFunction()  : string
Returns function for getting current date without time part.
getCurrentDateTimeFunction()  : string
Returns function for getting current time.
getDatetimeToDateFunction()  : string
Returns function cast $value to datetime database type.
getDateToCharFunction()  : string
Returns function for converting database field or expression into string.
getDescendingOrder()  : string
Returns descending order specifier for ORDER BY clause.
getFieldByColumnType()  : ScalarField
Returns instance of a descendant from Entity\ScalarField that matches database type.
getIlikeOperator()  : string
Returns case insensitive like expression.
getInsertIgnore()  : string
Makes an insert statement which will ignore duplicate keys errors.
getIsNullFunction()  : string
Returns function for testing database field or expressions against NULL value. When it is NULL then $result will be returned.
getLeftQuote()  : string
Returns an identificator escaping left character.
getLengthFunction()  : string
Returns function for getting length of database field or expression.
getMatchAndExpression()  : mixed
getMatchFunction()  : mixed
getMatchOrExpression()  : mixed
getOrderByIntField()  : string
getOrderByStringField()  : string
getQueryDelimiter()  : string
Returns database specific query delimiter for batch processing.
getQueryTables()  : array<string|int, mixed>
getRandomFunction()  : string
Returns function for getting random number.
getRegexpOperator()  : string
Returns regexp expression.
getRightQuote()  : string
Returns an identificator escaping right character.
getRowNumber()  : mixed
getSha1Function()  : string
Returns function to generate sha1 hash.
getSubstrFunction()  : string
Returns function for getting part of string.
getTopSql()  : string
Transforms Sql according to $limit and $offset limitations.
initRowNumber()  : mixed
isBigType()  : bool
Checks is the field type is BIG
prepareAssignment()  : string
Builds the string for the SQL assignment operation of the given column.
prepareCorrelatedUpdate()  : string
Builds correlated update DML.
prepareDeleteLimit()  : string
Builds the DML string for the SQL DELETE command for the given table with limited rows number.
prepareInsert()  : array<string|int, mixed>
Builds the strings for the SQL INSERT command for the given table.
prepareMerge()  : array<string|int, mixed>
Builds the strings for the SQL MERGE command for the given table.
prepareMergeMultiple()  : array<string|int, mixed>
Builds the DML strings for the SQL REPLACE INTO command for the given table.
prepareMergeSelect()  : string
Builds the DML strings for the SQL INSERT INTO ON CONFLICT UPDATE command for the given table.
prepareMergeValues()  : string
Returns prepared sql string for upsert multiple rows
prepareUpdate()  : array<string|int, mixed>
Builds the strings for the SQL UPDATE command for the given table.
quote()  : string
Returns quoted identifier.
softCastTextToChar()  : string
Returns expression for text field being used in group or order
values()  : string
Returns identifier for usage in VALUES.

Methods

addDaysToDateTime()

Returns function for adding days time interval to $from.

public addDaysToDateTime(int $days[, int $from = null ]) : string

If $from is null or omitted, then current time is used.

$days and $from parameters are SQL unsafe.

Parameters
$days : int

How many days to add.

$from : int = null

Datetime database field of expression.

Tags
abstract
Return values
string

addSecondsToDateTime()

Returns function for adding seconds time interval to $from.

public addSecondsToDateTime(mixed $seconds[, mixed $from = null ]) : string
Parameters
$seconds : mixed

How many seconds to add.

$from : mixed = null

Datetime database field of expression.

Tags
inheritdoc
Return values
string

castToChar()

Returns CAST expression for converting field or expression into string

public castToChar(mixed $fieldName) : string
Parameters
$fieldName : mixed
Tags
inheritdoc
Return values
string

convertDateField()

public convertDateField(string $value) : null|Date

Converts string into \Bitrix\Main\Type\Date object.

Helper function.

Parameters
$value : string

Value fetched.

Tags
see
SqlHelper::getConverter
Return values
null|Date

convertDatetimeField()

public convertDatetimeField(string $value) : null|DateTime

Converts string into \Bitrix\Main\Type\DateTime object.

Helper function.

Parameters
$value : string

Value fetched.

Tags
see
SqlHelper::getConverter
Return values
null|DateTime

convertFromDb()

Returns $value converted to a type according to $field type.

public convertFromDb(mixed $value, IReadable $field) : mixed

For example if $field is Entity\DatetimeField then returned value will be the instance of Type\DateTime.

Parameters
$value : mixed

Value to be converted.

$field : IReadable

Type "source".

convertFromDbDate()

public convertFromDbDate(mixed $value) : Date
Parameters
$value : mixed
Tags
inheritdoc
Return values
Date

convertFromDbDateTime()

public convertFromDbDateTime(mixed $value) : DateTime
Parameters
$value : mixed
Tags
inheritdoc
Return values
DateTime

convertFromDbFloat()

public convertFromDbFloat(mixed $value[, int $scale = null ]) : float
Parameters
$value : mixed
$scale : int = null
Return values
float

convertFromDbInteger()

public convertFromDbInteger(mixed $value) : int
Parameters
$value : mixed
Return values
int

convertFromDbString()

public convertFromDbString(mixed $value[, mixed $length = null ]) : string
Parameters
$value : mixed
$length : mixed = null
Tags
inheritdoc
Return values
string

convertFromDbText()

public convertFromDbText(mixed $value) : string
Parameters
$value : mixed
Return values
string

convertToDb()

Converts values to the string according to the column type to use it in a SQL query.

public convertToDb(mixed $value[, IReadable|null $field = null ]) : string
Parameters
$value : mixed

Value to be converted.

$field : IReadable|null = null

Type "source".

Return values
string —

Value to write to column.

convertToDbBinary()

Returns binary safe data representation.

public convertToDbBinary(string $value) : string
Parameters
$value : string

Value to be encoded.

Return values
string

convertToDbDate()

Converts value to the string according to the data type to use it in a SQL query.

public convertToDbDate(mixed $value) : string
Parameters
$value : mixed

Value to be converted.

Tags
throws
ArgumentTypeException
Return values
string —

Value to write to column.

convertToDbDateTime()

Converts value to the string according to the data type to use it in a SQL query.

public convertToDbDateTime(mixed $value) : string
Parameters
$value : mixed

Value to be converted.

Tags
throws
ArgumentTypeException
Return values
string —

Value to write to column.

convertToDbFloat()

Converts value to the string according to the data type to use it in a SQL query.

public convertToDbFloat(mixed $value[, int|null $scale = null ]) : string
Parameters
$value : mixed

Value to be converted.

$scale : int|null = null

Precise to round float value.

Return values
string —

Value to write to column.

convertToDbInteger()

Converts value to the string according to the data type to use it in a SQL query.

public convertToDbInteger(mixed $value[, int $size = 8 ]) : int
Parameters
$value : mixed

Value to be converted.

$size : int = 8

Size in bytes.

Return values
int —

Value to write to column.

convertToDbString()

Converts value to the string according to the data type to use it in a SQL query.

public convertToDbString(mixed $value[, int|null $length = null ]) : string
Parameters
$value : mixed

Value to be converted.

$length : int|null = null

Maximum acceptable length of the value

Return values
string —

Value to write to column.

convertToDbText()

Converts value to the string according to the data type to use it in a SQL query.

public convertToDbText(mixed $value) : string
Parameters
$value : mixed

Value to be converted.

Return values
string —

Value to write to column.

formatDate()

Returns database expression for converting $field value according the $format.

public formatDate(mixed $format[, mixed $field = null ]) : string
Parameters
$format : mixed

Format string.

$field : mixed = null

Database field or expression.

Tags
inheritdoc
Return values
string

forSql()

Escapes special characters in a string for use in an SQL statement.

public forSql(mixed $value[, mixed $maxLength = 0 ]) : string
Parameters
$value : mixed

Value to be escaped.

$maxLength : mixed = 0

Limits string length if set.

Tags
inheritdoc
Return values
string

getAliasLength()

Returns maximum length of an alias in a select statement

public getAliasLength() : int
Tags
inheritdoc
Return values
int

getAscendingOrder()

Returns ascending order specifier for ORDER BY clause.

public getAscendingOrder() : string
Return values
string

getCharToDateFunction()

Returns function for converting string value into datetime.

public getCharToDateFunction(mixed $value) : string
Parameters
$value : mixed

String in YYYY-MM-DD HH:MI:SS format.

Tags
inheritdoc
Return values
string

getColumnTypeByField()

Returns a column type according to ScalarField object.

public getColumnTypeByField(ScalarField $field) : string
Parameters
$field : ScalarField

Type "source".

Tags
inheritdoc
Return values
string

getConcatFunction()

Returns function for concatenating database fields or expressions.

public getConcatFunction() : string
Tags
inheritdoc
Return values
string

getConditionalAssignment()

public getConditionalAssignment(string|SqlExpression $field, string $value) : string
Parameters
$field : string|SqlExpression
$value : string
Return values
string

getConverter()

Returns callback to be called for a field value on fetch.

public getConverter(ScalarField $field) : false|callable
Parameters
$field : ScalarField

Type "source".

Tags
inheritdoc
Return values
false|callable

getCurrentDateFunction()

Returns function for getting current date without time part.

public getCurrentDateFunction() : string
Tags
inheritdoc
Return values
string

getCurrentDateTimeFunction()

Returns function for getting current time.

public getCurrentDateTimeFunction() : string
Tags
inheritdoc
Return values
string

getDatetimeToDateFunction()

Returns function cast $value to datetime database type.

public getDatetimeToDateFunction(mixed $value) : string
Parameters
$value : mixed

Database field or expression to cast.

Tags
inheritdoc
Return values
string

getDateToCharFunction()

Returns function for converting database field or expression into string.

public getDateToCharFunction(mixed $fieldName) : string
Parameters
$fieldName : mixed

Database field or expression.

Tags
inheritdoc
Return values
string

getDescendingOrder()

Returns descending order specifier for ORDER BY clause.

public getDescendingOrder() : string
Return values
string

getFieldByColumnType()

Returns instance of a descendant from Entity\ScalarField that matches database type.

public getFieldByColumnType(mixed $name, mixed $type[, array<string|int, mixed> $parameters = null ]) : ScalarField
Parameters
$name : mixed

Database column name.

$type : mixed

Database specific type.

$parameters : array<string|int, mixed> = null

Additional information.

Tags
inheritdoc
Return values
ScalarField

getIlikeOperator()

Returns case insensitive like expression.

public getIlikeOperator(string $field, string $value) : string

All parameters are SQL unsafe.

Parameters
$field : string

Database field or expression.

$value : string

String to match.

Tags
abstract
Return values
string

getInsertIgnore()

Makes an insert statement which will ignore duplicate keys errors.

public getInsertIgnore(string $tableName, int $fields, int $sql) : string
Parameters
$tableName : string

Table to insert.

$fields : int

Fields list in braces.

$sql : int

Select or values sql.

Tags
abstract
Return values
string

getIsNullFunction()

Returns function for testing database field or expressions against NULL value. When it is NULL then $result will be returned.

public getIsNullFunction(mixed $expression, mixed $result) : string
Parameters
$expression : mixed

Database field or expression for NULL test.

$result : mixed

Database field or expression to return when $expression is NULL.

Tags
inheritdoc
Return values
string

getLeftQuote()

Returns an identificator escaping left character.

public getLeftQuote() : string
Tags
inheritdoc
Return values
string

getLengthFunction()

Returns function for getting length of database field or expression.

public getLengthFunction(mixed $field) : string
Parameters
$field : mixed

Database field or expression.

Tags
inheritdoc
Return values
string

getMatchAndExpression()

public getMatchAndExpression(mixed $values[, mixed $prefixSearch = false ]) : mixed
Parameters
$values : mixed
$prefixSearch : mixed = false
Tags
abstract

getMatchFunction()

public getMatchFunction(mixed $field, mixed $value) : mixed
Parameters
$field : mixed
$value : mixed
Tags
abstract

getMatchOrExpression()

public getMatchOrExpression(mixed $values[, mixed $prefixSearch = false ]) : mixed
Parameters
$values : mixed
$prefixSearch : mixed = false
Tags
abstract

getOrderByIntField()

public getOrderByIntField(string $field, array<string|int, mixed> $values[, bool $quote = true ]) : string
Parameters
$field : string
$values : array<string|int, mixed>
$quote : bool = true
Return values
string

getOrderByStringField()

public getOrderByStringField(string $field, array<string|int, mixed> $values[, bool $quote = true ]) : string
Parameters
$field : string
$values : array<string|int, mixed>
$quote : bool = true
Return values
string

getQueryDelimiter()

Returns database specific query delimiter for batch processing.

public getQueryDelimiter() : string
Tags
inheritdoc
Return values
string

getQueryTables()

public getQueryTables(string $sql[, int $maxLevel = -1 ]) : array<string|int, mixed>
Parameters
$sql : string
$maxLevel : int = -1
Return values
array<string|int, mixed>

getRandomFunction()

Returns function for getting random number.

public getRandomFunction() : string
Return values
string

getRegexpOperator()

Returns regexp expression.

public getRegexpOperator(string $field, string $regexp) : string

All parameters are SQL unsafe.

Parameters
$field : string

Database field or expression.

$regexp : string

Regexp to match.

Tags
abstract
Return values
string

getRightQuote()

Returns an identificator escaping right character.

public getRightQuote() : string
Tags
inheritdoc
Return values
string

getRowNumber()

public getRowNumber(mixed $variableName) : mixed
Parameters
$variableName : mixed
Tags
abstract

getSha1Function()

Returns function to generate sha1 hash.

public getSha1Function(string $field) : string

$field parameter is SQL unsafe.

Parameters
$field : string

Database field or expression.

Return values
string

getSubstrFunction()

Returns function for getting part of string.

public getSubstrFunction(mixed $str, mixed $from[, mixed $length = null ]) : string
Parameters
$str : mixed

Database field or expression.

$from : mixed

Start position.

$length : mixed = null

Maximum length.

Tags
inheritdoc
Return values
string

getTopSql()

Transforms Sql according to $limit and $offset limitations.

public getTopSql(mixed $sql, mixed $limit[, mixed $offset = 0 ]) : string
Parameters
$sql : mixed

Sql text.

$limit : mixed

Maximum number of rows to return.

$offset : mixed = 0

Offset of the first row to return, starting from 0.

Tags
inheritdoc
Return values
string

initRowNumber()

public initRowNumber(mixed $variableName) : mixed
Parameters
$variableName : mixed
Tags
abstract

isBigType()

Checks is the field type is BIG

public isBigType(mixed $type) : bool
Parameters
$type : mixed
Return values
bool

prepareAssignment()

Builds the string for the SQL assignment operation of the given column.

public prepareAssignment(string $tableName, string $columnName, string $value) : string
Parameters
$tableName : string

A table name.

$columnName : string

A column name.

$value : string

A value to assign.

Return values
string

prepareCorrelatedUpdate()

Builds correlated update DML.

public prepareCorrelatedUpdate(string $tableName, string $tableAlias, array<string|int, mixed> $fields, string $from, string $where) : string
Parameters
$tableName : string

A table name.

$tableAlias : string

A table alias.

$fields : array<string|int, mixed>

Array("column" => "expression")[] Update columns list.

$from : string

Correlated tables.

$where : string

Where clause.

Tags
abstract
Return values
string

prepareDeleteLimit()

Builds the DML string for the SQL DELETE command for the given table with limited rows number.

public prepareDeleteLimit(string $tableName, array<string|int, mixed> $primaryFields, string $where, array<string|int, mixed> $order, int $limit) : string
Parameters
$tableName : string

A table name.

$primaryFields : array<string|int, mixed>

Array("column")[] Primary key columns list.

$where : string

Sql where clause.

$order : array<string|int, mixed>

Array("column" => asc|desc)[] Sort order.

$limit : int

Rows to delete count.

Tags
abstract
Return values
string —

(replace)

prepareInsert()

Builds the strings for the SQL INSERT command for the given table.

public prepareInsert(string $tableName, array<string|int, mixed> $fields[, bool $returnAsArray = false ]) : array<string|int, mixed>
Parameters
$tableName : string

A table name.

$fields : array<string|int, mixed>

Array("column" => $value)[].

$returnAsArray : bool = false
Return values
array<string|int, mixed> —

(columnList, valueList, binds)

prepareMerge()

Builds the strings for the SQL MERGE command for the given table.

public prepareMerge(mixed $tableName, array<string|int, mixed> $primaryFields, array<string|int, mixed> $insertFields, array<string|int, mixed> $updateFields) : array<string|int, mixed>
Parameters
$tableName : mixed

A table name.

$primaryFields : array<string|int, mixed>

Array("column")[] Primary key columns list.

$insertFields : array<string|int, mixed>

Array("column" => $value)[] What to insert.

$updateFields : array<string|int, mixed>

Array("column" => $value)[] How to update.

Tags
inheritdoc
Return values
array<string|int, mixed> —

(merge)

prepareMergeMultiple()

Builds the DML strings for the SQL REPLACE INTO command for the given table.

public prepareMergeMultiple(string $tableName, array<string|int, mixed> $primaryFields, array<string|int, mixed> $insertRows) : array<string|int, mixed>
Parameters
$tableName : string

A table name.

$primaryFields : array<string|int, mixed>

Array("column")[] Primary key columns list.

$insertRows : array<string|int, mixed>

Array(Array("column" => $value)[])[] Rows to insert.

Tags
abstract
Return values
array<string|int, mixed> —

(replace)

prepareMergeSelect()

Builds the DML strings for the SQL INSERT INTO ON CONFLICT UPDATE command for the given table.

public prepareMergeSelect(string $tableName, array<string|int, mixed> $primaryFields, array<string|int, mixed> $selectFields, mixed $select, mixed $updateFields) : string
Parameters
$tableName : string

A table name.

$primaryFields : array<string|int, mixed>

Array("column")[] Primary key columns list.

$selectFields : array<string|int, mixed>
$select : mixed
$updateFields : mixed
Tags
abstract
Return values
string —

(replace)

prepareMergeValues()

Returns prepared sql string for upsert multiple rows

public prepareMergeValues(string $tableName, array<string|int, mixed> $primaryFields, array<string|int, mixed> $insertRows[, array<string|int, mixed> $updateFields = [] ]) : string
Parameters
$tableName : string

Table name

$primaryFields : array<string|int, mixed>

Fields that can be conflicting keys (primary, unique keys)

$insertRows : array<string|int, mixed>

Rows to insert [['FIELD_NAME' =>'value',...],...], Attention! use same columns in each row

$updateFields : array<string|int, mixed> = []

Fields to update, if empty - update all fields, can be only field names, or fieldname => expression or fieldname => value

Tags
throws
ArgumentException
Return values
string

prepareUpdate()

Builds the strings for the SQL UPDATE command for the given table.

public prepareUpdate(string $tableName, array<string|int, mixed> $fields) : array<string|int, mixed>
Parameters
$tableName : string

A table name.

$fields : array<string|int, mixed>

Array("column" => $value)[].

Return values
array<string|int, mixed> —

(update, binds)

quote()

Returns quoted identifier.

public quote(string $identifier) : string

For example Title become : - `Title` for MySQL - "TITLE" for Oracle - [Title] for Ms SQL

Parameters
$identifier : string

Table or Column name.

Tags
see
SqlHelper::getLeftQuote
see
SqlHelper::getRightQuote
Return values
string

softCastTextToChar()

Returns expression for text field being used in group or order

public softCastTextToChar(mixed $fieldName) : string
Parameters
$fieldName : mixed
Tags
inheritdoc
Return values
string

values()

Returns identifier for usage in VALUES.

public values(string $identifier) : string
Parameters
$identifier : string

Column name.

Tags
abstract
see
SqlHelper::quote
Return values
string 

        

        
    




    

    

                                

                

                                
    

        On this page

        
    


                

                            

            

    

        

            
Search results