Birt-j script Debugging & Dynamic SQL implementation

A better Birt Problem resolution website: http://www.myexception.cn/h/1335919.html

Simple debugging of 1,birt JavaScript scripts

The JS script in Birt cannot be prompted directly with alert, nor can it be traced with a breakpoint (at least I didn't see it). A simple way to print, write in a script

importpackage (Packages.java.lang);
System.out.println ("test===");

You can print it out in the console and do a simple debugging.

It is important to note that the report must be deployed under Tomcat in order to print and cannot be printed with preview mode.

2,Eclipse Birt Create data set using dynamic SQL from: http://3ccoder.iteye.com/blog/545550Eclipse Birt can use SQL query to create a dataset, pass user input to the client by setting parameters (Parameter) in the report, and can set the data set parameter and associate with the report parameter (these two parameters are different). This allows the user input of the client to be passed to the parameters of the data set for use by the WHERE statement of SQL query, which is the usual practice of creating SQL statements, but the method is only suitable for fixed SQL statements, which does not apply to dynamic SQL.
Such a SQL query
SQL code

1. Select user.first_name firstName,user.second_name secondname,user.sex sex,user.age age, User.birth_day birthDay
2. From user_table user
3. where user.second_name =?
4. and user.sex =?

Define two report parameters Secondname,sex define two data set parameters Secondname,sex and associated with the report parameter, the user must enter the last name and gender as the query condition when viewing the report, if not lose the corresponding parameter value is null, if the person surnamed Li, The gender does not lose, then the SQL statement becomes so select ... where user.second_name = ' lee ' and user.sex = null obviously such SQL is not the result, which has sex is null person? For this situation should be if the user a parameter does not lose it as a query condition, the above SQL should become a SELECT ... where user.second_name = ' Lee ' is reasonable,There are two ways of doing this:
The date set editor has property binging, you can set the input SQL statement in the query text box on the right, or use the Expression Builder to help generate SQL statements
JavaScript code

2. var query = "Select User.first_name firstname,user.second_name secondname,user.sex sex,user.age age,user.birth_day  BirthDay ";
4. Query + = "from user_table user";
6. Query + = "where User.second_name =?"
8. if (param["Sex"].value! = null) {
10. Query + = "and User.sex = '" + param["sex"].value + "'";
12. }
14. this.text = query;

I tried, this method did not succeed, maybe I write wrong.

Second, using script, in the data set of the Before Open event
JavaScript code

2. var query = "Select User.first_name firstname,user.second_name secondname,user.sex sex,user.age age,user.birth_day  BirthDay ";
4. Query + = "from user_table user";
6. Query + = "where User.second_name =?"
8. var sex = reportcontext.getparametervalue ("sex");
10. if (sex! = null) {
12. Query + = "and User.sex = '" + Sex + "'";
14. }
16. This.querytext = query;

I tried it, and it worked.Note: It is still necessary to write the SQL statements in the query of the dataset, usually we write the select XXX from YYY, which is written in the script after the where statement, especially if the conditional judgment is required.
like I wrote: A little bit of footage

// Agency number this. querytext+= "where selfcur.biz_main_trans.bank_no= '" + params["self-service Bank"] + "'"; // The terminal number or the teller number two select one if NULL  this. QueryText + = "and selfcur.biz_main_trans.term_id=" + params["terminal ID"] + "'";} Else {this. QueryText + = "and selfcur.biz_main_trans.tell_no=" + params["Teller id"] + "'";}

The difference between the two methods is that the first method is to create the data set when the report is run, so there is no way to preview it as usual in the data set editor and the second method is written in the beforeopen of data set, so open the data Data set is created before set so you can preview the result of the dataset as usual, and so on
The first method uses param["Sex"].value to get the sex parameter value, while the second method needs to use Reportcontext to get the value of the sex parameterafter my trial of the current version of the Birt, in the second, get the report parameter values using params["Terminal id" or params["terminal id"].value .


It is also important to be careful when piecing together SQL, for example, if the argument is a string type and must be preceded by a pair of ' ' (depending on the database used, I am using Oracle)