SQL – Dynamic Date Range, Number Range in Oracle, SQL Server, and DB2/400

Often, we need to make coverage reports that span over a period of time and where all points in the period do not contain data.

Assume a shop has 50 outlets and we want to find the number of sales in each branch over the past ten days. Business is bad in some areas and so some outlets had no sale at all. A query like the following will give only those outlets that had sales but not a list of all outlets:

SELECT outletid, saledate, count(*)
FROM sales
GROUP BY outletid, saledate
ORDER BY outletid, saledate

ODI – Incremental Update and Surrogate Key using Database Sequence

I am writing this because it had me lose my sanity for some time. If,

• You’re using Oracle Data Integrator
• You’ve defined a dimension table with a surrogate key even if it is not a SCD type 2 (recommended by Kimball)
• The surrogate key is maintained through a database sequence (Oracle sequence in my case)
• Your IKM in Incremental Update
• You’re frustrated about how to control updates to a dimension having a sequenced surrogate key

Then read on for the solution that worked for me. The solution is pretty standard – you only need to know what you’re doing and setting the right options.

ODI – How to Reverse Flat Files

The most important thing to remember before using flat files as datastores in ODI is that the Designer looks for them in the client PC where it runs. I haven’t tried using a UNC (\\computer\share\…) but that should work.

ODI comes pre-configured with a folder for dealing with flat files – in the Topology Manger, you’ll find the FILE_GENERIC data server set up to serve files from the ODI_HOME/oracledi/demo/file directory. But if you want your own file data server then follow these steps:

Active Directory Authentication using Java/JNDI

I’ve successfully performed AD authentication using JNDI from Java. It is almost a copy of the code found in Mauricio Rojas Blog. Thanks Mauricio!

The ADAuthenticator class tries to connect to the AD using the given credentials and retuns a Map containing some information of the user if authentication succeeds. It can be instantiated to authenticate to a default AD or a specific AD in the constructor.

iSeries, Oracle Reports 10g and ODBC-JDBC

I’ve been pulling my hair on this issue:

A complex sql with subqueries and a union written and executed OK in iSeriese Navigator for V5R4. Then I copied and pasted the same SQL in the query definition window of Oracle Reports Builder JDBC query dialog. The connection to iSeries is through the JDBC-ODBC bridge driver. I put _xxx parameters in the query.

Guess what? I got an error ‘Variable P_xxx not defined or not usable’.

Earlier, I tested using a simple query with a parameter and it worked fine. After a lot of head-scratching and trial-and-error, finally I got the solution:

Place a blank/space (‘ ‘) around the parameter(s) even if it is at the end of a line (not query)!

The parser seems to stumble upon detecting tokens without spaces.

Storing Membership Data in ASP.NET 2.0 Web Application’s SQL Server Database

This is a summary of the MSDN article How To: Use Forms Authentication with SQL Server in ASP.NET 2.0.

ASP.NET 2.0 allows storing web-site users and profiles in a database and maintain access control based on that information. The concept is referred to as ‘Forms Authentication’. The database can be SQL Server or Active Directory. Here, you will see how to store them in SQL Server.

Java Date is so stupid

Everytime I try to work with java.util.Date, I get frustrated. Why is the API so cumbersome?

Have you ever tried to find the difference between to Date values? There is nothing except a millisecond value. I don’t want milliseconds! I want rounded days and the timestamp comes to taunt me!

Why can’t you simply add to or subtract from a Date? I don’t want to handle multiple Calendar objects when working with multiple Date values simultaneously. It just bloats the code!

I cannot instantiate a specific Date if I want to. Go through Calendar again? But why??

Maybe you will tell me about various design issues. But I don’t want to bother. I just want a simple interface for manipulating Date values.

Spring MVC Tutorial, 2

This is my second installment of the tutorial. For the first part, see Spring MVC Tutorial.

6 Adding Localization Support

These days, there is much touting about i18n (internalization) and l10n (localization). So, I guess I need to support them also. In fact, I’ve already done the ground work for localizing error messages (error.invalid.principal in LoanCalcValidator, <form:errors> in loanCalc.jsp).

The first thing I’ll do now is add a resource bundle in springweb-servlet.xml for localized messages:

Why Microsoft is so popular?

The last time I used VB5 was back in the year 2000. I worked with VC++ and C++Builder during 2001. From then on, I have been working with Java (Eclipse, IDEA) without the AWT/Swing part. Java GUI always scared me.

Java SE, EE and ME have come a long way and J-lovers bark at Microsoft and .NET almost all the time. Yesterday, I wanted to make a list of files of a directory and decided to give VisualStudio (installed sometime ago) a try. To my surprise, I was able to develop a VB.NET windows application in just half-an-hour that allowed me to select a directory, list its files in a listbox and save it to a text file. I wanted to copy the list to the clipboard but that seemed like a bit of work.

So, after 6 years of Java programming, I was able to use VB.NET quite easily. The help system was indeed helpful. And everything seemed so intuitive. I hope Java GUI development will be that easy someday – or has it already?