For Oracle databases, a sample script is provided in the the file DatabaseNotify_Oracle.sql in the Repository/Component/CtsComponents subdirectory of your EAServer installation. The sample script below is for Sybase Adaptive Server Enterprise. Modifications are required for use on other databases:
use master
go
if not exists (select name from sysdatabases where name = "notifydb")
begin
create database notifydb
exec sp_dboption notifydb, "trunc log on chkpt", "true"
end
go
use notifydb
go
checkpoint
go
if not exists (select 1 from sysobjects where name="cms_notify" and type="U")
begin
create table cms_notify
(
id numeric(16,0) identity primary key,
type char(1) not null,
name varchar(100) not null,
message varchar(255) not null,
options varchar(255) not null
)
end
go
if not exists (select 1 from sysusers where name="guest")
exec sp_adduser guest
go
use sybsystemprocs
go
if exists (select 1 from sysobjects where name="sp_notify" and type="P")
drop proc sp_notify
go
create proc sp_notify
(@from numeric(16,0),
@last numeric(16,0))
as
if @from <= @last
delete from notifydb..cms_notify where id >= @from and id <= @last
declare @loop int
select @loop = 1
while @loop <= 60
begin
declare @rows int
select @rows = count(*) from notifydb..cms_notify
if @rows > 0
begin
set rowcount 100
select id, type, name, message, options
from notifydb..cms_notify
order by id
return
end
waitfor delay "00:00:01"
select @loop = @loop + 1
end
go
sp_procxmode sp_notify, anymode
go
grant execute on sp_notify to public
go
if exists (select 1 from sysobjects where name="sp_publish" and type="P")
drop proc sp_publish
go
create proc sp_publish
(@topic varchar(255),
@message varchar(255),
@options varchar(255))
as
insert into notifydb..cms_notify (type, name, message, options)
values ("T", @topic, @message, @options)
go
sp_procxmode sp_publish, anymode
go
grant execute on sp_publish to public
go
if exists (select 1 from sysobjects where name="sp_send" and type="P")
drop proc sp_send
go
create proc sp_send
(@topic varchar(255),
@message varchar(255),
@options varchar(255))
as
insert into notifydb..cms_notify (type, name, message, options)
values ("Q", @topic, @message, @options)
go
sp_procxmode sp_send, anymode
go
grant execute on sp_send to public
go
