Failure in retrieval when using non-key fields

Author: pari_shelar@yahoo.com (pari_shelar)

Hi,

I am getting one strange behavior; I still don’t know it is strange or fundamental of Uniface. J

I am initializing two fields to table. One is primary key, second is non- primary key and then retrieves the entity.  In this case, record is retrieved on the basis of primary key and simply ignores the second field (non-primary key).

e.g.

clear/e "customer"

 

customerid.customer/init     = "1"       ; primary key

customerActiveflag.customer/init = "T"   ; non primary key field

 

retrieve/e "customer"

 

Customerid = 1 fetch valid data but don’t have data where customerActiveflag = ’T’ in database. So it should not retrieve the record but it is retrieving the record on the basis of primary key (customerid = 1).

Note: I tried with different fields with different data type for non primary key field but still result is same.

I don’t know why?

 

I have two solutions to overcome this issue.

1.       Check primary key in read trigger (u_where) & initialize non primary key while retrieving.

 

2.       Use gold equal to (.=)  while initializing primary key

e.g.

        clear/e "customer"

 

        customerid.customer/init   = ".=1"      ; primary key

        customerActiveflag.customer/init = "T"  ; non primary key field

 

        retrieve/e "customer"

 

In above case, record is not retrieved which is right. I don’t know why it is working with gold equal to and not working with simply initializing the value?

 

I am using Uniface 9.4 & MS SQL 2005.

Thanks in advance.

 

4 Comments

  1. Hello,

    yes, this is very tricky situation and one can think of this being a bug or a feature? But AFAIK, this is how Uniface has worked for ages, so I suppose it will be the same in the future.

    As long as you provide complete primary key, Uniface fatches only that one row (regardless of data in other fields). We bypass this by generating our own where, since if you use where, it is always limited to this (we use Uniface 9.3 and Oracle).

    I have tried to find it out in documentation, but I found only one sentence at a retrieve statement: "If a specific primary key value for an outer entity is supplied, only the Read trigger of a single outer entity (and all its inner entities) is activated." So it seems not to be quite clearly documented (or at least I am unable to find it).

    As for your gold-equal-sign "workaround", I suppose if you use any gold character, it is considered to be a profile (not a plain data) and Uniface takes data of all fields and try to find records matching the profile provided. Without a gold character, it is considered a data, that is complete primary key, and no other data are used to fetch record (this can speed up fetching but at the same time it discards all other conditions, just as you mentioned).

    Zdenek


    Author: sochaz (zdenek.socha@fullsys.cz)
  2. As Zdenek stated:

    when uniface gets a complete primary key, this one is used to get the record

    - for performance reasons.
    - for re-read a record after modifications of non-pk fields (optimistic locking).

    Append a <GOLD>| to your PK value to make it "this value or NULL" if your user likely mixes PK and additional profile input.

    Uli


    Author: ulrich-merkel (ulrichmerkel@web.de)
  3. Are you able to just specify the primary key prior to the retrieve/e, and then have a read u_where in the read trigger

     

    clear/e "customer"

    customerid.customer/init     = "1"       ; primary key

    retrieve/e "customer"

    <READ>

    read u_where customerActiveflag.customer = "T"   ; non primary key field

    Since you already know the primary key (and therefore are trying to retrieve a unique record) then you simply want to check whether it is Active or not.  Alternatively, you could just retrieve the record and do an if statement after to check if it is active.

     

    HTH

     

    Martin


    Author: byjones (martinandheather@gmail.com)
  4. ... if you have this situation in your sourcecode (means you are controling the game)

    1) retrieve the record with the PK
    2) filter your hitlist: if the other conditions are not met, DISCARD the occurence

    clear/e "customer"

    customerid.customer/init     = "1"       ; primary key
    retrieve/e "customer"
    if customerActiveflag.customer/init = "T"   ; non primary key field
       discard "customer"
    endif

    Same retrieve-and-filter works in very complex selections wher you need access to different "detail" entities

    Uli


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