4DotNet Developers Blog

Systeem Integratie is een fascinerende tak van de automatisering. Wat ooit begon als losstaande eilandjes kan op een mooie dag zomaar met elkaar verbonden worden. Nou ja, zomaar: ons werk heet dan wel automatisering maar dat betekent natuurlijk niet dat alles zomaar vanzelf gaat. Jaren geleden heb ik een Meldingensysteem gemaakt waarmee je fouten en wensen in applicaties kunt registreren. Hierbij kun je een prioriteit en een streefdatum invullen en ook bijhouden welke aanpassingen je hebt gedaan bij een melding. Als je daarbij ook releases bijhoudt, is het bovendien een prima basis voor releasenotes.

De mensen die mijn blog een beetje gevolgd hebben, weten dat ik recent bezig ben geweest met de applicatie Remember The Milk (RTM) : een tot do lijst waarbij je online taken kunt beheren. Sinds kort heb ik op eenvoudige wijze een koppeling gemaakt tussen deze twee applicaties. Het idee is dat er vanuit de database van het meldingensysteem een e-mail wordt gestuurd naar mijn geheime RTM e-mail adres, voorzien van relevante tags. Iedereen die zich aanmeldt bij RTM krijgt een ‘geheim’ e-mail adres. Wanneer je een bericht stuurt naar dit e-mail adres, wordt hiervan een taak voor je aangemaakt. Door in deze mail speciale codes op te nemen, kun je direct bepaalde kenmerken meegeven, zoals een categorie of een duedate.

Met deze koppeling is het bijvoorbeeld mogelijk om van niet opgeloste meldingen die tegen hun einddatum aanzitten een selectie te maken en deze als een extra reminder toe te voegen aan de takenlijst van RTM.

Hieronder staat het belangrijkste deel van de stored procedure die de mail genereert.

CREATE PROCEDURE stp_VerstuurMail
 
	 @NewID		int	= NULL
 
AS
BEGIN
SET NOCOUNT ON
----------------------------------------------------------------------------
--  declaration of variables
	DECLARE @ontvanger		varchar(100)
	DECLARE @onderwerp		varchar(200)
	DECLARE @tekst			varchar(8000)
	DECLARE @NewLineChar	        char(2)
	Declare @projectOms		varchar(50)
----------------------------------------------------------------------------
--  assignment of variables
	SET	@NewLineChar = CHAR(13) + CHAR(10)
	SELECT	@ProjectOms  = ProjectOms
	FROM	Project INNER JOIN
		Melding ON Melding.projectID = Project.ProjectID
	WHERE	MeldingID = @NewID
 
	IF @ProjectOms IS NOT NULL
		BEGIN
			SET @ProjectOms = ', ' + @ProjectOms
		END
	ELSE
		BEGIN
			SET @ProjectOms = ''
		END
 
        SET	@ontvanger = 'rene_xxxxxxxxxxxxx_@rmilk.com'
----------------------------------------------------------------------------
-- processing
 
BEGIN
 
	SELECT	@onderwerp = MeldingNr + ' ' + LEFT(MeldingOms, 100),
		@tekst     = 'Due: ' + Convert(char(10), isnull(Streefdatum,getdate()), 105) + @NewLineChar +
		     	     'Tags: ReleaseNotes' + @ProjectOms +
			     ', ' + MeldingSoortCode					     + @NewLineChar +
			     'List: computer' 		   				     + @NewLineChar +
			     '---'							     + @NewLineChar +
    			     MeldingOms							     + @NewLineChar +
			     '-end-'
	FROM	MELDING	INNER JOIN
		MeldingSoort ON Melding.MeldingSoortID = MeldingSoort.MeldingSoortID
	WHERE	MeldingID = @NewID
 
	-- e-mail sturen
	EXEC msdb.dbo.sp_send_dbmail
		@profile_name	= 'René',
		@recipients	= @ontvanger,
		@body		= @tekst,
		@subject	= @onderwerp
 
	UPDATE	Melding
	SET	MailDatum = GETDATE()
	WHERE	MeldingID = @NewID
END

Bij mijn opdrachtgever is het een goede gewoonte stored procedures op te delen in kleinere overzichtelijke stukken. Bij het beheer hebben we hier veel profijt van. Ik heb dit principe ook in deze stored procedure toegepast: eerst is er de declaratie van variabelen, daarna een gedeelte waarin deze variabelen een waarde krijgen. De feitelijke query (processing) staat aan het eind.

De variabele @NewLineChar gebruik je om een regelovergang mee aan te geven.
Het RTM e-mailadres eindigt op @rmilk.com. De x-jes staan er normaal niet, maar die heb ik er maar even bij gezet om de hackers niet op ideeën te brengen.
De woorden Due:, Tags: en List: zijn keywords, hierdoor weet RTM de einddatum, categorie en de lijst waarin deze melding terecht komt. Voor deze meldingen gebruik ik de weinig creatieve naam computer. Voor de tags doe ik iets meer moeite: hiervoor gebruik ik kenmerken uit het meldingensysteem.  De eerste 100 karakters van de onderliggende melding komen in de takenlijst. De volledige melding wordt in RTM als een notitie toegevoegd.

sp_send_dbmail

Voor het versturen van de mail gebruik ik een standaard stored procedure uit SQL Server: sp_send_dbmail. Hiervoor is het wel nodig dat je bij Database Mail eenmalig een profile aanmaakt waarin je je eigen mailprovider invult en opgeeft onder welke naam de e-mails verstuurd worden. In de Object Expolorer van de SQL Server Enterprice Manager vind je dit onder het kopje Management.

Hier wil ik het voorlopig maar weer even bij laten: ik zie nl. dat er nog een aantal meldingen open staan die vandaag eigenlijk nog afgehandeld moeten worden…

Stem of voeg toe aan :