Up until now in the tutorial series, we have been connecting to the sample database in Eclipse for BIRT , jdbc:classicmodels:sampledb
You can connect to multiple data sources from your Eclipse IDE for BIRT.
In the real world, data is stored in a variety of systems and formats. According to the Field Guide to BIRT, you can use Open Data Access (ODA) framework to write custom data drivers to access data from any source – even data stored in proprietary systems.
That’s beyond the scope of what we will be discussing here. We are sticking to the basics.
BIRT connection to JDBC Data Source
If your organization stores its data on SQL Server or a MySQL database, you will need to know how to connect to a “JDBC Data Source”.
This opens the “New Data Source” window. You will see a long list of Data Source types, by default:
• Classic Models Inc. Sample Database
• Excel Data Source
• Flat File Data Source
• Hive Data Source
• JDBC Database Connection for Query Builder
• JDBC Data Source
• Scripted Data Source
• Web Services Data Source
• XML Data Source
For this lesson, we are not interested in creating a connection profile from the profile store. We want to select our connection from the default list.
Don’t mix it up with “JDBC Database Connection for Query Builder”. We do not want the “JDBC Database Connection for Query Builder”.
You need to create a “Data Source Name” before advancing to the Next window in the wizard.
Two things here.
1. You can’t use the same “Data Source Name” twice on the same report. Data Source names must be unique for each report file.
2. I suggest you use a descriptive name. As a reports developer, you could waste a lot of time trying to figure out the data source for Data Sources named “DataSource1”, “DataSource2”, and “DataSource3”, etc. It’s not fun trying to pick up a report designed by another reports developer who used vague names for Data Sources. You also want be able to come back to your own work in 6 months and easily know what Data Sources are without opening up the Data Source edit window.
Once, you have a good “Data Source Name”, click “Next”
If not, go back to the previous step and re-select “JDBC Data Source”.
You now need to specify the connection information for the JDBC data source.
Select a “Driver Class” from the drop-down menu. If you do not see the “Driver Class” you need, you can add it. That’s going to be in the next lesson.
The Field Guide to BIRT uses a MySQL database “JDBC Data Source” as an example.
Select the “Driver Class” for a MySQL database (if you have one). The “Driver Class” may not by clearly labeled “MySQL”, but it should include “mysql” somewhere in the context of the title of the Driver like the one pictured here, “org.git.mm.mysql.Driver (v5.1)”.
After you have a MySQL “Driver Class” selected, type in the “Database URL”.
You don’t have to specify the port unless it’s something special.
As a side note, SQL Server formatting is a little different.
Formatting matters. You will want to double check the formatting of the “Database URL” before you move on to the next step. If you do have connectivity issues, formatting of the “Database URL” is often the culprit.
Next, type in a valid “User Name” and “Password” to connect to the JDBC data source. The characters of your password will be hidden as you type it — so be careful. In the unlikely event that you have no user name and/or password on your database, data source does NOT require them. But, as a side note, you may want to re-think your database security.
If you are not using a connection pooling service, you can leave “JNDI URL” blank.
If you are using a connection pooling service, then type the full path. Here is an example:
In the example, “MyDataSource” is the name of the JNDI database service.
If you have done it correctly, the message in the “Test Connection” window will read “Connection successful.”
If not, there will be a message there that will help you troubleshoot the source of the problem. Access Denied messages mean that there is probably some sort of misspelling in the “Database URL”, “User Name”, and/or “Password”.
After you establish a successful connection to the “JDBC Data Source”, click “OK” to close the “Test Connection” window. Then click “Finish” to save and close the “New JDBC Data Source Profile” window. Be careful not to hit “Cancel” or “X” out of the window because you will lose all of your work.