Paging problem with page 2 and above in Oracle

Posts   
 
    
Dick Sena
User
Posts: 4
Joined: 29-Dec-2016
# Posted on: 29-Dec-2016 12:58:58   

Looking at SD.LLBLGen.Pro.DQE.Oracle.DynamicQueryEngine.WrapCommandInPagingQuery (5.1.1) we noticed that "addStartParameter" is default false but not set to true for defaultCompatibilityLevel other then OracleCompatibilityLevel.Oracle12c. So the "{0}" in 'lowerLimitPattern = " WHERE r___ >={0}";' never gets replaced by the correct number resulting in an invalid query for Oracle.

We just migrated from 4.2 where it was working. Looking at "SD.LLBLGen.Pro.DQE.Oracle.DynamicQueryEngine.CreatePagingSelectDQ" I noticed that addStartParameter is always true there...

Anyone having the same issues?

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 29-Dec-2016 17:18:21   

For the compatibility level, which one are you using?

Please note:

docs wrote:

•0 for OracleCompatibilityLevel.Oracle9i10g11g •1 for OracleCompatibilityLevel.Oracle12c

The default is 0 or Oracle 9i, 10g, 11g. If you're using Oracle 12c, please set it to 1. Setting it to 1, Oracle12c, will make the DQE to emit 12c compatible paging queries using OFFSET FETCH ONLY, and will not emit sequence value retrieval queries for identity fields.

Dick Sena
User
Posts: 4
Joined: 29-Dec-2016
# Posted on: 29-Dec-2016 17:30:54   

That would be an option if we could find all required places to call the DataAccessAdapter.SetOracleCompatibilityLevel.

Since we do not, please fix the old way of pagination too so we can move on to 5.1.2... sunglasses

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 30-Dec-2016 06:02:40   

I can't see any breaking change related to this (default compatibility level). Anyway, the compatibility level could be set at your .config file, not need to to in every place. Is that what you want? Ref: http://www.llblgen.com/documentation/5.1/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/gencode_applicationconfiguration.htm#dqe-compatibility-mode-oracle-only

David Elizondo | LLBLGen Support Team
Dick Sena
User
Posts: 4
Joined: 29-Dec-2016
# Posted on: 30-Dec-2016 09:58:46   

Attached I added two screenshots of both versions to show the breaking change... an initialization of a variabele is changed making the flow of the application being changed...

Added the setting in appSettings of the config, that helps and gives us the ability to use the new pagination syntax, which is better anyway.

Attachments
Filename File size Added on Approval
OracleODP 4.2.jpg 253,072 30-Dec-2016 09:59.01 Approved
OracleODP 5.1.1.jpg 229,128 30-Dec-2016 09:59.05 Approved
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 30-Dec-2016 10:49:49   

Thanks for reporting. The change was made for the plain SQL API, but we introduced a bug. We also have to check why a test succeeded because of this. Looking at the code it seems it doesn't introduce invalid sql but simply doesn't introduce a lower limit.

Looking into fixing it.

Frans Bouma | Lead developer LLBLGen Pro
Dick Sena
User
Posts: 4
Joined: 29-Dec-2016
# Posted on: 30-Dec-2016 11:30:47   

The result is that


lowerLimitFragment = string.Format(lowerLimitPattern, startNoParameter.ParameterName);

is never performed causing the "{0}" in the lowerLimitFragment to end up in the SQL since lowerLimitFragment is assigned with the pattern containing the "{0}":


string lowerLimitFragment = lowerLimitPattern;

This all results in an SQL ending with " WHERE r___ >={0}", which is clearly invalid.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 30-Dec-2016 11:46:42   

Yes we saw that now too (I based my premature conclusion on reading the code). We've fixed it. Please download 5.1.2 hotfix build to correct the issue.

Sorry for this inconvenience!

Frans Bouma | Lead developer LLBLGen Pro