New release of Perl DBD::ODBC with support for MS SQL Server_MySQL
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.
- Added FAQ entry of maximum number of allowed parameters.
[MISCELLANEOUS]
上一篇: 新版node.js_v6安装详解
下一篇: C#图片加水印实例与代码