How Can I use Offset and Limit in Active Objects with MSSSQL Server

[Gorka Puente _Appfire_]

We found out that when using offset and limit in an active objects query, if the underlying db is MS SQL Server, it fails with an SQL exception. The following query crashes

@Override
    public List<ListsDataEntity> loadAllInRange(int pageSize, int startIndex) {
        final List<ListsDataEntity> listsDataEntityList = new ArrayList<ListsDataEntity>();
        ao.stream(ListsDataEntity.class, Query.select(queryFields).limit(pageSize).offset(startIndex),
                new EntityStreamCallback<ListsDataEntity, Integer>() {
                    @Override
                    public void onRowRead(ListsDataEntity foundListDataEntity) {
...

with this trace

There was a SQL exception thrown by the Active Objects library:
Database:
	- name:Microsoft SQL Server
	- version:10.50.2861
	- minor version:50
	- major version:10
Driver:
	- name:jTDS Type 4 JDBC Driver for MS SQL Server and Sybase
	- version:1.2.2
java.sql.SQLException: ResultSet may only be accessed in a forward direction.
	at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.stream(EntityManagedActiveObjects.java:221)
	at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects.stream(TenantAwareActiveObjects.java:359)
	at sun.reflect.GeneratedMethodAccessor835.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
...
at 
loadAllInRange(LinksDataAOServiceImpl.java:45)

This is a known and "Won't fix" issue https://ecosystem.atlassian.net/browse/AO-358, which seems to be fixed in the original AO project https://java.net/jira/browse/ACTIVEOBJECTS-17

Any idea on how to bypass this without much "harm" for those MS SQL server users?

/CC  @Alexander Courtis // @Alex Courtis I've seen that you worked in the project (https://bitbucket.org/acourtis/ao), any insight on this? 

Thanks!

[alex · Accepted solution]

Fixed in 1.1.2 by https://ecosystem.atlassian.net/browse/AO-685

Note @Gorka Puente [Comalatech]'s awesome replicator project!

[alex]

LIMIT and OFFSET have always been problematic under Oracle and MSSQL databases, due to their nonstandard syntax used to achieve these operations under older versions.

What is the actual query that is being executed? Is there perhaps a negative offset or limit?

[Gorka Puente _Appfire_]

The query is Query.select(queryFields).limit(pageSize).offset(startIndex) that translated is Query.select(ID, KEY, CREATOR, TIMESTAMP, ...).limit(10).offset(0) Here is the log in case it helps pastebin.com/9s0uL3BJ Thanks!

[alex]

Interesting - we have specific fields in the select - what happens if you just execute a select()? Which AO version are you running against i.e. which confluence version are you running? This could be a problem with the SQL statement rendering by the AO library itself. Please can you enable logging so we can see what is actually rendered: https://developer.atlassian.com/docs/atlassian-platform-common-components/active-objects/developing-your-plugin-with-active-objects/active-objects-faq/enabling-sql-logging

[Gorka Puente _Appfire_]

Hi Alex, If I use just the select it doesn't return the fields values (only the ID, the rest are 0 or nulls). I'm building with Confluence 5.7 with AO 0.28.12. Here's the log with SQL logging enabled http://pastebin.com/AX7DhqQD Thanks for the help!!

[alex]

Apologies for the very belated response! {code} SELECT TOP 10 ID,"KEY",CLICKS,CREATOR,CREATED_TIMESTAMP,LATEST_ACCESS FROM AO_C2BE6F_LINKS_DATA_ENTITY {code} The query looks quite reasonable in terms of syntax, except for that {{"KEY"}} select. Is that the name of one of your columns? Could it be some sort of MS SQL Server hidden keyword that is confusing things?

[alex]

Can you do a most horrible bit of refactoring and change that KEY column's name, to see if it's the source of the problems?

[Gorka Puente _Appfire_]

Hi Alex! I changed the column's name to CLAVE, but that didn't work, here's the log http://pastebin.com/SnagMbEs That shouldn't be a problem, because it's also used in other columns' names like this query SELECT "DATE",ID,USER_KEY,PLUGIN_KEY,"KEY" FROM AO_6384AB_DISCOVERED WHERE USER_KEY = ? In the original project they solved it by modifying a prepared statement, check the comment here https://java.net/jira/browse/ACTIVEOBJECTS-17 Thanks!!

[Gorka Puente _Appfire_]

Hi, any update on this? Cheers

[alex]

Apologies for the delay! Everything looks like it should work, as far as I can see. The fix for the above issue (ACTIVEOBJECTS-17) appears to be still in place... however we might be traversing a code path that doesn't invoke it. Please can you create a standalone replicator for this and create a new AO ticket https://ecosystem.atlassian.net/browse/AO with that attached. The replicator could be: * a small plugin that does just that * a fork of https://bitbucket.org/activeobjects/ao with a new test, similar to, say, TestManyToManyWithPreload added

[Gorka Puente _Appfire_]

Hi! I just created https://ecosystem.atlassian.net/browse/AO-685. There's a small add-on to replicate the behaviour attached. Thank you Alex!

[alex]

Thanks for putting that together! We can spin it up and have a look.