Multiple Active Statements (MAS) and DBD::ODBC

Although this is a generic document about using Multiple Active Statements (MAS) with DBD::ODBC, the principal focus is on using MAS with Microsoft SQL Server, as ODBC drivers for most other databases support MAS automatically.

Although most of the examples in this document are written in Perl, the majority of the information on MAS is programming-language neutral.

If you require further information about DBD::ODBC, refer to the our other tutorials.

Contents

• What are Multiple Active Statements (MAS)?
• How to overcome MAS restrictions in an ODBC driver neutral way
• A note about default result sets in SQL Server
• Driver-specific ways around MAS restrictions

What are Multiple Active Statements (MAS)?

Multiple Active Statements (MAS) are more than one statement in the same connection that have active work (or result sets, Multiple Active Result Sets (MARS)). A typical example:

# connect and create a test table
my $h = DBI->connect();
$h->{RaiseError} = 1;
eval {
    local $h->{PrintError} = 0;
    $h->do(q/drop table example1/)
};
$h->do(q/create table example1 (a int not null primary key, b char(10))/);
foreach (1..2) {
    $h->do(q/insert into example1 values(?,?)/, undef, $_, 'fred');
}

# loop through all the rows in the table changing each one's b field to
# 'dave' yes, we know no one would really do this - it is just an example
my $s1 = $h->prepare(q/select a from example1/);
$s1->execute;
my $s2 = $h->prepare(q/update example1 set b = ? where a = ?/);
while(my $row = $s1->fetchrow_arrayref) {
    $s2->execute('dave', $row->[0]);
}

When you run this with some ODBC drivers, you typically get an error on the $s2->execute saying something like "connection is busy with results of another hstmt."

How to overcome MAS restrictions in an ODBC driver neutral way

When people first started hitting MAS restrictions, the obvious solution was to create multiple connections, each running one active statement. Using this solution, our earlier example becomes:

my $h1 = DBI->connect();
my $h2 = DBI->connect();
$h1->{RaiseError} = 1;
$h2->{RaiseError} = 1;
eval {
    local $h1->{PrintError} = 0;
    $h1->do(q/drop table example1/)
};
$h1->do(q/create table example1 (a int not null primary key,
                                 b char(10))/);
foreach (1..2) {
    $h1->do(q/insert into example1 values(?,?)/, undef, $_, 'fred');
}

# loop through all the rows in the table changing each one's b field to
# 'dave' yes, we know no one would really do this - it is just an example
my $s1 = $h1->prepare(q/select a from example1/);
$s1->execute;
my $s2 = $h2->prepare(q/update example1 set b = ? where a = ?/);
while(my $row = $s1->fetchrow_arrayref) {
    $s2->execute('dave', $row->[0]);
}

You often get code that opens a connection, executes the update, and closes the connection inside a loop (which is even worse). However, this is generic solution for an ODBC driver that does not support MAS, and, although this solution works:

• It requires multiple connections and therefore more resources.
• Contributors to the code needs to know it uses MAS and open additional connections for each MAS.
• Licensing in database engines is often based around the number of concurrent connections.
• It can cause deadlocks, depending on the isolation level, as it uses two different connections and therefore introduces two different and potentially conflicting environments.

A note about default result sets in SQL Server

Results are generally produced by SELECT and FETCH statements (although procedures and functions with output parameters and return values also do this). SQL Server executes a SELECT statement by streaming the results back to the client and, as rows are produced by the query, they are written back to the caller over the network. If the client fails to read the network data as quickly as SQL Server writes it, a point is reached where SQL Server will block, and execution is suspended until the client reads more data. Note that in this scenario, the client does not need to ask for more data from the result set. The result set is continually streamed until there is no more to send and if at any point the client knows it needs no more (if the cursor is closed), it has no choice but to read the remaining result set. (This is also the reason why issuing a query that returns a huge result set, of which the client reads only a few rows before closing the cursor, can take a long time to complete, even though the application has not got all the result set.) This producer/consumer scenario was previously referred to as "firehose cursors" and is now more generally known as "default result sets."

Driver-specific ways around MAS restrictions

At the time of writing, there are three main ways to get around MAS restrictions without resorting to multiple connections. The following script provided by the DBIx::Class team tests each workaround out and whether the workaround is required:

use strict;
use DBI;
use Data::Dumper;

my $attrs = { RaiseError => 1, PrintError => 0, AutoCommit => 1 };

# you need to change the following for your ODBC connection
# make sure you use DSN= in the DSN
my %connect_args = (DSN => 'dbi:ODBC:DSN=mydsn',
                    USER => 'db_user_name',
                    PASS => 'db_password');

