Post by Richard HughesHi all,
I needed support for SQL Server 2005's Query Notifications feature, so
here's a patch against tip.
I've only given the patch a light sprinkling of testing so far, but
could I ask somebody to review it and confirm that I'm on the right
track so that I can create a v2 patch that is (hopefully) mergeable?
Both the Microsoft API and the TDS implementation feel extremely
kludgy to me and hence some of that kludginess needed to be reflected
in the patch; sorry about that, but don't shoot the messenger.
Query Notifications are designed to support client-side caching of
datasets by providing a mechanism to allow the application to be
notified whenever its cache may have become out of date (i.e. because
somebody updated the underlying table(s)). Fundamentally the feature
works by delivering a query to the server and, rather than executing
it, the server does internal magic to register that query and a
user-specified cookie. When the result set that the query would have
returned (had it actually been executed) changes, the cookie is
delivered back to the caller through a WAITFOR/RECEIVE statement.
User-level doc: http://technet.microsoft.com/en-us/library/ms130764.aspx
Protocol doc: http://msdn.microsoft.com/en-us/library/dd304949.aspx
Only the registering of the query and cookie with the server needs
FreeTDS changes. The WAITFOR statement is just executed as a normal
statement with a normal result set.
Richard.
The patch is really good and fit very well with FreeTDS coding standards.
The big concern is that I think that this feature works also for every
queries not for just sql ones. I'll add some comments on your code. I
think we should test what happens for prepared statements or RPCs.
Post by Richard Hughesdiff --git a/include/freetds/odbc.h b/include/freetds/odbc.h
index 4413b61..1a75080 100644
--- a/include/freetds/odbc.h
+++ b/include/freetds/odbc.h
@@ -379,6 +379,9 @@ struct _hsattr
/* SQLGetStmtAttr only */
/* TDS_DESC *imp_row_desc; */
/* TDS_DESC *imp_param_desc; */
+ DSTR qn_msgtext;
+ DSTR qn_options;
+ SQLLEN qn_timeout;
I think should be SQLINTEGER here. Protocol and OLEDB use 32 bit,
SQLLEN is 64 on 64 bit platforms. This change ABI in SQLSetStmtAttr
and similars.
Post by Richard Hughes};
typedef enum
diff --git a/include/freetds/tds.h b/include/freetds/tds.h
index 413f1d8..d9d0fc5 100644
--- a/include/freetds/tds.h
+++ b/include/freetds/tds.h
@@ -1278,6 +1278,7 @@ TDSRET tds_submit_query_params(TDSSOCKET * tds, const char *query, TDSPARAMINFO
TDSRET tds_submit_queryf(TDSSOCKET * tds, const char *queryf, ...);
TDSRET tds_submit_prepare(TDSSOCKET * tds, const char *query, const char *id, TDSDYNAMIC ** dyn_out, TDSPARAMINFO * params);
TDSRET tds_submit_execdirect(TDSSOCKET * tds, const char *query, TDSPARAMINFO * params);
+TDSRET tds72_submit_query_notification(TDSSOCKET * tds, const char *query, const char *msgtext, const char *options, TDS_INT timeout);
TDSRET tds71_submit_prepexec(TDSSOCKET * tds, const char *query, const char *id, TDSDYNAMIC ** dyn_out, TDSPARAMINFO * params);
TDSRET tds_submit_execute(TDSSOCKET * tds, TDSDYNAMIC * dyn);
TDSRET tds_send_cancel(TDSSOCKET * tds);
diff --git a/include/odbcss.h b/include/odbcss.h
index 6582559..bb5f5b3 100644
--- a/include/odbcss.h
+++ b/include/odbcss.h
@@ -22,3 +22,6 @@
#define SQL_DIAG_SS_MSGSTATE (-1150)
#define SQL_DIAG_SS_LINE (-1154)
+#define SQL_SOPT_SS_QUERYNOTIFICATION_TIMEOUT 1233
+#define SQL_SOPT_SS_QUERYNOTIFICATION_MSGTEXT 1234
+#define SQL_SOPT_SS_QUERYNOTIFICATION_OPTIONS 1235
diff --git a/src/odbc/native.c b/src/odbc/native.c
index decfcb9..eb8f6d1 100644
--- a/src/odbc/native.c
+++ b/src/odbc/native.c
@@ -36,6 +36,7 @@
#include <assert.h>
#include <freetds/odbc.h>
+#include <freetds/string.h>
#ifdef DMALLOC
#include <dmalloc.h>
@@ -228,6 +229,11 @@ prepare_call(struct _hstmt * stmt)
else
return SQL_ERROR;
+ if (tds_dstr_isempty(&stmt->attr.qn_msgtext) != tds_dstr_isempty(&stmt->attr.qn_options))
+ return SQL_SUCCESS_WITH_INFO;
+ if (!tds_dstr_isempty(&stmt->attr.qn_msgtext) && !IS_TDS72_PLUS(stmt->dbc->tds_socket->conn))
+ return SQL_SUCCESS_WITH_INFO;
+
I'm not sure this is the right place. Also I think MS add some error
to the statement. I didn't find from documentation which error should
be reported.
Post by Richard Hughesif ((rc = to_native(stmt->dbc, stmt, buf)) != SQL_SUCCESS)
return rc;
diff --git a/src/odbc/odbc.c b/src/odbc/odbc.c
index 2f57ac3..613b17e 100644
--- a/src/odbc/odbc.c
+++ b/src/odbc/odbc.c
@@ -55,6 +55,7 @@
#include <freetds/convert.h>
#include "replacements.h"
#include "sqlwparams.h"
+#include <odbcss.h>
#ifdef DMALLOC
#include <dmalloc.h>
@@ -71,9 +72,9 @@ static SQLRETURN _SQLFreeEnv(SQLHENV henv);
static SQLRETURN _SQLFreeStmt(SQLHSTMT hstmt, SQLUSMALLINT fOption, int force);
static SQLRETURN _SQLFreeDesc(SQLHDESC hdesc);
static SQLRETURN _SQLExecute(TDS_STMT * stmt);
-static SQLRETURN _SQLSetStmtAttr(SQLHSTMT hstmt, SQLINTEGER Attribute, SQLPOINTER ValuePtr, SQLINTEGER StringLength);
+static SQLRETURN _SQLSetStmtAttr(SQLHSTMT hstmt, SQLINTEGER Attribute, SQLPOINTER ValuePtr, SQLINTEGER StringLength WIDE);
static SQLRETURN _SQLGetStmtAttr(SQLHSTMT hstmt, SQLINTEGER Attribute, SQLPOINTER Value, SQLINTEGER BufferLength,
- SQLINTEGER * StringLength);
+ SQLINTEGER * StringLength WIDE);
static SQLRETURN _SQLColAttribute(SQLHSTMT hstmt, SQLUSMALLINT icol, SQLUSMALLINT fDescType, SQLPOINTER rgbDesc,
SQLSMALLINT cbDescMax, SQLSMALLINT FAR * pcbDesc, SQLLEN FAR * pfDesc _WIDE);
static SQLRETURN _SQLFetch(TDS_STMT * stmt, SQLSMALLINT FetchOrientation, SQLLEN FetchOffset);
@@ -1070,10 +1071,10 @@ SQLParamOptions(SQLHSTMT hstmt, SQLULEN crow, SQLULEN FAR * pirow)
tdsdump_log(TDS_DBG_FUNC, "SQLParamOptions(%p, %lu, %p)\n", hstmt, (unsigned long int)crow, pirow);
/* emulate for ODBC 2 DM */
- res = _SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMS_PROCESSED_PTR, pirow, 0);
+ res = _SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMS_PROCESSED_PTR, pirow, 0 _wide0);
if (res != SQL_SUCCESS)
return res;
- return _SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER) (TDS_INTPTR) crow, 0);
+ return _SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER) (TDS_INTPTR) crow, 0 _wide0);
}
ODBC_FUNC(SQLPrimaryKeys, (P(SQLHSTMT,hstmt), PCHARIN(CatalogName,SQLSMALLINT),
@@ -1744,6 +1745,9 @@ _SQLAllocStmt(SQLHDBC hdbc, SQLHSTMT FAR * phstmt)
assert(stmt->ird->header.sql_desc_rows_processed_ptr == NULL);
stmt->attr.simulate_cursor = SQL_SC_NON_UNIQUE;
stmt->attr.use_bookmarks = SQL_UB_OFF;
+ tds_dstr_init(&stmt->attr.qn_msgtext);
+ tds_dstr_init(&stmt->attr.qn_options);
+ stmt->attr.qn_timeout = 432000;
stmt->sql_rowset_size = 1;
@@ -1760,7 +1764,7 @@ _SQLAllocStmt(SQLHDBC hdbc, SQLHSTMT FAR * phstmt)
*phstmt = (SQLHSTMT) stmt;
if (dbc->attr.cursor_type != SQL_CURSOR_FORWARD_ONLY)
- _SQLSetStmtAttr(stmt, SQL_CURSOR_TYPE, (SQLPOINTER) (TDS_INTPTR) dbc->attr.cursor_type, SQL_IS_INTEGER);
+ _SQLSetStmtAttr(stmt, SQL_CURSOR_TYPE, (SQLPOINTER) (TDS_INTPTR) dbc->attr.cursor_type, SQL_IS_INTEGER _wide0);
ODBC_EXIT_(dbc);
}
@@ -3281,7 +3285,12 @@ _SQLExecute(TDS_STMT * stmt)
stmt->row_count = TDS_NO_COUNT;
- if (stmt->prepared_query_is_rpc) {
+ if (!tds_dstr_isempty(&stmt->attr.qn_msgtext)) {
+ char *name = stmt->query;
+ if (!name)
+ name = stmt->prepared_query;
Here you use prepared query but this usually came with some required
parameters you don't use.
Post by Richard Hughes+ ret = tds72_submit_query_notification(tds, stmt->query, tds_dstr_cstr(&stmt->attr.qn_msgtext), tds_dstr_cstr(&stmt->attr.qn_options), stmt->attr.qn_timeout);
+ } else if (stmt->prepared_query_is_rpc) {
/* TODO support stmt->apd->header.sql_desc_array_size for RPC */
/* get rpc name */
/* TODO change method */
@@ -4203,6 +4212,8 @@ _SQLFreeStmt(SQLHSTMT hstmt, SQLUSMALLINT fOption, int force)
odbc_errs_reset(&stmt->errs);
odbc_unlock_statement(stmt);
tds_dstr_free(&stmt->cursor_name);
+ tds_dstr_free(&stmt->attr.qn_msgtext);
+ tds_dstr_free(&stmt->attr.qn_options);
desc_free(stmt->ird);
desc_free(stmt->ipd);
desc_free(stmt->orig_ard);
@@ -4283,7 +4294,7 @@ _SQLFreeDesc(SQLHDESC hdesc)
}
static SQLRETURN
-_SQLGetStmtAttr(SQLHSTMT hstmt, SQLINTEGER Attribute, SQLPOINTER Value, SQLINTEGER BufferLength, SQLINTEGER * StringLength)
+_SQLGetStmtAttr(SQLHSTMT hstmt, SQLINTEGER Attribute, SQLPOINTER Value, SQLINTEGER BufferLength, SQLINTEGER * StringLength WIDE)
{
void *src;
size_t size;
@@ -4438,6 +4449,20 @@ _SQLGetStmtAttr(SQLHSTMT hstmt, SQLINTEGER Attribute, SQLPOINTER Value, SQLINTEG
size = sizeof(stmt->sql_rowset_size);
src = &stmt->sql_rowset_size;
break;
+ size = sizeof(stmt->attr.qn_timeout);
+ src = &stmt->attr.qn_timeout;
+ break;
+ {
+ SQLRETURN rc = odbc_set_string_oct(stmt->dbc, Value, BufferLength, StringLength, tds_dstr_cstr(&stmt->attr.qn_msgtext), tds_dstr_len(&stmt->attr.qn_msgtext));
+ ODBC_EXIT(stmt, rc);
+ }
+ {
+ SQLRETURN rc = odbc_set_string_oct(stmt->dbc, Value, BufferLength, StringLength, tds_dstr_cstr(&stmt->attr.qn_options), tds_dstr_len(&stmt->attr.qn_options));
+ ODBC_EXIT(stmt, rc);
+ }
/* TODO SQL_COLUMN_SEARCHABLE, although ODBC2 */
odbc_errs_add(&stmt->errs, "HY092", NULL);
@@ -4458,7 +4483,7 @@ SQLGetStmtAttr(SQLHSTMT hstmt, SQLINTEGER Attribute, SQLPOINTER Value, SQLINTEGE
tdsdump_log(TDS_DBG_FUNC, "SQLGetStmtAttr(%p, %d, %p, %d, %p)\n",
hstmt, (int)Attribute, Value, (int)BufferLength, StringLength);
- return _SQLGetStmtAttr(hstmt, Attribute, Value, BufferLength, StringLength);
+ return _SQLGetStmtAttr(hstmt, Attribute, Value, BufferLength, StringLength _wide0);
}
#ifdef ENABLE_ODBC_WIDE
@@ -4468,7 +4493,7 @@ SQLGetStmtAttrW(SQLHSTMT hstmt, SQLINTEGER Attribute, SQLPOINTER Value, SQLINTEG
tdsdump_log(TDS_DBG_FUNC, "SQLGetStmtAttr(%p, %d, %p, %d, %p)\n",
hstmt, (int)Attribute, Value, (int)BufferLength, StringLength);
- return _SQLGetStmtAttr(hstmt, Attribute, Value, BufferLength, StringLength);
+ return _SQLGetStmtAttr(hstmt, Attribute, Value, BufferLength, StringLength, 1);
}
#endif
#endif
@@ -4479,7 +4504,7 @@ SQLGetStmtOption(SQLHSTMT hstmt, SQLUSMALLINT fOption, SQLPOINTER pvParam)
tdsdump_log(TDS_DBG_FUNC, "SQLGetStmtOption(%p, %d, %p)\n",
hstmt, fOption, pvParam);
- return _SQLGetStmtAttr(hstmt, (SQLINTEGER) fOption, pvParam, SQL_MAX_OPTION_STRING_LENGTH, NULL);
+ return _SQLGetStmtAttr(hstmt, (SQLINTEGER) fOption, pvParam, SQL_MAX_OPTION_STRING_LENGTH, NULL _wide0);
}
SQLRETURN ODBC_PUBLIC ODBC_API
@@ -6253,7 +6278,7 @@ SQLSetConnectOptionW(SQLHDBC hdbc, SQLUSMALLINT fOption, SQLULEN vParam)
#endif
static SQLRETURN
-_SQLSetStmtAttr(SQLHSTMT hstmt, SQLINTEGER Attribute, SQLPOINTER ValuePtr, SQLINTEGER StringLength)
+_SQLSetStmtAttr(SQLHSTMT hstmt, SQLINTEGER Attribute, SQLPOINTER ValuePtr, SQLINTEGER StringLength WIDE)
{
SQLULEN ui = (SQLULEN) (TDS_INTPTR) ValuePtr;
SQLUSMALLINT *usip = (SQLUSMALLINT *) ValuePtr;
@@ -6525,6 +6550,47 @@ _SQLSetStmtAttr(SQLHSTMT hstmt, SQLINTEGER Attribute, SQLPOINTER ValuePtr, SQLIN
}
stmt->sql_rowset_size = ui;
break;
+ if (*lp < 1) {
+ odbc_errs_add(&stmt->errs, "HY024", NULL);
+ break;
+ }
+ stmt->attr.qn_timeout = *lp;
+ break;
+ if (!IS_VALID_LEN(StringLength)) {
+ odbc_errs_add(&stmt->errs, "HY090", NULL);
+ break;
+ }
+ {
+ DSTR s;
+
+ tds_dstr_init(&s);
+ if (!odbc_dstr_copy_oct(stmt->dbc, &s, StringLength, (ODBC_CHAR*) ValuePtr)) {
+ odbc_errs_add(&stmt->errs, "HY001", NULL);
+ break;
+ }
+ tds_dstr_free(&stmt->attr.qn_msgtext);
+ stmt->attr.qn_msgtext = s;
+ }
I don't think setting on a temporary is needed. odbc_dstr_copy_oct
should take care of it.
Post by Richard Hughes+ break;
+ if (!IS_VALID_LEN(StringLength)) {
+ odbc_errs_add(&stmt->errs, "HY090", NULL);
+ break;
+ }
+ {
+ DSTR s;
+
+ tds_dstr_init(&s);
+ if (!odbc_dstr_copy_oct(stmt->dbc, &s, StringLength, (ODBC_CHAR*) ValuePtr)) {
+ odbc_errs_add(&stmt->errs, "HY001", NULL);
+ break;
+ }
+ tds_dstr_free(&stmt->attr.qn_options);
+ stmt->attr.qn_options = s;
+ }
+ break;
odbc_errs_add(&stmt->errs, "HY092", NULL);
break;
@@ -6539,7 +6605,7 @@ SQLSetStmtAttr(SQLHSTMT hstmt, SQLINTEGER Attribute, SQLPOINTER ValuePtr, SQLINT
tdsdump_log(TDS_DBG_FUNC, "SQLSetStmtAttr(%p, %d, %p, %d)\n",
hstmt, (int)Attribute, ValuePtr, (int)StringLength);
- return _SQLSetStmtAttr(hstmt, Attribute, ValuePtr, StringLength);
+ return _SQLSetStmtAttr(hstmt, Attribute, ValuePtr, StringLength _wide0);
}
#ifdef ENABLE_ODBC_WIDE
@@ -6549,7 +6615,7 @@ SQLSetStmtAttrW(SQLHSTMT hstmt, SQLINTEGER Attribute, SQLPOINTER ValuePtr, SQLIN
tdsdump_log(TDS_DBG_FUNC, "SQLSetStmtAttr(%p, %d, %p, %d)\n",
hstmt, (int)Attribute, ValuePtr, (int)StringLength);
- return _SQLSetStmtAttr(hstmt, Attribute, ValuePtr, StringLength);
+ return _SQLSetStmtAttr(hstmt, Attribute, ValuePtr, StringLength, 1);
}
#endif
#endif
@@ -6560,7 +6626,7 @@ SQLSetStmtOption(SQLHSTMT hstmt, SQLUSMALLINT fOption, SQLULEN vParam)
tdsdump_log(TDS_DBG_FUNC, "SQLSetStmtOption(%p, %u, %u)\n", hstmt, fOption, (unsigned)vParam);
/* XXX: Lost precision */
- return _SQLSetStmtAttr(hstmt, (SQLINTEGER) fOption, (SQLPOINTER) (TDS_INTPTR) vParam, SQL_NTS);
+ return _SQLSetStmtAttr(hstmt, (SQLINTEGER) fOption, (SQLPOINTER) (TDS_INTPTR) vParam, SQL_NTS _wide0);
}
ODBC_FUNC(SQLSpecialColumns, (P(SQLHSTMT,hstmt), P(SQLUSMALLINT,fColType), PCHARIN(CatalogName,SQLSMALLINT),
@@ -7269,10 +7335,10 @@ SQLSetScrollOptions(SQLHSTMT hstmt, SQLUSMALLINT fConcurrency, SQLLEN crowKeyset
ODBC_EXIT_(stmt);
}
- _SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER) (TDS_INTPTR) cursor_type, 0);
- _SQLSetStmtAttr(hstmt, SQL_ATTR_CONCURRENCY, (SQLPOINTER) (TDS_INTPTR) fConcurrency, 0);
- _SQLSetStmtAttr(hstmt, SQL_ATTR_KEYSET_SIZE, (SQLPOINTER) (TDS_INTPTR) crowKeyset, 0);
- _SQLSetStmtAttr(hstmt, SQL_ROWSET_SIZE, (SQLPOINTER) (TDS_INTPTR) crowRowset, 0);
+ _SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER) (TDS_INTPTR) cursor_type, 0 _wide0);
+ _SQLSetStmtAttr(hstmt, SQL_ATTR_CONCURRENCY, (SQLPOINTER) (TDS_INTPTR) fConcurrency, 0 _wide0);
+ _SQLSetStmtAttr(hstmt, SQL_ATTR_KEYSET_SIZE, (SQLPOINTER) (TDS_INTPTR) crowKeyset, 0 _wide0);
+ _SQLSetStmtAttr(hstmt, SQL_ROWSET_SIZE, (SQLPOINTER) (TDS_INTPTR) crowRowset, 0 _wide0);
ODBC_EXIT_(stmt);
}
diff --git a/src/tds/query.c b/src/tds/query.c
index a076255..2c1ac3e 100644
--- a/src/tds/query.c
+++ b/src/tds/query.c
@@ -312,6 +312,76 @@ tds_start_query(TDSSOCKET *tds, unsigned char packet_type)
}
/**
+ * tds72_submit_query_notification() sends a request for the server to call
+ * us back when the results of the query would have changed; although this
+ * involves sending the query to the server, the server does not execute it.
+ * \tds
+ * \param query query to analyze for change detection. There are specific
+ * limitations on the statement which depend on the server;
+ * consult your server's documentation
+ * \param msgtext arbitrary extra data to be passed along with the
+ * notification request and which will be delivered back to
+ * the client when the notification happens
+ * \param options specification of how the notification is to be delivered.
+ * Consult your server's documentatin for syntax
+ * \param timeout number of seconds to keep the notification request active
+ * before automatically expiring it (with a notification)
+ * \return TDS_FAIL or TDS_SUCCESS
+ */
+TDSRET
+tds72_submit_query_notification(TDSSOCKET * tds, const char *query, const char *msgtext, const char *options, TDS_INT timeout)
+{
+ size_t query_len;
+ int qn_len;
+ const char *converted_msgtext;
+ const char *converted_options;
+ size_t converted_msgtext_len;
+ size_t converted_options_len;
+
+ CHECK_TDS_EXTRA(tds);
+ if (!query || !msgtext || !options || !IS_TDS72_PLUS(tds->conn))
+ return TDS_FAIL;
+
+ if (tds_set_state(tds, TDS_QUERYING) != TDS_QUERYING)
+ return TDS_FAIL;
+
+ converted_msgtext = tds_convert_string(tds, tds->conn->char_convs[client2ucs2], msgtext, (int)strlen(msgtext), &converted_msgtext_len);
+ if (!converted_msgtext) {
+ tds_set_state(tds, TDS_IDLE);
+ return TDS_FAIL;
+ }
+
+ converted_options = tds_convert_string(tds, tds->conn->char_convs[client2ucs2], options, (int)strlen(options), &converted_options_len);
+ if (!converted_options) {
+ tds_convert_string_free(query, converted_msgtext);
+ tds_set_state(tds, TDS_IDLE);
+ return TDS_FAIL;
+ }
+ qn_len = 2 + converted_msgtext_len + 2 + converted_options_len + 4;
+
+ query_len = strlen(query);
+ tds->out_flag = TDS_QUERY;
+ tds_put_int(tds, 4 + 18 + 6 + qn_len); /* total length */
+ tds_put_int(tds, 18); /* length: transaction descriptor */
+ tds_put_smallint(tds, 2); /* type: transaction descriptor */
+ tds_put_n(tds, tds->conn->tds72_transaction, 8); /* transaction */
+ tds_put_int(tds, 1); /* request count */
+ tds_put_int(tds, 6 + qn_len); /* length: query notification */
+ tds_put_smallint(tds, 1); /* type: query notification */
+ tds_put_smallint(tds, converted_msgtext_len); /* notifyid */
+ tds_put_n(tds, converted_msgtext, converted_msgtext_len);
+ tds_put_smallint(tds, converted_options_len); /* ssbdeployment */
+ tds_put_n(tds, converted_options, converted_options_len);
+ tds_put_int(tds, timeout); /* timeout */
+ tds_put_string(tds, query, (int)query_len);
+
+ tds_convert_string_free(query, converted_msgtext);
+ tds_convert_string_free(query, converted_options);
+
+ return tds_query_flush_packet(tds);
+}
+
+/**
* tds_submit_query_params() sends a language string to the database server for
* processing. TDS 4.2 is a plain text message with a packet type of 0x01,
* TDS 7.0 is a unicode string with packet type 0x01, and TDS 5.0 uses a
I think we should send this header not only for normal query, perhaps
tds_start_query is the better place to put it.
Frediano