Open amirmf opened 6 years ago
i fixed this with developing this method in EntityFindBuilder class
public void addLimitOffset(Integer limit, Integer offset) {
if (limit == null && offset == null) return;
MNode databaseNode = this.efi.getDatabaseNode(mainEntityDefinition.getEntityGroupName());
// if no databaseNode do nothing, means it is not a standard SQL/JDBC database
if (databaseNode != null) {
String offsetStyle = databaseNode.attribute("offset-style");
if ("limit".equals(offsetStyle)) {
// use the LIMIT/OFFSET style
sqlTopLevel.append(" LIMIT ").append(limit != null && limit > 0 ? limit : "ALL");
sqlTopLevel.append(" OFFSET ").append(offset != null ? offset : 0);
}else if ("oracle".equals(offsetStyle)) {
sqlTopLevel.insert(sqlTopLevel.indexOf(" FROM "),", ROWNUM RN");
sqlTopLevel.insert(0,"SELECT * FROM (");
Integer lastIndex= offset+limit;
sqlTopLevel.append(") TEMPQRY WHERE TEMPQRY.RN >= "+ offset +" AND TEMPQRY.RN <= "+ lastIndex);
}else if (offsetStyle == null || offsetStyle.length() == 0 || "fetch".equals(offsetStyle)) {
// use SQL2008 OFFSET/FETCH style by default
sqlTopLevel.append(" OFFSET ").append(offset != null ? offset.toString() : '0').append(" ROWS");
if (limit != null) sqlTopLevel.append(" FETCH FIRST ").append(limit).append(" ROWS ONLY");
}
// do nothing here for offset-style=cursor, taken care of in EntityFindImpl
}
}
Hi @jonesde I've changed a few lines of your query builder class to make Moqui compatible with oracle. You've used few SQL commands which aren't standard query for example: OFFSET & ... I have just changed all specific queries to standard queries. Here's the link of new files: https://we.tl/t-TMjWISAG0o
i've test the new classes it's working fine.
Could you submit this as a patch so I don't have to manually diff? To do this just run something like 'git diff > issue327.patch'. You can also attach things to comments on GitHub, just drag them into the little grey area below where you type in comments as mentioned in that box (says 'Attach files by...').
I'm not sure what you mean by specific and standard queries, but I'll take a look at your patch to see. For what it's worth, my experience with Oracle is that the word 'standard' just doesn't apply... if they supported the SQL2008 standard the 'fetch' offset-style would work. :)
i'm using oracle 11g with ojdbc8 driver and moqui latest version (master branch)
this is my conf:
AND
1- when trying to open any pages in tools & system menu i got this error of list query