Planet Ingres

I recently learnt that Ingres 9.2 has introducted the facility to restrict the rows returned from a query by specifying an offset.

Where previously you would only be able to select the first n rows:
SELECT FIRST n col_name FROM table WHERE col_name = 'value';

There is now an "OFFSET" clause:
SELECT FIRST n col_name FROM table WHERE col_name = 'value' OFFSET m;

I went about implementing this in OpenJPA, but found that this dual-location for the Select Range specification was going to cause trouble, so I'd have to override a core method. The problem with that was that the method in question was marked "private", so an override wouldn't have the desired effect! I posted on the developer mailing list about this and they had no problem to me opening up the access, but then I heard about another syntax:

SELECT col_name FROM table WHERE col_name = 'value' OFFSET m FETCH FIRST n ROWS ONLY;

(FIRST can also be replaced by the keyword "NEXT")

This solved my problems, as I was able to specify the range at the end of the query; OpenJPA had a constant for specifying this position (DBDictionary.RANGE_POST_SELECT).

I had a quick play with this new syntax, wondering what
SELECT FIRST x col_name FROM table OFFSET y FETCH NEXT z ROWS ONLY;
would do. Fortunately Ingres prevented me from mixing the FIRST and FETCH FIRST z tokens in a single statement!

There's always loads of new features being added to Ingres; these were added in the 9.2 release. For a summary of the new features in a given release, see the Release Summary document; this is currently available for Ingres 9.2 on docs.ingres.com.

A while ago we were approached by a company using OpenJPA for their product wanting us to help them port it to Ingres. It fell to me to do the work and it ended up being really interesting, so I thought I'd share some of the progress that's been made.

JPA is a "Persistence Framework" for Java, meaning a way to store a program's data, usually represented in an application as objects, into a database. OpenJPA is the Apache Foundation's implementaion of this framework. Its design allows for the underlying datastore to be easily changed with minimal reconfiguration - and no recoding. This is exactly the sort of vendor-neutral design that we strive for at Luminary and so getting Ingres support would be really useful for the whole community.

The initial development went well and the company have been using their product against Ingres for a little while now, so it seems to do the trick! It will soon be submitted to the Apache Foundation as part of the main OpenJPA build but there's still a few bugs to iron out.

The really interesting part of the development was looking inside the Ingres JDBC driver and getting to know some of the Ingres development team who look after it.