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:
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
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

  1. SELECT * FROM (
  2. SELECT TOP n * FROM (
  3. SELECT TOP z columns -- (z=n+skip)
  4. FROM tablename
  5. ORDER BY key ASC
  6. ) AS FOO ORDER BY key DESC -- ('FOO' may be anything)
  7. ) 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$sqlSQL query string.
integer$limitmaximum number of rows, -1 to ignore limit.
integer$offsetrow 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$ordersordering obtained from findOrdering()
Output
string concat the orderings
Exception

reverseDirection

protected array reverseDirection (array $orders )

Input
array$ordersoriginal 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$sqlsql query
integer$limit> 0
integer$offset> 0
Output
sql modified sql query applied with limit and offset.
Exception