Page Comparison

...

Run the query to get the digest and date when the form was last imported:

Code Block

language	sql

MySQL:

select 
  l.form_id, l.md5_digest, l.executed_on 
from 
  os_import_forms_log l
  inner join dyextn_containers f on f.identifier = l.form_id
where 
  f.name = '<corrupted event form name>'
order by 
  l.executed_on desc
limit 1

Code Block

language	sql

Oracle:

select 
  * 
from (
  select 
    tab.*, rownum rnum 
  from (
    select 
      l.form_id, l.md5_digest, l.executed_on 
    from 
      os_import_forms_log l
      inner join dyextn_containers f on f.identifier = l.form_id
    where 
      f.name = '<corrupted event form name>'
    order by 
      l.executed_on desc
  ) tab 
  where 
    rownum <= 1
) 
where 
  rnum >= 1;

Update the form digest so that it can be re-imported or restored to its working status:

Code Block

language	sql

update 
  os_import_forms_log 
set 
  md5_digest = concat(md5_digest, '_updated') 
where 
  md5_digest = '<first row of the query in step 1>';
  
commit;

Trick the importer that the form was not modified since last import:

Code Block

language	sql

update 
  dyextn_containers 
set 
  last_modify_time = <executed_on of the first row of the query in step 1>null 
where 
  identifier = <form_id of the first row of the query in step 1>;
  
commit;

Restart OpenSpecimen.
Skip to step 8 if your database is MySQL.

Oracle Only: Add a couple of helper functions to replace the contents of a BLOB column.

Code Block

language	sql

CREATE OR REPLACE FUNCTION convert_to_clob(l_blob BLOB) RETURN CLOB IS
  l_clob         CLOB;
  l_dest_offset  NUMBER := 1;
  l_src_offset   NUMBER := 1;
  l_lang_context NUMBER := dbms_lob.default_lang_ctx;
  l_warning      NUMBER;
BEGIN
  dbms_lob.createtemporary(l_clob, TRUE);
  dbms_lob.converttoclob(
    dest_lob     => l_clob,
    src_blob     => l_blob,
    amount       => dbms_lob.lobmaxsize,
    dest_offset  => l_dest_offset,
    src_offset   => l_src_offset,
    blob_csid    => nls_charset_id('AL32UTF8'),
    lang_context => l_lang_context,
    warning      => l_warning
  );
  RETURN l_clob;
END convert_to_clob;

Oracle Only:

Code Block

language	sql

CREATE OR REPLACE FUNCTION convert_to_blob(l_clob CLOB) RETURN BLOB IS
  l_blob         BLOB;
  l_dest_offset  NUMBER := 1;
  l_src_offset   NUMBER := 1;
  l_lang_context NUMBER := dbms_lob.default_lang_ctx;
  l_warning      NUMBER;
BEGIN
  dbms_lob.createtemporary(l_blob, TRUE);
  dbms_lob.converttoblob(
    dest_lob     => l_blob,
    src_clob     => l_clob,
    amount       => dbms_lob.lobmaxsize,
    dest_offset  => l_dest_offset,
    src_offset   => l_src_offset,
    blob_csid    => nls_charset_id('AL32UTF8'),
    lang_context => l_lang_context,
    warning      => l_warning
  );
  RETURN l_blob;
END convert_to_blob;

Make the default specimen event forms as user managed forms.

Code Block

language	sql

MySQL:

update
  dyextn_containers
set
  xml = replace(
    xml, 
    '<managedTables>true</managedTables>', 
    '<managedTables>false</managedTables>'
  )
where
  name in (
    'SpecimenCellReviewEvent', 
    'SpecimenCheckInCheckOutEvent', 
    'SpecimenEmbeddedEvent', 
    'SpecimenFluidEvent', 
    'SpecimenProcedureEvent', 
    'SpecimenSpunEvent', 
    'SpecimenFixedEvent', 
    'SpecimenMolecularReviewEvent', 
    'SpecimenTissueReviewEvent',
    'SpecimenFrozenEvent'
  );

commit;

Code Block

language	sql

Oracle:

update
  dyextn_containers
set  
  xml = convert_to_blob(
     replace(
       convert_to_clob(xml), 
       '<managedTables>true</managedTables>', 
       '<managedTables>false</managedTables>'
     )
   )
 where
  name in (
    'SpecimenCellReviewEvent', 
    'SpecimenCheckInCheckOutEvent', 
    'SpecimenEmbeddedEvent', 
    'SpecimenFluidEvent', 
    'SpecimenProcedureEvent', 
    'SpecimenSpunEvent', 
    'SpecimenFixedEvent', 
    'SpecimenMolecularReviewEvent', 
    'SpecimenTissueReviewEvent',
    'SpecimenFrozenEvent'
  );
  
commit;

On successful execution of all the above steps, restart OpenSpecimen.
You should be now able to edit the specimen event forms using form designer.

...

Versions Compared

Old Version 9

New Version Current

Key