FrontPage » Tutorials » Inforama Studio Tutorial

3 - Datasources and Datasets

Inforama tutorial

You can download the Inforama project files for this example by clicking the attachments link at the bottom of this page.

Importing the MySQL JDBC driver

In this tutorial we are working with the Sakila database which runs on MySQL. In order to connect to this database we need to use a JDBC driver which is compatible with MySQL. You can download a MySQL JDBC driver from the MySQL site at http://dev.mysql.com/downloads/connector/j/5.1.html.

Once downloaded, you can reference the jar file by clicking the Options – Classpath editor... menu item and the Classpath dialog will appear as shown in figure 1.

Figure 1: The classpath dialog

Click the Add a JAR button and you will be asked to locate the jar file. Select the jar file and the path to it will appear in the list. Click the Save classpath button to save your changes.

Creating a MySQL DataSource

Now that the JDBC driver has been referenced it's possible to connect to the Sakila database. To do this click the Data – Connections/Datasources... menu item and the wizard shown on the left of figure 2 will be displayed. Click the New button and the screen shown on the right of figure 2 will appear.

Figure 2: Wizard to add JDBC connection

Select JDBC Connection from the list and click the Next button. The next screen is where the database connection is established.

Figure 3: Configuring the JDBC connection

Field	Value	Description
Name	sakilaDB	This is the name of the connection being created and will be use throughout Inforama as a reference
Database	MySQL	This is a dropdown list for convenience. Select the MySQL driver and the following fields will be populated with template configurations
Driver	com.mysql.jdbc.Driver	This is the name of the JDBC class file which is used to register the MySQL JDBC driver
URL	jdbc:mysql://[server]/sakila	This is the URL to the database. Replace the servername with localhost if the database is running on the local machine
User	[MySQL User]	This is a valid MySQL user
Password	[MySQL Password]	This is the password for the MySQL user

When the connection information has been entered click the Test connection button and you wil see the message Connection succeeded if everything has been configured correctly. Click the Save button to save this connection.

Creating a MySQL Dataset

Now that the dataconnection has been created it's possible to define a query which will retrieve data from the database. To do this click the Data – Datasets... menu item. The Datasets dialog will appear as in figure 4.

Figure 4: Datasets dialog

In this case there is only one Datasource configured for the project – sakilaDB. It's possible to have more so make sure that the correct datasource is selected. Enter a name for the new Dataset – in this case were calling it customerDS. Enter the query into the main query area. The query being used is...

SELECT customer_id, first_name, last_name, email, cust_addr.address, cust_addr.address2, cust_addr.district, cust_addr.postal_code, cust_addr.phone, city.city, country.country FROM customer, address as cust_addr, city, country WHERE customer.address_id = cust_addr.address_id AND city.city_id = cust_addr.city_id AND country.country_id = city.country_id

Click the Execute button and a list of the fields returned by the query will appear on the list on the right of the dialog. Click the Save button to save the dataset and the Close button to dismiss the dialog.

This new dataset can now be accessed from the Data frame in the editor. To see this expand the Data frame as shown in figure 5.

Figure 5: Previewing a dataset in the data frame

Item	Description
1	Launch the Dataset editor
2	Display the fields from the dataset selected in 4 below
3	Prefix field names with their corresponding table names
4	Menu to allow the dataset to be switched
5	List of field names retrieved from the dataset
6	Preview of data in the dataset
7	Show parameters dialog to preview a specific row in the dataset where parameters are used
8	Insert the selected field into the current document with formatting applied
9	Insert a text box acro field into the current document

Click the down arrow icon identified by the number 2 to preview the fields in the dataset. Click the table icon identified by the number 3 to prefix the field names with their corresponding table names. This is useful where fields from different tables have the same name. Click the button identified by the number 7 to launch the parameter dialog and preview the data in the dataset.

We now want to start merging the data from this Dataset into documents within our project. Re-open the WelcomeLetter document template which was created in the previous step in the tutorial and where it said 'Dear Subscriber', delete the word 'Subscriber', place the cursor after the word 'Dear' and double-click the field first_name in the Data frame. The field name will be inserted into the document as shown in figure 6 below.

Figure 6: Field placeholder within the document

Now preview the document as described in the previous section of this tutorial and the generated document will appear as shown in figure 7 below.

Figure 7: Previewing the document with merged data field

Specifying Parameters for Datasets

So far we have merged data with the document in such a way that the first row from the dataset is used. Naturally we want to control what row is used by applying a filter to the dataset query. This is achieved by using a parameter.

Create a new parameter as described in the previous section called customer_id. Once again open the Dataset dialog shown in figure 4 and select the Dataset customerDS. Change the query to add this clause to the end...

AND country.country_id = city.country_id and customer.customer_id = '$P{customer_id}'

As a convenience you can automatically insert the parameter by clicking the Add a parameter button. The dialog shown in figure 8 below will be displayed. Select the parameter you wish to insert and click the OK button.

Figure 8: Adding a parameter automatically

Now when we go to preview the document again we can supply the new customer_id parameter in order to filter on the row we require.

Figure 9: Previewing filtered data

Creating a Spreadsheet Datasource

In many instances, customer and other data is stored in spreadsheets so it can be very convenient to work directly with these spreadsheets. Since Inforama version 1.2 data from spreadsheets can be used in much the same way as database data.

In order to demonstrate this feature of Inforama we will export the data from the staff table to a spreadsheet. Run the following query to generate a CSV file from the staff table...

SELECT "staff_id", "first_name", "last_name", "email", "username" FROM staff

Union SELECT staff_id, first_name, last_name, email, username FROM staff

INTO OUTFILE 'c:/temp/staff.csv'

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

LINES TERMINATED BY '\n'

Note the path to the csv file as specified by the line beginning INTO OUTFILE. Open the generated csv file in Excel or Calc and save as a .xls file. A preview of the spreadsheet can be seen in figure 10 below.

Figure 10: The exported staff spreadsheet

Now we need to create a Datasource from the spreadsheet as before. Open the Datasource dialog shown in figure 2 by clicking the Data – Connections/Datasources... menu item. On the next page in the wizard select Spreadsheet from the list, click next and the screen shown in figure 11 will appear.

Figure 11: Creating the spreadsheet datasource

Again, give the datasource a name – in this case we have called it staffSheet. Locate the spreadsheet file on the filesystem and click the Save button to complete this step.

Now we need to create a Dataset from this spreadsheet which will filter on the staff_id. Using the parameters frame create the parameter staff_id. Click the Data – Datasets... menu item. The Datasets dialog will appear as in figure 4. Select the staffSheet Datasource from the list of datasources and enter the following query into the query editor...

SELECT * FROM staff where staff_id = '$P{staff_id}'

Enter the Dataset name staff_ss_DS, click Execute and then Save to complete. The dataset configuration will appear as in figure 12.