pears.BlobstoreWrite

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
}