labtech Database

Parameters

Name	Type	Mode

Definition

BEGIN
	DECLARE done INT DEFAULT FALSE;
	DECLARE originalOSName VARCHAR(255) DEFAULT '';
	DECLARE formattedOSName VARCHAR(255) DEFAULT '';
	DECLARE os_cursor CURSOR FOR SELECT DISTINCT computers.os FROM computers JOIN hotfix WHERE computers.computerid = hotfix.computerid;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
		OPEN os_cursor;
		update_os: LOOP
		FETCH os_cursor INTO originalOSName;
		IF done THEN
			LEAVE update_os;
		END IF;           
		# Call FormatOsForPatching to format the operating system name for patching
		SET formattedOSName = FormatOsForPatching(originalOSName);
		#Update the hotfix/hotfixdata tables to have the correct OS names
		INSERT IGNORE INTO hotfixdata (`hotfixid`, `kbid`, `title`, `category`, `downloadurl`, `filename`, `switches`, `restart`, `os`, `date_added`, `description`, `supporturl`, `severity`, `categoryname`, `patchtype`, `uninstall`, `ignore`, `discovered`, `missing`, `labtechapproved`, `communityapproved`, `userapproved`) (SELECT hotfixdata.`hotfixid`, hotfixdata.`kbid`, hotfixdata.`title`, hotfixdata.`category`, hotfixdata.`downloadurl`, hotfixdata.`filename`, hotfixdata.`switches`, hotfixdata.`restart`, formattedOSName, hotfixdata.`date_added`, hotfixdata.`description`, hotfixdata.`supporturl`, hotfixdata.`severity`, hotfixdata.`categoryname`, hotfixdata.`patchtype`, hotfixdata.`uninstall`, hotfixdata.`ignore`, hotfixdata.`discovered`, hotfixdata.`missing`, hotfixdata.`labtechapproved`, hotfixdata.`communityapproved`, hotfixdata.`userapproved` FROM hotfixdata JOIN hotfix ON hotfix.os = hotfixdata.os AND hotfix.hotfixid = hotfixdata.hotfixid JOIN computers ON computers.computerid = hotfix.computerid WHERE computers.os = originalOSName AND hotfixdata.os <> BINARY formattedOSName);
		UPDATE hotfix JOIN computers on hotfix.computerid = computers.computerid SET hotfix.os = formattedOSName WHERE computers.os  = originalOSName AND hotfix.os <> BINARY formattedOSName;
		END LOOP update_os;               
		CLOSE os_cursor;
	#Delete any entries in the hotfix/hotfixdata tables that are obsolete
	DELETE hotfix FROM hotfix LEFT JOIN computers ON hotfix.computerid = computers.computerid WHERE computers.computerid IS NULL;
	DELETE hotfixdata FROM hotfixdata LEFT JOIN hotfix ON hotfix.os = hotfixdata.os AND hotfix.hotfixid = hotfixdata.hotfixid WHERE hotfix.os IS NULL OR hotfix.hotfixid IS NULL;
END

sp_UpdateHotfixOSData

Parameters

Definition