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:

To enable Linux and UNIX applications to request a query notification subscription, the SQLGetStmtAttr and SQLSetStmtAttr attributes:

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:

SQLExecDirect( hStmt1, "WAITFOR (RECEIVE * FROM ContactChangeMessages)", 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 = 1

a 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

The SQL Server ODBC driver.

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_BROKER

You 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:

  1. Download and build unixODBC 2.3.3 or later.
  2. In your ODBC data source, use the DMStmtAttr attribute 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}
  3. 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 (Unified 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);
?>

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.
$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();
}
?>