DataLayer.ActiveSQL Details

Element Details: DataLayer.ActiveSQL

Available in: Logi Info

Source code name: DataLayer.ActiveSQL

Introduced in: v11.0.43

DataLayer.ActiveSQL causes its parent element, such as an AnalysisGrid, to generate and modify SQL queries as the user requests different views of the data. More data processing is done by the database server, improving performance with large data sets.

ActiveSQL differs from conventional DataLayers in that it does not initially retrieve all the rows from the database in the initial request. This can help the parent element with much larger data sets while still providing good performance. Interactive parent elements like the AnalysisGrid manage the final data presentation, so user queries should not include the 'Order By' clause.

The SQL generated is modified by child elements that start with "Sql". For example, the SqlConditionFilter element adds a SQL WHERE clause to the generated SQL. SqlGroup adds a GROUP BY clause.

ActiveSQL understands standard dialects of SQL and automatically adjusts for different database servers. ActiveSQL does not support every type of SQL.

DataTables can also take advantage of ActiveSQL. ActiveSQL generates SQL queries that limit the number of rows returned, helping with paging and sorting. With InteractivePaging, the SQL requests just a number of pages of records instead of the full query, helping with performance. As the user moves beyond the first set pages, the database is queried again to get the next set of records.

While the MaxRows attribute is available with DataLayer.ActiveSQL, it normally should not be used because ActiveSQL does its own buffering to ensure reasonably-sized resultsets from the database server.

Element Group:Relational Database DataLayers

ATTRIBUTES

Click attribute Name to drill down for more information.

Name

Possible Values

Description

ActiveSqlBufferSize

UserDefined

An integer representing the maximum number of rows buffer when reading from the data source. The buffer is used when paging tables so that the data source need not be queried for every new page of rows. Defaults to 200.

ConnectionID

UserDefined

Specifies a connection to a data source that is defined in the Settings. For elements connecting to relational databases, default is the first Connection element in _settings.lgx.

HandleQuotesInTokens

True

False

Handle Quotes in Tokens makes it possible to work with tokens, such as @Request tokens, that contain single quotes in their values. When set to True, any tokens in the Source attribute will have single quotes "doubled" so that they work within the SQL statement. For example, imagine a SQL statement in a Source attribute like "SELECT * FROM Customers WHERE CompanyName LIKE '@Request.Name~%' ". Then, if the Name we are searching for is "Trail's Head", the SQL executed becomes "SELECT * FROM Customers WHERE CompanyName LIKE 'Trail''s Head%' ". The default for Handle Quotes in Tokens is False.

UserDefined

The ID attribute is a pervasive attribute that uniquely identifies an element within a definition file. The ID needs to be a unique value within the definition.

MaxRows

UserDefined

The maximum number of rows to retrieve from the data source.

Source

UserDefined

(Required) A SQL statement that returns a set of records. Use tokens, such as @Request and @Session, inside of the SQL command to control the result set.

PARENT ELEMENTS

Click element to drill down for more information.

CHILD ELEMENTS

Click element to drill down for more information.

Analysis Filter Insert
File Column
Formatted Column
Generated Sql Plugin Call

Sql Aggregate Column
Sql Calculated Column
Sql Condition Filter
Sql Group

SQL Parameters
Sql Sort
Sql Time Period Column