New release of Perl DBD::ODBC with support for MS SQL Server_MySQL

I just released the 3^rddevelopment 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.