Writing Back to a Database, and Other Posts From This Week

This week in the BIRT Forums there was a question about how to write data back to a database.  Generally when you’re using BIRT the goal is to consume data from a database, but sometimes you want to write back data too.  To accomplish this, I simply created a Class that took the connection parameters and query, and let the Class take care of the heavy lifting. Here’s the code:

package com.actuate.developer;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;


public class WriteBackSQL {
        // User properties
        private String username;
        private String password;
        private String database;
        private String hostname;
        private int    port;
        
        public WriteBackSQL(String username, String password, String database, String hostname, int port) {
                this.username = username;
                this.password = password;
                this.database = database;
                this.hostname = hostname;
                this.port     = port;
        }
        
        public void executeQuery(String query) {
                try {
                        Class.forName("com.mysql.jdbc.Driver");
                        
                        // Create the URL, connection, and statement
                        String     url  = "jdbc:mysql://" + hostname + ":" + port + "/" + database;
                        Connection conn = DriverManager.getConnection(url, username, password);
                        Statement  stmt = conn.createStatement();
                        
                        // Execute the query
                        stmt.executeUpdate(query);
                        
                        // Close the connection
                        conn.close();
                }catch(Exception ex) {
                        ex.printStackTrace();
                }
        }
        
        public void setUsername(String username) {
                this.username = username;
        }
        
        public void setPassword(String password) {
                this.password = password;
        }
        
        public void setDatabase(String database) {
                this.database = database;
        }
        
        public void setHostname(String hostname) {
                this.hostname = hostname;
        }
        
        public void setPort(int port) {
                this.port = port;
        }
        
        public String getUsername() {
                return username;
        }
        
        public String getDatabase() {
                return database;
        }
        
        public String getHostname() {
                return hostname;
        }
        
        public int getPort() {
                return port;
        }
}

As you can see, this is pretty basic. The constructor takes the connection parameters and executeQuery() executes the query that was passed to it. And while they aren’t needed for this example, I added a few getters and setters for completeness.

The next step is to create a JAR and add it to my resources. To do this you’ll need to drag the JAR into your BIRT Projects then add it to your resources from Property Editor > Resources > Add File.

add resources

Once the JAR has been added to your project, the last step is to use it in your design. To do this you’ll need to use the following script. For my example I added the script to the onCreate() of a label, though you can add it just about anywhere in your design.

//username, password, database, hostname, port
test = new WriteBackSQL("root", "root", "test", "localhost", 3306);
test.executeQuery("INSERT INTO test values('Name', 5);");

And that’s it! The ability to use JavaScript and Java in your design opens a whole new world of flexibility and integration. If you’ve integrated a custom Class into your report let me know about it in the comments. I’m always looking to see how people are using BIRT out in the world!

Here are some other BIRT Forum posts worth taking a look at:

Also, here are some unanswered posts from this week:

If you have a suggestion or solution for any of these, please post them in the thread!

As the week comes to an end I hope everyone has a good weekend. Thanks for reading! If you have any questions or comments please feel free to comment below!
-Kris

About Kristopher Clark

Kristopher is a Solutions Consultant for OpenText Analytics.

Check Also

Introducing BIRT iHub F-Type: Sharing Designs and Other Resources

Actuate’s BIRT iHub F-Type is an easy to use, feature packed, data visualization platform that …

How to Charge Credit Cards from a BIRT Design, and Other Posts From This Week

Last weekend I had the opportunity to attend a local hackathon put together by hackmidwest. …