|
Class TMssqlCommandBuilder
TMssqlCommandBuilder provides specifics methods to create limit/offset query
commands for MSSQL servers.
-
TComponent
-
TDbCommandBuilder
-
TMssqlCommandBuilder
Methods summary
public
integer
|
#
getLastInsertID( )
Overrides parent implementation. Uses "SELECT @@Identity".
Overrides parent implementation. Uses "SELECT @@Identity".
Returns
integer last insert id, null if none is found.
Overrides
|
public
string
|
#
applyLimitOffset( string $sql, integer $limit = -1, integer $offset = -1 )
Overrides parent implementation. Alters the sql to apply $limit and $offset.
The idea for limit with offset is done by modifying the sql on the fly with
numerous assumptions on the structure of the sql string. The modification is
done with reference to the notes from http://troels.arvin.dk/db/rdbms/#select-limit-offset
Overrides parent implementation. Alters the sql to apply $limit and $offset.
The idea for limit with offset is done by modifying the sql on the fly with
numerous assumptions on the structure of the sql string. The modification is
done with reference to the notes from http://troels.arvin.dk/db/rdbms/#select-limit-offset
SELECT * FROM (
SELECT TOP n * FROM (
SELECT TOP z columns -- (z=n+skip)
FROM tablename
ORDER BY key ASC
) AS FOO ORDER BY key DESC -- ('FOO' may be anything)
) AS BAR ORDER BY key ASC -- ('BAR' may be anything)
Regular expressions are used to alter the SQL query. The resulting SQL
query may be malformed for complex queries. The following restrictions
apply
- In particular, commas should NOT be used as part of the
ordering expression or identifier. Commas must only be used for separating the
ordering clauses.
- In the ORDER BY clause, the column name should NOT be be qualified with a
table name or view name. Alias the column names or use column index.
- No clauses should follow the ORDER BY clause, e.g. no COMPUTE or FOR
clauses.
Parameters
- $sql
string SQL query string.
- $limit
integer maximum number of rows, -1 to ignore limit.
- $offset
integer row offset, -1 to ignore offset.
Returns
string SQL with limit and offset.
Overrides
|
protected
sql
|
#
rewriteLimitOffsetSql( string $sql, integer $limit, integer $offset )
Rewrite sql to apply $limit > and $offset > 0 for MSSQL database. See
http://troels.arvin.dk/db/rdbms/#select-limit-offset
Parameters
- $sql
string sql query
- $limit
integer $limit > 0
- $offset
integer $offset > 0
Returns
sql modified sql query applied with limit and offset.
|
protected
array
|
#
findOrdering( string $sql )
|
protected
string
|
#
joinOrdering( array $orders )
Parameters
- $orders
array ordering obtained from findOrdering()
Returns
string concat the orderings
|
protected
array
|
#
reverseDirection( array $orders )
Parameters
- $orders
array original ordering
Returns
array ordering with reversed direction.
|
Methods inherited from TDbCommandBuilder
__construct(),
applyCriterias(),
applyOrdering(),
bindArrayValues(),
bindColumnValues(),
createCommand(),
createCountCommand(),
createDeleteCommand(),
createFindCommand(),
createInsertCommand(),
createUpdateCommand(),
getColumnBindings(),
getDbConnection(),
getInsertFieldBindings(),
getPdoType(),
getSearchCondition(),
getSearchExpression(),
getSelectFieldList(),
getTableInfo(),
hasIntegerKey(),
setDbConnection(),
setTableInfo()
|
Methods inherited from TComponent
__call(),
__destruct(),
__get(),
__isset(),
__set(),
__sleep(),
__unset(),
__wakeup(),
addParsedObject(),
asa(),
attachBehavior(),
attachBehaviors(),
attachClassBehavior(),
attachEventHandler(),
canGetProperty(),
canSetProperty(),
clearBehaviors(),
createdOnTemplate(),
detachBehavior(),
detachBehaviors(),
detachClassBehavior(),
detachEventHandler(),
disableBehavior(),
disableBehaviors(),
enableBehavior(),
enableBehaviors(),
evaluateExpression(),
evaluateStatements(),
fxAttachClassBehavior(),
fxDetachClassBehavior(),
getAutoGlobalListen(),
getBehaviorsEnabled(),
getClassHierarchy(),
getEventHandlers(),
getListeningToGlobalEvents(),
getSubProperty(),
hasEvent(),
hasEventHandler(),
hasProperty(),
isa(),
listen(),
raiseEvent(),
setSubProperty(),
unlisten()
|
|