Tuesday, November 22, 2011

Excel Macros, VB Script and HTTP Basic Authentication

I came across an Excel sheet that retrieve data from a web site by calling a URL using a macro (VB Script). The site was using basic authentication and the macro wasn't working since it was not sending the user credentials in the request header. I haven't worked with Excel Macros or VB Script so turned to Google for some help.

Didn't find anything that was working but found some half answers which I managed to cobble together to get a working solution. Thought I would write it down here for anyone trying to do the same in future.

Base64Encoding function copied from here and

Don' forget to click the +1 button below if this post was helpful.

Wednesday, November 16, 2011

Why Developers Shouldn't Be Using JDBC Connections, Statements and PreparedStatements

I cringe in pain every time I come across a project that's still using JDBC API interfaces / classes like Connection, Statement, PreparedStatement. Most of them are not legacy projects either.

I truly believe in this day and age you shouldn't be working directly with these low level interfaces and the associated boiler plate code you eventually end up writing. Basically you will have to get the connection, create statements or prepared statements, get the result set and once you are done close everything in a finally clause. This will not only create long, ugly and hard to read code but will make it very easy to introduce errors by say forgetting to close a connection.

So if you are not building a toy Java application you should really be using a high level API that automatically handles these tasks for you. I am not a very big fan of fancy ORM libraries and my preferred data access library has been Spring JDBC for the last two years or so. It gives you the full power of raw SQLs while making it easy do what you want to do with minimum coding. End result is simple, readable and most importantly maintainable and less error prone data access code.

Once you have configured Spring JDBC, an update or a select can be done in one line of code.

So if you are still using low level JDBC code in your applications, its time to consider moving to something like Spring JDBC. You can move your code gradually to the new APIs as Spring JDBC can and will coexist with low level JDBC code.

Take a look at the Spring documentation to get a more in depth idea of all available features of Spring JDBC

Don' forget to click the +1 button below if this post was helpful.

Friday, November 11, 2011

Exporting / Generating CSV Files in Java

I have had to generate CSV files in several projects in last few months and I have settled on using OpenCSV library as it make the task very simple. All you need is the opencsv.jar and a few lines of code.

Don' forget to click the +1 button below if this post was helpful.

Reading All Items from an Amazon SimpleDB Query Result

As you may have noticed select queries in SimpleDB have a maximum limit to the number of results returned and you will have to use the nextToken mechanism to get all results from a query. A solution was posted on aws forum but since I have modified it a bit, thought I will post it here again.

The variable sdb is the AmazonSimpleDB implementation class which in my case is the AmazonSimpleDBClient initialized and injected using Spring. Following is the relevant Spring configuration section (I am initializing an AmazonS3Client too which is not required if you are only using SimpleDB).

AWS ID and Secret values are read from the aws.properties file in the classpath, which looks like below.

Don' forget to click the +1 button below if this post was helpful.