CODEWITHSUNDEEP
sqlsql-serverjdbc
steelbull
steelbull

Reputation: 141

JDBC & Microsoft SQL Server / How to set AllowMultipleQueries

How to set AllowMultipleQueries on JDBC for Microsoft SQL Server?

My connection string is currently following, but it not works. 

private final String url = "jdbc:sqlserver://localhost:11435;databaseName=myDatabase;allowMultiQueries=true";

Upvotes: 2

Views: 1844

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123799

With Microsoft's JDBC driver for SQL Server you don't need to add anything special to your connection URL in order to enable multiple statements in a single execute. This works fine:

connectionUrl = "jdbc:sqlserver://localhost:52865;databaseName=myDb";
try (Connection conn = DriverManager.getConnection(connectionUrl, myUserID, myPassword)) {
    System.out.printf("Driver version %s%n", conn.getMetaData().getDriverVersion());

    try (Statement st = conn.createStatement()) {
        st.execute("CREATE TABLE #test (id INT IDENTITY PRIMARY KEY, textcol NVARCHAR(50))");
    }

    String[] itemsToInsert = new String[] { "foo", "bar" };

    String sql = 
            "SET NOCOUNT ON;" +
            "INSERT INTO #test (textcol) VALUES (?);" +
            "SELECT @@IDENTITY;";
    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        for (String item : itemsToInsert) {
            ps.setString(1, item);
            try (ResultSet rs = ps.executeQuery()) {
                rs.next();
                int newId = rs.getInt(1);
                System.out.printf("'%s' inserted with id=%d%n", item, newId);
            }
        }
    }

} catch (Exception e) {
    e.printStackTrace(System.err);
}

producing

Driver version 6.0.7728.100
'foo' inserted with id=1
'bar' inserted with id=2

However, in this particular case it would be better to use JDBC's built-in support for retrieving generated keys:

String sql = "INSERT INTO #test (textcol) VALUES (?)";
try (PreparedStatement ps = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS)) {
    for (String item : itemsToInsert) {
        ps.setString(1, item);
        ps.executeUpdate();
        try (ResultSet rs = ps.getGeneratedKeys()) {
            rs.next();
            int newId = rs.getInt(1);
            System.out.printf("'%s' inserted with id=%d%n", item, newId);
        }
    }
}

producing the exact same results.

Upvotes: 3

Rahul
Rahul

Reputation: 77896

why not use a stored procedure instead? Instead of setting allowMultiQueries you should rather use a stored procedure like below

create procedure usp_data
as
begin
INSERT INTO ......; 
SELECT @@IDENTITY AS [id];
end

Now call that stored procedure from your code behind. You can as well parameterize the procedure if needed. See this existing post Multiple queries executed in java in single statement

Upvotes: 0

Related Questions