{"id":3417,"date":"2016-05-21T03:00:33","date_gmt":"2016-05-20T19:00:33","guid":{"rendered":"http:\/\/www.finereport.com\/en\/?p=3417"},"modified":"2018-09-11T19:31:28","modified_gmt":"2018-09-11T11:31:28","slug":"finereport-database-query","status":"publish","type":"post","link":"https:\/\/frg.fineres.com\/en\/2016\/05\/21\/finereport-database-query\/","title":{"rendered":"FineReport Database Query"},"content":{"rendered":"

We will introduce database query function of FineReport reporting tool<\/a> in this article.<\/p>\n

1. Overview<\/h3>\n

SQL query means to select required data field from the defined database connection (i.e., data source) with SQL statements. In general, you can directly operate the database tables in the data source. In designer, data source is divided into Server Data Set and Report Data Set, which are different in reference scope. Descriptions will be given below taking Server Data Set as an example.<\/p>\n

2. Database query<\/h3>\n

Select Server Data Set under Server. Click\"\"button at the left top of Server Data Set Window. Select Database Query. Name the database query. Then, we can add SQL query.<\/p>\n

\"\"<\/p>\n

Use sql. After selecting data source, we can operate the database table with SQL. Select the Query Editor and input SQL statement select * from Employees where EmployeeID = ${ID}<\/span>.This is a SQL query with parameter, i.e., the ID between \u201c${\u201cand\u201d}\u201d is a parameter. During preview, the result of input parameter ID=4 can be viewed in the Preview Group. Actually, a SQL query of select * from Employees where EmployeeID=4 is executed.<\/p>\n

\"\"<\/p>\n

Note: FineReport adopts all common SQLs. However, when setting parameters, please refer to the forms listed above.<\/span><\/p>\n

Some basic attributes of the entire data set interface will be described below, which will not be repeated in later chapters.<\/p>\n\n\n\n\n
Attribute<\/td>\nType<\/td>\nWizard<\/td>\nQuery Editor<\/td>\nParameter<\/td>\nMaximum previews<\/td>\n<\/tr>\n
Description<\/td>\nFor query type, FineReport supports common SQL query and stored procedure. In addition, query statements of those two types can contain parameters. Note: With JDBC, both SQL query and stored procedure are finally parsed and executed through database software, and the results will be returned to FineReport for display. Therefore, query speed and performance are completely determined by corresponding database software<\/td>\nFineReport provides simple wizard to help user to create simple SQL query and stored procedure Note: Given complex SQL languages, FineReport wizard can only define common and simple SQL queries (not supporting grouping, sub-query, etc); stored procedure wizard basically can meet requirements. It can list all stored procedures of connected database and automatically obtain the parameters of stored procedure based on the name of selected stored procedure, to generate executable stored procedure statements.<\/td>\nWhen the query generated by wizard cannot fully meet requirements, edit any complicated query statements via Text Editor (in general, the stored procedure generated by wizard requires no secondary editing)<\/td>\nFineReport gives perfect support of parameter query. In defining query statements, you only need to use special character string “${XXX}”(when parameter value is numeric type, the character string is ${parameter}; otherwise, the character string is ‘${XXX}’). Enclose the front and rear portions of the parameter name. You can assign a default value for the parameter by clicking the Refresh button at bottom so that when the report engine executes this SQL statement, the C\/S system will pop up a dialog, which requires user to input the value of corresponding parameter. In addition, the default value will be displayed automatically. In B\/S, it will traverse all parameter values transported from the Client (in general, browser); then, FineReport will replace the corresponding character string \u201c${XXX}\u201d in query with such parameter values and submit the replaced query statements to the database for parsing and execution. For example: 1.the user inputs the query statement: \u201cselect * from Customers where ID = ${ID}\u201d; 2. The dynamic ID value obtained from Client side is: 1; 3. Generate a new query statement based on parameter values: \u201cselect * from Customers where ID = 1\u201d. With JDBC, send this new query statement to corresponding database for parsing and execution.<\/td>\nHere, Max Preview Rows (default value: 200) is set only to fasten preview speed, which enables only when the (Preview) button is clicked and will not act on the report template file referring to this query in error.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

After the Report Data Set Dialog is opened, the remaining operation methods for report database are basically same as those of server data set. The difference is that: in server data set, the configuration information is saved in the datasource.xml file of current server. Any report designed on current server can use this data set. However, in report data set, the information is saved in current CPT file, which only applies to the currently opened report.<\/p>\n

3. View query<\/h3>\n

View query is to operate the defined views in data source.<\/p>\n

3.1 Database query<\/strong><\/p>\n

Select Server Data Set under Server. Click\"\"button at the left top of Server Data Set Window. Select Database Query. Name the database query. Then, we can add View Query.<\/p>\n

\"\"<\/p>\n

3.2 Use view query<\/strong><\/p>\n

After selecting data source, we can operate the View Query with SQL. FRDemo-View database is taken as an example:<\/p>\n

In the Query Editor, write sql, select * from Ship Order<\/span> (name of a view query), Then, click Preview and you can see that view query is successfully referenced.<\/p>\n

\"\"<\/p>\n

Note: Here is the tip. We can directly drag the table and view in the data connection to the Query Editor.<\/span><\/p>\n

4. Fuzzy query<\/h3>\n

A blank search box is set under the table for fuzzy query. If you want to search for reports related to customer, fill in Customer in the search box, and the system will automatically search for fuzzy matching:<\/p>\n

\"\"<\/p>\n","protected":false},"excerpt":{"rendered":"

We will introduce database query function of FineReport…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[111],"tags":[117],"_links":{"self":[{"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/posts\/3417"}],"collection":[{"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/comments?post=3417"}],"version-history":[{"count":5,"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/posts\/3417\/revisions"}],"predecessor-version":[{"id":3422,"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/posts\/3417\/revisions\/3422"}],"wp:attachment":[{"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/media?parent=3417"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/categories?post=3417"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/tags?post=3417"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}