Jelix 1.8.13

jDbSqlsrvTools extends jDbTools
in package

Provides utilities methods for a database.

Tags
subpackage

db_driver

author

Yann Lecommandoux

contributor

Julien, Laurent Jouanneau

copyright

2008 Yann Lecommandoux, 2010 Julien, 2017 Laurent Jouanneau

see
http://jelix.org
licence

http://www.gnu.org/licenses/lgpl.html GNU Lesser General Public Licence, see LICENCE file

Table of Contents

IBD_EMPTY_TABLE_BEFORE  = 1
IBD_IGNORE_IF_EXIST  = 3
IBD_INSERT_ONLY_IF_TABLE_IS_EMPTY  = 2
IBD_NO_CHECK  = 0
IBD_UPDATE_IF_EXIST  = 4
$falseValue  : mixed
$trueValue  : mixed
$_conn  : jDbConnection
the database connector.
$dbmsStyle  : mixed
regular expression to detect comments and end of query.
$functionNameCorrespondence  : mixed
$keywordNameCorrespondence  : mixed
$typesInfo  : mixed
$unifiedToPhp  : mixed
__construct()  : mixed
encloseName()  : string
Enclose the field name.
escapeValue()  : string
execSQLScript()  : mixed
execute a list of queries stored in a file.
getBooleanValue()  : string
getFieldList()  : array<string|int, jDbFieldProperties>
retrieve the list of fields of a table.
getNativeSQLFunction()  : string
Give the expression that works with the target database, corresponding to the given function name.
getSQLColumnsList()  : string
getTableList()  : array<string|int, string>
returns the list of tables.
getTypeInfo()  : array<string|int, mixed>
Get informations about the given SQL type.
insertBulkData()  : int
Insert several records into a table.
parseCREATETABLE()  : array<string|int, mixed>|bool
Parse a SQL CREATE TABLE statement and returns all of its components separately.
parseSQLFunctionAndConvert()  : mixed
parseSQLType()  : array<string|int, mixed>
Parse a SQL type and gives type, length.
stringToPhpValue()  : string
unifiedToPHPType()  : string
Return the PHP type corresponding to the given unified type.
extractDateConverter()  : mixed

Constants

IBD_EMPTY_TABLE_BEFORE

public mixed IBD_EMPTY_TABLE_BEFORE = 1
Tags

IBD_INSERT_ONLY_IF_TABLE_IS_EMPTY

public mixed IBD_INSERT_ONLY_IF_TABLE_IS_EMPTY = 2
Tags

Properties

$dbmsStyle

regular expression to detect comments and end of query.

protected mixed $dbmsStyle = array('/^\\s*(#|\\-\\- )/', '/;\\s*$/')
Tags

$functionNameCorrespondence

