niota

This is an old revision of the document!

---

call mdConfig_CatalogObjectBLOB('ADD', 'WITHUCA($userName)', 'myApp', $catalogId, 'BULKLOAD', '$uploadFile')

mdConfig_CatalogObjectBLOB

/*!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 */;