Class TMssqlCommandBuilder
TMssqlCommandBuilder provides specifics methods to create limit/offset query commands for MSSQL servers.
Method Summary |
string
|
Overrides parent implementation. Alters the sql to apply $limit and $offset.
|
protected
array
|
Base on simplified syntax http://msdn2.microsoft.com/en-us/library/aa259187(SQL.80).aspx
|
integer
|
Overrides parent implementation. Uses "SELECT @@Identity".
|
protected
string
|
|
protected
array
|
|
protected
sql
|
Rewrite sql to apply $limit > and $offset > 0 for MSSQL database.
|
Methods Inherited From TDbCommandBuilder |
TDbCommandBuilder::applyCriterias(), TDbCommandBuilder::applyLimitOffset(), TDbCommandBuilder::applyOrdering(), TDbCommandBuilder::bindArrayValues(), TDbCommandBuilder::bindColumnValues(), TDbCommandBuilder::createCommand(), TDbCommandBuilder::createCountCommand(), TDbCommandBuilder::createDeleteCommand(), TDbCommandBuilder::createFindCommand(), TDbCommandBuilder::createInsertCommand(), TDbCommandBuilder::createUpdateCommand(), TDbCommandBuilder::getColumnBindings(), TDbCommandBuilder::getDbConnection(), TDbCommandBuilder::getInsertFieldBindings(), TDbCommandBuilder::getLastInsertID(), TDbCommandBuilder::getPdoType(), TDbCommandBuilder::getSearchCondition(), TDbCommandBuilder::getSearchExpression(), TDbCommandBuilder::getTableInfo(), TDbCommandBuilder::hasIntegerKey(), TDbCommandBuilder::setDbConnection(), TDbCommandBuilder::setTableInfo()
|
Methods Inherited From TComponent |
TComponent::addParsedObject(), TComponent::attachEventHandler(), TComponent::canGetProperty(), TComponent::canSetProperty(), TComponent::createdOnTemplate(), TComponent::detachEventHandler(), TComponent::evaluateExpression(), TComponent::evaluateStatements(), TComponent::getEventHandlers(), TComponent::getSubProperty(), TComponent::hasEvent(), TComponent::hasEventHandler(), TComponent::hasProperty(), TComponent::raiseEvent(), TComponent::setSubProperty(), TComponent::__get(), TComponent::__set()
|
Method Details |
applyLimitOffset
public string applyLimitOffset |
(string $sql , integer $limit , integer $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
Input |
string | $sql | SQL query string. |
integer | $limit | maximum number of rows, -1 to ignore limit. |
integer | $offset | row offset, -1 to ignore offset. |
Output |
string
| SQL with limit and offset. |
Exception |
|
findOrdering
protected array findOrdering |
(string $sql ) |
Base on simplified syntax http://msdn2.microsoft.com/en-us/library/aa259187(SQL.80).aspx
Input |
string | $sql | |
Output |
array
| ordering expression as key and ordering direction as value |
Exception |
|
getLastInsertID
public integer getLastInsertID |
() |
Overrides parent implementation. Uses "SELECT @@Identity".
Output |
integer
| last insert id, null if none is found. |
Exception |
|
joinOrdering
protected string joinOrdering |
(array $orders ) |
Input |
array | $orders | ordering obtained from findOrdering() |
Output |
string
| concat the orderings |
Exception |
|
reverseDirection
protected array reverseDirection |
(array $orders ) |
Input |
array | $orders | original ordering |
Output |
array
| ordering with reversed direction. |
Exception |
|
rewriteLimitOffsetSql
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
Input |
string | $sql | sql query |
integer | $limit | > 0 |
integer | $offset | > 0 |
Output |
sql
| modified sql query applied with limit and offset. |
Exception |
|
|