On 11/04/2011 03:00 PM, Sean Kelly wrote:
nless something has changed in the last few years, the way to do this in MSSQL
is via a plugin in the SQL server itself. Back in the days of yore this was
via extended stored procedures, which were loaded as a DLL by the SQL server,
but I think more recent iterations can call COM or .NET code or something like
that as well. In short, the event notification isn't typically done via a
back-feed through a client connection. Do any modern DBMSes have pub/sub
functionality like this?
F.I. PostgreSQL
1)
CREATE OR REPLACE FUNCTION notify_demo() RETURNS TRIGGER AS '
BEGIN
NOTIFY demoApp;
RETURN NULL;
END;
2)
CREATE TRIGGER demo AFTER UPDATE
ON users FOR EACH ROW
EXECUTE PROCEDURE notify_demo();
3) C#
using System;
using Npgsql;
namespace TriggerDemo
{
class Program
{
static void Main(string[] args)
{
string connString = "Server=127.0.0.1;Port=5432;User
Id=postgres;Password=pass;SyncNotification=true";
NpgsqlConnection conn = new NpgsqlConnection(connString);
try
{
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand("listen
demoApp;", conn);
cmd.ExecuteNonQuery();
conn.Notification += new
NotificationEventHandler(conn_Notification);
Console.ReadLine();
conn.Close();
}
catch (NpgsqlException ex)
{
Console.WriteLine(ex.Message);
Console.ReadLine();
}
}
static void conn_Notification(object sender,
NpgsqlNotificationEventArgs e)
{
Console.WriteLine("Row was updated");
}
}
}
Firebird is very simular.
I think MSSQL doesn't need a plugin. I have some test-code hanging
around, the in case that it is more than just being curious, I can send
you a copy. Guess it was SQLServer 2008, But the dotnet code is pretty
complex.
Regarding MySQL--Simply has no support for server side events.
Bjoern