my $dbhmakers = {
  normal => sub {
    DBI->connect (
      (map { $connect_args{"$_"} } (qw/DSN USER PASS/) ),
      $attrs,
    );
  },
  MARs => sub {
    local $connect_args{DSN} = $connect_args{DSN} . ';MARS_Connection=Yes';
    DBI->connect (
      (map { $connect_args{$_} } (qw/DSN USER PASS/) ),
      $attrs,
    );
  },
  server_cursors_hack => sub {
    DBI->connect (
      (map { $connect_args{$_} } (qw/DSN USER PASS/) ),
      { %$attrs, odbc_SQL_ROWSET_SIZE => 2 },
    );
  },
  cursor_type => sub {
    DBI->connect (
      (map { $connect_args{$_} } (qw/DSN USER PASS/) ),
      { %$attrs, odbc_cursortype => 2 },
    );
  },
};

for (sort keys %$dbhmakers) {
  print "\n\nTrying with $_\n";

  my $dbh = $dbhmakers->{$_}->();
  $dbh->{odbc_SQL_ROWSET_SIZE} = 2;
  eval { $dbh->do ('DROP TABLE test_foo') };
  $dbh->do ('CREATE TABLE test_foo ( bar VARCHAR(20) )');

  $dbh->do ("INSERT INTO test_foo (bar) VALUES ( 'baz_$_' )")
    for (1..5);

  eval {
    my @sths;
    push @sths, $dbh->prepare("SELECT * FROM test_foo") for (1..5);
    $_->execute for @sths;

    LOOP:
    while (1) {
      for (0 .. $#sths) {
        my $res = $sths[$_]->fetchrow_arrayref
          or last LOOP;
        print "Result from sth $_: $res->[0]\n";
      }
    }
  };
  warn "Died with $@\n" if $@;

  eval { $dbh->do ('DROP TABLE test_foo') };
}

__END__

Refer to the notes under Changing the SQL_ROWSET_SIZE value if you get an error when setting odbc_SQL_ROWSET_SIZE during the connection above.

We will now discuss each workaround with reference to the Microsoft SQL Server ODBC driver.

Microsoft SQL Server ODBC driver

Changing the SQL_ROWSET_SIZE value

A long time ago in the history of DBD::ODBC, someone discovered the first "hack" to get around MAS restrictions. The hack was based around the discovery that if you set SQL_ROWSET_SIZE to anything greater than 1, SQL Server would allow MAS. The odbc_SQL_ROWSET_SIZE connection attribute was added to DBD::ODBC, and so our basic example now becomes:

# connect and create a test table
my $h = DBI->connect();
$h->{RaiseError} = 1;
eval {
    local $h->{PrintError} = 0;
    $h->do(q/drop table example1/)
};
$h->do(q/create table example1 (a int not null primary key, b char(10))/);
foreach (1..2) {
    $h->do(q/insert into example1 values(?,?)/, undef, $_, 'fred');
}

$h->{odbc_SQL_ROWSET_SIZE} = 2;

# loop through all the rows in the table changing each one's b field to
# 'dave' yes, we know no one would really do this - it is just an example
my $s1 = $h->prepare(q/select a from example1/);
$s1->execute;
my $s2 = $h->prepare(q/update example1 set b = ? where a = ?/);
while(my $row = $s1->fetchrow_arrayref) {
    $s2->execute('dave', $row->[0]);
}

This works because setting the SQL_ROWSET_SIZE attribute forces SQL Server to use a server forward-only cursor. This does not harm DBD::ODBC, as DBD::ODBC never uses SQLExtendedFetch, which SQL_ROWSET_SIZE affects. However, this workaround has side effects that result in more round trips to the server when retrieving data and introduces potential problems if you don't ensure you retrieve all result sets before closing a statement. (Refer to the DBD::ODBC POD.)

There is an additional problem if you attempt to set odbc_SQL_ROWSET_SIZE in the connect call (on some platforms, with some ODBC Driver Managers) with versions of DBI before 1.616 or DBD::ODBC before 1.28_1. A bug in DBI before 1.616 causes it to FETCH all attribute values passed on the connect method call, and some Driver Managers will error if asked for SQL_ROWSET_SIZE on a connection handle when running in an ODBC 3.0 connection. You can overcome this problem by:

• Installing DBI 1.616 or above.

  –Or–

• Installing DBD::ODBC 1.28_1 or above (which caches the value).

  –Or–

• Setting odbc_SQL_ROWSET_SIZE on the connection handle after the connect call.

Directly changing the cursor type

