Navigation problem

Author: sbr444@gmail.com (bharathi_sb)

I want to navigate through records in the Uniface web page. to avoid bulk loading of data I've given "maxhits" option in the read trigger. I want to see only certain number of records per page, like 5 or 10. currently I'm able to do this by sorting primary key field using "ORDER BY" query. But the problem is in some of the table composite primary key exists. In this case how can I sort without using "ORDER BY" query?

15 Comments

  1. You can specify more than one field in the order by clause of the read statement.


    Author: Theo Neeskens (tneeskens@itblockz.nl)
  2. I also have this problem tried as you said but it is reading records in jumbled order because of the data type of the fields.

    Is there ideal solution for this then please let me know.

     

     


    Author: alaganslm (mathiyalagan.sundaram@in.ibm.com)
  3. Can you give an example of what you want to do?

    What field are in the key? How do you want them sorted?

    More info please !


    Author: Theo Neeskens (tneeskens@itblockz.nl)
  4. hmmm ... is this a sort of PAGINATION what he intends?! then a good READ WHERE (not u_where) will do!


    Author: -GHAN- (hansen@ahp-gmbh.de)
  5. If there is any sample for this then send me.

    because my case is

    A table which have a composite primary key which consists 6 columns.

    Some of the columns are string and some of them are numeric.

    And my requirement is to navigate thru the records like first,previous,next,last

    what should i do for this. anybody knows then please let me know.


    Author: alaganslm (mathiyalagan.sundaram@in.ibm.com)
  6. Hi,

     

    attached please find a concept how to do pagination with a little help from a temporary table.

    http://www.uli-merkel.de/dito/download_incubation/dITo%20Pagination.pdf

    Some chaps rated this as a straight-foreward easy to implement concept (and I have no objections on this).

    Success, Uli


    Author: ulrich-merkel (ulrichmerkel@web.de)
  7. I found some of my old slides from back then, when i started off with this. Take a look:

     

    [[Media:2358110]]

    [[Media:2358111]]

     

    And finally we break out here ...

    [[Media:2358112]]

    George has some code to it, on which we hacked a bit around. We found this is working with all MAJOR DBMS ... (tested here on ORACLE, MS SQL, SOLID* and MySQL*)

    So, let me know, how you like that :)

    -GHAN-


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


    I have tried the solution that you gave.
    It is working fine .
    But i have two more issues in that.
    When use this with table which has big number of records then it take a lot of time to fetch
    the records.
    The second is i want to display those records in the same order as they inserted.
    Is there any optimized solution for this


    Author: alaganslm (mathiyalagan.sundaram@in.ibm.com)
  9. Hi there again,

     

    good to hear, that you got it up and running. Let's look at your issues

    Large amount of occs slows the request down
    I remember Goerge and I went through such an effect while using Microsoft SQL server in different versions. While I ran into trouble with my SQL EXPRESS EDITION, George was at normal "fast" speed. Oracle hat no problems in the testset.

    The testset of George had 1.000.000 Occurences and pagination was at a glance. Myself here has a customer using this with same ammount on a heavy weight table (about 192 fields per occ) and this is still fast. I suppose it to be a setting on the DMBS driver. What DBMS do you use anyway? Are your keys set up properly?

    In case you are using MS-SQL try this:

    USYS$MSS_PARAMS = procs: off, keysetcursor: on, mapping=2, logtime: 128, locktime: 30, mlw: 20

    Back then, the keysetcurser setting was warm rain!

    Same order as they have been inserted
    hmmm ... well ... how are they inserted!? Can you see this in the fields?! Timestamps!? Orders?! Try to put an ORDER BY into that point, where you build the SQL statement. Just append an "ORDER BY XXXX" there ... I guess :)

    The Intention of it is to speed up the whole thing enormous! It simply cuts down the overhead of the DBMS by reducing the requests to the stuff that is needed! If it lags of speed, then something is wrong :(

    Have a look at it and try-out the order stuff!

    kind regards,

    -GHAN-

     

    (IF EXISTS "GEORGE" : could you provide him your sample?! )


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

    Again i'm facing different issue with this paging approach

    my table has 631 records  and my pagesize is 10

    my last page should contain single record as per the total records and pagesize

    when i'm trying to navigate to the last page i'm using the below query

    select field1, field2, field3 ... fieldn  from mytable
    where (keyfield1,keyfield2) in
    (select field1, field2, field3 ... fieldn from
    (select rownum as rnum, field1, field2, field3 ... fieldn  from mytable)
    where rnum between 631 and 631) order by keyfied1,keyfield2

    when i'm using the same query in toad it returns the last row

     

    but in uniface when i'm clicking last button i'm calling the  retrieve statement  retrieve/e "entity_name"

    which intrun hits the below statement in the read trigger.

    read where (keyfield1,keyfield2) in (select field1, field2, field3 ... fieldn from

    (select rownum as rnum, field1, field2, field3 ... fieldn  from mytable)

    where rnum between 631 and 631) order by keyfied1,keyfield2

     

    the actual result should be one row. But instead of returning the one row it doesn' t retrieve any rows.

    may i know what would be the issue ?

    would you please help me in this regards

    Thanks in advance.

     

     

     


    Author: alaganslm (mathiyalagan.sundaram@in.ibm.com)
  11. Sure thing ... here you go.

     

    First I would like to mention, that I decided to control the page links like to say "Give me page 17" instead of "Give me 10 Occs from Offset 23529". This makes it easier (for me) to handle the calculation :)

    So if sombody requests page 17 the offset  range becomes:

    17 x 10 = 170 - 180

    And four YOU: if your finale Page would be like 63 then the request would look like:

    START: 630
    STOP :  640

    It doesn't matter how many occurences there are or even not are ;) The only thing that really matters is, that OCC 631 is in between 630 and 640!

    I guess, thats it!

    SOLID and MySQL

    The solution here is to fix the page range a bit. What I then do is:

    START  = (PAGE x OCC_PER_PAGE) -1
    STOP    = (PAGE x OCC_PER_PAGE) + OCC_PER_PAGE

    That's because they take that request with other commands than ORACLE (offset range / limit ).


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

    I'm using oracle version 10g.

    When i write the same query as i mentioned above and tried in both toad and the Edit SQL utility in uniface.

    It is working fine.But when i used with uniface read statement then it is not working.

    And could you explain why it is not working in oracle with uniface.

    regards,

    Mathiyalagan.S


    Author: alaganslm (mathiyalagan.sundaram@in.ibm.com)
  13. Raise the IOPRINT level and take a look into the PUTMESS LOG (defined in your ASN file)

    A level of 32 should show you, what you are looking for or what goes wrong while uniface tries to build and executer your statement.

    What exactly fails is hard to come by from here.  But the answer will be found in the putmess log!

    Give it a try!

     


    Author: -GHAN- (hansen@ahp-gmbh.de)
  14. You read trigger needs to go something like.

    read (maxhits...) u_where ((key_field1 > old_key_field1) | (key_field1 = old_key_field1 & key_field2 > old_key_field2) | (key_field1 = old_key_field1 & key_field2 = old_key_field2 & key_field3 > old_key_field3).......) order by "key_field1, key-field2, key_field3, ....."

    Note you cannot test for key_field1 >= old_key_field1 & key_field2 >= old_key_field2 etc, this will skip records....

    Iain


    Author: Iain Sharp (i.sharp@pcisystems.co.uk)
  15. Please be aware that Uniface 9.6 supports pagination on a number of databases.


    Author: Theo Neeskens (tneeskens@itblockz.nl)