niota

This is an old revision of the document!

---

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
 
DELIMITER //
CREATE DEFINER=`niota`@`localhost` PROCEDURE `mdConfig_CatalogObjectBLOB`(
  IN CMDSYP_I        nvarchar(16)  -- CoMmanD SYstem Procedure           
 ,IN OPTSYP_I        nvarchar(128) -- OPTion SYstem Procedure
 ,IN APPCODE_I       nvarchar(5)   -- APPlication CoDe
 ,IN CATALOG_ID_I    integer       -- Catalog ID    
 ,IN BLOB_ID_I       nvarchar(32)  -- Binary Large OBject ID         
 ,IN FILENAME_I      nvarchar(256)     
)
this_sp: BEGIN
-- drop procedure if exists `mdConfig_CatalogObjectBLOB`
-- *******************************************************************************
-- author.........: Reinhard Reisinger
-- copyright......: ritconsult IT und Projektconsulting Ges.m.b.H.
-- date written...: 2020.12.12
-- 
-- manage etx_catalog_object_blob meta data entries
-- ===========+========+===== changelog ==========================================
-- YYYY.MM.DD | author | change
-- -----------+--------+----------------------------------------------------------
-- 2020.12.12   fmrre    created
--
-- *******************************************************************************
--	
 
declare loop_max bigint default 1000;
declare loop_max_c nvarchar(10);
 
declare row_number bigint default 0;
declare row_number_c nvarchar(10);
declare sep_mp varchar(01) default mdinfo_SysConstant('SEP_MSGPAR');
declare sep_sp varchar(01) default mdinfo_SysConstant('SEP_SYPPAR');
 
declare l_this_sp nvarchar(128) default 'mdConfig_CatalogObjectBLOB';
declare l_msg_id_final integer default mdinfo_SysConstant('MSG_ID_SP_ERR');
declare l_msg_par_final nvarchar(4000) default ' ';
declare l_message_text varchar(128) DEFAULT ' ';
DECLARE l_syscfg_secure_file_priv nvarchar(32) DEFAULT 'secure_file_priv';
 
declare l_cmdsyp nvarchar(16);
declare l_ind_cmdsyp bit default 0;
 
declare l_cmdsyp_add nvarchar(16) default 'ADD';
declare l_ind_cmdsyp_add bit default 0;
 
-- all possible system options
declare l_optsyp_withuca nvarchar(16) default 'WITHUCA';
declare l_ind_optsyp_withuca bit default 0;
 
declare l_optsyp nvarchar(128);
declare l_ind_optsyp bit default 0;
 
declare l_alloptsyp nvarchar(512);
declare l_allcmdsyp nvarchar(512);
 
-- internal strings (e.g. logging command and its options)
declare l_cmd_log varchar(16) default "LOG";
declare l_opt_cmdlog varchar(128) default "";
 
declare l_username nvarchar(16) default CURRENT_USER();
 
declare l_appcode nvarchar(5);
declare l_catalog_id INTEGER;
declare l_catalog_id_c nvarchar(10);
declare l_blob_id nvarchar(32);
declare l_filename nvarchar(256);
declare l_foldername nvarchar(256);
 
declare l_loadfile nvarchar(512);
declare l_len_blob integer;
declare l_len_blob_c nvarchar(10);
 
 
set l_cmdsyp = CMDSYP_I;
set l_optsyp = OPTSYP_I;
set l_appcode = APPCODE_I;
set l_catalog_id = CATALOG_ID_I;
set l_blob_id = BLOB_ID_I;
set l_filename = FILENAME_I;
 
 
SET l_foldername = mdinfo_SysConfigValue(mdinfo_SysConstant('APPCODE'), l_syscfg_secure_file_priv);
SET l_loadfile = CONCAT( coalesce(l_foldername,'uploadfolder') , '/' , coalesce(l_filename, 'uploadfile'));
 
