i just released the 3rddevelopment release of dbd::odbc (1.39_3). apart from a few bug fixes and other changes (see below) this release adds support for ms sql server query notification. query notification allows an application to request a notification from sql server when the results of a query change. once set up you can block on an execute call waiting for the query to change. here is an excerpt from the pod:
ms sql server query notificationquery notifications were introduced in sql server 2005 and sql server native client. query notifications allow applications to be notified when data has changed.
dbd::odbc supports query notification with ms sql server using the additional prepare attributes odbc_qn_msgtxt, odbc_qn_options and odbc_qn_timeout. when you pass suitable values for these attributes to the prepare method, dbd::odbc will make the appropriate sqlsetstmtattr calls after the statement has been allocated.
it is beyond the scope of this document to provide a tutorial on doing this but here are some notes that might help you get started.
on sql servercreate database mydatabasealter database mydatabase set enable_brokeruse mydatabasecreate table qntest (a int not null primary key, b nchar(5) not null, c datetime not null)insert qntest (a, b, c) select 1, 'alfki', '19991212'create queue myqueuecreate service myservice on queue myqueueto subscribe to query notification for this example# prepare the statement.
# this is the sql you want to know if the result changes later
my$sth=$dbh->prepare(q/select a, b, c from dbo.qntest where a = 1/,
                         {odbc_qn_msgtxt=>'message text',
                           odbc_qn_options=>'service=myservice;local database=mydatabase',
                           odbc_qn_timeout=>430000});
# fetch and display the result set value.
while(my@row=$sth->fetchrow_array){
   print@row/n;
}
# select * from sys.dm_qn_subscriptions will return a record now you are subscribed
to wait for notification# this query generates a result telling you which query has changed
# it will block until the timeout or the query changes
my$sth=$dbh->prepare(q/waitfor (receive * from myqueue)/);
$sth->execute();
# in the mean time someone does update dbo.qntest set c = '19981212' where a = 1
# fetch and display the result set value.
while(my@row=$sth->fetchrow_array){
     print@row/n;
}
# you now need to understand the result and look to decide which query has changed
now for the complete changes since 1.381.49_3 2014-05-01[change in behaviour]as warned years ago, this release removes the odbc_old_unicode attribute. if you have a good reason to use it speak up now before the next non-development release.[bug fixes]fix rt89255: fails to create test table for tests using postgresql odbc driver. change test suite to fallback on precision if column_size is not found.[enhancements]added support for ms sql server query notification. see the new section in the pod.added a currently undocumented (and experimental) odbc_describe_param method on a statement handle which takes a parameter number as the only argument and returns an array of the data type, parameter size, decimal digits and nullable (as per sqldescribeparam).[documentation]added faq on truncated column names with freetds.[miscellaneous]i have removed the experimental tag for odbc_getdiaffield and odbc_getdiagrec methods.1.49_2 2014-04-26[bug fixes]change to data_sources in 1.49_1 could lead to a compile error since data_sources was not returning a value if an error occurred.1.49_1 2014-04-25[bug fixes]if you had a lot of dsns on windows (more than 280 but it depends on the length of their names) and called the data_sources method it could crash your script. code internally changed to stop putting the dsns returned on the stack.[change in behaviour]as warned years ago, the private data_sources method has been removed - use dbi one instead.[miscellaneous]added faq entry of maximum number of allowed parameters.