#5 offset-style="rownum" for use with MSSQL 2005+

[JConXTC]

I've tested connecting and loading the data in a MSSQL 2008 R2 64 bit database and in general it worked without a problem.
For jdbc used the MS version 4. (http://www.microsoft.com/en-us/download/details.aspx?id=11774) configured for XA.

So far the only issue I can't overcome is the pagination (queries with limit/offset).
For MSSQL 2005+, in my opinion, the best way to implement this is this one:
http://stackoverflow.com/questions/187998/row-offset-in-sql-server

Attached is my xml config.

Extremely nice piece of software. Keep up the good work!

Cheers!
- Pencho

[JConXTC]

DB xml config

[David E. Jones]

Have you tried offset-style="fetch" instead of offset-style="limit"?

According to the link you included that should be supported in at least SQL Server 2012... and perhaps it is supported in the 2008 version you mentioned you are using.

If not, what do you think of moving to SQL Server 2012?

The other methods look like they would require some complex SQL generation to handle possibly different ID/PK fields and such.

For now it might be best to use the EntityListIterator.getPartialList() method instead of the EntityFind limit and offset settings. On a side note, using a cursor like this is how OFBiz does all of this sort of query as it doesn't support the limit/offset stuff).

This woud require some small code changes for the Auto Screens and such, but isn't all that complicated.

Let me know what looks like the best solution and I'll see what I can do to help facilitate it.

[JConXTC]

I have tried both offset-style options with no luck.
The query:
SELECT col1, col2
FROM (
SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
FROM MyTable
) AS MyDerivedTable
WHERE MyDerivedTable.RowNum BETWEEN @startRow AND @endRow

runs for sure on both SQL Server 2005 and SQL Server 2008.
For me, moving to SQL Server 2012 is not an option. At least not a quick one. :)

I would say that EntityListIterator.getPartialList() is the solution that will make the framework most portable, but from other hand may be this won't be the perfect solution for paginating say 100,000 rows result set.

It will be perfect if you can for now just add f/e offset-style="jdbc" which to use the EntityListIterator.getPartialList() method.

Cheers,
- Pencho

[David E. Jones]

Thanks jconxtc/Pencho, that's a good idea.

This is implemented in commit feb7c3d in the git repo.

There is one caveat with this as implemented now: if you use EntityFind.iterator() it will seek to the offset, but doesn't enforce the limit and so may behave different from the other offset-style settings.

BTW, I decided to go with offset-style=cursor instead of "jdbc".

[JConXTC]

Thank you, David. It works like charm!