pears.BlobstoreWrite

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