I. Overview
1. Version
| Designer | Function Changes |
|---|---|
| 10.0 | - |
| 10.0.17 | The function of copying SQL statements is added to the query interface, as described in Section III. of this article |
2. Introduction
1) We can input SQL statement in DB Query to get data from the existing database.
2) Data sources in the Designer can be divided into two types according to their scope of application: Template dataset and server dataset. We use template dataset as an example here. The operation method of server datasets is similar to that of template datasets, and will not be discussed here.
II. Steps
1. Add a DB Query
Select the “Template dataset” tab and then click the [+] above it to add a DB Query.
2. DB Query interface - Name of DB Query
You can define the name of the DB Query at the top, e.g., the name is ds1 by default. Finishing the definition, you can see that a ds1 dataset is added in the template dataset list at the bottom left.
Note: To create a DB Query, there should be no “.” in the name of the dataset, or otherwise, after the field is dragged into a cell, there will be no data to be displayed in the preview mode and an error will be reported in the log.
3. DB Query interface - Database List
1)The left part of the DB Query window that pops up is a database list. You can click the drop-down box to switch different databases. All sheets or views in the database are displayed below.
2)You can click on the top right to edit the data connection, and click to refresh.
3)The sheets or views of the database at the bottom left can be checked or unchecked.
4)The blank space can be used for fuzzy search of sheets or views of the database that we need.
4. DB Query interface - Query Editor
1)SQL query statement can be entered in the block on the right, or dragged from the left database list.
Note1:FineReport DB query only supports the query of one natural SQL statement, but does not support multiple natural SQL statements, such as SELECT * FROM A SELECT * FROM B.
Note2:Create and drop tables are not supported, and the software only supports “select” operations.
2)Click at the top to preview the query results. For other settings above, please refer to dataset caching, single dataset pagination, SQL to implement hierarchical reports.
5. DB Query interface - Parameters
1)The parameter block in at the bottom right. When defining query statements, you only need to use the special string ‘${XXX}’ to enclose the parameter name XXX. After clicking the Refresh button at the bottom, you can assign a type and a default value for the parameter.
2)You can click the following to set the parameter type. There are 6 types of parameters.
String: texts or numbers can be input.
Integer: only integer numbers can be input.
Double-precision: numbers with decimal point can be input.
Date: after the Date is selected, click the drop-down box to select the date in a calendar style.
Boolean: the Boolean value is true if the True is checked, or false if the True is not checked.
Formula: a Formula Definition window pops up, where you can input the formula.
3)In the input box on the right, you can input the default display value of parameters. For example, after the text “East China” is input, you can click “Preview”.
III. Copy SQL statements
In previous versions, if the SQL contained parameters, it was not convenient to copy SQL statements directly from the query interface to other places.
In versions 10.0.17 and later, the "View execution SQL" function is added to facilitate users to paste other database software for troubleshooting and testing.
1. Example
Click "View execution SQL" button at the top of the query page. Because the SQL statement contains parameters, the parameter input interface is automatically displayed. As shown below:
The execution SQL statements are automatically copied to the clipboard, and you can paste them to other database software for troubleshooting and testing. As shown below:
2. Precautions
Some users enable the escape function to Anti-SQL Injection. If a parameter matches an escape character, the query result will be affected. In this case, the system displays the following message: Escape some parameters Prevent SQL injection, the query result may not match the expectation, please check and modify the parameters or contact the administrator
Touch the icon in the upper right corner to view escaped parameters. In this example, the entered parameter value "--" is the escaped parameter.
