Use selectdb to replace lookup/sql count(*)

Author: christoffer.aronsson@ladok.umu.se (voltagecontrol)

Hello everyone

I'm writing a $hits replacement in Uniface 8.4 (I'm sure all of you have done this at one point...) and have opted to use selectdb since lookup refuses to accept an entity parameter (oh, god, why?!).

However I can't seem to figure out the correct syntax for the selectfb function count(). I was under the impression that it'd be possible to write something similar to normal SQL, like this:

 

entry hits_replacement
    returns numeric
    params
        string v_table : in
        string v_field : in
    endparams
    variables
        numeric v_hits
    endvariables

    selectdb count(v_field) from v_table to v_hits
   
    return(v_hits)
end

 

I though this code would make it possible to specify a specific field, and also that it'd be possible to replace v_field with an asterisk (*) to get a regular:

 

selectdb count(*) from v_table to v_hits

 

However, I keep getting compiler-errors whenever I don't supply a litteral fieldname for count(). Am I missing something fundamental here? The ulibrary pages for selectdb do say that I need to enter a LitFieldName, but having to hardcode the fieldname makes no sence, since it makes it quite hard to write dynamic code. How have you solved this?

 

Best regards

Christoffer A, Sweden

8 Comments

  1. Hi Christoffer A,

    can you tell us in which context you want to use your hits_replacement?

    Perhaps we can work out a useful alternative

    Success, Uli

    P.S. lookup is implemented to act as the normal "retrieve" on a complete component.


    Author: ulrich-merkel (ulrichmerkel@web.de)
  2.  

    In this particular case I'm going to use this code to decide wether the search profile that has been entered will return a result set > $max_hits$, to prevent a user from retrieving a ton of posts - and instead force them to enter a more detailed profile. We previously sometimes used things like: setocc v_table, $max_hits$ to decide wether the hitlist was out of bounds, but I want a faster and more useful way to get this information.

    Writing a more general and globaly accessible implementation would mean that we could use this code in other situations as well, such as displaying quicker "numer of hits"-results, or to provide endpoint information to progressbars etc.

     

    I was planning on implement this as a global proc and perhaps as an operation runing on service level. The importaint thing beeing that it should be accessible everywhere, without a need to put extra code inside read-triggers for each entity etc.

    I guess one could write some smart lookup-code and distribute via #includes in the model, but that's not really a viable option since our model is huge, and we have tons of modified read-triggers that would require manuall editing, hence lookup falls short.

     

    The thing that really puzzels me is that I've seen code-examples and different solutions for a $hits-replacement before (at TURF of cource). For example:

    http://hosted.byjones.net/turf/viewtopic.php?f=11&t=890&hilit=selectdb+count&start=15

    http://hosted.byjones.net/turf/viewtopic.php?f=8&t=836&p=2033&hilit=selectdb+count#p2033

     

    As I've mentioned before lookup falls short in our environment, and a pure sql statement is a bit too low-lvl in my opinion that could cause problems with different database drivers. Hence, I choose selectdb, since both the above threads mention it as beeing a viable option that should be able to performe a select count(*). I just don't understand how I should go about implementing it, since selectdb count(*) is not valid?

     

    In one of the threads mentioned above there's even a code example from you Uli:

    selectdb (count(*)) from "WORKER" u_where (company.worker = name.company) to local_no_of_workers

    ...wich is exactly what I want to do, but it doesn't seem to be possible to compile code like this.. at least not in my environment?

     

    It'd help a lot if someone could confirm wether selectdb (count(*)) is a valid statement or not, then I could focus on either an alternative sollutions or fixing my dev-environment.

     

    Thanks in advance

    Christoffer A, Sweden


    Author: voltagecontrol (christoffer.aronsson@ladok.umu.se)
  3. Hi Christoffer,

    since V8.4.01 you can specify a MAXHITS with the read instruction:

    Synopsis

    read{/lock} %\

      {using Index | options "ParamDef1;ParamDef2;...;ParamDefn"} %\

      {u_where (SelectionCriteria) | where DMLStatement | u_condition (Condition)} %\

      {order by OrderBySpecs}

    options

    The options clause allows you to specify the following performance related parameters when reading from a DBMS:

    • index number
    • maximum hits
    • cache size
    • step size

    The following table describes the parameters that can be used with the options clause:

    Parameters available with the options clause

    Parameter

    Description

    index=n

    Specifies which index UNIFACE should use for record-level DBMSs. This option is the same as the using clause. n must be a number or numeric expression.

    maxhits=n

    Specifies the maximum number of hits that can be returned by a query. n must be a number or numeric expression.

    cache=n

    Sets the maximum size (in bytes) of an occurrence that is retrieved and placed in the select cache. The complete occurrence will be placed in select cache when the field length is smaller than n. cache can also be set to ALL. This retrieves all occurrences which do not have overflow or BLOBs. If the cache size is not specified, the default value is 512 bytes.

    step=n

    Specifies the step size of the query. n must be a number or numeric expression. If the step size is not set, the default value is 10.

     

    Note: do decide wether there are more records or not,

    enter maxhits=n+1 as an option,

    if there is $totocc = n+1, discard the last occurence and report there are more records in the database

    Success, Uli

    P.S. a very old way to limit records returned (Uniface 5) is a check of $curocc the WRITE trigger of the topmost entity
    if it exceed a given value, you can return -1 to stop the retrieve.


    Author: ulrich-merkel (ulrichmerkel@web.de)
  4. Hi Christoffer,

    I think it is not too hard to add a selectcase to your entry which is based on the entity you provide:

    selectcase (v_table)
       case "enta"
         selectdb count(PK1_ENTA) from "ENTA"
       case "entb"
         selectdb count(PK1_ENTB) from "ENTB"
       case "entc"
         selectdb count(PK1_ENTC) from "ENTC"
    endselectcase

    return($status)

    It's easy to generate the complete case lines
    using the UCKEY table of the repository.

    Success, Uli

     


    Author: ulrich-merkel (ulrichmerkel@web.de)
  5. Thanks a lot for the help. I'll look into how we can implement them in a good way. Maxhits seem like a good idea, and it also seems that a case-list might be the way to go for the GP/Operation.

     

    Thanks again.

    Christoffer, Sweden


    Author: voltagecontrol (christoffer.aronsson@ladok.umu.se)
  6. I was lucky last time I did this.

    U_VERSION as a field in every table.\


    Author: Theo Neeskens (tneeskens@itblockz.nl)
  7. Hi,

    why the lookup shouldn't work for you?

    Do you have tried to use a setocc before the lookup?

    We use this variation in U8 and U9 and it work.

    For Example as operation in the entity-collection:

    partner operation load

    setocc "<$entname>", 1

    lookup

    if ($status < <MAX_HITS>)

      retrieve/e "<$entname>"

    else

      message/info $concat("to many hits for ", <$entname>

    endif

    end ; load

    Lookup use the active-path, so you have to set the active-path to the entity you want to retrieve!


    Author: Thomas.Young (thomas.young@young-consulting.de)
  8. Hi Christoffer,

    For your information:

    "selectdb count(*) from "INSPECTIE" to $1" (where entity "INSPECTIE" contains variable length fields)

    gives a syntax error in my current 7.2.06 environment; I can not even close the trigger editor.

    Theo's idea is a nice and simple one; lots of older Uniface apps still have a u_version field in every entity.

    Regards, Arjen


    Author: Arjen van Vliet (arjen.van.vliet@uniface.com)