Can JDBC client execute SET ARITHABORT ON on Microsoft SQL Server 2012?

Question

Can I set Microsoft SQL Server 2012 options such as ARITHABORT programmatically from a (remote) Java client that uses Microsoft JDBC Driver 4.0? I've noticed that statement.execute("SET ARITHABORT ON")returns false and the expected effect seems missing, hence the question.

Lupelupee

Read the JavaDocs for Statement.execute(). The return value indicates if the statement generated a ResultSet not if the statement was successful. – Jacquelynnjacquenetta

@a_horse_with_no_name I've read the JavaDocs before posting the question and concluded that I currently cannot know from the return value whether the statement was successful. – Lupelupee

Answer 1

Can I set Microsoft SQL Server 2012 options such as ARITHABORT programmatically from a (remote) Java client that uses Microsoft JDBC Driver 4.0?

It certainly appears so. The code

package com.example.sqlservertest;

import java.sql.*;

public class SqlServerTestMain {

    public static void main(String[] args) {
        String connectionUrl = "jdbc:sqlserver://localhost:52865;"
                + "databaseName=myDb;" + "integratedSecurity=true";
        try (Connection con = DriverManager.getConnection(connectionUrl)) {
            System.out.println("Connection established.");
            try (Statement stmt = con.createStatement()) {
                try (ResultSet rs = stmt.executeQuery("SELECT CONVERT(INT, SESSIONPROPERTY('ARITHABORT'))")) {
                    rs.next();
                    System.out.println(String.format(
                            "SESSIONPROPERTY('ARITHABORT') is %d",
                            rs.getInt(1)));
                }
                String sql = "SET ARITHABORT ON";
                System.out.println(sql);
                stmt.execute(sql);
                try (ResultSet rs = stmt.executeQuery("SELECT CONVERT(INT, SESSIONPROPERTY('ARITHABORT'))")) {
                    rs.next();
                    System.out.println(String.format(
                            "SESSIONPROPERTY('ARITHABORT') is %d",
                            rs.getInt(1)));
                }
                sql = "SET ARITHABORT OFF";
                System.out.println(sql);
                stmt.execute(sql);
                try (ResultSet rs = stmt.executeQuery("SELECT CONVERT(INT, SESSIONPROPERTY('ARITHABORT'))")) {
                    rs.next();
                    System.out.println(String.format(
                            "SESSIONPROPERTY('ARITHABORT') is %d",
                            rs.getInt(1)));
                }
            }
        } catch (Exception e) {
            e.printStackTrace(System.out);
        }
    }

}

returns

Connection established.
SESSIONPROPERTY('ARITHABORT') is 0
SET ARITHABORT ON
SESSIONPROPERTY('ARITHABORT') is 1
SET ARITHABORT OFF
SESSIONPROPERTY('ARITHABORT') is 0

The ARITHABORT state should remain in effect for the life of the connection unless some other code changes it.

Campobello