Database Pagination

Author: palgam0@hotmail.com (George Mockford)

For sometime now I've been experimenting with data pagination using the database and perhaps more specifically modification of the Uniface 'where' clause. Some of you have contributed to this so it's not entirely new but with new stored procedure support in 9.5 for MSSQL I thought I'd share with you what I have done already plus a version specific for MSSQL that calls a stored procedure to 'page' through the database.

Arguably the concepts shown here should be a part of or within our drivers but perhaps if there is sufficient support maybe????

Even if you do not like the concept hopefully the code examples will provide some real examples of how this works but you can be the judge. I've included in the zip more details of what this contains and how it works.

Feedback as always is welcome. -George

35 Comments

  1. Just discovered that the zip contained an invalid employee data file, sorry about that.

    I have updated the zip which is now correct. If you downloaded it prior to this message you should download the replacement. If you simply wish to see this sample in action then you can see the stored procedure driven example working here

    Once again sorry for the mistake -G

     

     


    Author: George Mockford (palgam0@hotmail.com)
  2. Dear Santa,

     

    thanx for another great piece of work of yours!

     

    You've received a mail with some more of this :D

    merry x-mas ... oh wait ... no! ... happy new year, dear friend

    -GHAN-


    Author: -GHAN- (hansen@ahp-gmbh.de)
  3. Currently Im really stuck with some DBMS for this topic, George. The bag of reasonable tricks is nearby empty and so I hope for a helping hand, a vision of a change in the future to change this.

     The problem is the more open DBMS like MySQL and Solid which support the Limit or Offset tag. With this I could easily make Uniface fly like an eagle while we are in to pagination. But the problem is to patch Uniface and still work as if we didn't change an thing!

    While using MS SQL 2005/8 I was able to do a very fine pagination, reliable and even fast. Oracle has never been a problem here as it does what you expect it to do. With these two DBMS the way in is READ WHERE:

    READ where var_containing_a_bunch_of_subselects_and_order_settings_including_paging

    At the very moment, I'm a bit stuck with MySQL (Solid hasnt been installed on my system and none of my customers even know about it :D). I really want to roll with MySQL. So, here is my idea and maybe some others, who are interested would like to place a comment about this (and NO, please do skip the 3GL ideas => Crap in this case).

    If we use MySQL / Solid then we need the additional paging statements BEHIND the ORDER:

    READ order var_containing_an_order_setting_AND_LIMIT_<OFFSET>,<PAGESIZE>

    Well, looking at it now, this doesnt seem that impossible, right?! But I'm a lame bastard in this and try to archieve a solution, where I do not have to care about, which DBMS I run with. Have a look at my READ trigger:

    ;### Do we have a paging request!? ###
    if ($WHERE$ != "")
     ;### maybe in combination?! ###
     if ($ORDERBY$ != "")
      read where $WHERE$ order by $ORDERBY$
     else
      read where $WHERE$
     endif
    else

     ;### in case of the order by setting ###
     if ($ORDERBY$ != "")
      read order by $ORDERBY$
     else

       ;### the plain normal read! ###
        read
     endif
    endif

     Behind the curtain: I do not paint the needed entities in every component. I've formed a bunch of DSV's (DataSerVices). They have just the entities painted and this code on the triggers. Public operations return a list of Occurences. This really works very good and you never have to work with subtypes. Its like doing an putlistitems/occ into a var!

     This is optimized to run the READ WHERE and the straight normal stuff. But making this able to also run the READ ORDER version ends up in a "little" mess!

     

    I would like to hear some good idea's as I cant think of me, beeing the only one to use pagination in the apps!? :D

     

    Asside of the talk here I'm preparing a pagination special on udev.info to just gather all notes about this.

    Is there anyone who has done something in this direction?

     

    @TON: is it possible to enable to upload stuff as footage again? There is no option for that at the moment. thx :)
    @GEORGE: Can u persuade TON to tell the lab to let me get the hands on the DBMS drivers, so we could get an option like "PAGESIZE" and "PAGE" for the READ statement?! :D I would kill for that!

     

    -GHAN-

     


    Author: -GHAN- (hansen@ahp-gmbh.de)
  4. Pagination would be such a neat feature to have.

    The ideas you put forth, -GHAN-, sound like what I would want in pagination as well.

    Here is hoping compuware hears our plea.

    m11thom


    Author: m11thom (thomsen@ahp-gmbh.de)
  5. READ with paging SOURCE CODE
    1. ;### a read with pagination (page size is defined in the entity (Model) ###
    2. read page $PAGE$ {pagesize $PAGESIZE$}
    3.  
    4. ;### gets all occurences from PAGE x ... ###

    This would be my desired way on how to do it! Setting up a default like in the image above and just say PAGE 5. And if I wanted to override that, I simpy append the option pagesize with a value.

    @Theo: Does this feel like Uniface to you? :D

    the extended READ statement
    read{/lock} {using Index | options "{index= n} }
                { ; maxhits= n }{ ; cache= n | ALL }{ ; step= n "} }
                {u_where (SelectionCriteria) | where DMLStatement | u_condition (Condition)}
                {page numeric_positive_value | pagesize numeric_positive_value}
                {order by OrderBySpecs}

    Author: -GHAN- (hansen@ahp-gmbh.de)
  6. Hi Günter

    What about this trick?

    READ WHERE "%%$WHERE$%%%) order by %%$ORDER$%%% LIMIT %%$OFFSET$%%%,%$$PAGESIZE$%%% --"

    Pay attention to ")" at the middle and the "--" and the end of the WHERE-clause!

    As we don't have any MySQL database, I could not verify this approach

    Ingo

     

     


    Author: istiller (i2stiller@gmx.de)
  7. Thanx for the Idea, Ingo! That is surely worth an approach.

    But mostly I "dont know" which DBMS I have in reach. Makes it difficult to optimize the READ that directly as every DMBS has its flavour.

    So, If somewhere a uniface ORDER option is placed, then the Statement becomes invalid and delivers a middleware error. Further ORACLE, SOLID and MS SQL dont like the "LIMIT" statement :D The previously delivered READ trigger is omni- it will work with all DMBS ... :D I want to keep it that way, Ingo.

    In case of MySQL and Solid I simply clear out the $WHERE$ and run the normal path. pretty simple and yet without headache :D

    Ingo ... what DBMS do you have? Do you do any pagination within there or did u ever have the wish to do so?

    Cheers to you guys,
    -GHAN-

     


    Author: -GHAN- (hansen@ahp-gmbh.de)
  8. Hi Günther

    About pagination:
    We don't have a web application but old style client-server so we don't  use only the built in hitlist mechanism.

     

    About the database:
    We are only using MS-SQL.
    In my opinion, MS-SQL a a database for the children playground, but ... :-)
    In the beginning, we do have MS-SQL,Solid,ORACLE,Informix. But our sales partner decides only to support MS-SQL :-(

    On the other side, I'll try to support all databases by not using any special READ's&Co.
    Sometime, there is a need for SQL/print.
    In the cases, I use SELECTCASE-statements to support at least MS-SQL, Oracle and Solid.

    But if I understand you right, you are using DSV's in which you collect all necassary WHERE and ORDER information.
    With this information there will only a

    READ where $WHERE$ order by $ORDERBY$

    in READ trigger.
    So not using U_WHERE, you have "raw" data for the database. The "ORDER BY" is easy to translate to a "real" SQL-"ORDER BY"
    With this information one can write instead:

    READ where "%%$WHERE$%%%)  order by %%$RAW_ORDERBY$%%% --"

    Now you are free to add any information you want to the SQl-statement:

    READ where "%%$WHERE$%%%)  order by %%$RAW_ORDERBY$%%%  %$RAW_EXTRAS$ --"

    where $RAW_EXTRAS$ depend on the database.

    SELECTCASE $DBMS$
    CASE "MQL"
       $RAW_EXTRA$="LIMIT %%$OFFSET$%%%,%%$PAGESIZE$%%%"
    ELSECASE
       $WHERE$="%%$WHERE$%%% AND <var_including_paging>"
       $RAW_EXTRA$=""
    ENDSELECTCASE
    READ where "%%$WHERE$%%%)  order by %%$RAW_ORDERBY$%%%  %$RAW_EXTRAS$ --"

     

    Just an idea :-)

    Ingo

     


    Author: istiller (i2stiller@gmx.de)
  9. its a bit more sophisticated (=> or should i say complicated). I do not have a WHERE and an ORDER BY every time. The DSVs are only for the core tasks like retrieval and write an occ.

    All the Paging code is generated to DBMS specific SQL which makes it a pain in the bottom to transfer a extraordinary Retrieve profile in to a DBMS specific SQL escaping the Uniface boundary and wasting a lot of time. I would love to get rid of that. If I only had MySQL, then this would be no problem, as I just would take the ORDER option to step beyond the boundaries. But ... i do not have this lucky situation.

    As we proclaim to be able to work database independent, we see a wide spread of DBMS here. As mentioned, there is Oracle, MS SQL, MySQL, DB2/400 (AS/400 or also known as IBM iSeries) just to mention the most common.

    We have approx 900 Entities in our system running and for this, its pretty hard to do a DSV for every DBMS :D

    From request to result

    The way from the request to the result is a bit longer than jumping into the READ trigger. We start of with the normal code oder user placing a retrieve profile. Next this is handled to a DSV (we have grouped them, reuse is high priority). This again figures the retrieve request needs a special DBMS Code and generates it via a central service component.
    After that, it returns to the DSV and does the RETRIEVE/E statement, a lookup and finally runs to the read trigger, where the occs are placed into the hitllist.

    After that, the DSV transforms the retrieved data into XML/uniface List/HTML depending on the setting. 90% is a Uniface list enrichtd with the $occcrc and the keyfields. This data then again goes back to the caller and is evaluated there. You see, this is a long road!

    I do not care about going so far for the usability! But I do care about doing the things my Dev enrivonment should enable for me: Pagination!

    Honestly: We have the year 2011 and a 4GL tool doing RIA. BUT it is not able to support the developer with a easy way to do paginiation. Like Armstrong once said on the moon:

    "It's a small step for me, but a great step for the mankind!" ... There is NOTHING tricky in placing the code into the Drivers. NOTHING!

    1) The driver already knows, how many occurences there are in the retrieval
    2) The driver already transfers our retrieve profiles to DMBS specific SQL
    3) The driver already manages a internal paging with (default 10) occurences for the windows widgets

    I've been fighting hard to do all this in the last years. But all there has been from Compuware by now has been : " Is possible / see music shop!".  Everytime you request a thing you hear :"GO place a wish!" But this wont change a thing!

    Are there any RIA developers here, who has made a pagination in their web?! How did u solve this!?

     

    Dear Compuware,

    dear friends, I dont want to be offending you here but the example in the music shop is far from usable in the real world! I think Gerton did a good job there back then (yes, u did, Gerton!) but let me conclude why I do point my fingers at it here so directly.

    Pagination gives you the option of jumping from page to page without loading every occurrence. Lets take an example from "my real world" where 200.000+ Occurences are nothing special and pretty common.

    The music shop did remember, what the highest and lowest key was in a page (if I remember right) (which is the point that wont work). So to enable this way of paging 200.000 occurences, i would need to do a complete table scan! How else could i provide the links to page 37 or 13 or 350 or or !? This is unrealistic or is it me? It gets even more ridiculos if you find entities, where u have more than one field in the primary key. Lets do a little calculation based on that:

    200.000 occurences => 30 occs a page => 6667 Links to click or create/pages to jump to! Ingo, and you others ... are you willing to build up 6667 (html) links for only one page like in the music shop?! ... I'm not! Pagination came from the web, and it walks hand in hand with it! Take your browser and surf the it. 8 of 10 Pages will show the results with paging mechanism.
    And paging cant be only faking the user with "fancy jump links". Or did google, amazon, bing, etc fake you?  Search for Uniface with Google. The result is about 1.2 millon hits! AND you can walk through every page in that result set. Now ... do you think this is rocket science? Do you think they remember every key for every page in such a result set? ...

    I still hope for somebody in the lab gets a green light to just implement a little patch to make this archievable without the odds and brings Uniface (in this special case) back to glory and start to use it in the RIA DSP (HALELUJA!) stuff.

    It makes me sad to write all this (for me obvious) stuff here. But maybe my idea about this is set to fantastic?! Then i've done this "fantasy" the last 10 years and been pretty successfull with it. Now I need to be successfull with Uniface, and therefore i do need a little hand from the Compuware guys!

    Compuware self says about Uniface:

    "Uniface Rich Internet Applications
    A rich Internet application (RIA) is an application or app deployed on the Web which is designed to deliver the same features and functions as traditional client server applications on the desktop.  RIA apps provide a modern user experience based on rich and efficient functionality which is easily customized and personalized. "

    (taken from http://www.compuware.com/rapid-application-development/)

    How can this idea of being able to page within a DBMS be that bad when Uniface wants to provide a modern user experience!? Why do these paging examples show up here and there when it is "already solved"!?
    Guys please play fair here: It ain't solved! And it wont be because a minor group of individuals start to hack on the parametes and rape them until they archieve their goals! This needs to be your homework, dear Compuware. And I dont want to just stand here and point with fingers! If you want help for this, then tell me. I want this done no matter how.

    Now you owe me some new pants as I once again went down on my knees for you to accept a "feature", dear Compuware! (And I guess this is the third time in this topic past the years!)

    -GHAN- ... -.-

     

     


    Author: -GHAN- (hansen@ahp-gmbh.de)
  10. ... couple of years ago (when the dITo initiative was still alive),

    I published a pagination solution here on uniface.info using a temp table build up with an sql statement
    Composed of an identifier, a sequencenumber and the PK-values.
    For better performance on deletion via uniface, I used an UP entity holding the identifier (with cascading delete)
    plus some admin information.

    Still a very easy way to handle these pagination problem based on the simple:

    data preparation
    data usage
    data cleanup

    Methodology (for germans "EVA Prinzip")

     


    Author: ulrich-merkel (ulrichmerkel@web.de)
  11. Yes, thats what you showed back then, and still this aint good enough to publish for our customers! To do a pagination, even on Microsofts SQL server there is  no need to use temporary tables. I remember doing so in my AS/400 times back then, when i was released to it :D long time ago ...


    Author: -GHAN- (hansen@ahp-gmbh.de)
  12. But still it works without crying for CPWR help.

    BTW: "our customers"  so you are a CPWR employee right now ???


    Author: ulrich-merkel (ulrichmerkel@web.de)
  13. Uli, try not to pick a fight today. I'm not in the mood. So I will answer polite and show my good will:

    Our custormers => AHP has customers, yes, and they deserve a stable product in my opinion. Try to read the text in the way it was ment to be read instead of try to twist my words.

    Your solution might work, but again, its a workaround for a problem, that remains in the database drivers. The origin is the past! But times change and so does the products if they want to compete with other stuff.

    My code did already work two years ago and i started asking polite already back then. And then in Amsterdam I had a well known Compuware member nodding to my situation and the problem. He claimed that this really is an issue but he could not change it.
    Since then nothing has changed. Older post are gone and so is the proof for the claims. But thats no problem for me.

    Further, we the VARs do pay for the Uniface licences. And its not cheaper than other products, just older :D So personally I think it's ok to tell Compuware to change something instead of building workarounds that may vanish or cause errors in the next update (like the custom mail headers).
    Maybe this is even better than just to say nothing and install other stuff like product X or Product Y doing the same thing as Uniface, just NEWER, BRIGHTER, BETTER and with OTHER PROBLEMS. Other problems might not be that bad, aslong they can be fixed ...

    But there is no need for that here at the moment. Because this is Uniface and I have to make my money with it. So that's the reason for me to tell Compuware to fix this issue.

    And if you still want to offend me, then fine, lets continue this next year in another way :) But for now .... relax!

     


    Author: -GHAN- (hansen@ahp-gmbh.de)
  14. As the person who started this thread I'd like to make a couple of comments.

    First Roger has reminded me that the original post did not reference the many contributors to this work particularily the SQL constructs, and that was an unintentional oversight on my part, my apologies.

    So to those who provided ideas, concepts and code, to this project including Gunter, Roger, Ingo, Uli and a great many others I'd just like to say thank you

    your contibutions are recognized and much appreciated.
    A few of the websites I used to extract concepts

    http://www.4guysfromrolla.com/webtech/041206-1.shtml
    http://www.codeguru.com/csharp/.net/net_data/article.php/c19611
    http://www.4guysfromrolla.com/webtech/042606-1.shtml
    http://www.davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx
    http://www.asp.net/web-forms/tutorials/data-access/paging-and-sorting/efficiently-paging-through-large-amounts-of-data-cs
    http://www.captaincodeman.com/2010/06/06/pattern-efficiently-paging-database-rows-web-page/

    and of course there are hundreds more


    The goal of this posting was not to invoke controversy or conflict but simply to obtain feedback on it's validity.

    I'm biased it's true but I'd like to think that the concept is valid and the desire for a driver enhancement request has been made and I'm reasonably
     sure that our Product Management has or will take note of this in the coming year.


    So no guys please don't pick fights over this it's a waste of energy.

    A happy new year to you all.


    Author: George Mockford (palgam0@hotmail.com)
  15. same to you, dear friend!


    Author: -GHAN- (hansen@ahp-gmbh.de)
  16. Hi,

    George, it was not my intention to get some credit of your splendid work. Actually I haven't been programming Uniface web for a year now and I'm not sure if Uniface 9.5 have added some features.

    A Uniface desktop application utilizes the hitlist, ie. having some kind of "paging" but just filling more and more records into the form-entity. At least as we started with Uniface 9.4 web there were nothing you could call paging or hitlist and the web-application crashed while you retrieved more than approx. 250 records. Of course Uniface customers/developers will get disappointed with this huge degradation compared to the desktop application.

    Most web development tools have some kind of "false" paging getting all records over the wire and then too late filtering those needed for the current "page-view". I have always wondered why they haven't incorporated true custom paging instead.

    It would be nice to hear about the strategy for Uniface retrieve. How is Uniface web supposed to browse database records by default, and then get some comparision to an Uniface desktop application. Is the default that you get all records? Is it possible to limit by eg. "read maxhits"? And to get true paging you will always have to do a lot of extra work and then get some opinions about when the programmer should start to consider true paging?

    Regards RogerW.

     


    Author: rogerw (roger.wallin@abilita.fi)
  17. Uhhh, I want to jump on rogers train here as I'm very interested in that aswell.

    But by default ALL occurences will still have to show up (if nothing else is stated). Ok, you can tell the READ to take max x occurences, BUT you cant reposition in that way. Think of the services (the normal "service component"). I would like Paging to be optional. This wont mess the default forms up.

    And the middleware error because of the huge amount of data was never nice. And supposing to raise the timeout param no help :) I guess, we may expect something better that this.

    Many thoughts and ideas ...


    Author: -GHAN- (hansen@ahp-gmbh.de)
  18. .. but Uniface C/S is stateful, so we can have a db-cursor open in the background to get the "next 20".

    The problem starts with the "stateless" nature where you have either to reselect+sort+massage again and again

    or you have a temp storage of the cursor info WHERE another process(!) can get the data

    in C/S you always stay in the same process so keeping info is a snap.


    Author: ulrich-merkel (ulrichmerkel@web.de)
  19. Yes, and having read the great pagination guide  by GHAN, everyone should understand why it's important to make this easy and flexible. With pagination added into Uniface, Compuware/Uniface could  incorporate paging into there first Web Training Course and after that everything would be much easier .

    Regards RogerW.
     


    Author: rogerw (roger.wallin@abilita.fi)
  20. Thank you, Roger!


    Author: -GHAN- (hansen@ahp-gmbh.de)

  21. >in C/S you always stay in the same process so keeping info is a snap.



    In C/S used efficiently then you cannot run stateful either. Unless one is using the -ex switch to provide one server for each client, the servers are shared, if you try to run stateful in that respect you get one user locked up behind another user.


    Author: Iain Sharp (i.sharp@pcisystems.co.uk)
  22. .. correct Iain,

    but YOU can choose an architecture where you can work statefull and all-in-one-process.

    if you use another one (remote services etc.) you have your cost/benefit reasons to so so.

    So efficiency is not a abstract value:
    If pagination support is your prime concern, you can use stepped hitlists on your client (built-in feature).

    In a stateless, multi-process world, you have to use some kind of persistency to achieve the same goal, don't you?

    Uli


    Author: ulrich-merkel (ulrichmerkel@web.de)
  23. Yes,

    So what you meant by C/S was forms based rather than DSP? We seem to run in to this confusion a lot with Compuware staff as well, we run an application which is NOT thick client, and also NOT web based.

    Internally we refer to (testing) versions with the ASN having no redirection to a urouter as Thick Client, and the (standard) versions using urouter/userver as Client Server, so I was confused by your reference to "TC" as "C/S". My apologies.

    Iain


    Author: Iain Sharp (i.sharp@pcisystems.co.uk)
  24. no problem, Iain.

    Nowadays, we have so many options for an "optimised" execution pattern with lots of pros and cons.

    Especialy if you want to use access via instancenames it's a nice playground (see the discussion from last year).

    Rationale: there is an instance named "local_law" in APS we have a newinstance but we may use different components (impelmenting the same API).
    The whole application should not bother but activate "local_law".DO_THIS(...) instead of all the if-then-else trouble.


    Author: ulrich-merkel (ulrichmerkel@web.de)
  25. Hi Günther

    Keep calm, don't  risk a heart attack. We need you at the next U.B.G.  :-)

     

    A first step from Compuware could be a function which returns to a given UnifAce "profile"  the DBMS specific SQL string(s).

    Example

    ; FLD_1 string       ,  $VAR1$="ABC"
    ; FLD_2 numeric  ,  $VAR2$=123
    ; FLD_3 string       ,  user did enter a profile XYZ*

    If the READ on TABLE1 looks like

    READ u_where "FLD_1=$VAR_1$ & FLD_2=$VAR2$" order by "FLD_3:a"

    A function called SQL_TBL_INFO should return the following

    READ/prepare u_where "FLD_1=$VAR_1$ & FLD_2=$VAR2$" order by "FLD_3:a"
    $WHERE$=$SQL_TBL_INFO("TABLE1","WHERE")
    $ORDER$=$SQL_TBL_INFO("TABLE1","ORDRE_BY")

    where
    $WHERE$="FLD_1='ABC' AND FLD_2=123 AND FLD_3 LIKE 'XYZ%' "
    $ORDER$="FLD_3 ASC"

    Further, it could by nice to know the SQL-datatypes of fields in a table

    $DT$=$SQL_FLD_INFO("FLD_2.TABLE1","datatype")

    which results in
    $DT$="int"

    And so on ...
    If I rember right, long time ago  I did put something like this into the whichlist, but who reads it :-(

     

    Ingo

     

     

     

     


    Author: istiller (i2stiller@gmx.de)
  26. Ingo ... still feeling groovy and yet disappointed. All the stuf WE do for this and how little is needed to make it happen without the hacks. Doesn't feel that good.

    And yes, Ingo, a little more access to the DBMS would be nice.

    The Wishllist indeed is a strange section aswell as the calculations within! I wonder how the stuff gets rated ...?!

    And the next U-B-G? Hmm ... A7 Ausfahrt 121 :) best Traveltime so far from here: 5h15min :D


    Author: -GHAN- (hansen@ahp-gmbh.de)
  27. Hi,

    I went the same discussions last year. I discussed with George and for Sql-Server I sent him the following sql that even could be used for several fields making the "key".

    "select * from orders where
    exists ( select * from (select row_number() over (order by ORDERNO) as row, ORDERNO from ORDERS ) as
    ORDERSwithrownumbers where row >10 and row <= 20 and
    ORDERSwithrownumbers.orderno=ORDERS.orderno)"

    $WhereClause$ = " exists ( select * from (select row_number() over (order by <PagePrimaryKey>) as row, <PagePrimaryKey> from <PrimaryEntity> %%WhereList%%%) as <PrimaryEntity>withrownumbers where row >%%row1%%% and row <= %%row2%%% and ORDERSwithrownumbers.orderno=ORDERS.orderno)"

    And this is just Sql-Server.

    But I fully agree with GHAN. Uniface/Compuware had (still have?) the chance to build a real nice web development tool. The key to this is custom data-pagination, especially for a product as Uniface that concentrates on business critical applications.
    True custom paging should be incorporated into the database-drivers and should be the default way of browsing through records in an Uniface web application.  

    Regards RogerW.


    Author: rogerw (roger.wallin@abilita.fi)
  28. Thank you, Roger. I used to work with your solution until i lately found another way. This has been a great help for my paging all the time. And it pleases me to know, that pagination is not only requested by me.

    If you like, I have another patch for your MS SQL server paging.

    Cheers and a happy new year,

    -GHAN-


    Author: -GHAN- (hansen@ahp-gmbh.de)
  29. For Ingo and all other interested in this topic:

    The pagination will not be possible for MySQL and SOLID due to the filtering in the ORDER BY setting of the READ statement. Got this confirmed from the support as I did my tests and found nothing.

    As this parameter is filtered, there will be no override of statements here.

    Therefore I will (once again) raise a Wish to the topic! Adrian, you hear me? This one is directly for you :D

     


    Author: -GHAN- (hansen@ahp-gmbh.de)
  30. Hi Günter

    Really a good integration work you did, and bad news for the two incompatibilities. I still can't stand the dbms dependant where clause in read triggers, but that still is a good way to get a generic and efficient pagination system.

    Cheers,
    Richard


    Author: richard.gill (richard.gill@agfa.com)
  31. Thank you, Richard.

    unfortunatly I did have no other option but this (crap).  I hope this will end some day ...


    Author: -GHAN- (hansen@ahp-gmbh.de)
  32. I've just released some infos about the topic here:

    http://www.udev.info/uniface/the-uniface-pagination-guide/

    Maybe you'll find some usefull infos on it.


    Author: -GHAN- (hansen@ahp-gmbh.de)
  33. Hi

    Found this one about MS-SQL 2012:

     

    =======================

    http://newtech.about.com/od/databasemanagement/a/Sql-Server-2011-Denali.htm

    Ad-Hoc Query Paging:

    Paging results in SQL Server has been discussed for years. The Order By option in the SQL SELECT statement has been enhanced in SQL Server 2012. Using a combination of OFFSET and FETCH along with ORDER BY gives you control of paging through a result set. Using this technique can really help performance by bring back only the results you want to show to your users when they are needed. The following TSQL code runs against the Person table in the AdventureWorks sample database (available from Microsoft). In the sample query below, SQL Server would return 10 records beginning with record 11. The OFFSET command provides a starting point for the SELECT statement in terms of paging, and the FETCH command provides how many records to return at a time. SELECT BusinessEntityID, FirstName, LastName
    FROM Person.Person
    ORDER BY BusinessEntityID
    OFFSET 10 ROWS
    FETCH NEXT 10 ROWS ONLY;

    =======================

    If Compuware just*) outreach the two parameter "OFFSET" and "LIMIT" (FETCH NEXT nn ROWS ONLY), it would so easy to implement a application with pagination :-)

    *) Simple way as with other feature: "only supported if database support this feature"
        Or on platforms with out this features UnifAce has to think about a "workaround" within the driver

    Ingo

     


    Author: istiller (i2stiller@gmx.de)
  34. Hi Ingo,

    looks like even Microsoft got the note, that Pagination still is a topic and easier solved in other DBMS :) Lets hope they implement it well.

    Similiar to MS SQL, the other syntax is are as follows:

    MySQL
    SELECT * from ENTITY [LIMIT {[offset,] row_count | row_count OFFSET offset}]

    (dev.mysql.com/doc/refman/5.0/en/select.html)

     

    SOLID
    SELECT * from ENTITY [LIMIT row_count [OFFSET skipped_rows] | LIMIT skipped_rows,row_count]

    (publib.boulder.ibm.com/infocenter/soliddb/v6r3/topic/com.ibm.swg.im.soliddb.sql.doc/doc/select.html)

     

    I didnt manage to figure how Informix or DB/2 (/400) are doing pagination. BUT Google surely knows.

     

     


    Author: -GHAN- (hansen@ahp-gmbh.de)
  35. Hi, Uniface 9.6 now supports paging on a number of databases. Please be aware that the "Sample for paging" in the Downloads section of www.uniface.info is for Uniface 9.4 Regards, Theo Neeskens


    Author: Theo Neeskens (tneeskens@itblockz.nl)