Yes, my musings have been very silent for a couple of months - my apologies. Since March, our agency has been in "move planning" mode, and we finally opened for business at our new digs this past Monday. Since then, I've actually managed to put the (mostly) finishing touches on some upgrades to parts of our B2B web portal. I even managed to hack together a little "event request + approve/deny" workflow app that some of our clients & administrators have been clamoring for. The new changes and beta for the new app should be getting the "rock party" treatment over the next few days, so hopefully I can get some notes cobbled together for a post or two.
On top of all of that, I also just returned a couple of weeks ago from the Percona Live MySQL 2012 Conference in Santa Clara, CA! It was my first time visiting the West Coast, and it was also my first industry IT conference. Even though I was one of a handful of MS SQL devs in a convention center full of LAMP stack aficionados, I felt very welcomed, inspired, and challenged throughout the week. I even have "install Apache, MySQL, Eclipse, Subversion, and Yii" on my "to do" list at home, so there goes my free time for the next foreseeable year or three! Huge shout-out to Sheeri Cabral from Mozilla for her AMAZING White-hat Google Hacking session, and also a big thank you to the folks from Couchbase for the RC helicopter drawings - house-pets and co-workers BEWARE!
Thursday, May 3, 2012
Thursday, February 9, 2012
My love for T-SQL's FOR XML PATH ('')
If you read my latest post on RTF-to-PDF conversion, you'll know that our agency, like many, has been working to migrate lots of old emailing processes over to a more modern platform. For us, currently, that means that we're building stored procedures that will take data from our SQL databases and massage it into a more readable form to be emailed out to users (usually with HTML styling). This usually means one thing: Cursors, and LOTS of them! The normal logic goes something like this:
Procedure: Send Order Notifications
If you wanted, you could drown yourself in MSDN's thorough descriptions of FOR XML and PATH mode, but the key is that its intended usage is to allow you to string together a result set into any XML schema you desire. The gold nugget here is that this same functionality can be user to concatenate a result set together into a string along with any other info you choose! Each of my inner cursor loops could now be replaced with a single SELECT query with the FOR XML PATH ('') clause at the end to smoosh everything together into a single string! Here's a simple example I used for building my list of recipient addresses:
The SELECT statement at the core of the code takes all the matching email addresses for the current store number, adds a comma to the front of them, and concatenates them together into one string. The STUFF statement that wraps it then removes the first character of the string, which should be a comma. If, however, there were no results returned, and the string doesn't have a first character, the STUFF statement returns NULL. I catch this case by wrapping it in a COALESCE statement, which will return a default address if no matching ones are found (in this case, our agency's default B2B inbox). I used a COALESCE statement rather than ISNULL. For some reason, using ISNULL in this case returns only the first character of "Dropbox@ourcompany.com" if the result set is NULL, rather than the whole address.
As you can see, this opens a lot of possibilities! What used to take a cursor loop and several lines of string concatenations to build an HTML table for an email body can now be replaced with one statement that strings the field and formatting HTML together. If you find this useful, or have yourself employed FOR XML PATH('') in your own work in some interesting ways, leave me a comment!
Procedure: Send Order Notifications
- Create a cursor to loop over all the orders placed
- Create an inner cursor to grab and list the order details
- Style details with HTML
- Create another inner cursor to grab additional details (backorder statuses, exceptions, etc)
- Style this information with more HTML
- Create a third cursor to grab all email addresses
- String these into a To: line
- Send the email
If you wanted, you could drown yourself in MSDN's thorough descriptions of FOR XML and PATH mode, but the key is that its intended usage is to allow you to string together a result set into any XML schema you desire. The gold nugget here is that this same functionality can be user to concatenate a result set together into a string along with any other info you choose! Each of my inner cursor loops could now be replaced with a single SELECT query with the FOR XML PATH ('') clause at the end to smoosh everything together into a single string! Here's a simple example I used for building my list of recipient addresses:
SET @To =(
SELECT COALESCE(
STUFF((
SELECT DISTINCT ','+EmailAddress
FROM UserTable
WHERE StoreNumber = @Store
FOR XML PATH ('')
),1,1,''),
'Dropbox@ourcompany.com'))
The SELECT statement at the core of the code takes all the matching email addresses for the current store number, adds a comma to the front of them, and concatenates them together into one string. The STUFF statement that wraps it then removes the first character of the string, which should be a comma. If, however, there were no results returned, and the string doesn't have a first character, the STUFF statement returns NULL. I catch this case by wrapping it in a COALESCE statement, which will return a default address if no matching ones are found (in this case, our agency's default B2B inbox). I used a COALESCE statement rather than ISNULL. For some reason, using ISNULL in this case returns only the first character of "Dropbox@ourcompany.com" if the result set is NULL, rather than the whole address.
As you can see, this opens a lot of possibilities! What used to take a cursor loop and several lines of string concatenations to build an HTML table for an email body can now be replaced with one statement that strings the field and formatting HTML together. If you find this useful, or have yourself employed FOR XML PATH('') in your own work in some interesting ways, leave me a comment!
Tuesday, January 31, 2012
RTF to PDF conversion - a legacy facelift
My latest bit of programmatic prestidigitation involved creating a sort of "modernizing" layer for a legacy report delivering system. Here's the scenario we had in place:
Numerous COBOL programs on a Unix server would produce text reports for clients in rich text (*.RTF) format. At the end of a business cycle (week, semi-period, or month), a number of these reports would be emailed to various client contacts by another COBOL program. This would result in thousands of emails going out, because the legacy process could only send one file to one address per email. This usually resulted in an end-of-period process that took several hours of churning-out emails before it was finished. On top of that, the attachments were UUEncoded, so several clients would constantly have trouble opening the reports.
We had wanted to replace or update this process for a long time, but didn't quite know where to start or what would be feasible. That's when we got the bright idea to leverage the mail-sending capabilities of SQL server, our web portal's list of email contacts, and a little bit of CLR "elven magic" to convert all of the old RTF files into more modern and portable Adobe Acrobat (*.PDF) files!
I won't bog things down with tons of lines of source code, partially because it might be an eyesore, but also because it surprisingly didn't take hundreds of lines of code. Thanks to a couple of free and open source libraries, we were able to easily take an RTF source and convert it to plain text and then dump that into a PDF.
You can see some great examples of the Itenso RTF Parsing library over at Codeplex (pay particular attention to the "Text converter" section). For the PDF portion of the solution, I decided to leverage iTextSharp - a .NET version of the Java iText library. We also had to made a small addition to our existing CDOsys-based mailing procedure on SQL, so that it could handle sending attachments. This thread over at SqlTeam.com was particularly helpful for me.
With all of those pieces in place, I merely had to tie it all together with a tiny VB.Net class library that I compiled and imported as a CLR assembly into our SQL Server instance. I was then able to create a stored procedure based upon that library that would take a path (local or UNC) to an RTF file and an output folder name as its parameters, and it would convert the RTF over to a PDF file. From there, I had everything in place we needed to create a simple job that would check for files that needed to be sent, convert them to PDF, add them to a single email, and deliver that single multi-attachment email to any number of recipients in one fell swoop.
The new process not only runs quicker and sends out less emails overall, but it also sends attachments that are smaller and able to be viewed on many more systems and platforms than the old UUEncoded RTF's were. Our first major trial of this new piece is coming tomorrow when we have an end-of-month and end-of-semi cycle, so here's hoping all goes well! I may post code details in a later blog if anyone's really interested in seeing how the nuts and bolts of this piece work (or if I get wild notion and just decide to post it for posting's sake).
And remember, just because it's legacy doesn't mean it has suck forever ;)
Numerous COBOL programs on a Unix server would produce text reports for clients in rich text (*.RTF) format. At the end of a business cycle (week, semi-period, or month), a number of these reports would be emailed to various client contacts by another COBOL program. This would result in thousands of emails going out, because the legacy process could only send one file to one address per email. This usually resulted in an end-of-period process that took several hours of churning-out emails before it was finished. On top of that, the attachments were UUEncoded, so several clients would constantly have trouble opening the reports.
We had wanted to replace or update this process for a long time, but didn't quite know where to start or what would be feasible. That's when we got the bright idea to leverage the mail-sending capabilities of SQL server, our web portal's list of email contacts, and a little bit of CLR "elven magic" to convert all of the old RTF files into more modern and portable Adobe Acrobat (*.PDF) files!
I won't bog things down with tons of lines of source code, partially because it might be an eyesore, but also because it surprisingly didn't take hundreds of lines of code. Thanks to a couple of free and open source libraries, we were able to easily take an RTF source and convert it to plain text and then dump that into a PDF.
You can see some great examples of the Itenso RTF Parsing library over at Codeplex (pay particular attention to the "Text converter" section). For the PDF portion of the solution, I decided to leverage iTextSharp - a .NET version of the Java iText library. We also had to made a small addition to our existing CDOsys-based mailing procedure on SQL, so that it could handle sending attachments. This thread over at SqlTeam.com was particularly helpful for me.
With all of those pieces in place, I merely had to tie it all together with a tiny VB.Net class library that I compiled and imported as a CLR assembly into our SQL Server instance. I was then able to create a stored procedure based upon that library that would take a path (local or UNC) to an RTF file and an output folder name as its parameters, and it would convert the RTF over to a PDF file. From there, I had everything in place we needed to create a simple job that would check for files that needed to be sent, convert them to PDF, add them to a single email, and deliver that single multi-attachment email to any number of recipients in one fell swoop.
The new process not only runs quicker and sends out less emails overall, but it also sends attachments that are smaller and able to be viewed on many more systems and platforms than the old UUEncoded RTF's were. Our first major trial of this new piece is coming tomorrow when we have an end-of-month and end-of-semi cycle, so here's hoping all goes well! I may post code details in a later blog if anyone's really interested in seeing how the nuts and bolts of this piece work (or if I get wild notion and just decide to post it for posting's sake).
And remember, just because it's legacy doesn't mean it has suck forever ;)
Monday, January 30, 2012
Response.Write ("Hello World!")
Just like the ubiquitous "Hello World" coding example, this post is my simple way of saying "Greetings!" and welcoming you in the spirit of my little blog's intent. This is probably the 4th or 5th blog I've ever started in my lifetime, and this one makes it over 3 posts, I think it will break my personal record. As such, I wanted to keep the focus of this one concise, yet hopefully useful for anyone that may happen by my corner of the blogging world.
"Musing in code" is where I intend to share nuggets, ideas, and "Aha!" moments I have during the course of my work as a software developer. Expect to see a heaping helping of content geared to web application solutions in VB.Net, T-SQL, and Javascript as well as some occasional links to other sites and articles that will hopefully be as much of a boon to you as they have been to me. Again, thanks for visiting, leave a comment if you feel inclined, and enjoy!
"Musing in code" is where I intend to share nuggets, ideas, and "Aha!" moments I have during the course of my work as a software developer. Expect to see a heaping helping of content geared to web application solutions in VB.Net, T-SQL, and Javascript as well as some occasional links to other sites and articles that will hopefully be as much of a boon to you as they have been to me. Again, thanks for visiting, leave a comment if you feel inclined, and enjoy!
Subscribe to:
Posts (Atom)