Search code examples
javamongodbjdbcprogress

ROW_NUMBER function with Progress MongoDB JDBC driver

I am getting a memory issue when I run the following query in my Java program with Progress DataDirect MongoDB JDBC driver with a large table in a database (40 million records):

String query = "SELECT * FROM (SELECT tablename.*, ROW_NUMBER() OVER() AS rowid FROM tablename)";

The query above works perfectly fine if I have a small table in the database.

If I just run "SELECT ROW_NUMBER() OVER() AS rowid FROM tablename", then the name of the column will become null. The AS seems not effect in the statement:

Column Name: null class java.lang.Integer

Exception in thread "main" java.lang.NullPointerException

If I just run "SELECT * FROM tablename", it is very fast to get the ResultSet object back and I can easily read the 40 million records in less than few minutes.

So what am I missing? I don't know how to use the ROW_NUMBER function correctly. Any idea? Thanks.

Solution

  • In your query, the row_number() has no column name, it only has a label (the as clause).

    Luckily, the JDBC specification (section 15.2.3) requires you to retrieve columns by label and not by name. The column label in JDBC is either the value of the as clause - if specified - or otherwise the original column name.

    You can obtain the column label by using ResultSetMetaData.getColumnLabel().