Retrieve lost record

[oldemanw]

Occasionally we have an error in the database (10.1B) "index for recid # could not be deleted". It is then impossible to remove/update a record in the database using standard software or the progress editor.


When this happens, we dump the record, remove it using "proutil -C idxfix" and then reload the dumped record.
In proutil we use the delete option and give it the recid to delete.
(It's not pretty, I know, but it works.)

Now, we have given proutil a wrong recid and something got deleted, but I don't know what.

We have a test database which is refreshed with production (using probkup and prorest) on a regular basis.
Can I retrieve that record (tablename, values) from test?

Is it something like
for each _index where recid(_index) = 2109586 no-lock: disp _index.
?

Any hint would be greatly appreciated.


Willem

 

[Casper]

Rowid (recid) is unique per area. So folowing code can give you more then 1 result and works only correct if the record wasn't created after the last probkup/prorest action:

DEFINE VARIABLE hBuffer AS HANDLE     NO-UNDO.
DEFINE VARIABLE rRecid as RECID NO-UNDO.
DEFINE VARIABLE lOK as LOGICAL NO-UNDO.
ASSIGN rRecid = 2109586.
FOR EACH _file WHERE _file._hidden = NO:
    CREATE BUFFER hBuffer FOR TABLE _file._file-name.
    lOk = hBuffer:FIND-UNIQUE("where recid("  + hBuffer:TABLE + ") = " + string(rRecid)) NO-ERROR.
    IF lOK THEN DISPLAY hBuffer:TABLE.
    DELETE OBJECT hBuffer.
END.

I'm assuming 2109586 is the recid you accidently removed from the database.


HTH,

Casper.

 

[TomBascom]

If the RECID didn't change after the backup that you have then you could potentially retrieve the deleted record. (Restoring from backup won't change the RECID -- but the application code might cause it to change; for instance by deleting and re-creating the record.)

If you know what table it was from then:

/* untested */

find tableName no-lock where recid( tableName ) = 12345 no-error.
if available tableName then
  do:
    output to value( "tableName.d" ).
    export tableName.
    output close.
  end.

Would do the trick.

If you don't know the table name then you need to cycle through all of the possibilities like so:

/* untested */

define variable bh as handle no-undo.
define variable qh as handle no-undo.
define variable wc as character no-undo.

define stream expFile.

function exportData returns logical ( input bh as handle ):

  define variable f  as integer   no-undo.
  define variable i  as integer   no-undo.
  define variable q  as character no-undo.
  define variable bf as handle    no-undo.

  do f = 1 to bh:num-fields:

    bf = bh:buffer-field( f ).

    q = ( if bf:data-type = "character" then '"' else '' ).

    if bf:extent = 0 then
      put stream expFile unformatted q string( bf:buffer-value ) q field_sep.
     else
      do i = 1 to bf:extent:
        put stream expFile unformatted q string( bf:buffer-value( i ) ) q field_sep.
      end.

  end.

  put stream expFile skip.

  return true.

end.

for each _file no-lock where not _hidden:

  wc =
    "for each " + _file._file-name +
     " no-lock where recid( " + _file._file-name + " ) = 12345"
  .

  create buffer bh for table _file._file-name.
  create query  qh.

  qh:set-buffers( bh ).
  qh:query-prepare( wc ).
  qh:query-open().
  qh:get-first( no-lock ) no-error.

  if qh:query-off-end = no then
    do:
      output stream expFile to value( _file._dump-name + ".d" ).
      exportData( bh ).
      output stream expFile close.
    end.

  delete object qh.
  delete object bh.

end.

Keep in mind that it is possible for a RECID to exist in more than 1 storage area. If you have multiple storage-areas you may get multiple hits on the RECID.

 

[oldemanw]

Casper, Tom,

Thanks guys!
It was a record in area #9 (static data) containing pricelists.
One record was missing there (when compared to test). I managed to reload it.
These are crafty little programs, I'll add them to my toolbox



Willem
(who can now happily continue his vacation)