Can JDBC client execute SET ARITHABORT ON on Micro

2019-08-02 21:15发布

问题:

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.

回答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.