I need to set some ODBC statement attributes but my application does not let me do this. What can I do?

The unixODBC attribute DMStmtAttr lets you set statement attributes in your ODBC data source. The process for setting standard ODBC statement attributes (those defined in the ODBC specification) is described in our ODBC on Linux and UNIX platforms.

Some ODBC drivers provide non-standard statement attributes that allow database-specific functionality to be requested or configured. In unixODBC 2.3.3 and later, you can also use DMStmtAttr to set these custom statement attributes:

  1. Download and build unixODBC 2.3.3 or later.
  2. In the relevant ODBC driver header file, locate the statement attributes you want to set.

    For example, the header file sqlncli.h contains a number of SQL Server statement attributes. This header file is included in the SQL Server ODBC driver distribution. For example:

    $ cd /usr/local/easysoft/sqlserver/include
$ vi sqlncli.h

    We wanted to request a SQL Server query notification from PHP. The relevant statement attributes for us were:

    #define SQL_SOPT_SS_BASE                            1225
.
.
.
/* Query notification options */
#define SQL_SOPT_SS_QUERYNOTIFICATION_TIMEOUT   (SQL_SOPT_SS_BASE+8) 
#define SQL_SOPT_SS_QUERYNOTIFICATION_MSGTEXT   (SQL_SOPT_SS_BASE+9)
#define SQL_SOPT_SS_QUERYNOTIFICATION_OPTIONS   (SQL_SOPT_SS_BASE+10)
  3. Note down the integer value for each attribute you want to set.

    For example, we wanted to set all three query notification options. The integer values for these attributes were 1333 (1225+8), 1334 and 1335.

  4. In your ODBC data source, use the DMStmtAttr attribute to set the statement attribute values.

    The format is:

    DMStmtAttr=[xxxx]=\yyyy;[xxxx]={ssss}

    where xxxx is the integer attribute to set, yyyy is a decimal numeric value and ssss is a string value.

    We used DMStmtAttr to request a query notification subscription, set a timeout, and define a query notification message:

    DMStmtAttr=[1233]=\3600;[1234]={Person.Contact has changed};[1235]={service=ContactChangeNotifications}
  5. Make sure that your application is using the unixODBC Driver Manager you have just built as opposed to the Driver Manager supplied with your application 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