====== pears.ValidateExternalBlobStoreFilesExist ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
create procedure "pears"."ValidateExternalBlobStoreFilesExist"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in @AlternativeLocation long varchar default null ) 
result( "Class" char(1),"ID" char(20),"ExternalFilePath" long varchar,"Issue" smallint,"Description" char(250) ) 
begin
  declare @Folder long varchar;
  declare @StandardLocation long varchar;
  -- create temp table to hold errors
  declare local temporary table "BlobStoreCheckIssue"(
    "Class" char(1) null, -- regularise Standard and Alternative locations
    "ID" char(20) null,
    "ExternalFilePath" long varchar null,
    "Issue" char(10) null,) not transactional;set @StandardLocation = (select "BlobExternalRootFolder" from "params");
  if @AlternativeLocation is null then set @AlternativeLocation = @StandardLocation end if;
  if "right"(@StandardLocation,1) <> '\\' then set @StandardLocation = "string"(@StandardLocation,'\\') end if;
  if "right"(@AlternativeLocation,1) <> '\\' then set @AlternativeLocation = "string"(@AlternativeLocation,'\\') end if;
  -- loop through external blobs
  for "BlobLoop" as "BlobCursor" no scroll cursor for
    select "Class" as "BClass","ID" as "BID","replace"("ExternalFilePath",@StandardLocation,@AlternativeLocation) as "BExtPath" from "BlobStore" where "ExternalFilePath" is not null order by "Class" asc,"ID" asc for read only
  do
    if(select "byte_substr"("xp_read_file"("BExtPath",1),0,1)) is null then // ie error reading file
      insert into "BlobStoreCheckIssue"( "Class","ID","ExternalFilePath","Issue" ) values( "BClass","BID","BExtPath",1 ) 
    end if end for;
  -- recheck errors in case files were in use
  for "BlobLoop2" as "BlobCursor2" no scroll cursor for
    select "Class" as "BClass","ID" as "BID","ExternalFilePath" as "BExtPath" from "BlobStoreCheckIssue" where "Issue" = 1 order by "Class" asc,"ID" asc for read only
  do
    if(select "byte_substr"("xp_read_file"("BExtPath",1),0,1)) is null then // ie error reading file
      update "BlobStoreCheckIssue" set "Issue" = 2 where "Class" = "BClass" and "ID" = "BID" // still a problem
    else update "BlobStoreCheckIssue" set "Issue" = 0 where "Class" = "BClass" and "ID" = "BID" // now OK
    end if end for;
  -- check existence of remaining issues  
  for "BlobLoop3" as "BlobCursor3" no scroll cursor for
    select "Class" as "BClass","ID" as "BID","ExternalFilePath" as "BExtPath" from "BlobStoreCheckIssue" where "Issue" = 2 order by "Class" asc,"ID" asc for read only
  do
    set @Folder = null;
    set @Folder = "left"("BExtPath","locate"("BExtPath",'\\',-1)-1);
    if exists(select * from "sp_list_directory"(@Folder,1) where "file_path" = "BExtPath" and "file_type" = 'F') then
      update "BlobStoreCheckIssue" set "Issue" = 3 where "Class" = "BClass" and "ID" = "BID" // file present in folder - must be locked or insufficient rights to read
    else update "BlobStoreCheckIssue" set "Issue" = 4 where "Class" = "BClass" and "ID" = "BID" // file NOT present       
    end if end for;
  select "Class","ID","ExternalFilePath","Issue",case when "Issue" = 3 then 'File present but not accessible' when "Issue" = 4 then 'File missing' end as "Description" from "BlobStoreCheckIssue"
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."ValidateExternalBlobStoreFilesExist" IS 
{create procedure ValidateExternalBlobStoreFilesExist 

/* Application Maintained Function / Procedure - DO NOT EDIT*/

( in @AlternativeLocation long varchar default null ) 
result( Class char(1),ID char(20),ExternalFilePath long varchar,Issue smallint,Description char(250) ) 
begin
  declare @Folder long varchar;
  declare @StandardLocation long varchar;
  -- create temp table to hold errors
  declare local temporary table BlobStoreCheckIssue(
    Class char(1) null, -- regularise Standard and Alternative locations
    ID char(20) null,
    ExternalFilePath long varchar null,
    Issue char(10) null,) not transactional;set @StandardLocation = (select BlobExternalRootFolder from params);
  if @AlternativeLocation is null then set @AlternativeLocation = @StandardLocation end if;
  if "right"(@StandardLocation,1) <> '\\' then set @StandardLocation = string(@StandardLocation,'\\') end if;
  if "right"(@AlternativeLocation,1) <> '\\' then set @AlternativeLocation = string(@AlternativeLocation,'\\') end if;
  -- loop through external blobs
  for BlobLoop as BlobCursor no scroll cursor for
    select Class as BClass,ID as BID,replace(ExternalFilePath,@StandardLocation,@AlternativeLocation) as BExtPath from BlobStore where ExternalFilePath is not null order by Class asc,ID asc for read only
  do
    if(select byte_substr(xp_read_file(BExtPath,1),0,1)) is null then // ie error reading file
      insert into BlobStoreCheckIssue( Class,ID,ExternalFilePath,Issue ) values( BClass,BID,BExtPath,1 ) end if
  end for;
  -- recheck errors in case files were in use
  for BlobLoop2 as BlobCursor2 no scroll cursor for
    select Class as BClass,ID as BID,ExternalFilePath as BExtPath from BlobStoreCheckIssue where Issue = 1 order by Class asc,ID asc for read only
  do
    if(select byte_substr(xp_read_file(BExtPath,1),0,1)) is null then // ie error reading file
      update BlobStoreCheckIssue set Issue = 2 where Class = BClass and ID = BID // still a problem
    else update BlobStoreCheckIssue set Issue = 0 where Class = BClass and ID = BID // now OK
    end if
  end for;
  -- check existence of remaining issues  
  for BlobLoop3 as BlobCursor3 no scroll cursor for
    select Class as BClass,ID as BID,ExternalFilePath as BExtPath from BlobStoreCheckIssue where Issue = 2 order by Class asc,ID asc for read only
  do
    set @Folder = null;
    set @Folder = "left"(BExtPath,locate(BExtPath,'\\',-1)-1);
    if exists(select * from sp_list_directory(@Folder,1) where file_path = BExtPath and file_type = 'F') then
      update BlobStoreCheckIssue set Issue = 3 where Class = BClass and ID = BID // file present in folder - must be locked or insufficient rights to read
    else update BlobStoreCheckIssue set Issue = 4 where Class = BClass and ID = BID // file NOT present       
    end if
  end for;
  select Class,ID,ExternalFilePath,Issue,case when Issue = 3 then 'File present but not accessible' when Issue = 4 then 'File missing' end as Description from BlobStoreCheckIssue
end
}
</code>