Add support for setting SQL Server transaction isolation level

4 files changed, 83 insertions, 0 deletions

diff --git a/odb/mssql/connection.cxx b/odb/mssql/connection.cxx
index 4c69a0c..6310bf3 100644
--- a/odb/mssql/connection.cxx
+++ b/odb/mssql/connection.cxx
@@ -18,6 +18,15 @@ namespace odb
 {
   namespace mssql
   {
+    static const long transaction_isolation_map[] =
+    {
+      SQL_TXN_READ_UNCOMMITTED,
+      SQL_TXN_READ_COMMITTED,
+      SQL_TXN_REPEATABLE_READ,
+      SQL_TXN_SS_SNAPSHOT,
+      SQL_TXN_SERIALIZABLE
+    };
+
     connection::
     connection (database_type& db)
         : odb::connection (db),
@@ -63,6 +72,40 @@ namespace odb
       if (!SQL_SUCCEEDED (r))
         translate_error (r, handle_, SQL_HANDLE_DBC);
 
+      // Set transaction isolation level.
+      //
+      transaction_isolation ti (db_.transaction_isolation ());
+      switch (ti)
+      {
+      case isolation_read_committed:
+        {
+          break; // SQL Server default.
+        }
+      case isolation_read_uncommitted:
+      case isolation_repeatable_read:
+      case isolation_serializable:
+        {
+          r = SQLSetConnectAttrA (handle_,
+                                  SQL_ATTR_TXN_ISOLATION,
+                                  (SQLPOINTER) transaction_isolation_map[ti],
+                                  0);
+          if (!SQL_SUCCEEDED (r))
+            translate_error (r, handle_, SQL_HANDLE_DBC);
+          break;
+        }
+      case isolation_snapshot:
+        {
+          r = SQLSetConnectAttrA (handle_,
+                                  SQL_COPT_SS_TXN_ISOLATION,
+                                  (SQLPOINTER) transaction_isolation_map[ti],
+                                  SQL_IS_INTEGER);
+
+          if (!SQL_SUCCEEDED (r))
+            translate_error (r, handle_, SQL_HANDLE_DBC);
+          break;
+        }
+      }
+
       // Connect.
       //
       {
diff --git a/odb/mssql/database.cxx b/odb/mssql/database.cxx
index 1c42c80..388a32f 100644
--- a/odb/mssql/database.cxx
+++ b/odb/mssql/database.cxx
@@ -27,6 +27,7 @@ namespace odb
               const std::string& server,
               const std::string& driver,
               const std::string& extra_connect_string,
+              transaction_isolation_type transaction_isolation,
               SQLHENV environment,
               transfer_ptr<connection_factory> factory)
         : odb::database (id_mssql),
@@ -38,6 +39,7 @@ namespace odb
           server_ (server),
           driver_ (driver),
           extra_connect_string_ (extra_connect_string),
+          transaction_isolation_ (transaction_isolation),
           environment_ (environment),
           factory_ (factory.transfer ())
     {
@@ -53,6 +55,7 @@ namespace odb
               const std::string& instance,
               const std::string& driver,
               const std::string& extra_connect_string,
+              transaction_isolation_type transaction_isolation,
               SQLHENV environment,
               transfer_ptr<connection_factory> factory)
         : odb::database (id_mssql),
@@ -65,6 +68,7 @@ namespace odb
           port_ (0),
           driver_ (driver),
           extra_connect_string_ (extra_connect_string),
+          transaction_isolation_ (transaction_isolation),
           environment_ (environment),
           factory_ (factory.transfer ())
     {
@@ -79,6 +83,7 @@ namespace odb
               unsigned int port,
               const std::string& driver,
               const std::string& extra_connect_string,
+              transaction_isolation_type transaction_isolation,
               SQLHENV environment,
               transfer_ptr<connection_factory> factory)
         : odb::database (id_mssql),
@@ -90,6 +95,7 @@ namespace odb
           port_ (port),
           driver_ (driver),
           extra_connect_string_ (extra_connect_string),
+          transaction_isolation_ (transaction_isolation),
           environment_ (environment),
           factory_ (factory.transfer ())
     {
@@ -98,11 +104,13 @@ namespace odb
 
     database::
     database (const string& connect_string,
+              transaction_isolation_type transaction_isolation,
               SQLHENV environment,
               transfer_ptr<connection_factory> factory)
         : odb::database (id_mssql),
           protocol_ (protocol_auto),
           port_ (0),
+          transaction_isolation_ (transaction_isolation),
           connect_string_ (connect_string),
           environment_ (environment),
           factory_ (factory.transfer ())
@@ -115,12 +123,14 @@ namespace odb
               char* argv[],
               bool erase,
               const std::string& extra_connect_string,
+              transaction_isolation_type transaction_isolation,
               SQLHENV environment,
               transfer_ptr<connection_factory> factory)
         : odb::database (id_mssql),
           protocol_ (protocol_auto),
           port_ (0),
           extra_connect_string_ (extra_connect_string),
+          transaction_isolation_ (transaction_isolation),
           environment_ (environment),
           factory_ (factory.transfer ())
     {
diff --git a/odb/mssql/database.hxx b/odb/mssql/database.hxx
index e73a332..a133634 100644
--- a/odb/mssql/database.hxx
+++ b/odb/mssql/database.hxx
@@ -40,10 +40,20 @@ namespace odb
       protocol_np   // Named pipes.
     };
 
+    enum transaction_isolation
+    {
+      isolation_read_uncommitted,
+      isolation_read_committed,   // SQL Server default.
+      isolation_repeatable_read,
+      isolation_snapshot,
+      isolation_serializable
+    };
+
     class LIBODB_MSSQL_EXPORT database: public odb::database
     {
     public:
       typedef mssql::protocol protocol_type;
+      typedef mssql::transaction_isolation transaction_isolation_type;
 
       // Connect to the specified server using the latest available SQL
       // Server Native Client ODBC driver by default. If user is empty,
@@ -56,6 +66,7 @@ namespace odb
                 const std::string& server,
                 const std::string& driver = "",
                 const std::string& extra_connect_string = "",
+                transaction_isolation_type = isolation_read_committed,
                 SQLHENV environment = 0,
                 details::transfer_ptr<connection_factory> =
                   details::transfer_ptr<connection_factory> ());
@@ -74,6 +85,7 @@ namespace odb
                 const std::string& instance = "",
                 const std::string& driver = "",
                 const std::string& extra_connect_string = "",
+                transaction_isolation_type = isolation_read_committed,
                 SQLHENV environment = 0,
                 details::transfer_ptr<connection_factory> =
                   details::transfer_ptr<connection_factory> ());
@@ -88,6 +100,7 @@ namespace odb
                 unsigned int port,
                 const std::string& driver = "",
                 const std::string& extra_connect_string = "",
+                transaction_isolation_type = isolation_read_committed,
                 SQLHENV environment = 0,
                 details::transfer_ptr<connection_factory> =
                   details::transfer_ptr<connection_factory> ());
