Forms and Reports

DbSchema Forms and Reports can be used for:

The Designer

The easiest way to try the forms and reports are using the DbSchema sample projects. To open the sample project close any project in DbSchema and in the welcome panel you will have an option to open the sample projects. Alternative you can start a new form or report from the menu, which will open the form wizard. Here you can choose to use data from a table.
Forms Wizard
An simple example generated report is below.
Sample Form Output

The same designer is used for both, forms and reports. The designer consist of panels or tables and cells and . Panels and tables are similar in the designer: they contain cells and they have a title. A cell may contain any component: label, text field, buttons, etc. or another panel or table. This gives the flexibility of creating panels or tables inside other panels or tables. Like this is easy to design reports with multiple master-detail sections.
Sample Form

Components can be created by clicking an empty cell.
Create Component

Existing components can be sized to cover multiple cells by dragging from the bottom-right grip. To move a component to a different cell drag the component text.
Move Component

Clicking an component the component editor will show on the right. Here we edit a label. The label text may contain variables in form of ${...} which will be replaced at runtime with the variable data.
Edit Component

By clicking a table or panel title, you can edit the data source. Here the data source is a SELECT query. For each result column a variable with the same name will be created. The panel will execute the data source query and for each data record will output its body record in the output. Labels can be placed in the report to show the variables data.
Edit Component

Form Scripts

The most important form script is the data source. This can be a simple SQL query: 

SELECT city_id, city, country_id, inhabitants, last_update from city
The data source will create variables like ${city_id}, ${country_id}, ${country_id}, ${inhabitants}, ${last_update} which will be available inside the current pane or table and inside all children panels and tables. We can create then a label with text: 
The city is ${inhabitants<1000?'small':'large'}. It has ${inhabitants} people.
The label text is evaluated, the ${...} will be evaluated as Java expression and generate for example: 
The city is small. It has 800 people.

Beside data source scripts, the panels, tables and the form itself can have:

Master-Detail Report

Panels or tables can be embedded one in another. Look the master-detail below. The detail pane data source is using country_id=${country_id}, where the variable country_id is provided by the master pane.
Master-Detail Form

Input Variables

Beside labels, forms may contain also text fields, buttons, radio buttons, charts, etc. Buttons can open other form pages and send data as variables. In the target form the variables are received as input variables.
Input Variable
Input variables can be edited. A data type can be set and a error message for the case the variable should be always present and is missing.
Edit Input Variable
In the next image there is a form with two labels, text fields and a button. The button has set as action on the right to open the index page when clicked. This will send automatic the variables email and password.
Form button

Short Introduction in Groovy

Groovy is pure Java plus closures. It is known for Gradle (alternative to Maven). All Java simple operations are supported: 

int id = 20;
id++
def array = ["Groovy", "is", "the", "Best"]
def multilineString = """This is a
multiline String"""

// GStrings replace variables inside. This will output 'Age=21'
println "Age=${age}"

// Iterate an result set
sql.eachRow("SELECT f.film_id, f.title FROM sakila.film"){ r ->
   println r.film_id
}

Groovy Scripts

The forms and reports are making use of scripts to implement the afferent logic. Two languages are supported: JavaScript and Groovy. For both we set a variable 'sql' as the connection to the database. Scripts will also receive all variables send by the previous form ( if any ). The output of print commands can be read in Help / Output Logs.

Sample Groovy script which modifies data in a table 


if ( 'edit'.equals( operation )){
  println "Going to update ${actor_id} ${first_name}"
  sql.executeUpdate( "UPDATE SAKILA.actor SET first_name=?, last_name=? WHERE actor_id=?",
    [first_name, last_name, actor_id] )
  sql.commit()
    }

Same example in JavaScript 


  print("Operation=" + operation);
  if ( "edit"== operation ){
    print("Updating actor_id=" + actor_id + " first_name=" + first_name + " lastname=" + last_name );
    st = sql.prepareStatement("UPDATE SAKILA.actor SET first_name=?, last_name=? WHERE actor_id=?" );
    st.setString(1,first_name);
    st.setString(2,last_name);
    st.setInt(3,actor_id);
    st.executeUpdate();
    sql.commit();
  }

Groovy scripts can be used in Data Sources as well


sql.eachRow("""
  SELECT f.film_id, f.title, f.description
  FROM sakila.film f limit 5
"""){r->
  film_id= r.film_id
  title= r.title
  description= r.description
  addRecord()
}

Deploy Tomcat WAR

The forms applications can be deployed as Tomcat war applications. Like this you can export the created application under any tomcat instance. For this you will need: