<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
David<br>
<br>
I think the most 'elegant' solution would be to script write a set
of DML triggers that wrote to a log and you could poll that, AFAIK
the notifications system in 2008 only works on DDL events. At least
if you did it with a set of triggers then you wouldn't need a
timestamp column and you wouldn't need to alter your polling code
on each DB Change<br>
<br>
a quick script to gen the code would be something like<br>
<br>
DECLARE<br>
@SQL VARCHAR(MAX),<br>
@Template VARCHAR(MAX) = 'CREATE TRIGGER TR_#Tablename# ON
#Tablename# FOR INSERT, UPDATE AS<br>
BEGIN<br>
DECLARE<br>
@I INTEGER, <br>
@RowCount INTEGER = @@ROWCOUNT,<br>
@ErrorNumber INTEGER,<br>
@Action VARCHAR(10)<br>
<br>
IF @ROWCOUNT = 0 RETURN<br>
SET NOCOUNT ON;<br>
<br>
/* Get The Task */<br>
SET @I = (SELECT COUNT(*) FROM inserted) - (SELECT COUNT(*) FROM
deleted)<br>
SET @Action = <br>
CASE <br>
WHEN @I = 0 THEN ''Update''<br>
WHEN @I > 0 THEN ''INSERT''<br>
ELSE ''Delete''<br>
END<br>
<br>
INSERT INTO ChangeLog(TableName, Action, When)<br>
SELECT ''#Tablename#'', @Action, GETDATE()<br>
END <br>
go<br>
'<br>
<br>
DECLARE C1 CURSOR LOCAL STATIC FOR<br>
SELECT REPLACE(@Template, '#Tablename#', a.Name) FROM sysobjects
a WHERE xtype = 'u' <br>
OPEN C1<br>
FETCH NEXT FROM C1 INTO @SQL<br>
WHILE @@FETCH_STATUS = 0 BEGIN<br>
-- do work here <br>
PRINT @SQL<br>
FETCH NEXT FROM C1 INTO @SQL<br>
END<br>
CLOSE C1<br>
DEALLOCATE C1<br>
<br>
HTH Neven<br>
<blockquote
cite="mid:45218D834A0CE743BA57A2A8521AFC27032894@iccs-server.ICCS.local"
type="cite">
<pre wrap="">Yes, thanks. That is the solution at the moment, I thought there may be a more elegant one :-)
Crosstalk looks to be a brilliant solution for a number of issues here (mixed .net and native).
________________________________
From: <a class="moz-txt-link-abbreviated" href="mailto:delphi-bounces@listserver.123.net.nz">delphi-bounces@listserver.123.net.nz</a> on behalf of Xander van der Merwe
Sent: Tue 3/04/2012 9:51 a.m.
To: 'NZ Borland Developers Group - Delphi List'
Subject: Re: [DUG] SqlDependency
I very simple (but reliable) alternative approach might be to periodically (say every 30 seconds or whatever makes sense for your app) fire a simple query like "select max(DateUpdated) from MyTable" (assuming you have a DateUpdated column) and if the result returned is greater than the last value you have, you just do a refresh on that particular table/query in the UI.
Regards
From: <a class="moz-txt-link-abbreviated" href="mailto:delphi-bounces@listserver.123.net.nz">delphi-bounces@listserver.123.net.nz</a> [<a class="moz-txt-link-freetext" href="mailto:delphi-bounces@listserver.123.net.nz">mailto:delphi-bounces@listserver.123.net.nz</a>] On Behalf Of David O'Brien
Sent: Tuesday, 3 April 2012 8:47 a.m.
To: NZ Borland Developers Group - Delphi List
Subject: Re: [DUG] SqlDependency
Thanks.
From: <a class="moz-txt-link-abbreviated" href="mailto:delphi-bounces@listserver.123.net.nz">delphi-bounces@listserver.123.net.nz</a> [<a class="moz-txt-link-freetext" href="mailto:delphi-bounces@listserver.123.net.nz">mailto:delphi-bounces@listserver.123.net.nz</a>] On Behalf Of Jolyon Smith
Sent: Tuesday, 3 April 2012 8:08 a.m.
To: NZ Borland Developers Group - Delphi List
Subject: Re: [DUG] SqlDependency
As far as I can tell, SQLDepedency is just a high level wrapper around SQL Server Query Notifications - you could (if someone hasn't done it already) create such a high level wrapper yourself, or just build what you need on the core services themselves. Instead of Google'ing "SQLDependency", try instead "SQL Server Query Notification".
These articles in particular may be useful:
<a class="moz-txt-link-freetext" href="http://msdn.microsoft.com/en-us/library/ms130764.aspx">http://msdn.microsoft.com/en-us/library/ms130764.aspx</a>
<a class="moz-txt-link-freetext" href="http://www.simple-talk.com/sql/t-sql-programming/using-and-monitoring-sql-2005-query-notification/">http://www.simple-talk.com/sql/t-sql-programming/using-and-monitoring-sql-2005-query-notification/</a>
You will note that there is quite a bit of infrastructure required in the DB itself to get this stuff up and running and there are some quite specific conditions that have to be met by the query itself - it's not just a case of "notify me about this query". At the very least you need a queue and Service Broker to be running. Whether you are in a position to put that infrastructure in place may determine whether or not this approach is even viable in your case.
hth
On 3 April 2012 02:33, Jackson Gomes <a class="moz-txt-link-rfc2396E" href="mailto:jfdmg@hotmail.com"><jfdmg@hotmail.com></a> wrote:
you may try CrossTalk, which allows you to import .NET classes and Libs and use them on Delphi
<a class="moz-txt-link-freetext" href="http://www.atozed.com/crosstalk/index.en.aspx">http://www.atozed.com/crosstalk/index.en.aspx</a>
________________________________
Date: Mon, 2 Apr 2012 11:49:25 +1200
From: <a class="moz-txt-link-abbreviated" href="mailto:Dave@iccs.co.nz">Dave@iccs.co.nz</a>
To: <a class="moz-txt-link-abbreviated" href="mailto:delphi@listserver.123.net.nz">delphi@listserver.123.net.nz</a>
Subject: [DUG] SqlDependency
I would like to get SQL Server (2008 R2) to notify my app when data in a table changes.
I'm looking for something like the SqlDependency class in .net, but for an XE2 VCL (Pascal) app.
Does anyone know of a component, class, or way to replicate this (Not .net)?
Cheers,
Dave.
_______________________________________________ NZ Borland Developers Group - Delphi mailing list Post: <a class="moz-txt-link-abbreviated" href="mailto:delphi@listserver.123.net.nz">delphi@listserver.123.net.nz</a> Admin: <a class="moz-txt-link-freetext" href="http://delphi.org.nz/mailman/listinfo/delphi">http://delphi.org.nz/mailman/listinfo/delphi</a> Unsubscribe: send an email to <a class="moz-txt-link-abbreviated" href="mailto:delphi-request@listserver.123.net.nz">delphi-request@listserver.123.net.nz</a> with Subject: unsubscribe
_______________________________________________
NZ Borland Developers Group - Delphi mailing list
Post: <a class="moz-txt-link-abbreviated" href="mailto:delphi@listserver.123.net.nz">delphi@listserver.123.net.nz</a>
Admin: <a class="moz-txt-link-freetext" href="http://delphi.org.nz/mailman/listinfo/delphi">http://delphi.org.nz/mailman/listinfo/delphi</a>
Unsubscribe: send an email to <a class="moz-txt-link-abbreviated" href="mailto:delphi-request@listserver.123.net.nz">delphi-request@listserver.123.net.nz</a> with Subject: unsubscribe
</pre>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
NZ Borland Developers Group - Delphi mailing list
Post: <a class="moz-txt-link-abbreviated" href="mailto:delphi@listserver.123.net.nz">delphi@listserver.123.net.nz</a>
Admin: <a class="moz-txt-link-freetext" href="http://delphi.org.nz/mailman/listinfo/delphi">http://delphi.org.nz/mailman/listinfo/delphi</a>
Unsubscribe: send an email to <a class="moz-txt-link-abbreviated" href="mailto:delphi-request@listserver.123.net.nz">delphi-request@listserver.123.net.nz</a> with Subject: unsubscribe</pre>
</blockquote>
<br>
</body>
</html>