How do I use SQL Server query notifications from Linux and UNIX?
Query notifications, introduced in SQL Server 2005, allow an application to request a notification from SQL Server when the underlying data of a query changes. Query notifications reduce round-trips to the database. Database round-trips can be particularly expensive for applications that fetch multiple result sets. For example, a web page with multiple drop-down boxes where each drop-down box is populated by running a database query.
To request a query notification subscription, an application submits:
- A query.
- A query notification options string that specifies the service that will handle notifications for the query.
- A query notification message that provides a way of identifying the notification. For example, the message might specify details about the query whose result set the application is being notified about.
- A query notification timeout. This is optional.
To enable Linux and UNIX applications to request a query notification subscription, the SQLGetStmtAttr and SQLSetStmtAttr attributes:
SQL_SOPT_SS_QUERYNOTIFICATION_OPTIONSSQL_SOPT_SS_QUERYNOTIFICATION_MSGTEXTSQL_SOPT_SS_QUERYNOTIFICATION_TIMEOUT
The following example uses the SQL Server ODBC driver to request a notification if data in the specified columns of the AdventureWorks Person.Contact table changes.
SQLSetStmtAttr( hStmt, SQL_SOPT_SS_QUERYNOTIFICATION_OPTIONS, "service=ContactChangeNotifications", SQL_NTS ); SQLSetStmtAttr( hStmt, SQL_SOPT_SS_QUERYNOTIFICATION_MSGTEXT, "AdventureWorks Person.Contact table", SQL_NTS ); SQLSetStmtAttr( hStmt, SQL_SOPT_SS_QUERYNOTIFICATION_TIMEOUT, 3600, SQL_NTS ); SQLExecDirect( hStmt, "SELECT ContactID, FirstName, LastName, EmailAddress, EmailPromotion FROM Person.Contact WHERE EmailPromotion IS NOT NULL;", SQL_NTS ); To receive notifications, applications use the Transact-SQL RECEIVE command. For example:
SQLSetStmtAttr( hStmt, SQL_SOPT_SS_QUERYNOTIFICATION_OPTIONS, "service=ContactChangeNotifications", SQL_NTS );
SQLSetStmtAttr( hStmt, SQL_SOPT_SS_QUERYNOTIFICATION_MSGTEXT, "AdventureWorks Person.Contact table", SQL_NTS );
SQLSetStmtAttr( hStmt, SQL_SOPT_SS_QUERYNOTIFICATION_TIMEOUT, 3600, SQL_NTS );
SQLExecDirect( hStmt, "SELECT ContactID, FirstName, LastName, EmailAddress, EmailPromotion FROM Person.Contact
WHERE EmailPromotion IS NOT NULL;", SQL_NTS );If the relevant data changes, the application will receive the notification. For example, if this UPDATE statement is executed:
UPDATE Person.Contact
SET
LastName = 'Smith'
WHERE
ContactID = 1a notification is returned to the application:
<qn:QueryNotification xmlns:qn="http://schemas.microsoft.com/SQL/Notifications/QueryNotification"
id="28" type="change" source="data" info="update" database_id="7"
sid="0x0105000000000005150000007700B1F6FE6845CE97BAB682E8030000">
<qn:Message>Person.Contact has changed</qn:Message>
</qn:QueryNotification>Examples
Prerequisite software and configuration
To request a query notification from Perl, you need DBD::ODBC 1.49 or later.
Notifications are delivered through a SQL Server Service Broker queue that applications may poll for available notifications. You enable Service Broker on the database. This example is for the AdventureWorks database:
USE master
ALTER DATABASE AdventureWorks
SET
ENABLE_BROKERYou need to create a Service Broker service and queue. The Service Broker uses the service to deliver messages to the correct queue. Queues store messages. When a message arrives for a service, the Service Broker puts the message on the queue associated with the service. This example creates a queue to store messages about changes to the AdventureWorks Person.Contact table.
USE AdventureWorks
CREATE QUEUE ContactChangeMessages
CREATE SERVICE ContactChangeNotifications
ON QUEUE ContactChangeMessages
([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);Now grant the SQL Server permissions required to subscribe for query notifications. Replace MyUser with the login for the user who will request the query notification. (The user you specify will need SELECT permission on the table containing the data you want to query.)
GRANT RECEIVE ON QueryNotificationErrorsQueue TO "MyUser"
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO "MyUser"Note Run the statements in the order shown or in the same batch (in any order). Otherwise you'll get an error similar to the following when attempting to subscribe for query notifications:
User "mynewuser" does not have permission to request query notification subscriptions on database "AdventureWorks".
On the Linux or UNIX machine where you install the SQL Server ODBC driver, add an ODBC data source that connects to the SQL Server database containing the data you want to know has changed. In the data source, specify the user who has permission to subscribe for query notifications. For example:
[SQLSERVER_SAMPLE] Driver=Easysoft ODBC-SQL Server Server=MyMachine\sqlexpress User=MyUser Password=password Database=AdventureWorks
Example: Requesting a query notification from Perl
DBD::ODBC 1.49+ supports query notifications through 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 makes the appropriate SQLSetStmtAttr calls after the statement has been allocated.
Subscribing for query notifications
use strict;
use DBI;
my $data_source = q/dbi:ODBC:SQLSERVER_SAMPLE/;
my $user = q/MyUser/;
my $password = q/password/;
# Connect to the data source and get a handle for that connection.
my $dbh = DBI->connect($data_source, $user, $password)
or die "Can't connect to $data_source: $DBI::errstr";
# We want to know if the data returned by running this query changes.
# Not all queries are compatible with query notifications. Refer to
# the SQL Server documentation for further information:
# http://technet.microsoft.com/en-us/library/ms181122(v=sql.105).aspx
my $sql = "SELECT ContactID, FirstName, LastName, EmailAddress, EmailPromotion FROM Person.Contact
WHERE EmailPromotion IS NOT NULL;";
# If you want to leave the query notification timeout set to its default (5 days), omit the odbc_qn_timeout attribute.
my $sth = $dbh->prepare($sql, {odbc_qn_msgtxt => 'Person.Contact has changed',
odbc_qn_options => 'service=ContactChangeNotifications',
odbc_qn_timeout=> 3600})
or die "Can't prepare statement: $DBI::errstr";
$sth->execute();Waiting for notification
# Avoid "String data, right truncation" error when retrieving
# the message.
$dbh->{LongReadLen} = 800;
# This query retrieves the query notification message.
# It will block until the timeout expires or the query's underlying
# data changes.
my $sth = $dbh->prepare(q/WAITFOR (RECEIVE * FROM ContactChangeMessages)/);
$sth->execute();
# If the relevant data changes, display the notification message. (For the AdventureWorks
# example shown in this article, a query notification message would be returned if
# someone ran "UPDATE Person.Contact SET LastName = 'Smith' WHERE ContactID = 1".
# This message will contain the text you specified with the odbc_qn_msgtxt attribute.
while ( my @row = $sth->fetchrow_array ) {
print "@row\n";
}Example: Requesting a query notification from C
Waiting for notification
#ifdef WIN32
#include <windows.h>
#endif
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>
#include <stdlib.h>
#include <string.h>
#include <sqlncli.h>
void extract_error(
char *fn,
SQLHANDLE handle,
SQLSMALLINT type)
{
SQLSMALLINT i = 0;
SQLINTEGER native;
SQLCHAR state[ 7 ];
SQLCHAR text[256];
SQLSMALLINT len;
SQLRETURN ret;
fprintf(stderr,
"\n"
"The driver reported the following diagnostics whilst running "
"%s\n\n",
fn);
do
{
ret = SQLGetDiagRec(type, handle, ++i, state, &native, text,
sizeof(text), &len );
if (SQL_SUCCEEDED(ret))
printf("%s:%d:%ld:%s\n", state, i, native, text);
}
while( ret == SQL_SUCCESS );
printf("\nPress enter to exit.\n");
getchar() ;
}
main()
{
/* ODBC data source. */
SQLCHAR szDSN[] = "SQLSERVER_SAMPLE";
SQLRETURN ret;
SQLHANDLE envHandle;
SQLHANDLE hStmt;
SQLHANDLE conHandle;
int i, j;
SQLSMALLINT cols;
char str1[ 64 ], str2[ 128 ];
SQLLEN ival1, ival2;
SQLSMALLINT typ, scale, nul, sptr;
SQLULEN def;
SQL_DATE_STRUCT ts1;
SQLINTEGER i1;
SQLGUID guid;
SQLCHAR name[ 64 ];
SQLSMALLINT nlen, dtyp, dig, isnul;
SQLULEN csize;
SQLCHAR sql[ 128 ], buffer[ 1024 ];
SQLLEN len;
ret = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &envHandle );
if ( ret != SQL_SUCCESS)
printf( "SQLAllocHandle failed" );
ret = SQLSetEnvAttr( envHandle, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_UINTEGER );
if ( ret != SQL_SUCCESS)
printf( "SQLSetEnvAttr conn failed" );
ret = SQLAllocHandle( SQL_HANDLE_DBC, envHandle, &conHandle );
if ( ret != SQL_SUCCESS)
printf( "SQLAllocHandle conn failed" );
ret = SQLConnect( conHandle, szDSN, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS);
printf( "ret = %d\n", ret );
if ( !SQL_SUCCEEDED( ret ))
{
extract_error("SQLDriverConnect conHandle", conHandle, SQL_HANDLE_DBC);
exit(1);
}
ret = SQLAllocHandle( SQL_HANDLE_STMT, conHandle, &hStmt );
if ( !SQL_SUCCEEDED( ret ))
{
extract_error("SQLAllocStmt conHandle", conHandle, SQL_HANDLE_DBC);
exit(1);
}
while( 1 ) {
/* This query retrieves the query notification message. */
/* It will block until the timeout expires or the query's underlying */
/* data changes. */
ret = SQLExecDirect( hStmt, "WAITFOR (RECEIVE * FROM ContactChangeMessages)", SQL_NTS );
printf( "ret = %d\n", ret );
ret = SQLNumResultCols( hStmt, &cols );
printf( "cols = %d %d\n", cols, ret );
/* If the relevant data changes, display the notification message. (For the AdventureWorks */
/* example shown in this article, a query notification message would be returned if */
/* someone ran "UPDATE Person.Contact SET LastName = 'Smith' WHERE ContactID = 1". */
/* This message will contain the text you specified with the odbc_qn_msgtxt attribute. */
while (1) {
ret = SQLFetch( hStmt );
if ( ret != SQL_SUCCESS ) {
break;
}
printf( ": " );
for ( i = 0; i < cols; i ++ ) {
ret = SQLGetData( hStmt, i + 1, SQL_C_CHAR, buffer, sizeof( buffer ), &len );
if ( len == SQL_NULL_DATA ) {
printf( "NULL" );
}
else {
if ( i == 13 ) {
unsigned short val;
char x[ 5 ];
memcpy( x, buffer, 4 );
x[ 4 ] = '\0';
val = strtol( x, NULL, 16 );
val &= 0xFFFF;
if ( val == 0xFFFE ) {
for ( j = 4; j < len; j += 4 ) {
memcpy( x, buffer + j, 4 );
x[ 4 ] = '\0';
val = strtol( x, NULL, 16 );
val &= 0xFFFF;
if ( val & 0x00FF ) {
printf( "." );
}
else {
val >>= 8;
printf( "%c", val );
}
}
}
else {
printf( "%s", buffer );
}
}
else {
printf( "%s", buffer );
}
}
printf( " : " );
}
printf( "\n" );
}
}
SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
SQLDisconnect(conHandle);
SQLFreeHandle(SQL_HANDLE_DBC, conHandle);
SQLFreeHandle(SQL_HANDLE_ENV, envHandle);
return SQL_SUCCESS;
}Subscribing for query notifications
#ifdef WIN32
#include <windows.h>
#endif
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>
#include <stdlib.h>
#include <string.h>
#include <sqlncli.h>
void extract_error(
char *fn,
SQLHANDLE handle,
SQLSMALLINT type)
{
SQLSMALLINT i = 0;
SQLINTEGER native;
SQLCHAR state[ 7 ];
SQLCHAR text[256];
SQLSMALLINT len;
SQLRETURN ret;
fprintf(stderr,
"\n"
"The driver reported the following diagnostics whilst running "
"%s\n\n",
fn);
do
{
ret = SQLGetDiagRec(type, handle, ++i, state, &native, text,
sizeof(text), &len );
if (SQL_SUCCEEDED(ret))
printf("%s:%d:%ld:%s\n", state, i, native, text);
}
while( ret == SQL_SUCCESS );
printf("\nPress enter to exit.\n");
getchar() ;
}
main()
{
/* ODBC data source. */
SQLCHAR szDSN[] = "SQLSERVER_SAMPLE";
SQLRETURN ret;
SQLHANDLE envHandle;
SQLHANDLE hStmt1;
SQLHANDLE conHandle;
ret = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &envHandle );
if ( ret != SQL_SUCCESS)
printf( "SQLAllocHandle failed" );
ret = SQLSetEnvAttr( envHandle, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_UINTEGER );
if ( ret != SQL_SUCCESS)
printf( "SQLSetEnvAttr conn failed" );
ret = SQLAllocHandle( SQL_HANDLE_DBC, envHandle, &conHandle );
if ( ret != SQL_SUCCESS)
printf( "SQLAllocHandle conn failed" );
ret = SQLConnect( conHandle, szDSN, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS);
printf( "ret = %d\n", ret );
if ( !SQL_SUCCEEDED( ret ))
{
extract_error("SQLDriverConnect conHandle", conHandle, SQL_HANDLE_DBC);
exit(1);
}
ret = SQLAllocHandle( SQL_HANDLE_STMT, conHandle, &hStmt1 );
if ( !SQL_SUCCEEDED( ret ))
{
extract_error("SQLAllocStmt conHandle", conHandle, SQL_HANDLE_DBC);
exit(1);
}
ret = SQLSetStmtAttr( hStmt1, SQL_SOPT_SS_QUERYNOTIFICATION_MSGTEXT, "Person.Contact has changed",
SQL_NTS );
ret = SQLSetStmtAttr( hStmt1, SQL_SOPT_SS_QUERYNOTIFICATION_OPTIONS, "service=ContactChangeNotifications",
SQL_NTS );
/* If you want to leave the query notification timeout set to its default (5 days), omit this line.
ret = SQLSetStmtAttr( hStmt1, SQL_SOPT_SS_QUERYNOTIFICATION_TIMEOUT, "3600", SQL_NTS );
/* We want to know if the data returned by running this query changes. */
/* Not all queries are compatible with query notifications. Refer to */
/* the SQL Server documentation for further information: */
/* http://technet.microsoft.com/en-us/library/ms181122(v=sql.105).aspx */
ret = SQLExecDirect( hStmt1, "SELECT ContactID, FirstName, LastName, EmailAddress, EmailPromotion "
"FROM Person.Contact "
"WHERE EmailPromotion IS NOT NULL", SQL_NTS );
printf( "ret = %d\n", ret );
SQLFreeHandle(SQL_HANDLE_STMT, hStmt1);
SQLDisconnect(conHandle);
SQLFreeHandle(SQL_HANDLE_DBC, conHandle);
SQLFreeHandle(SQL_HANDLE_ENV, envHandle);
return SQL_SUCCESS;
}Example: Requesting a query notification from PHP
Setting query notification attributes
PHP does not provide a way of setting custom SQLSetStmtAttr attributes such as the ones required to set the query notification message and options string. To work around this, unixODBC 2.3.3 and later provides a way of setting non standard statement attributes via DMStmtAttr.
At the time of writing, unixODBC 2.3.3 is available as a pre-release on the unixODBC FTP site.
To set the query notification attributes with unixODBC:
- Download and build unixODBC 2.3.3 or later.
- In your ODBC data source, use the
DMStmtAttrattribute to configure your query notification request:DMStmtAttr=[1233]=\timeout;[1234]={message};[1235]={service=service}For example, to follow along with the example shown in this article, you would add this line:
DMStmtAttr=[1234]={Person.Contact has changed};[1235]={service=ContactChangeNotifications} - Make sure that your PHP ODBC interface is using the unixODBC Driver Manager you have just built as opposed to the Driver Manager supplied with your PHP package or operating system or ODBC driver. To do this, set the appropriate linker environment variable or create a symbolic link to the 2.3.3+ Driver Manager. For example:
LD_LIBRARY_PATH=/usr/local/lib php subscribe.php
Changing the default cursor type
By default, the PHP ODBC interfaces use a static cursor, and this cursor type is incompatible with Microsoft's requirements for notification queries. To work around this, change the cursor type to forward only, by setting the odbc.default_cursortype php.ini option to 0:
odbc.default_cursortype = 0
Waiting for notification (Unified ODBC)
<?php
$data_source='SQLSERVER_SAMPLE';
$user='MyUser';
$password='password';
// Connect to the data source and get a handle for that connection.
$conn=odbc_connect($data_source,$user,$password);
if (!$conn){
exit("Connection Failed:" . odbc_errormsg() );
}
// This query retrieves the query notification message.
// It will block until the timeout expires or the query's underlying
// data changes.
$sql="WAITFOR (RECEIVE status, priority, queuing_order, conversation_group_id, conversation_handle,
message_sequence_number, service_name, service_id, service_contract_name, service_contract_id,
message_type_name, message_type_id, validation, CAST(message_body as varchar(500)) AS message_body,
message_enqueue_time FROM ContactChangeMessages)";
// Execute the statement.
$rs=odbc_exec($conn,$sql);
// Fetch and display the result set.
if (!$rs){
exit("Error in SQL");
}
// If the relevant data changes, display the notification message. (For the AdventureWorks
// example shown in this article, a query notification message would be returned if
// someone ran "UPDATE Person.Contact SET LastName = 'Smith' WHERE ContactID = 1".
// This message will contain the text you specified with the odbc_qn_msgtxt attribute.
while (odbc_fetch_row($rs)){
$col1=odbc_result($rs, "status");
$col2=odbc_result($rs, "priority");
$col3=odbc_result($rs, "queuing_order");
$col4=odbc_result($rs, "conversation_group_id");
$col5=odbc_result($rs, "conversation_handle");
$col6=odbc_result($rs, "message_sequence_number");
$col7=odbc_result($rs, "service_name");
$col8=odbc_result($rs, "service_id");
$col9=odbc_result($rs, "service_contract_name");
$col10=odbc_result($rs, "service_contract_id");
$col11=odbc_result($rs, "message_type_name");
$col12=odbc_result($rs, "message_type_id");
$col13=odbc_result($rs, "validation");
$col14=odbc_result($rs, "message_body");
$col15=odbc_result($rs, "message_enqueue_time");
echo "$col1\n";
echo "$col2\n";
echo "$col3\n";
echo "$col4\n";
echo "$col5\n";
echo "$col6\n";
echo "$col7\n";
echo "$col8\n";
echo "$col9\n";
echo "$col10\n";
echo "$col11\n";
echo "$col12\n";
echo "$col13\n";
echo "$col14\n";
echo "$col15\n";
}
// Disconnect the database from the database handle.
odbc_close($conn);
?>
Waiting for notification (PDO_ODBC)
<?php
$dsn ="odbc:SQLSERVER_SAMPLE";
try {
// Connect to the data source and get a database handle for that connection.
$dbh = new PDO($dsn);
// This query retrieves the query notification message.
// It will block until the timeout expires or the query's underlying
// data changes.
$stmt = $dbh->prepare("WAITFOR (RECEIVE status, priority, queuing_order, conversation_group_id,
conversation_handle, message_sequence_number, service_name, service_id,
service_contract_name, service_contract_id, message_type_name,
message_type_id, validation, CAST(message_body as varchar(500)) as message_body,
message_enqueue_time FROM ContactChangeMessages)");
$stmt->execute();
// If the relevant data changes, display the notification message. (For the AdventureWorks
// example shown in this article, a query notification message would be returned if
// someone ran "UPDATE Person.Contact SET LastName = 'Smith' WHERE ContactID = 1".
// This message will contain the text you specified with the DMStmtAttr attribute.
while ($row = $stmt->fetch()) {
print_r($row);
}
$stmt = NULL;
$dbh = NULL;
} catch (PDOException $e) {
echo "Failed to connect: " . $e->getMessage();
}
?>Subscribing for query notifications (PDO_ODBC)
<?php
// This data source needs to set query notification attributes via
// DMStmtAttr. For example:
// [SQLSERVER_SAMPLE_REQUEST_QN]
// Driver=Easysoft ODBC-SQL Server
// Server=myserver\sqlexpress
// User=mynewuser
// Password=mypassword
// DMStmtAttr=[1234]={Person.Contact has changed};[1235]={service=ContactChangeNotifications}
// You need unixODBC 2.3.3 or later.
$data_source='SQLSERVER_SAMPLE_REQUEST_QN';
$user='MyUser';
$password='password';
// Connect to the data source and get a handle for that connection.
$conn=odbc_connect($data_source,$user,$password);
if (!$conn){
exit("Connection Failed:" . odbc_errormsg() );
}
// We want to know if the data returned by running this query changes.
// Not all queries are compatible with query notifications. Refer to
// the SQL Server documentation for further information:
// http://technet.microsoft.com/en-us/library/ms181122(v=sql.105).aspx
// You also need to change the default cursor type by setting
// odbc.default_cursortype to 0 in php.ini.
$sql="SELECT ContactID, FirstName, LastName, EmailAddress, EmailPromotion FROM Person.Contact
WHERE EmailPromotion IS NOT NULL AND ContactID = 1;";
// Execute the statement.
$rs=odbc_exec($conn,$sql);
// Fetch and display the result set.
if (!$rs){
exit("Error in SQL");
}
while (odbc_fetch_row($rs)){
$col1=odbc_result($rs, "ContactID");
$col2=odbc_result($rs, "FirstName");
$col3=odbc_result($rs, "LastName");
$col4=odbc_result($rs, "EmailAddress");
$col5=odbc_result($rs, "EmailPromotion");
echo "$col1\t";
echo "$col2\t";
echo "$col3\t";
echo "$col4\t";
echo "$col5\n";
}
// Disconnect the database from the database handle.
odbc_close($conn);
?>
<?php
// This data source needs to set query notification attributes via
// DMStmtAttr. For example:
// [SQLSERVER_SAMPLE_REQUEST_QN]
// Driver=Easysoft ODBC-SQL Server
// Server=myserver\sqlexpress
// User=mynewuser
// Password=mypassword
// DMStmtAttr=[1234]={Person.Contact has changed};[1235]={service=ContactChangeNotifications}
// You need unixODBC 2.3.3 or later.
$dsn ="odbc:SQLSERVER_SAMPLE_REQUEST_QN";
try {
// Connect to the data source and get a database handle for that connection.
$dbh = new PDO($dsn);
// We want to know if the data returned by running this query changes.
// Not all queries are compatible with query notifications. Refer to
// the SQL Server documentation for further information:
// http://technet.microsoft.com/en-us/library/ms181122(v=sql.105).aspx
// You also need to change the default cursor type by setting
// odbc.default_cursortype to 0 in php.ini.
$stmt = $dbh->prepare("SELECT ContactID, FirstName, LastName, EmailAddress, EmailPromotion FROM Person.Contact
WHERE EmailPromotion IS NOT NULL AND ContactID = 1;");
$stmt->execute();
while ($row = $stmt->fetch()) {
print_r($row);
}
$stmt = NULL;
$dbh = NULL;
} catch (PDOException $e) {
echo "Failed to connect: " . $e->getMessage();
}
?>