Database Query

Version

 Functions

Database query refers to selecting the required fields through SQL statements from a defined database connection, namely the data source. With database query, you can manipulate the database tables in the data source directly.

There are three types of database query methods: table query, view query, and fuzzy query.

Table Query

Query Steps

1. Click Template > Template Dataset to go to the setting page. Click  in the upper left corner of Template Dataset setting page. Select DB Query and name the query. Select the required data connection.

2. Select the query editor and enter the SQL statement SELECT * FROM ORDERS where ORDERID=${ID}. This is a SQL query with a parameter. ${} is the parameter format, and the ID in ${} is the name of the parameter. Click Preview, and a prompt will pop up. Enter the parameter ID=10004. You can see the result in the preview group, which is actually executing the SQL query SELECT * FROM ORDERS where ORDERID=10004.

Note:

1. Standard SQL statements are applicable. You just need to put parameters in ${}.

2. If the parameter name is a combination of letters and numbers (such as A1), the system will consider it as a cell. If the default value is not set, it will trigger template calculation, which will cause one SQL execution. Therefore, it is recommended for you to avoid using a combination of letters and numbers as parameters. If you have to use this type of parameters, you need to set a default value for the parameter.

 Basic Properties of Dataset Interface

View Query

View query refers to the operations on the views defined in the data source.

Database Query

1. Click Template > Template Dataset to go to the setting page. Click  in the upper left corner of Template Dataset setting page. Select DB Query and name the query. Select the required data connection.

 Using View Query

Select data source and enter the SQL statement for view query. For example:

Enter the SQL statement SELECT * FROM [sys].[types] in the query editor. [sys].[types] is the name of a view query. Click Preview to see the reference to the view query.

Note: You can drag tables and views in data connection to query editor.

Fuzzy Query

There is a blank search box below the table, which is used for fuzzy queries. For example, if you want to search for tables related to client, enter client in the search box. Fuzzy matching will be performed automatically.

Note:

1. FineReport database query only supports one SQL statement such as SELECT * FROM A SELECT * FROM B. It does not support multiple SQL statements.

2. Statements like CREATE and DROP are not officially supported. FineReport only supports SELECT statements.

In previous versions, it was inconvenient to directly copy SQL statements from the query interface to other interfaces if the statement contains parameters.

FineReport 10.0.17 and later versions added View execution SQL function, which allows users to copy and paste SQL statements to other database software for troubleshooting and testing.

Examples

Click View execution SQL button at the top of the query interface. Because the SQL statement contains parameters, the parameter input interface will automatically pop up.

Executed SQL statements are automatically copied to the clipboard, and you can paste them to other database software for troubleshooting and testing.

Notes

1. If you call a custom function in SQL, View Executed SQL cannot run normally. The copied SQL statements cannot run properly in other database software.

2. If you have enabled the escape function for anti-SQL injection, when a parameter matches an escape character, the query result will be affected. In this case, the system will prompt: Escape some parameters Prevent SQL injection, the query result may not match the expectation, please check and modify the parameters or contact the administrator.

Hover your cursor over  in the upper right corner to view escaped parameters. In this example, the entered parameter value 1002 is the escaped parameter.

FineReport 11.0.16 and later versions allow adding comments in database log before SQL statements to help troubleshoot performance problems.

SQL Comments

The SQL query scenarios and corresponding comments are shown in the following table:

Note:

1. Template name, dataset name and task name in the comments are encrypted with base64. You can use decoding tools to decode and view them.

2. Does not support displaying SQL comments in stored procedures.

Database Log Query

MySQL

Query statements executed by MySQL server will be recorded in General Query Log. General Query Log is disabled by default.

You can use the following commands to check whether General Query Log is enabled. The variable general_log_file defines the location of log files.

mysql>SHOW VARIABLES LIKE '%general%';

 If General Query Log is disabled, you can enable it by modifying the configuration file my.cnf of MySQL.

general_log_file = /var/log/mysql/mysql.log    #general_log_file is the output directory of log.
general_log = 1    #Setting general_log to 1 means enabling log query.

 After modification, save the configuration file and restart MySQL.

Oracle

v$sql records your SQL execution information in shared pools. You can query detailed SQL information and the time of execution through SQL statements.

Note: If you cannot query the executed SQL statements, the executed SQL might have been replaced from the shared SQL area.

SELECT SQL_TEXT，FIRST_LOAD_TIME
FROM v$sql

SQL Server

sys.dm_exec_query_stats records query statements in SQL Server cache. You can query detailed SQL information and the time of execution through SQL statements.

Note: sys.dm_exec_query_stats only records query data that have been executed.

SELECT TOP 1000
--Creation time
QS.creation_time,
--Query statements
SUBSTRING(ST.text,(QS.statement_start_offset/2)+1,
((CASE QS.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1
) AS statement_text,
--Execution text
ST.text,
--Execution plans
QS.total_worker_time,
QS.last_worker_time,
QS.max_worker_time,
QS.min_worker_time
FROM
sys.dm_exec_query_stats QS
--Keys
CROSS APPLY
sys.dm_exec_sql_text(QS.sql_handle) ST
WHERE
QS.creation_time BETWEEN '2011-10-20 16:00:00' AND '2023-10-20 17:00:00'
AND ST.text LIKE '%%'
ORDER BY
QS.creation_time DESC