I was recently on a project where the goal was to allow an AS400 to pull a list of printers from a Microsoft SQL Server database using Web API. Next, pull and merge pdf documents from an Oracle database, pass them back to the AS400, and print the merged document to a network printer. Simple right?
OK, there were a few challenges…
First – Oracle connectivity:
The client had some existing code using a deprecated version of the Microsoft Oracle Driver (System.Data.OracleClient). Visual Studio happily reminded me of this every time I built the project. I swear, it was almost smug about it. Since I didn’t want to use the ADO style queries, I thought I would just fire up Entity Framework and then go to lunch. It didn’t end up so easy and I got pretty hungry before I was done. I found that I needed to install two different driver sets from oracle. I had to install the 32 bit drivers with the developer tools to get Entity Framework to play nice in Visual Studio 2012 for me. I also needed the 64 bit drivers for .Net 4.5 to talk to the 64 bit Oracle database. This all works fine once set up but I found the Oracle documentation to be pretty dense. It was complete and good but there was a lot there.
Second Issue – Adobe:
To save our client money, we used PDFSharp to merge pdf documents streamed out of the oracle database. This works great and everything merges correctly. Just doing that saved $1500 or so that we might have paid for a third party library. The problem though was that PDFSharp would not print to network printers in this company’s domain. After a bit of research, Uncle Google provided a way that worked but in involved marshalling calls the Windows kernel and some other libraries in a way that I didn’t feel good about leaving the client with. I did not think that it would be supportable for the next person that came along. Since this was a contract and developers at the client company would own this code, it seemed more responsible to pay for a third party library (with support) just for this one piece. The support bit was worth it and came in handy before the project was over. So, we ended up using a little open source and a little pay-to-play software to get the printing right.
Third – bitness bites again:
The third party library that we bought for the printing came in a 32 bit or a 64 bit flavor. If you recall, I was using a 32 bit and a 64 bit set of libraries from oracle and so had all my projects set to compile for ‘Any CPU.’ This seemed to confound the printing library. I emailed the vendor and they responded that you could not use Any CPU with their libraries, the project had to specify bitness. Blah.
In the end, with some help from the vendor, I was able to get the 32 bit library to print pdfs happily to network printers.
A few other helpful things:
Since this was a Web API application, we had to set IIS to allow 32 bit applications to run.
When a new printer gets added to the list in the SQL Server database, it will not be initially mapped on the Web API web server. We created a domain user that had permissions to load print drivers on the server and permissions to print on the network.
Lastly, there were quite a few printers on the network that were going to be used, so I wrote a batch to map all those drivers so things would be up and running quickly when we first hit prod.
I don’t often get to use an AS400, Web API, SQL Server, Adobe, and Oracle in one small project. but it was a great project, the client got what they needed, and I learned a lot.