Similar to the SQL_ROWSET_SIZE workaround, it's possible to achieve MAS support by directly changing the cursor type. If you use a dynamic cursor, you will get MAS support. Our simple example now becomes:

# connect and create a test table
my ($dsn, $user, $password) = ('dbi:ODBC:asus2', 'sa', undef);

my $h = DBI->connect($dsn, $user, $password, {odbc_cursortype => 2});
# NOTE if you are using DBI after 1.41 you can use
# odbc_cursortype => DBI::SQL_CURSOR_DYNAMIC
$h->{RaiseError} = 1;
eval {
    local $h->{PrintError} = 0;
    $h->do(q/drop table example1/)
};
$h->do(q/create table example1 (a int not null primary key, b char(10))/);
foreach (1..2) {
    $h->do(q/insert into example1 values(?,?)/, undef, $_, 'fred');
}

# loop through all the rows in the table changing each one's b field to
# 'dave' yes, we know no one would really do this - it is just an example
my $s1 = $h->prepare(q/select a from example1/);
$s1->execute;
my $s2 = $h->prepare(q/update example1 set b = ? where a = ?/);
while(my $row = $s1->fetchrow_arrayref) {
    $s2->execute('dave', $row->[0]);
}

The first thing to note here is that you need to set the cursor type in the connect call, as setting it later on the connection is not supported by DBD::ODBC.

As with setting odbc_SQL_ROWSET_SIZE, the main disadvantage of setting a server cursor is that more round trips between the client and server are required, since the client has to keep asking for more rows.

Using MARS_Connection

Since SQL Server 2005, you can use the MARS_Connection connection attribute to allow Multiple Active Result Sets (MARS). In ODBC, you do this by adding MARS_Connection=yes to the connection string. Our example now becomes:

# connect and create a test table
my ($dsn, $user, $password) =
    ('dbi:ODBC:DSN=nativec;MARS_Connection=Yes', 'sa', undef);

my $h = DBI->connect($dsn, $user, $password);
$h->{RaiseError} = 1;
eval {
    local $h->{PrintError} = 0;
    $h->do(q/drop table example1/)
};
$h->do(q/create table example1 (a int not null primary key, b char(10))/);
foreach (1..2) {
    $h->do(q/insert into example1 values(?,?)/, undef, $_, 'fred');
}

# loop through all the rows in the table changing each one's b field to
# 'dave' yes, we know no one would really do this - it is just an example
my $s1 = $h->prepare(q/select a from example1/);
$s1->execute;
my $s2 = $h->prepare(q/update example1 set b = ? where a = ?/);
while(my $row = $s1->fetchrow_arrayref) {
    $s2->execute('dave', $row->[0]);
}

Note that you need SQL Server 2005 or later to use MARS_Connection. Also, if you're using the Microsoft SQL Server ODBC driver instead of the Native Client Driver, you need the ODBC driver from the SQL Server distribution.

Note that although this is a connection attribute, there is no way to enable MARS_Connection by using the Microsoft ODBC driver GUI setup. You also need to enable this attribute at connection time or before the connection by setting SQL_COPT_SS_MARS_ENABLED (use SQLSetConnectAttr, which you cannot directly do with DBD::ODBC).

Notes:

• Don't just enable MARS for your application without reviewing the code, as it may affect performance. The default method used by SQL Server is pretty network efficient.
• MARS provides interleaving of result sets, but things can go horribly wrong if, for instance, you change server state with set or use or use T-SQL transaction statements.
• Don't consider using MARS if you require locking on rows in the result set.
• You can't begin a transaction when a result set is already open and if you commit or rollback a transaction, any open result sets become unusable.
• If you need Preserve On Commit behavior, you must use a server cursor.

Using SQL_COPT_SS_PRESERVE_CURSORS

You can set the connection attribute SQL_COPT_SS_PRESERVE_CURSORS before opening a connection, to enable MAS. The following C code illustrates this on a table defined as:

create table xtest(ky int primary key, txt varchar(100));

Insert rows with ky values of 1 to 100 and any txt values. The code reads one row at a time, and, for each row where ky mod 4 is 0, it updates the row (in another statement) to set the ky value to WORLD. You can't currently do this in DBD::ODBC.

#ifdef WIN32
#include <windows.h>
#endif

#include <stdio.h>
#include <sql.h>
#include <sqlext.h>
#include <stdlib.h>
#include <string.h>
#include <math.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 );
}

