The Uniface pagination guide
Paging with Uniface in a USP
Pagination explainedPagination is a feature which came up with all those database enabled web sites. You search for something and get 50.000 hits! Nobody needs to have all 50.000 hits on one reply. This is simply to much! So we define a page size. The result is sliced into this size and called BY PAGES (VERY IMPORTANT: not by occurrence nr!). This is because it is far easier to go to page 50 instead of finding occurrence 23424 to 23454 or even to do this with the values of the prime key values. Remember the music shop did so which may be defined as possible, as well as it is possible to walk on water :D I personally take this attempt as a EPIC FAIL!
Some reasons might be:
1) Page 50 always stays page 50!
Page 50 will always stay page 50 while the values of the prime keys or occurence numbers can change as we are not supposed to be the only user in an application. As we need to get to page 50, we multiply 50 times the page size and put that into our sql statement or for Uniface into the retrieve request.
2) Page x only needs one parameter and not two
Calling page x is more simple than taking two params for a start and an end. As you know, I'm a lazy bastard but hey ... I'm fine with that :D
3) We dont want to figure on every hitlist, where page x is starting and ending!
Honestly, I dont want to code this for every entity. Further, we need to read ahead or forth and back to make the other pages accessable with would be the same as a full table scan or just parsing through all occurrences. This gets a horrible thought as I do mess around with tables having 20kb per occ and 200.000 occurences in store. Please do not start stuff like that! I want something reusable. We simply handle this to the dbms and are done with this!
Uniface does pagination!technically ... yes, but only in its internals. On a retrieve, the hitlist get filled with the primary keys of the hits. This happens in the first read statement or the moment, you use a field. If the developer wants to use this, he will fail. Well ... until the moment, when you force Uniface to do what you want. The job is to fill the hitlist with our desired paged result and without loading the complete entity for that. If you want to load 40 occurences from page 148 (40x 148 occs) then it would please me to only load 40 occs instead of 5920! The effords are big but the result will please you.
not HOW, but WHERETo use pagination, we need to use the READ WHERE in the Read triggers of the entities. We need to pimp the read request with the WHERE option in the read statement. So what do i mean by writing "pimp the read request"? First we need to understand, what Uniface does when it forms the sql statement. Then we have to modify it to our needs. The read statement is as follows:
Yes, I know you know the syntax :) But I just wanted to be sure, right? So, we pick out the things we really need here (which are "where" and "order").
When we choose to make a READ WHERE, then Uniface forms a statement looking like:
We now need to fill out the X in the bracket. And this is not that tricky, when you know how to do it. I will explain that somewhere below for every supported DBMS. If we want to have any hits where acme_field=50 then X would get acme_field=50. But we want pagination. So we need to get beyond this bracket and take over control. This is done by setting X with values closing the bracket and reopening a new one, while there are statements in between. Here comes an example:
Now we have escaped the bracket. The point ist to form a statement, that both Uniface and the DMBS like. Uniface will do as you like, but return in an error if the dbms fails with the statement (Middleware error which has become my favorite error message so far!).
As mentioned above, the result of the statement is NOT a bunch of occurences, but a modified hitlist. Uniface will act as if nothing happened because it gets the keyfields into the hitlist. And after that we just run through the hitlist.
Please note, that the u_where option is not usable for this as the Uniface driver turns the retrieve request into "valid" sql for the target dbms.
Limitations: Supported databases so farAt the moment, I have my system working on:
- Oracle (using rnum)
- MS SQL Server 2005 and above (using timestamp).
The other DBMS could easily be convinced to do pagination but as we need to obey the Uniface SQL statement and its setup. My next future step in this topic is to enable MySQL (and probably SOLID too) to this functionality.
UPDATE: Due to filtering systems in Uniface, we wont be able to do paging in this way with MySQL or SOLID. The problem is that the READ ORDER BY cant be outsmarted. Uniface filters the fields within the ORDER BY to only let field names pass.
Paging OracleOracle needs some subselects to be able to page in the right way. Further we do need the primary keyfield of the paged entity in order to break out of Uniface's statement. This is how it looks:
Paging MS SQL 2005 / 2008MS SQL ist a bit more tricky. We need to put hands on the database itself and will not inform Uniface about it. This is because of the limitations in MS SQL which may not allow to work with two fields in a primary key (in that way we want to). Therefore we add another field to the tables in MS SQL SERVER and call it ghan_row_id (or whatever). The field gets the type "timestamp" which then is managed by the server and stays unique. Uniface wont care about this as no operations from uniface will affect the field.
Further we need set this setting for the driver to get a good performance
With that done, we can easily page the DBMS from Uniface:
As you can see, the most of the code is pretty reuseable! The best option to do the paging code is somewhere centralized (e.g. a service) you call, before you enter the read trigger. Further this way of paging will do for ANY other (not-Uniface) system aswell.
How a good paging option would look like in UnifaceI agree, making all this stuff running is a little mess. Ask yourself, wouldnt it be much better to just have this in YOUR Uniface? This should not be impossible. But we need to tell Compuware about this. So, go, join the discussion and tell Compuware to make this possible to you!
Paging setup in Uniface Model(Entity)
By now, the second part with MySQL and Solid are missing here. But as stated above, it will not be possible by now. Compuware needs to enable pagination nativly in the drivers to make us, the developers, able to page any page we like with a retrieve profile. Let me know, what you think and how you like this. Until then, my workaround is an option to those, who are using MS SQL or Oracle. This technique has now run over two years and is stable!
Further, I have placed a wish to inform Uniface, that there is work to be done. By now it performs pretty fine. Please support it and make native pagination support possible in YOUR Uniface Environment. You will find it here.
@RichardBollee Ah, I understand. But allthough it is a nice workaround, it will get had to explain to the customer, why we want another bloating database
Hi Guenter, The problem lies within the database and how this technology is handling paging in general. I solved this issue by not using a relation database but using a search engine instead. I already did a presentation on this subject a few years ago on a Face2Face meeting in the Netherlands. Its not easy to implement since you have a secondary database to maintain but data retrieval works sensational ... not only the paging stuff but querying the dataset non-structured (like google does) is fast as well ... one can never get this from an sql based database (which is de facto structured).
Hi Eitan, yep, there is a little bug here, thx for the hint. fixed it :) Sorry for the truncated question :/ In addition there was a missing part to the lv_orderby. I added this aswell. Feel free to contact me directly for more questions and issues.
It seems that my majour question was truncated :-( I didnt understand what select %%lv_orderby%%% rnum is for, and what the value of lv_orderby should be. I didnt understand how the fields profile will work, if in the inner select there is no reference to it... Thanks
Hi. Thanks for the idea and explantion. I do have some comments, and one majour question... It seems youve got some typing mystakes in the source code for paging Oracle: Line 3 should be lv_keys as the first argument to $Replace