@@ -96,6 +109,7 @@ namespace odb
       // conection string.
       //
       database (const std::string& connect_string,
+                transaction_isolation_type = isolation_read_committed,
                 SQLHENV environment = 0,
                 details::transfer_ptr<connection_factory> =
                   details::transfer_ptr<connection_factory> ());
@@ -119,6 +133,7 @@ namespace odb
                 char* argv[],
                 bool erase = false,
                 const std::string& extra_connect_string = "",
+                transaction_isolation_type = isolation_read_committed,
                 SQLHENV environment = 0,
                 details::transfer_ptr<connection_factory> =
                   details::transfer_ptr<connection_factory> ());
@@ -408,6 +423,12 @@ namespace odb
         return extra_connect_string_;
       }
 
+      transaction_isolation_type
+      transaction_isolation () const
+      {
+        return transaction_isolation_;
+      }
+
       const std::string&
       connect_string () const
       {
@@ -462,6 +483,7 @@ namespace odb
       std::string server_;
       std::string driver_;
       std::string extra_connect_string_;
+      transaction_isolation_type transaction_isolation_;
       std::string connect_string_;
 
       auto_handle<SQL_HANDLE_ENV> auto_environment_;
diff --git a/odb/mssql/mssql.hxx b/odb/mssql/mssql.hxx
index f6d434e..8fa4303 100644
--- a/odb/mssql/mssql.hxx
+++ b/odb/mssql/mssql.hxx
@@ -46,6 +46,14 @@
 #  define SQL_MARS_ENABLED_YES 1L
 #endif
 
+#ifndef SQL_COPT_SS_TXN_ISOLATION
+#  define SQL_COPT_SS_TXN_ISOLATION (SQL_COPT_SS_BASE + 27)
+#endif
+
+#ifndef SQL_TXN_SS_SNAPSHOT
+#  define SQL_TXN_SS_SNAPSHOT 0x00000020L
+#endif
+
 #ifndef SQL_SS_TIME2
 #  define SQL_SS_TIME2           (-154)
 #  define SQL_SS_TIMESTAMPOFFSET (-155)