Author: Ryan Fligg
I just came across this blog post written by a former Windward programmer and it’s still relevant, timely, and useful.
It shows how you can save a template file inside a database, retrieve the actual template as a BLOB object from the database location using the IMPORT tag, run the report, and then save the generated output back into a different location in the database as a BLOB (binary large object).
So I’m reprinting it here in my blog. Credit goes to former intern Drew.
SQL, XML and Import Tag Example
I was recently asked to make an example demonstrating the ability of storing templates and data sources as BLOBs in an SQL database, then generating reports from the stored BLOBs, and finally linking those reports to import tags in a master template.
As a lowly intern, it was nice to do something different from focusing on unit testing. So here is what I have come up with. I don’t know who’ll be reading this article as a reference, but being the new guy and the low-hanging fruit at the office, I have tried to include as much explanation as possible.
This article will show the code section by section with an explanation.
Step 1: Storing DOCX and XML as BLOBs in an SQL Database
For anyone who hasn’t had to store BLOBs in an SQL database using Java, it isn’t difficult, but finding a good example on the Internet took me a while to locate. I will explain my code in a sec, but here is a link to an article that I also found very useful. Also, if you are going to be storing DOCX in an MSSQL database, use the data type nvarchar(max).
Before we delve into the code, I would like to talk about the theme of our BLOBs and the master report we are going to generate from them. The report we are going to create stems from a car accident that happened to our childhood friend, Christopher Robin. He has finally grown up and has just received his driver’s license. Yes, people age quite slowly in the Hundred’s Acre Forest.
During his first ride, he is so overwhelmed with his new-found freedom that he gets into a minor fender bender! While getting his 1970 Ford Chevelle slowly towed back home, Eeyore experiences blissful schadenfreude as he tells Christopher that he told him so.
Christopher enters his giant oak tree home, The Mighty Acorn, and logs onto the Internet to file a claim with his 16-minutes car insurance company, Kanga & Roo Insurance. He fills out a few forms, and his insurance company generates a simple master claims report.
Alright, enough with the tangential story, here is what I will be doing. I will be inserting three templates and two data sources into our MSSQL database, Ranier:
policy_holder_template.docx
policy_holder_datasource.xml,
type_of_accident_template.docx
type_of_accident.xml
master_template.docx
The first two templates and data sources will be used to generate two XML reports, policy_holder_info.xml and type_of_accident_info.xml. The master_template.docx has two import tags that will reference these generated reports. The reference for these generated reports will be stored in the master_data.xml file.
In this example, the master_template.docx’s import tags must reference an XML node in the master_data.xml in order for this setup to work. The nodes do not have to contain the location of the XML beforehand. We will insert the location of the files into the XML’s node before we generate the master report. Since the reference to the file in the import tag is based on a relative path in master_data.xml, we should be able to store our two generated reports almost anywhere and be able to access them later. I decided to put them in the source folder of project.
So let’s look at the code.
Step 2: Generating Reports That Will Be Referenced by the Master Template’s Import Tag
For all of you who didn’t need to do step 1, we are now at the heart of the matter. We are going to query the database, retrieve 2 templates, 2 data sources and generate 2 reports which we’ll store locally and later be referenced by the master_template.docx imports’ tags via its data source, master_datasource.xml.
And there ya go.
For more useful tips and answers about storing templates and data sources, visit our newly updated Documentation Wiki.
The post How to Store Templates as BLOB appeared first on Windward.