The DataLayer.SQL element gives developers the ability to define and run a query against a SQL-compliant datasource.
The DataLayer.SQL element can be used as a child of data tables, charts, and many other elements.
The text of a SQL query can be entered directly into the DataLayer.SQL element's Source attribute, or generated using the special SQL Query Builder tool, which can be accessed by clicking the browse button at the end of the Source attribute. This tool allows developers to create a correctly-formatted SQL query using a graphical interface. You can also test your query in the tool, and view the resulting data in it.
All valid SQL queries can be executed using this element. The number of rows returned can be controlled using the Maximum Rows attribute.
Process Tasks can make use of a similar element, Procedure.SQL, to retrieve data and write it back to the database. For more information, see Work Process Definitions.
DataLayer.SPallows you to use SQL Stored Procedures, which provides better protection against SQL injection attacks and better performance.
Developers using v11.2.040+ and interested in issuing queries to three-dimensional datasources, such as cubes, for use with two-dimensional visualizations, can make use of Studio's MDX Query Builder tool to formulate valid queries. For more information, see The MDX Query Builder section of Use Studio 11.
The DataLayer.SQL element has the following attributes:
(Required through v10.1.46) A unique element ID.
(Required) The SQL statement to be executed. Typically this is a SQL query, e.g. SELECT * FROM MyTable. Multi-line SQL statements, including DECLARE, can be entered into the Attribute Zoom window for this attribute.
Tokens can be used here so, for example, you could create a constant (in the _Settings definition) to hold your query text, then use a token like @Constant.myQuery~ in this attribute. That would allow you to maintain the query in a single place, while using it in multiple datalayers.
EXECUTE statements can be entered here as well, though use of the DataLayer.SP element is recommended instead for stored procedures.
When a connection to a non-OLAP datasource is being used, the Browse button for this attribute can be used to launch the SQL Query Builder wizard. If an OLAP connection is being used, the Browse button can be used to launch the MDX Query Builder. For more information, see Use Studio 11.
|Connection ID||The ID of a datasource connection defined in the _Settings definition. If this value is left blank, the datalayer will use the first connection in the _Settings definition. For clarity, developers are advised to enter an ID here in all cases.|
When True, ensures SQL syntax validity by doubling any single-quotes that may be included in tokens used to form part of the SQL statement in the Source attribute.
For example, imagine the SQL statement "SELECT * FROM Customers WHERE CompanyName LIKE '%@Request.Name~%' ". If the value of the Request token is "Trail's Head", the embedded single-quote could be problematic. With the HandlesQuotesInTokens attribute set to True, the SQL statement sent to the database server will become "SELECT * FROM Customers WHERE CompanyName LIKE '%Trail''s Head%' ".
|Maximum Rows||The maximum number of results rows to retrieve. Default: no limit.|
The datalayer receives and caches the results returned by the SQL statement. You can add child elements beneath the datalayer to affect the results, including:
- Filtering: Sort, group, or restrict the result data
- Joining: Apply SQL JOINs to the data in the datalayer
- Extending: Add virtual columns to the datalayer that contain aggregated, calculated, or totaled result values
- Securing: Limit access to the data using Logi security
- Linking: Make the results reusable elsewhere in your report defintions
The use of many of these elements is described in separate DevNet documents.
Data retrieved into the datalayer is cached in XML format, in memory (v8 and earlier), or in memory and/or on the web server's file system (v9+). The latter is discussed in The Logi Server Engine and may be of interest to developers working with extremely large datasets or large numbers of concurrent users.
The data retrieved with a datalayer is available using @Data tokens, in the format @Data.ColumnName~. The spelling of the column name is case-sensitive. The data is only available within the scope of the parent element of the datalayer, not throughout the entire report definition. The DataLayer.Linked element can be used to make the data reusable in another datalayer outside this scope.
In Logi Info, the Auto Columns element can be used to quickly display in your report all the data in a datalayer.
The data retrieved into the datalayer can be viewed by turning on the Debugging Link in your _Settings definition (General element) and using the resulting link at the bottom of your report page to view the Application Trace page. A link on the Trace page will display the retrieved data.
Using Datalayers to Retrieve SQL Data
DataLayer.SQL runs a SQL query to retrieve data from a SQL datasource. The element's Source attribute value is the actual SQL statement, and any valid SQL statement (or statements) will be run.
The example above shows a simple SQL query that returns data to a simple data table.
However, as shown in the example above, the Source attribute value (opened in the Attribute Zoom Window) can consist of multiple SQL statements. They will all be executed, as long as the syntax is correct, and any results will be returned to the datalayer. The Attribute Zoom window is opened for any attribute by double-clicking the attribute name.
All valid SQL statements can be executed. The Handle Quotes Inside Tokens attribute handles tokens that might have embedded single quotes, such as the text Trail's Head. When set to True, token values in the SQL Command will be wrapped in single-quotes, "doubling" them so the syntax will be valid. The default is False.
Tokens for string values should be wrapped in quotes to conform to your database SQL syntax. For example, if you'd use a query with a WHERE clause including quotes, like this:
...WHERE user_lastname = 'Smith'
Then with tokens it would be:
...WHERE user_lastname = '@Request.UserName~'
The If Data Error element can be used beneath DataLayer.SQL to handle errors that may occur, by switching to an alternate datalayer.
While the multi-statement capability makes it easy to use them, your best performance for complex SQL queries and best security will be found by using stored procedures and the DataLayer.SP element.
Using SQL Parameters
The SQL Parameters and SQL Parameter elements can be used to include tokenized parameters, if you prefer not to embed tokens directly into the SQL statement. This approach also allows you to enforce a data type for the parameter value and also offers protection against SQL Injection attacks.
To use parameters, you write your SQL statement using "placeholder" notation. The exact syntax depends on your database and the Connection element you're using. For example, if you're using Connection.SQLServer, you use this notation:
SELECT * FROM Customers WHERE State=@state AND City=@city
and the ID attribute of each SQL Parameter element must match a placeholder name. So, in the example, the value of an element with an ID of state will replace the @state placeholder at runtime.
Similarly, if you're using Connection.Oracle, you use this notation:
SELECT * FROM Customers WHERE State=:state AND City=:city
and, as before, the ID attribute of each SQL Parameter element must match a placeholder name.
If you're using Connection.OLEDB, you use this notation:
SELECT * FROM Customers WHERE State=? AND City=?
Unlike the previous examples, these are positional parameters so, at runtime, placeholders in the statement will be replaced, starting with the first placeholder, by the values specified in the SQL Parameter elements, based on the elements' top-to-bottom order in the definition. The element IDs are ignored.
The example above illustrates the relationship, in this scenario, between SQL Parameter element order and placeholders in the statement.
SQL Parameters will not work when using Connection.ODBC.
Consult your database documentation for specific placeholder information.
Studio includes a wizard that can assist you in configuring DataLayer.SQL. The wizard assumes that you have already added an appropriate database Connection element in the _Settings definition and configured it.
As shown above, the wizard can be started by selecting and then right-clicking the parent element under which you want to add the datalayer, and using the context menus to select "Add a SQL DataLayer." The wizard will open; use it as follows:
- Select the ID of the Connection element from the drop-down list of available connections. Click Next to continue.
- Enter the SQL query that will be used to retrieve data, either by typing it in directly or by building it using the Query Builder tool (see Use Logi 10 Studio). If you attempt to use the Query Builder and receive an error, type in a simple query instead and proceed with the wizard; you can adjust the query later. Click Next to continue.
- Click Finish to close the wizard.
- The wizard has inserted the datalayer and configured its attributes, as shown above.