protected mixed $functionNameCorrespondence = array( // sqlsrv, -> date+time //'sysdatetime' => '', // sqlsrv, -> date+time+offset //'sysdatetimeoffset' => '', // sqlsrv, -> date+time at utc //'sysutcdatetime' => '', // sqlsrv -> date+time //'getdate' => '', // sqlsrv -> date+time at utc //'getutcdate' => '', // sqlsrv,mysql (datetime)-> integer //'day' => '', // sqlsrv,mysql (datetime)-> integer //'month' => '', // sqlsrv, mysql (datetime)-> integer //'year' => '', // mysql -> date 'curdate' => 'DATEFROMPARTS(DATEPART(year,GETDATE()),DATEPART(month,GETDATE()),DATEPART(day,GETDATE()))', // mysql -> date 'current_date' => 'DATEFROMPARTS(DATEPART(year,GETDATE()),DATEPART(month,GETDATE()),DATEPART(day,GETDATE()))', // mysql -> time 'curtime' => 'TIMEFROMPARTS(DATEPART(hour,GETDATE()),DATEPART(minute,GETDATE()),DATEPART(second,GETDATE()),0,0)', // mysql -> time 'current_time' => 'TIMEFROMPARTS(DATEPART(hour,GETDATE()),DATEPART(minute,GETDATE()),DATEPART(second,GETDATE()),0,0)', // mysql,pgsql -> date+time 'now' => 'GETDATE()', // mysql date+time 'current_timestamp' => 'GETDATE()', // mysql (datetime)->date, sqlite (timestring, modifier)->date 'date' => 'DATEFROMPARTS(DATEPART(year,%!p),DATEPART(month,%!p),DATEPART(day,%!p))', // mysql = day() 'dayofmonth' => 'day(%!p)', // mysql -> date+time 'localtime' => 'GETDATE()', // mysql -> date+time 'localtimestamp' => 'GETDATE()', // mysql utc current date 'utc_date' => 'DATEFROMPARTS(DATEPART(year,GETUTCDATE()),DATEPART(month,GETUTCDATE()),DATEPART(day,GETUTCDATE()))', // mysql utc current time 'utc_time' => 'TIMEFROMPARTS(DATEPART(hour,GETUTCDATE()),DATEPART(minute,GETUTCDATE()),DATEPART(second,GETUTCDATE()),0,0)', // mysql utc current date+time 'utc_timestamp' => 'GETUTCDATE()', // mysql (datetime)->time, , sqlite (timestring, modifier)->time 'time' => 'TIMEFROMPARTS(DATEPART(hour,%!p),DATEPART(minute,%!p),DATEPART(second,%!p),0,0)', // mysql (datetime/time)-> hour 'hour' => 'DATEPART(hour,GETDATE())', // mysql (datetime/time)-> minute 'minute' => 'DATEPART(minute,GETDATE())', // mysql (datetime/time)-> second 'second' => 'DATEPART(second,GETDATE())', // sqlite (timestring, modifier)->datetime //'datetime' => '!sqliteDateTime', // oci, mysql (year|month|day|hour|minute|second FROM <datetime>)->value , // pgsql (year|month|day|hour|minute|second <datetime>)->value 'extract' => '!extractDateConverter', // pgsql ('year'|'month'|'day'|'hour'|'minute'|'second', <datetime>)->value 'date_part' => '!extractDateConverter', )
Tags

$keywordNameCorrespondence

protected mixed $keywordNameCorrespondence = array( // sqlsrv,mysql,oci,pgsql -> date+time //'current_timestamp' => '', // mysql,oci,pgsql -> date 'current_date' => 'DATEFROMPARTS(DATEPART(year,GETDATE()),DATEPART(month,GETDATE()),DATEPART(day,GETDATE()))', // mysql -> time, pgsql -> time+timezone 'current_time' => 'TIMEFROMPARTS(DATEPART(hour,GETDATE()),DATEPART(minute,GETDATE()),DATEPART(second,GETDATE()),0,0)', // oci -> date+fractional secon + timezone 'systimestamp' => 'GETDATE()', // oci -> date+time+tz 'sysdate' => 'GETDATE()', // pgsql -> time 'localtime' => 'TIMEFROMPARTS(DATEPART(hour,GETDATE()),DATEPART(minute,GETDATE()),DATEPART(second,GETDATE()),0,0)', // pgsql -> date+time 'localtimestamp' => 'GETDATE()', )
Tags

$typesInfo

protected mixed $typesInfo = array( // type native type unified type minvalue maxvalue minlength maxlength 'bool' => array('tinyint', 'boolean', 0, 1, \null, \null), 'boolean' => array('tinyint', 'boolean', 0, 1, \null, \null), 'bit' => array('bit', 'integer', 0, 1, \null, \null), 'tinyint' => array('tinyint', 'integer', 0, 255, \null, \null), 'smallint' => array('smallint', 'integer', -32768, 32767, \null, \null), 'mediumint' => array('int', 'integer', -8388608, 8388607, \null, \null), 'integer' => array('int', 'integer', -2147483648, 2147483647, \null, \null), 'int' => array('int', 'integer', -2147483648, 2147483647, \null, \null), 'bigint' => array('bigint', 'numeric', '-9223372036854775808', '9223372036854775807', \null, \null), 'serial' => array('int', 'integer', -2147483648, 2147483647, \null, \null), 'bigserial' => array('bigint', 'numeric', '-9223372036854775808', '9223372036854775807', \null, \null), 'autoincrement' => array('int', 'integer', -2147483648, 2147483647, \null, \null), // for old dao files 'bigautoincrement' => array('bigint', 'numeric', '-9223372036854775808', '9223372036854775807', \null, \null), // for old dao files 'float' => array('float', 'float', \null, \null, \null, \null), //4bytes 'money' => array('money', 'float', \null, \null, \null, \null), //8bytes 'smallmoney' => array('smallmoney', 'float', \null, \null, \null, \null), //4bytes 'double precision' => array('real', 'float', \null, \null, \null, \null), //8bytes 'double' => array('real', 'float', \null, \null, \null, \null), //8bytes 'real' => array('real', 'float', \null, \null, \null, \null), //8bytes 'number' => array('real', 'decimal', \null, \null, \null, \null), //8bytes 'binary_float' => array('real', 'float', \null, \null, \null, \null), //4bytes 'binary_double' => array('real', 'decimal', \null, \null, \null, \null), //8bytes 'numeric' => array('numeric', 'decimal', \null, \null, \null, \null), 'decimal' => array('decimal', 'decimal', \null, \null, \null, \null), 'dec' => array('decimal', 'decimal', \null, \null, \null, \null), 'date' => array('date', 'date', \null, \null, 10, 10), 'time' => array('time', 'time', \null, \null, 8, 16), //23:59:59.9999999 'datetime' => array('datetime', 'datetime', \null, \null, 19, 23), // 9999-12-31 23:59:59.997 'datetime2' => array('datetime2', 'datetime', \null, \null, 19, 27), // 9999-12-31 23:59:59.9999999 'datetimeoffset' => array('datetimeoffset', 'datetime', \null, \null, 19, 34), // 9999-12-31 23:59:59.9999999 +14:00 'smalldatetime' => array('smalldatetime', 'datetime', \null, \null, 19, 19), // 2079-06-06 23:59 'timestamp' => array('datetime', 'datetime', \null, \null, 19, 19), // oracle/pgsql timestamp 'utimestamp' => array('integer', 'integer', 0, 2147483647, \null, \null), // mysql timestamp 'year' => array('integer', 'year', \null, \null, 2, 4), 'interval' => array('datetime', 'datetime', \null, \null, 19, 19), 'char' => array('char', 'char', \null, \null, 0, 0), 'nchar' => array('nchar', 'char', \null, \null, 0, 0), 'varchar' => array('varchar', 'varchar', \null, \null, 0, 0), 'varchar2' => array('varchar', 'varchar', \null, \null, 0, 0), 'nvarchar' => array('nvarchar', 'varchar', \null, \null, 0, 0), 'nvarchar2' => array('nvarchar', 'varchar', \null, \null, 0, 0), 'character' => array('varchar', 'varchar', \null, \null, 0, 0), 'character varying' => array('varchar', 'varchar', \null, \null, 0, 0), 'name' => array('varchar', 'varchar', \null, \null, 0, 64), 'longvarchar' => array('varchar', 'varchar', \null, \null, 0, 0), 'string' => array('varchar', 'varchar', \null, \null, 0, 0), // for old dao files 'tinytext' => array('text', 'text', \null, \null, 0, 255), 'text' => array('text', 'text', \null, \null, 0, 0), 'ntext' => array('ntext', 'text', \null, \null, 0, 0), 'mediumtext' => array('text', 'text', \null, \null, 0, 0), 'longtext' => array('text', 'text', \null, \null, 0, 0), 'long' => array('text', 'text', \null, \null, 0, 0), 'clob' => array('text', 'text', \null, \null, 0, 0), 'nclob' => array('text', 'text', \null, \null, 0, 0), 'tinyblob' => array('varbinary', 'blob', \null, \null, 0, 255), 'blob' => array('varbinary', 'blob', \null, \null, 0, 65535), 'mediumblob' => array('varbinary', 'blob', \null, \null, 0, 16777215), 'longblob' => array('varbinary', 'blob', \null, \null, 0, 0), 'bfile' => array('varbinary', 'blob', \null, \null, 0, 0), 'bytea' => array('varbinary', 'varbinary', \null, \null, 0, 0), 'binary' => array('binary', 'binary', \null, \null, 0, 8000), 'varbinary' => array('varbinary', 'varbinary', \null, \null, 0, 8000), 'raw' => array('varbinary', 'varbinary', \null, \null, 0, 2000), 'long raw' => array('varbinary', 'varbinary', \null, \null, 0, 0), 'image' => array('image', 'varbinary', \null, \null, 0, 0), 'enum' => array('varchar', 'varchar', \null, \null, 0, 65535), 'set' => array('varchar', 'varchar', \null, \null, 0, 65535), 'xmltype' => array('varchar', 'varchar', \null, \null, 0, 65535), 'xml' => array('xml', 'text', \null, \null, 0, 0), 'point' => array('varchar', 'varchar', \null, \null, 0, 16), 'line' => array('varchar', 'varchar', \null, \null, 0, 32), 'lsed' => array('varchar', 'varchar', \null, \null, 0, 32), 'box' => array('varchar', 'varchar', \null, \null, 0, 32), 'path' => array('varchar', 'varchar', \null, \null, 0, 65535), 'polygon' => array('varchar', 'varchar', \null, \null, 0, 65535), 'circle' => array('varchar', 'varchar', \null, \null, 0, 24), 'cidr' => array('varchar', 'varchar', \null, \null, 0, 24), 'inet' => array('varchar', 'varchar', \null, \null, 0, 24), 'macaddr' => array('integer', 'integer', 0, 0xffffffffffff, \null, \null), 'bit varying' => array('varchar', 'varchar', \null, \null, 0, 65535), 'arrays' => array('varchar', 'varchar', \null, \null, 0, 65535), 'complex types' => array('varchar', 'varchar', \null, \null, 0, 65535), )
Tags

$unifiedToPhp

protected mixed $unifiedToPhp = array('boolean' => 'boolean', 'integer' => 'integer', 'float' => 'float', 'double' => 'float', 'numeric' => 'numeric', 'decimal' => 'decimal', 'date' => 'string', 'time' => 'string', 'datetime' => 'string', 'year' => 'string', 'char' => 'string', 'varchar' => 'string', 'text' => 'string', 'blob' => 'string', 'binary' => 'string', 'varbinary' => 'string')
Tags

Methods

encloseName()

Enclose the field name.

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

the field name

Tags
Return values
string —

the enclosed field name

escapeValue()

public escapeValue(string $unifiedType, mixed $value[, mixed $checkNull = false ][, mixed $toPhpSource = false ]) : string
Parameters
$unifiedType : string

the unified type name

$value : mixed

the value

$checkNull : mixed = false
$toPhpSource : mixed = false
Tags
since
1.2
Return values
string —

the value which is ready to include a SQL query string

execSQLScript()

execute a list of queries stored in a file.

public execSQLScript(string $file) : mixed
Parameters
$file : string

path of the sql file

Tags
Return values
mixed —

getBooleanValue()

public getBooleanValue(bool|string $value) : string
Parameters
$value : bool|string

a value which is a boolean

Tags
since
1.2
Return values
string —

the string value representing a boolean in SQL

getFieldList()

retrieve the list of fields of a table.

public getFieldList(string $tableName[, string $sequence = '' ][, string $schemaName = '' ]) : array<string|int, jDbFieldProperties>
Parameters
$tableName : string

the name of the table

$sequence : string = ''

the sequence used to auto increment the primary key (not supported here)

$schemaName : string = ''

the name of the schema (only for PostgreSQL, not supported here)

Tags
Return values
array<string|int, jDbFieldProperties> —

keys are field names and values are jDbFieldProperties objects

getNativeSQLFunction()

Give the expression that works with the target database, corresponding to the given function name.

public getNativeSQLFunction(string $name[, null|string $parametersString = null ]) : string
Parameters
$name : string

a SQL function, maybe a SQL function of another database type

$parametersString : null|string = null

parameters given to the function. Null if no parenthesis

Tags
Return values
string —

the SQL expression, possibly with a native SQL function corresponding to the given foreign SQL function

getSQLColumnsList()

public getSQLColumnsList(array<string|int, string> $columns) : string
Parameters
$columns : array<string|int, string>

list of column names

Tags
since
1.6.16
Return values
string —

the list in SQL

getTableList()

returns the list of tables.

public getTableList() : array<string|int, string>
Tags
throws
jException
Return values
array<string|int, string> —

list of table names

getTypeInfo()

Get informations about the given SQL type.

public getTypeInfo(string $nativeType) : array<string|int, mixed>
Parameters
$nativeType : string

the SQL type

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

an array which contains characteristics of the type array ( 'nativetype', 'corresponding unifiedtype', minvalue, maxvalue, minlength, maxlength, autoincrement) minvalue, maxvalue, minlength, maxlength can be null

insertBulkData()

Insert several records into a table.

public insertBulkData(string $tableName, array<string|int, string> $columns, array<string|int, array<string|int, mixed>> $data[, null|string|array<string|int, string> $primaryKey = null ], int $options) : int
Parameters
$tableName : string
$columns : array<string|int, string>

the column names in which data will be inserted

$data : array<string|int, array<string|int, mixed>>

the data. each row is an array of values. Values are in the same order as $columns

$primaryKey : null|string|array<string|int, string> = null

the column names that are the primary key. Don't give the primary key if it is an autoincrement field, or if option is not IBD_*_IF_EXIST

$options : int

one of IDB_* const

Tags
since
1.6.16
Return values
int —

number of records inserted/updated

parseCREATETABLE()

Parse a SQL CREATE TABLE statement and returns all of its components separately.

public parseCREATETABLE( $createTableStatement) : array<string|int, mixed>|bool
Parameters
$createTableStatement :
Tags
since
1.6.16
Return values
array<string|int, mixed>|bool —

false if parsing has failed. Else an array : 'name' => the schema/table name, 'temporary'=> true if there is the temporary keywork , 'ifnotexists' => true if there is the IF NOT EXISTS statement, 'columns' => list of columns definitions, 'constraints' => list of table constraints definitions, 'options' => all options at the end of the CREATE TABLE statement.

parseSQLFunctionAndConvert()

public parseSQLFunctionAndConvert(mixed $expression) : mixed
Parameters
$expression : mixed
Tags
Return values
mixed —

parseSQLType()

Parse a SQL type and gives type, length.

public parseSQLType(string $type) : array<string|int, mixed>

..

Parameters
$type : string
Tags
Return values
array<string|int, mixed> —

[$realtype, $length, $precision, $scale, $otherTypeDef]

stringToPhpValue()

public stringToPhpValue(string $unifiedType, string $value[, mixed $checkNull = false ]) : string
Parameters
$unifiedType : string

the unified type name

$value : string

the value

$checkNull : mixed = false
Tags
since
1.2
Return values
string —

the php value corresponding to the type

unifiedToPHPType()

Return the PHP type corresponding to the given unified type.

public unifiedToPHPType(string $unifiedType) : string
Parameters
$unifiedType : string
Tags
throws
Exception
since
1.2
Return values
string —

the php type

extractDateConverter()

protected extractDateConverter(mixed $parametersString) : mixed
Parameters
$parametersString : mixed
Tags
Return values
mixed —

Search results