New release of Perl DBD::ODBC with support for MS SQL Server Query Notification

I just released the 3rd development 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 Notification

Query 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 Server

  • Create database MyDatabase
  • ALTER DATABASE MyDatabase SET ENABLE_BROKER
  • use MyDatabase
  • CREATE 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 myQueue
  • CREATE SERVICE myService ON QUEUE myQueue

To 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.38

1.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.