Robotframework + JayDeBeApi + SQLServer gives SQLServerException: Cannot invoke a rollback operation when the AutoCommit mode is set to "true"

Question

I setup robotframework test by using robotframework DatabaseLibrary, JayDeBeApi, MS SQL Server JDBC driver and have successful connection. However I'm getting SQLServerException: Cannot invoke a rollback operation when the AutoCommit mode is set to "true" when run any sql.

I tried with turning off AutoCommit by adding "autoCommit=false" on jdbc connection url or adding "SET IMPLICIT_TRANSACTIONS ON" in sql statement or even check SET IMPLICIT_TRANSACTIONS ANSI option on SQL Server Management Studio, but have no luck from any of those attempts.

Here is robotframework maven plugin setup

pom.xml

<plugin>
    <groupId>org.robotframework</groupId>
    <artifactId>robotframework-maven-plugin</artifactId>
    <version>1.4.5</version>
    <executions>
        <execution>
            <goals>
                <goal>run</goal>
            </goals>
        </execution>
    </executions>
    <configuration>
        <extraPathDirectories>
            <extraPathDirectory>/opt/virtualenv_envs/robot/lib/python2.7/site-packages</extraPathDirectory>
            <extraPathDirectory>/usr/local/lib/python2.7/dist-packages</extraPathDirectory>
        </extraPathDirectories>
    </configuration>
    <dependencies>
        <dependency>  <!-- this jar from local nexus -->
            <groupId>com.microsoft.sqlserver.jdbc</groupId>
            <artifactId>sqljdbc41</artifactId>
            <version>4.1</version>
        </dependency>
    </dependencies>
</plugin>

Below is robot test

dbTest.robot

*** Settings ***
Library                         DatabaseLibrary
Library                         String
Library                         OperatingSystem
Suite Setup                     SuiteSetup
Suite Teardown                  SuiteTearDown 

*** Variables ***
${JDBC_DRIVER}                  'com.microsoft.sqlserver.jdbc.SQLServerDriver'
${JDBC_URL}                     'jdbc:sqlserver://{ipAddress}:1433;databaseName={myDB};user={username};password={password}'

*** Test Cases ***
Test Prepare Data
    [Documentation]             Reset Data
    Execute Sql String          UPDATE dbo.ImportHeader SET ExtractedOn=null;


*** Keywords ***
SuiteSetup
    Connect To Database Using Custom Params    jaydebeapi    ${JDBC_DRIVER},${JDBC_URL}

SuiteTearDown
    Disconnect From Database

This is the error I'm getting when I run the robot test

Test Prepare Data :: Reset Data...        | FAIL |
DatabaseError: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot invoke a rollback operation when the AutoCommit mode is set to "true".

One drastic solution I did on this is modifying robotframework DatabaseLibary connection_manager.py with setting autoCommit to false on jaydebeapi connection like below self._dbconnection.jconn.setAutoCommit(False), but I hope to know better solution than this if there is any.

{your python virtualenv}/site-packages/DatabaseLibrary/connection_manager.py

import ConfigParser
from robot.api import logger

class ConnectionManager(object):
    """
    Connection Manager handles the connection & disconnection to the database.
    """




    def connect_to_database_using_custom_params(self, dbapiModuleName=None, db_connect_string=''):
        """
        Loads the DB API 2.0 module given `dbapiModuleName` then uses it to 
        connect to the database using the map string `db_custom_param_string`.

        Example usage:
        | # for psycopg2 |
        | Connect To Database Using Custom Params | psycopg2 | database='my_db_test', user='postgres', password='s3cr3t', host='tiger.foobar.com', port=5432 |

        | # for JayDeBeApi | 
        | Connect To Database Using Custom Params | JayDeBeApi | 'oracle.jdbc.driver.OracleDriver', 'my_db_test', 'system', 's3cr3t' |
        """
        db_api_2 = __import__(dbapiModuleName)

        db_connect_string = 'db_api_2.connect(%s)' % db_connect_string

        self._dbconnection = eval(db_connect_string)
        self._dbconnection.jconn.setAutoCommit(False)

I encountered a similar problem with a JDBC driver to an RDB database, when running SQL from Robot Framework / DatabaseLibrary. DatabaseError: oracle.rdb.jdbc.common.RdbException: Invalid operation when autoCommit enabled. I resorted to modifying connection_manager.py with self._dbconnection.jconn.setAutoCommit(False) according to your "drastic" solution. Which could be formulated to an accepted answer (unless a better comes around). — AnnTea, Oct 25 '16 at 10:03

score 0 · Answer 1 · answered Dec 03 '15 at 09:19

I also did encounter this problem some time ago, but as i remember it worked with autocommit in url (example from my solution):

Connect To Database Using Custom Params    jaydebeapi     'net.sourceforge.jtds.jdbc.Driver', ['jdbc:jtds:sqlserver://${DB_HOST}:${DB_PORT}/${database};autocommit=false','${DB_USER}','${DB_PASSWORD}'],${jdbcDriver}

But unfortunately it didn't work for oracle and I had to modify library like you did.

score 0 · Answer 2 · answered Apr 02 '20 at 10:19

You can also set autocommit false in DatabaseLibrary instance:

Connect To Database Using Custom Params    jaydebeapi    'oracle.jdbc.driver.OracleDriver', 'jdbc:oracle:thin:@oracle.database.com:1234/foo', ['username_here', 'password_here'], '${CURDIR}/databasedrivers/ojdbc8.jar'
${DatabaseLibrary}    Get library instance    DatabaseLibrary
Evaluate    $DatabaseLibrary._dbconnection.jconn.setAutoCommit(False)

Robotframework + JayDeBeApi + SQLServer gives SQLServerException: Cannot invoke a rollback operation when the AutoCommit mode is set to "true"

2 Answers2