Uniface User Forum

 View Only

  • 1.  selectdb and the retrieve profile

    PARTNER
    Posted 01-18-2024 12:55

    Is there a simple way of having selectdb pay attention to the retrieve profile? I have a pagination issue where the selectdb to get the total number of hits needs to use the retrieve profile, but the fields are of different types and I don't know how to build the u_condition. 

    Regards, 

    Iain



    ------------------------------
    Iain Sharp
    Head of Technical Services
    Pci Systems Ltd
    Sheffield GB
    ------------------------------


  • 2.  RE: selectdb and the retrieve profile

    Posted 01-19-2024 06:46

    Hey Iain,

    Have you tried to use "lookup"??

    Regards
    Knut



    ------------------------------
    Knut Dybendahl
    ------------------------------

    Original Message


  • 3.  RE: selectdb and the retrieve profile

    PARTNER
    Posted 01-19-2024 07:59

    As it happens, this service has only one entity so lookup can work. I'm now going to go to the customer data to see how slow it is processing 8000 hits. 

    Regards,

    Iain



    ------------------------------
    Iain Sharp
    Head of Technical Services
    Pci Systems Ltd
    Sheffield GB
    ------------------------------

    Original Message


  • 4.  RE: selectdb and the retrieve profile

    Posted 01-19-2024 08:11

    afaik - the request is done directly against the db - so no hitlist.

    Also - I think - even in a multi entity component - by doing a "setocc" to the entity you 
    want to lookup - uf would look up on the entity with the focus...  I could be wrong...

    Good luck,
    Knut



    ------------------------------
    Knut Dybendahl
    ------------------------------

    Original Message


  • 5.  RE: selectdb and the retrieve profile

    PARTNER
    Posted 01-19-2024 09:36

    Yeah but 'focus' in a service component is a tough one... Who knows what the 'current entity' is in a .svc? 

    Working so far on this one though. 

    I also had a look at u_where, because the help pages were ambivalent about whether it used the populated fields on the entity in selectdb, turns out it doesn't, so lookup seems to be the winner here. Thanks for the pointer. 

    I checked out the sql script using $ioprint and it does a count(*) so it's about as efficient as it's gonna get, (not sure why it does a select count(*) with rowlock though. 

    Regards, 

    Iain



    ------------------------------
    Iain Sharp
    Head of Technical Services
    Pci Systems Ltd
    Sheffield GB
    ------------------------------

    Original Message


  • 6.  RE: selectdb and the retrieve profile

    Posted 01-19-2024 10:07

    Hi Iain,

    for me it looks like selectdb can work with profiles as statet in the Doku.

    <pre>

    trigger detail
    variables 
    numeric V_SUM
    endvariables
    selectdb count(rnr) from "ESB" u_where (esb = 'I100*22') to V_SUM
    message "%%$datim  Sum: (%%V_SUM)"
    end

    </pre>

    Instead of count(*) you can use any fieldname. rnr is a field in table ESB.

    The * in I10022 is a Gold

    Regards

    Norbert



    ------------------------------
    Norbert Lauterbach
    Infraserv Gmbh & Co. Höchst Kg
    Frankfurt DE
    ------------------------------

    Original Message


  • 7.  RE: selectdb and the retrieve profile

    PARTNER
    Posted 01-19-2024 11:17

    Hi Norbert, 

    The scenario is that the user is presented with a grid of the entity, they fill in a retrieve profile against one, many or none of the fields and this is passed (via XML) to a service, which then retrieves the records and passes them back (via XML) to the form. 

    As such I don't know which fields, if any, will have valid retrieve data in them (which may or may not have profile characters). 

    One of the fields is a datetime. 

    I was therefore having great difficulty working out how to parse the current 'retrieve profile', which will be used by the subsequent retrieve to actually fetch the data, into a u_condition to be passed to the selectdb to determine the total hits (for pagination), and the selectdb doesn't use the current field profiles to filter the count(field). 

    if the field DT_CR contains '.>.=20230506000000' (greater than or equals 06/05/2023) how to parse that to work with selectdb, which needs it's operands in plain text, and it's dates in a completely different format. 

    lookup does use the current "retrieve profile"  and therefore filters the hits using whichever fields are populated. The examples in the documentation however, use selectdb to calculate the same number, but there's no practical example of how to filter this based on user input containing wildcards in only some of the fields. 

    Regards, 

    Iain



    ------------------------------
    Iain Sharp
    Head of Technical Services
    Pci Systems Ltd
    Sheffield GB
    ------------------------------

    Original Message