- Home
- About
- Testimonials
- Demos
- Download
- Documentation
- Blogs
- Forum
- Support

Packages:
default
System
System.Caching
System.Collections
System.Data
System.Data.ActiveRecord
System.Data.ActiveRecord.Relations
System.Data.ActiveRecord.Scaffold
System.Data.ActiveReecord.Scaffold.InputBuilder
System.Data.Commom.Sqlite
System.Data.Common
System.Data.Common.Mssql
System.Data.Common.Mysql
System.Data.Common.Oracle
System.Data.Common.Pgsql
System.Data.Common.Sqlite
System.Data.DataGateway
System.Data.SqlMap
System.Data.SqlMap.Configuration
System.Data.SqlMap.Statements
System.Exceptions
System.I18N
System.IO
System.Security
System.Util
System.Web
System.Web.Services
System.Web.UI
System.Web.UI.ActiveControls
System.Web.UI.WebControls
System.Web.UI.WebControls.assets
System.Xml
Classes:
TDbCommandBuilder
TDbMetaData
TDbTableColumn
TDbTableInfo
TMssqlCommandBuilder
TMysqlCommandBuilder
TOracleCommandBuilder
TOracleTableInfo
TPgsqlCommandBuilder
TSqliteCommandBuilder

Keyword

# Class TMssqlCommandBuilder

    TComponent
       |
       --TDbCommandBuilder
          |
          --TMssqlCommandBuilder

TMssqlCommandBuilder provides specifics methods to create limit/offset query commands for MSSQL servers.

Since: 3.1
Author: Wei Zhuo <weizho[at]gmail[dot]com>

Method Summary
string | applyLimitOffset ( string $sql, integer $limit, integer $offset) Overrides parent implementation. Alters the sql to apply $limit and $offset.
protected array | findOrdering ( string $sql) Base on simplified syntax http://msdn2.microsoft.com/en-us/library/aa259187(SQL.80).aspx
integer | getLastInsertID () Overrides parent implementation. Uses "SELECT @@Identity".
protected string | joinOrdering ( array $orders)
protected array | reverseDirection ( array $orders)
protected sql | rewriteLimitOffsetSql ( string $sql, integer $limit, integer $offset) 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