====== pears.BlobstoreWrite ======
<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>
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."BlobstoreWrite" IS 
{create function BlobstoreWrite 

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

(in blobclass char(1),in blobid char(20),in blobzipped tinyint,in requestedby char(15))
returns integer
not deterministic
begin
  declare bAlreadyExists integer;
  declare bNewfilenameNeeded integer;
  declare bFail integer;
  declare xpath char(250);
  declare xfolder char(250);
  declare xfolder1 char(250);
  declare xdate date;
  declare fsize integer;
  if varexists('iqblobtemp') = 0 then // Invalid state - cannot proceed
    return-1
  end if;
  if iqblobtemp is null then // Invalid state - cannot proceed
    return-2
  end if;
  select 1,trim(externalfilepath),date(createdat) into bAlreadyExists,xpath,xdate from blobstore where class = blobclass and id = blobid;
  if bAlreadyExists = 1 and isnull(xpath,'') <> '' then
    set bNewfilenameNeeded=BlobstoreDeleteExternal(xpath) -- Will be 0 if the file was successfully deleted 
  else
    set bNewfilenameNeeded=1
  end if;
  set bNewfilenameNeeded=1;  -- Changed in March 2022 to force it to always use a new file 
  set fsize=byte_length(iqblobtemp)/1024;
  set xfolder=BlobStoreExtFolder(current date);  -- Changed in March 2022 to force it to always use latest date folder 
  if xfolder is null then
    set xpath=null
  else
    if bNewfilenameNeeded <> 0 then
      set xpath=xfolder+blobclass+blobid+'.dat'
    end if;
    set bFail=xp_write_file(BlobstoreFullPath(xpath),iqblobtemp);
    if bFail <> 0 then -- Might fail because previously used folder cannot now be written or new folder does not yet exist
      set xfolder1=BlobstoreFullPath(xfolder);
      if left(xfolder1,1)='\\' then  -- \\ folders give AV in sp_create_directory
         set bfail=xp_cmdshell('mkdir '+xfolder1,'no_output')
      else
         set bfail=sp_create_directory(xfolder1)
      end if; 
      if bfail=0 then
        set xpath=xfolder+blobclass+blobid+'.dat';
        set bFail=xp_write_file(BlobstoreFullPath(xpath),iqblobtemp);
      end if;
    end if;
    if bFail = 0 then -- Successfully written to external file
      set iqblobtemp=null
    else -- Revert to internal storage. Alternative would be to return bFail as error value
      set xpath=null
    end if
  end if;
  if bAlreadyExists = 1 then
    update blobstore set blob = iqblobtemp,filesize=fsize,externalfilepath = xpath,zipped = blobzipped,changedby = isnull(requestedby,changedby),changedat = ifnull(requestedby,changedat,current timestamp) where
      class = blobclass and id = blobid
  else
    insert into blobstore( class,id,blob,externalfilepath,zipped,createdby,createdat,changedby,changedat,accessedby,accessedat,filesize) values( 
      blobclass,blobid,iqblobtemp,xpath,blobzipped,requestedby,current timestamp,requestedby,current timestamp,requestedby,current timestamp,fsize) 
  end if;
  set iqblobtemp=null; -- Free the memory
  return 0
end
}
</code>