main( int argc, char **argv )
{

    SQLRETURN   ret;
    SQLHANDLE   envHandle;
    SQLHANDLE   hStmt1, hStmt2;
    SQLHANDLE   conHandle;
    SQLINTEGER  ival;
    SQLLEN      l1, l2;
    SQLCHAR     sval[ 129 ];
    int i;

        ret = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE,
                              &envHandle );

        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 = SQLSetConnectAttr( conHandle, SQL_ATTR_AUTOCOMMIT,
                             (SQLPOINTER)SQL_AUTOCOMMIT_OFF, 0 );
        if ( ret != SQL_SUCCESS)
                printf( "SQLSetConnectAttr failed" );

    ret = SQLSetConnectAttr( conHandle, SQL_COPT_SS_PRESERVE_CURSORS,
                             (SQLPOINTER)SQL_PC_ON, 0 );
        if ( ret != SQL_SUCCESS)
                printf( "SQLSetConnectAttr failed" );

    ret = SQLDriverConnect( conHandle, NULL, argv[ 1 ], SQL_NTS,
                            NULL, 0, NULL, SQL_DRIVER_NOPROMPT );
        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 = SQLAllocHandle( SQL_HANDLE_STMT, conHandle, &hStmt2 );
        if ( !SQL_SUCCEEDED( ret )) {
                extract_error("SQLAllocStmt conHandle", conHandle,
                              SQL_HANDLE_DBC);
                exit(1);
        }

    ret = SQLSetStmtAttr( hStmt1, SQL_CURSOR_TYPE,
                          (SQLPOINTER)SQL_CURSOR_STATIC, 0 );
        if ( ret != SQL_SUCCESS)
                printf( "SQLSetConnectAttr failed" );


    SQLPrepare( hStmt1, "select * from xtest", SQL_NTS );
    SQLPrepare( hStmt2, "update xtest set txt = ? where ky = ?", SQL_NTS );

    SQLBindCol( hStmt1, 1, SQL_C_LONG, &ival, 0, &l1 );
    SQLBindCol( hStmt1, 2, SQL_C_CHAR, sval, 128, &l2 );

    SQLBindParameter( hStmt2, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
                      SQL_CHAR, 128, 0, sval, 128, &l2 );
    SQLBindParameter( hStmt2, 2, SQL_PARAM_INPUT, SQL_C_LONG,
                      SQL_INTEGER, 0, 0, &ival, 0, &l1 );

    SQLExecute( hStmt1 );

    while( SQLFetch( hStmt1 ) == SQL_SUCCESS ) {
        printf( "%ld '%s'\n", ival, sval );
        if (( ival % 4 ) == 0 ) {
            strcpy( sval, "WORLD" );
            l2 = strlen( sval );
            ret = SQLExecute( hStmt2 );
            printf( "update = %d\n", ret );
            if ( ret != SQL_SUCCESS ) {
                extract_error( "SQLExecute", hStmt2, SQL_HANDLE_STMT );
            }
            else {
                ret = SQLEndTran( SQL_HANDLE_DBC, conHandle, SQL_COMMIT );
                if ( ret != SQL_SUCCESS ) {
                    extract_error( "SQLEndTran", conHandle,
                                   SQL_HANDLE_DBC );
                }
            }
        }
    }

        SQLFreeHandle(SQL_HANDLE_STMT, hStmt1);
        SQLFreeHandle(SQL_HANDLE_STMT, hStmt2);


        SQLDisconnect(conHandle);
        SQLFreeHandle(SQL_HANDLE_DBC, conHandle);
        SQLFreeHandle(SQL_HANDLE_ENV, envHandle);

    return SQL_SUCCESS;
}

Easysoft SQL Server ODBC driver

All of the methods detailed above for the Microsoft SQL Server ODBC drivers apply to the Easysoft SQL Server ODBC driver.

Easysoft ODBC-ODBC Bridge

All of the methods detailed above for Microsoft SQL Server ODBC drivers apply to the Easysoft ODBC-ODBC Bridge.

Easysoft Oracle ODBC driver

The Easysoft Oracle ODBC driver supports MAS without any attribute or cursor changes.

FreeTDS

Currently, the only way to enable MAS in FreeTDS is to use a dynamic cursor.

• About Easysoft

• Contact us
• About us
• Clients
• Blog
• Careers

• Products

• ODBC drivers
• JDBC drivers
• Bridges and gateways
• In development

• Services

• Consultancy
• Training
• Custom development

• Licensing

• Product licenses
• Prices

• Support

• Support home
• Solution wizard
• Getting Started Guides
• User Guides
• Knowledge Base

• Search

• Easysoft.com
• Documentation
• Knowledge Base

• Popular Resources

• Developer area
• Client applications

• Legal

• Privacy statement

© 1993 - 2024 Easysoft Limited. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other trademarks and registered trademarks appearing on easysoft.com are the property of their respective owners.

• 
• 
•