Fetching records with GET( key , val1 , vall2 ...)

code
1

Hi @BoxSoft

Regarding your comment on today’s webinar about a command to fetch records using just a key label and the corresponding values, I wrote exactly that a few years ago as a part of Table Manager (GitHub).

The implementation is very straightforward and relies only on Clarion’s native reflection:

TableManager.GET    PROCEDURE(KEY pKey1,? pKeyVal1,<? pKeyVal2>,<? pKeyVal3>,<? pKeyVal4>,<? pKeyVal5>,<? pKeyVal6>,<? pKeyVal7>,<? pKeyVal8>,<? pKeyVal9>,<? pKeyVal10>)!,BOOL
fileref               &FILE
grpref                &GROUP
idx                   LONG
fld                   ANY
  CODE  
  
  fileref &= pKey1{PROP:File} 
  grpref &= fileref{PROP:Record} 
  IF grpref &= NULL THEN RETURN FALSE.
  IF SELF.GetClearsBuffer THEN CLEAR(grpref).
  LOOP idx = 1 TO 10
    IF OMITTED(2+idx) THEN CYCLE. !1 class, 2 key
    IF idx > pKey1{PROP:Components}
      !Raise error
      BREAK
    .
    fld &= WHAT(grpref,pKey1{PROP:Field, idx})
    IF fld &= NULL 
      CYCLE
    .      
    EXECUTE idx
      fld = pKeyVal1 
      fld = pKeyVal2
      fld = pKeyVal3
      fld = pKeyVal4
      fld = pKeyVal5
      fld = pKeyVal6
      fld = pKeyVal7
      fld = pKeyVal8
      fld = pKeyVal9
      fld = pKeyVal10
    .
  .
  GET(fileref,pKey1)
  IF ERRORCODE() 
    RETURN FALSE
  .
  RETURN TRUE

!Example:
tm TableManager
  CODE
  IF tm.GET(ORD:KeyCustOrderNumber, 4, 3)  
    ! Some code
  .

I’ve been using it in all new code and whenever I refactor old code. It works great and saves a lot of lines.

Unfortunately, we can’t use the keyword GET because there’s an existing form of Clarion’s command that accepts a key and a pointer value:

GET( key , keypointer )
…
keypointer: A numeric constant, variable, or expression for the value returned by the POINTER(key) procedure.

That’s one reason I implemented this inside a class. Of course, a different keyword like GetKey() could be used instead.

It would be interesting to see if this GET(key,keypointer) form can be overridden when implementing a driver using CapeSoft’s new Clarion Driver Kit.

1 Like
2

That’s a lovely bit of code! I find it interesting that you used GET(Key), as I would typically use GET(File, Key) for normal fetches. For that, you could prototype a new procedure as:

GET  PROCEDURE(FILE pFile,KEY pKey,? pKeyVal1,<? pKeyVal2>,...,<? pKeyVal10>),BOOL

Note that I’ve not actually tried this particular approach, as my extended functionality has always been associated with a class.

1 Like
3

The code is doing GET(File,Key)
He only needs to pass Key as he’s getting the File from pKey{PROP:File}

Agreed, it is a lovely bit of code.

The only improvement I can think of is to support CLEAR 1 or -1, which might be reversed
by component based on PROP:Ascending

4

I think you misunderstood my comment. Carlos wrote, “Unfortunately, we can’t use the keyword GET”, due the prototype conflicting with GET(key, keypointer). In response, I indicated that I generally used GET(File,Key), where adding the field value parameters wouldn’t cause a conflict (as the first field was required and the rest optional).

5

Ah, I get the point now.
As my mentor used to say.

I’m slow, but my work is poor.

2 Likes
6

I thought that also, but then realized that’s needed for SET( Key, Key ) not Get() where likely all the key fields should be filled in with a value.

7

One issue with this command is that it’s not terribly forward compatible. Meaning that if the KEY gains more fields then it has a good chance of failing. One could call Clear, but I think that’s somewhat unintuitive (and would still likely fail). Leaving the field alone at least allows the user to prime the fields either way.

The CLEAR works to make SET more forwards compatible, but that’s a position not a record, so that’s more likely to work.

With the GET I might make it so that if you supply insufficient key fields then it fails, and sets an error. That would better identify where the code is broken, rather than where it’s “working by accident”.

It doesn’t take care of the case where the fields in the key changed order. the current GET pattern would handle that. A forward-compatible pattern would be
GET(key,field,value,field,value,field,value…)
That would also make the command somewhat easier to read - since you wouldn’t need to remember (or “know”) the fields in the key, and their order.

Also, unfortunately, it would need a different name (like GETROW) because GET is a System Intrinsic, and so can’t be overloaded.

1 Like
8

In my experience GET is mainly used with primary or foreign keys that refer to unique values, which don’t change very often, but you make a good point.

It might be easier to catch misplaced parameters using a pattern like GET(key,field1,field2,…fieldN,value1,value2,…valueN), similar to Table Manager’s queue version of get.

9

I think I prefer the alternating field+value pairs, as I would typically break this command into separate lines for readability:

  GET(File, Key,         |
        Field1, Value1,  |
        Field2, Value2,  |
        Field3, Value3,  |
        )

I realize this means I end up with the same number of lines as I would have when setting the values above. However, I’m more likely to use this with a single primary key field, or perhaps two at most.

  GET(File, Key, PrimaryKeyField, PrimaryKeyValue)
10

Hi Carlos,

yes, a different pattern would be field,field,field, .. value,value,value, but I’ve used the field,value,field,value pattern elsewhere in the class, so I think I’ll do that to be consistent.

Having spent some time thinking on this though, I’m not sure the key is even necessary. Just Get(File,field,value,…) is sufficient. Actually specifying the key is not necessary (you don’t really even need a key) and not specifying a key makes it more forward compatible.

The only reason GET(key) exists is because it’s a way of identifying which fields are being used in the GET, and so specifying the fields really makes that go away.

Bruce

11

If the backend is SQL, then keys are kinds of redundant in the call. Will your solution automatically find a suitable key for ISAM. This is similar to the way the VIEW engine works, and I realize you’re already implementing other VIEW elements, so I suppose that’s not an unexpected extension.

12

Interestingly, the VIEW’s JOIN structure uses the pattern JOIN(secondary key,linking fields), so there is a precedent in Clarion for the (key,val1,val2,…,valN) pattern.

In this context, we might think of GET(key,val1,val2,…,valN) or GET(file,field1,val1,…,fieldN,valN)as a kind of dynamic join.