====== 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>
create function "pears"."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>