Forms and Reports

DbSchema Forms and Reports are a module inside DbSchema. Using them you can :
  • Create small applications or reports for web, JavaFx or PDF.
  • Deploy applications as WAR Tomcat applications
  • Make use of bootstrap responsive HTML templates
Learn forms and reports using the tutorial available on DbSchema website.

The Form & Reports Designer

The sample images used in this chapter are from the DbSchema sample project. 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. The best would be to follow the tutorial present in DbSchema installation folder or on www.dbschema.com .

A form or report looks in DbSchema like below. Both forms and reports are designed in the same way, using the same interface. The difference between them is the look, the interactivity and the template you choose.
Sample Form

Forms and reports are hierarchical in the way that one panel can be created inside another. This gives the flexibility of creating reports with an unlimited number of master-details-details sections.

Look the image below. A standard form or report will contain first two panels : one navbar panel and the page panel. The navbar is for the menus. Here is empty and won't show in the output. The page panel contains another panel: the data panel, which will output the table data. The data panel has one header row which will show only one time as header of the table, and a body row which will repeat for each record coming from the database.
Sample Form

The data panel has a data source which can be a SQL query or a Groovy script. The panel will execute the data source query and for each data record will output its body record in the output.
Panel has data source

How to Edit Components

Create new form from the application menu. The form will be created in the active layout. A wizard will show up. Choose to skip editing data source and a form as below will be created. Inside each form there are cells. Click one cell and from the menu choose a component to place inside.
Form Menu
  • Created components in the panels can be edited by double-clicking them.
  • A cell can be resized to cover other cells by drag and drop from the right side bottom grip.
  • Move the component to other cell by drag & drop from the component itself.

Edit component

The size of the components ( width and height ) is mostly flexible, the HTML or Swing sizes them as needed. There are few sizable components, like charts. You can set a custom size by double-clicking them and editing in the dialog which will show up.

Implement the Form Logic

As already described a form is build as a hierarchy of panels, each panel has cells which can be empty or may contain components. The form or reports logic can be set by editing the form or panels properties. Open the properties dialog by clicking one of the titles.
Form and panel properties
Will open the form properties dialog :
Form Properties Dialog
...or the panel properties dialog :
Panel Properties Dialog
Both, forms and panels have similar properties :

Panel or Form Id

The id is a name used to identify the form or panel. This id could be sometimes used for hardcoded variables, like <id>PageNumber or <id>RecordsPerPage, which are used by panels with data sources displaying data as tables to limit the number of records and show a certain page.

Initialization Script

can be a Groovy script, executed when the form is open. It can be used to implement some initial logic for the form, authentication, etc.

Data Source Script

can be a SQL or Groovy script used to retrieve data from the database to display in the form or report. # For each returned row the panel body row will be displayed in the output.

The setting Always show one record in output is related to the data source and is implementing a kind of tabular / columnar for the panel. If set, the panel will always show an record in the output, even if the data source returned no data record. If multiple records are returned, only the first one will be displayed.

After Each Row Script

can be a Groovy script used to compute totals, average, etc. over the data delivered by the data source. In each panel you may add a row ( right -click a cell to get this option ), and in the dialog choose where the row should be added ( up or down to this cell ) and the checkbox footer to set it as footer row.
Add footer row
In the footer row you may place components which are using the variables created in the after each row script.

Input Variables

Input variables are used by the form. Here declare variables expected when the form or report is open.

Input variable

For them you may :

  • Set an exception to be thrown if the variable is missing in the input
  • Set a default value if no value is present in the input.

The HTML Template

is the representation of the form in the HTML output. The template applies only for HTML execution mode. A label, for example, can be in output a plain text, a heading ( <h1> ), etc. A panel can show as bootstrap grid, as table, etc. Read more about templates in the HTML Templates chapter.

Variables Workflow

The user may open a different form from a current form using buttons.

Forms may send data between them as variables. Variables can be generated by input fields ( like text fields, radio, combo, lists ) or by buttons. The form below is making use of input fields and a button.
Sample form

Double-click the button to open the button properties dialog, where you can set variables to set when the button is pressed. The variables generated by the input fields does not need to be listed here, they are sent by default to the target form.
Variable declared in the button
Putting all together the form logic will work like :

  1. User press a button in a form
    Most components excepting labels will send data to the target form, as variables with the component id.
  2. Input variables are checked
    The input variables declared in the target form are checked. If some variable is mandatory an error message is returned.
  3. Target form initialization script is called
    The script can execute some logic, implement authentication, etc.
  4. Data source scripts ( if any ) are executed.
    The data source scripts will extract data from the database and make it available in the graphical interface.

Form 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 JavaScript which modifies data in a table:

  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();
  }

Same script in Groovy

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()
}

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()
}

Groovy Strings

Labels in interface may contain a mixture of plain text and variables. for example
Name is ${name}
will evaluate the ${name} with the variable called 'name'.

Debugging Scripts

The easiest way to debug the forms Groovy Scripts is to place println lines in the script code. The output will be visible in the output logs accessible from the main help menu.

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:
  • Download and install Tomcat. The installation file is usually a zip which you have to unpack. Go in the 'bin' folder and execute 'startup.bat'. This will start the tomcat server. This may request to set the JAVA_HOME variable pointing to your current Java installation folder. Do this by pressing the 'Win' key ( with the Windows logo ) + Break, then 'Change settings', 'System properties' and 'Environment Variables'. After setting JAVA_HOME you will have to close and reopen the command prompt and execute again 'startup.bat'.
  • Edit the tomcat-users.xml file from the tomcat conf folder.
    <role rolename="tomcat"/>
    <user username="tomcat" password="tomcat" roles="manager-gui"/>
        
  • Restart tomcat server.
  • Login on http://localhost:8080 and folow the link 'Manage App'. There you can upload the generated war file.
  • Browse to http://localhost:8080/war_file_name