DataLayer.SQL

The DataLayer.SQL element gives developers the ability to define and run a query against a SQL-compliant datasource.

• About DataLayer.SQL
• Attributes
• Working with DataLayer.SQL
• Using Studio's DataLayer Wizard

About DataLayer.SQL

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.

Attributes

The DataLayer.SQL element has the following attributes:
 

Working with DataLayer.SQL

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.

Using Studio's DataLayer Wizard

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:

1. Select the ID of the Connection element from the drop-down list of available connections. Click Next to continue.
   
   
   
2. 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.
   
   
   
   
3. Click Finish to close the wizard.
   
   
   
   
   
4. The wizard has inserted the datalayer and configured its attributes, as shown above.