-- set indicators for syscommand to true if the string is found in den CMDSYP-Parameter;
if locate(l_cmdsyp_add, l_cmdsyp, 1) > 0 then set l_ind_cmdsyp_add = 1; end if;
 
-- check if at least one command is valid
set l_ind_cmdsyp = l_ind_cmdsyp_add; -- | next;
set l_allcmdsyp = concat(l_cmdsyp_add); -- next
 
-- set indicators for sysoptions to true if the string is found in den OPTSYP-Parameter;
 
if locate(l_optsyp_withuca, l_optsyp, 1) > 0 then set l_ind_optsyp_withuca = 1; end if;
 
-- check if at least one option is valid
set l_ind_optsyp = l_ind_optsyp_withuca; -- | next;
set l_alloptsyp = concat(l_optsyp_withuca); -- , sep_sp, next
if l_optsyp = '' OR l_optsyp IS NULL then SET l_ind_optsyp = 1; END if;
 
main: begin
if l_ind_cmdsyp = 0 then set l_msg_par_final = concat(l_cmdsyp, sep_mp, l_allcmdsyp); leave main; end if;
if l_ind_optsyp = 0 then set l_msg_par_final = concat(l_optsyp, sep_mp, l_alloptsyp); leave main; end if;
 
-- forward the withuca - option to internal logging
if l_ind_optsyp_withuca then set l_opt_cmdlog = l_optsyp_withuca; end if;
 
add_blob: begin
if not l_ind_cmdsyp_add then leave add_blob; end if;
 
-- get the username from the option-string or current user
set l_username = coalesce(mdInfo_OptionConfigValue(l_optsyp, l_optsyp_withuca),  substring(current_user(),1,16));
 
 
INSERT INTO etx_catalog_object_blob 
 (appcode_blob, 
  catalog_id, 
  blob_id, 
  foldername_blob, 
  filename_blob, 
  blob_data, 
  ts_load, 
  username_load
 )
VALUES (
    l_appcode, 
    l_catalog_id, 
	 l_blob_id, 
	 l_foldername, 
	 l_filename, 
	LOAD_FILE(l_loadfile),
	CURRENT_TIMESTAMP, 
	 l_username)
ON DUPLICATE KEY
UPDATE
   foldername_blob = VALUES(foldername_blob), 
   filename_blob = VALUES(filename_blob), 
   blob_data = VALUES(blob_data),
   ts_load = VALUES(ts_load),
   username_load = VALUES(username_load)
;
 
SET row_number = ROW_COUNT();
 
set row_number_c = convert(row_number, char);
set l_catalog_id_c = convert(l_catalog_id, CHAR);
 
 
SET l_len_blob = 0;
SELECT 
  LENGTH(blob_data) 
INTO
  l_len_blob  
FROM  etx_catalog_object_blob 
WHERE appcode_blob = l_appcode 
 AND catalog_id = l_catalog_id 
 AND blob_id = l_blob_id;
 
 
set l_len_blob_c = convert(coalesce(l_len_blob,0), CHAR);
 
if row_number = 0 OR (l_len_blob IS NOT NULL AND l_len_blob = 0) OR l_len_blob IS null then
   set l_msg_id_final = 1072;
   set l_msg_par_final = concat(l_appcode, sep_mp, l_catalog_id_c, sep_mp, l_blob_id, sep_mp, l_loadfile);
else
   set l_msg_id_final = 1071;
   set l_msg_par_final = concat(l_appcode, sep_mp, l_catalog_id_c, sep_mp, l_blob_id, sep_mp, l_len_blob_c);
end if;
 
end add_blob;
end main;
 
if l_msg_id_final <> 0 then
   call mdLog_UserMessage(l_cmd_log, l_opt_cmdlog, l_username, l_this_sp, l_msg_id_final, l_msg_par_final, l_message_text);
END if;
 
 
if l_ind_cmdsyp = 0 or l_ind_optsyp = 0 then 
   SIGNAL SQLSTATE '45000'
   SET MESSAGE_TEXT = l_message_text;
end if;
 
end//
DELIMITER ;
 
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;