- 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