ROWID on Dynamic Table

Hello Guys, hope you all are doing well.

Please suggest how can we use dynamic table name or a variable having table name in ROWID function. I am extracting table name from a string and need to get it's ROWID.

Regards,
 

Stefan

Well-Known Member
I was answering:

A dynamic buffer has a rowid attribute, you can find a record using the find-by-rowid method.

But your question makes no sense. Maybe show some (psuedo) code illustrating what you are trying to do.
 
Thanks for your reply Stefan!

Here in below code I need to use vTable instead of hardcoding table name.

Code:
vTable = SUBSTRING(PROGRAM-NAME(1),INDEX(PROGRAM-NAME(1),"-") + 1, 
        (INDEX(PROGRAM-NAME(1),".") - INDEX(PROGRAM-NAME(1),"-") - 1)).
                                                                
/* Firstly create a dynamic temp-table for all the required fields */
DEFINE VARIABLE vTest AS CHARACTER   NO-UNDO.
vTest = STRING(ROWID(test)).
 
And Even after hardcoding the value I am not getting buffer values (coming as ?) whereas my query prepared well:

Code:
CREATE BUFFER bh FOR TABLE vTable.

Regards
 
Last edited:

Stefan

Well-Known Member
Ah, so you are still inside your delete trigger and trying to get dynamic access to the buffer used by the delete trigger?

I do not think that that will work.

While you can create a dynamic buffer for the table, you do not have access to the static buffer used by the delete trigger.
 
"While you can create a dynamic buffer for the table, you do not have access to the static buffer used by the delete trigger."

So this is the reason I am not able to see any buffer value while If I copy the same dynamic query (prepared in query-prepare) and run it statically in different procedure editor then it's working well and displaying data.

Regards.
 
What should I do in this case, how to get specific values of static buffer (unique fields) dynamically.

Please share your inputs on this.

Regards.
 

Stefan

Well-Known Member
You can only access the static buffer dynamically if you can use the static buffer name.

Code:
trigger procedure for delete of abc.

def var hb as handle no-undo.

hb = buffer abc:handle.

message hb::buffer-field(1):buffer-value.

Since your delete triggers need to be static anyway due to the buffer name, you can just generate an extra line passing the static buffer handle in to another procedure that can use it dynamically:

Code:
trigger procedure for delete of abc.

run genericdeletetrigger.p ( ( buffer abc:handle ) ). // note the extra parentheses
 
Hi @Stefan , I am not able to understand what difference it creates after calling a separate procedure. I tired that as well.

Code:
TRIGGER Procedure FOR Delete OF test.
/*
vTable = SUBSTRING(PROGRAM-NAME(1),INDEX(PROGRAM-NAME(1),"-") + 1,
        (INDEX(PROGRAM-NAME(1),".") - INDEX(PROGRAM-NAME(1),"-") - 1)).
*/
run C:\Sports2\genericdeletetrigger.p ( ( buffer test:handle ) ).

Code:
genericdeletetrigger.p

DEFINE INPUT PARAMETER h1 AS HANDLE NO-UNDO.
vTest = STRING(ROWID(h1)). /* This is not working and I need rowid of current deleted record to be used in dynamic query */

CREATE BUFFER bh FOR TABLE vTable.
CREATE QUERY qh.
qh:SET-BUFFERS(bh).

qh:QUERY-PREPARE("for each " + vTable + " no-lock where rowid("
                  + vTable + ") = to-rowid(" + quoter(vTest) + ").").
qh:QUERY-OPEN().
qh:GET-FIRST().

   hDefault:BUFFER-CREATE().
   FOR FIRST _file NO-LOCK
       :
        
      ASSIGN fh = bh:BUFFER-FIELD( _field._field-name )
             vBufferValue = fh:BUFFER-VALUE() NO-ERROR.
          
      MESSAGE "Buffer Value = " vBufferValue
        VIEW-AS ALERT-BOX INFORMATION BUTTONS OK.    
      hDefault:BUFFER-FIELD(_field._field-name):BUFFER-VALUE = vBufferValue.
   
   END.

Could you please share your valuable inputs on this.

Regards.
 
Last edited:

Stefan

Well-Known Member
You seem to be mixing dynamic and static up incorrectly.

The rowid function only works on a static buffer - rowid( somebuffer ) - somebuffer must be a static buffer.
The rowid attribute is an attribute on a buffer handle. The buffer can be dynamic:

Code:
create buffer hb for table 'sometable'

or static:

Code:
hb = buffer sometable:handle.

So if you want the rowid of the record that was deleted - although I have no idea why you want the rowid, since you already have the entire buffer - it is just:

Code:
i_hb:rowid
 
Hi Stefan,

To answer this : although I have no idea why you want the rowid, since you already have the entire buffer

I need rowid attached with handle because I want to dynamically extract field values of only Unique index fields not full record.

Regards
 

Stefan

Well-Known Member
Hi Stefan,

To answer this : although I have no idea why you want the rowid, since you already have the entire buffer

I need rowid attached with handle because I want to dynamically extract field values of only Unique index fields not full record.

Regards

You already have the full record in your static buffer. By accessing the handle to this static buffer you can access the buffer dynamically. You do not need the rowid.
 
You meant, by passing the handle to different .p (genericdeletetrigger.p) and then using that handle to dynamically access the static buffer? I tried that also but I am not getting exactly what I need to do for that.

Regarding Rowid attribute : I tried doing that also but it's not working as well.

Code:
create buffer bh for table vTable.
DEFINE VARIABLE h2 AS HANDLE      NO-UNDO.
h2 = BUFFER bh:handle.
//h2 = BUFFER vTable:handle.
//h2:rowid.
//bh:FIND-BY-ROWID(h2:rowid).

Below code is working fine, but the problem with below code is hardcoding of table name.

Code:
DEFINE VARIABLE vTest AS ROWID   NO-UNDO.
vTest = ROWID(test).
CREATE BUFFER bh FOR TABLE vTable.
bh:FIND-BY-ROWID(vTest).

Regards.
 

Stefan

Well-Known Member
You will need to transform the static buffer to a buffer handle. Static means at compile time. So you will need a stub program per table that contains the table name, you already have this for your delete trigger procedure.

Maybe the following helps:

Code:
// this is the delete trigger, it contains two static references to the buffer
on delete of customer do: // static name

    run scankey ( ( buffer customer:handle ) ). // static name
    return error. // for demo

end.

// demo
find first customer.
delete customer.

// this procedure accepts the static buffer handle as input, the rest of the code is dynamic
procedure scankey:
   define input parameter i_hb as handle no-undo.

   def var cfield as char no-undo.

   for _file where _file._file-name = i_hb:name no-lock,
   first _index 
      where _index._file-recid = recid( _file )
      and   _index._unique = true
   no-lock,
   each _index-field where _index-field._index-recid = recid( _index ) no-lock,
   _field where recid( _field ) = _index-field._field-recid no-lock:

      cfield = _field._field-name.
      message cfield i_hb:buffer-field( cfield ):buffer-value.

   end.

end procedure.

 
I was trying to access static buffer with below code :

Code:
DEFINE VARIABLE vUniqueFields AS CHARACTER   NO-UNDO.
DEFINE VARIABLE vBufferValue AS CHARACTER   NO-UNDO.
DEFINE VARIABLE i AS INTEGER      NO-UNDO.
DEFINE VARIABLE fh AS HANDLE      NO-UNDO.
DEFINE VARIABLE h1 AS HANDLE      NO-UNDO.

FIND FIRST test NO-LOCK.                  

h1:DEFAULT-BUFFER-HANDLE. 

vUniqueFields = "one,two,three,four,five". /* Have this list */

DO i = 1 TO NUM-ENTRIES(vUniqueFields):
  fh = h1:BUFFER-FIELD(ENTRY(i,vUniqueFields)).
  vBufferValue = fh:BUFFER-VALUE() NO-ERROR.
END.

MESSAGE vBufferValue
  VIEW-AS ALERT-BOX INFORMATION BUTTONS OK.
 
You will need to transform the static buffer to a buffer handle. Static means at compile time. So you will need a stub program per table that contains the table name, you already have this for your delete trigger procedure.

Maybe the following helps:

Code:
// this is the delete trigger, it contains two static references to the buffer
on delete of customer do: // static name

    run scankey ( ( buffer customer:handle ) ). // static name
    return error. // for demo

end.

// demo
find first customer.
delete customer.

// this procedure accepts the static buffer handle as input, the rest of the code is dynamic
procedure scankey:
   define input parameter i_hb as handle no-undo.

   def var cfield as char no-undo.

   for _file where _file._file-name = i_hb:name no-lock,
   first _index
      where _index._file-recid = recid( _file )
      and   _index._unique = true
   no-lock,
   each _index-field where _index-field._index-recid = recid( _index ) no-lock,
   _field where recid( _field ) = _index-field._field-recid no-lock:

      cfield = _field._field-name.
      message cfield i_hb:buffer-field( cfield ):buffer-value.

   end.

end procedure.

Hi @Stefan , A Big Thanks to you for explaining me so many things. It was a very good learning for me as I was unnecessarily using dynamic query for the buffer which I already had. Thanks & Regards Again!
 

Stefan

Well-Known Member
Your trigger file:

Code:
{ deletetrigger.i customer }

the include file

Code:
// deletetrigger.i
procedure trigger for delete of {1}:

run scankey ( ( buffer {1}:handle ) ).

Or if you have a strong dislike for ordered parameters:

Code:
{ deletetrigger.i &table = customer }

with
Code:
// deletetrigger.i
procedure trigger for delete of {&table}:

run scankey ( ( buffer {&table}:handle ) ).
 
Top