SQL CLR stored procedure called via JDBC using setAutoCommit(false) does not update database.
I have come across this question a few times;
“I have a java client that connects to SQL Server via the JDBC driver. I then call a CLR stored procedure that interacts with the database.
When I run with setAutoCommit false the data is not committed. However, if I call a ‘normal’ TSQL stored procedure, the data is committed”
Well, this again calls for example code J
I trust that you have a Visual Studio and SQL Server installed, however, you also need the Java JDK and the Microsoft JDBC driver. These are found here:
"Microsoft SQL Server JDBC Driver 2.0"
"Java SE Downloads"
https://java.sun.com/javase/downloads/index.jsp
More info on the JDBC driver here:
"Microsoft SQL Server JDBC Driver"
https://msdn.microsoft.com/en-us/data/aa937724.aspx
I’ll leave it to you to setup your java paths etc in order to be able to compile and run the client.
So, let’s start with creating a table that we will use for the inserts from our stored procedures. From SSMS, just run:
create table ReproTable (cid int identity, ctext nvarchar(20))
--drop table ReproTable
Then we create the stored procedures, first a CLR stored procedure. In Visual Studio, create a new SQL Server Project and add a reference to your SQL Server.
Then right click your project and add a new Stored Procedure, call it InsertToReproTableClr. Replace the whole partial class so it looks as below:
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void InsertToReproTableClr(SqlString txtToInsert)
{
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO ReproTable VALUES (@insTxt);", connection);
cmd.Parameters.Add(new SqlParameter("@insTxt", txtToInsert));
cmd.ExecuteNonQuery();
connection.Close();
}
}
};
This simply inserts are row into the table, setting the ctext column to what we send as the argument.
Build and deploy the stored procedure to the SQL Server.
Now, create a stored procedure in ‘normal’ fashion, i.e. a TSQL stored procedure that does the same thing.
create procedure InsertToReproTableTsql (@insTxt nvarchar(20))
as
begin
insert into ReproTable values (@insTxt);
end
Finally, make sure these procedures work like so:
execute InsertToReproTableClr 'CLR insert'
execute InsertToReproTableTsql 'TSQL insert'
select * from ReproTable
This should give the following output of course:
cid ctext
----------- --------------------
1 CLR insert
2 TSQL insert
So, enough with the prepare stuff. Time for our client, as mentioned, I’ll leave it to you to setup the class paths etc and I’ll assume that this is now up and running.
Simply create a client that calls our stored procedures via the JDBC driver. One call is made with setAutoCommit(true), this is the default, and one call is made with setAutoCommit(false).
You can read more about this method here:
"setAutoCommit Method (SQLServerConnection)"
https://msdn.microsoft.com/en-us/library/ms378922(SQL.90).aspx
Client code, replace the connectionstring, save as JDBCRepro.java
import java.sql.*;
class JDBCTest
{
public static void main (String args[])
{
String connectionUrl = "jdbc:sqlserver://<server>;databaseName=<database>;User=<user>;Password=<password>";
RunTest(connectionUrl, true);
RunTest(connectionUrl, false);
}
public static void RunTest(String conUrl, boolean autoCommit)
{
System.out.println("Running Test, autocommit = " + autoCommit);
Connection con = null;
Statement stmt = null;
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager.getConnection(conUrl);
con.setAutoCommit(autoCommit);
stmt = con.createStatement();
stmt.executeUpdate("EXECUTE InsertToReproTableClr 'CLR, AC="+ autoCommit + "'");
stmt.executeUpdate("EXECUTE InsertToReproTableTsql 'TSQL, AC="+ autoCommit + "'");
con.commit();
stmt.close();
System.out.println("Done...");
}
catch (Exception e) {
e.printStackTrace();
}
finally {
if (stmt != null) try { stmt.close(); } catch(Exception e) {}
if (con != null) try { con.close(); } catch(Exception e) {}
}
}
}
Compile:
javac JdbcDemo.java
and run:
java JDBCTest
Output:
Running Test, autocommit = true
Done...
Running Test, autocommit = false
Done...
Now check what is in the ReproTable:
cid ctext
----------- --------------------
1 CLR, AC=true
2 TSQL, AC=true
4 TSQL, AC=false
So now we can see that the row inserted from the CLR stored procedure is not committed (even though the identity has been increased) when running with setAutoCommit(false).
In order to see what happens, we start SQL Server Profiler and rerun the client, this shows that when we run with setAutoCommit(true) the following happens:
set transaction isolation level read committed set implicit_transactions off
execute InsertToReproTableClr 'CLR, AC=true'
execute InsertToReproTableTsql 'TSQL, AC=true'
When running with setAutoCommit(false) the following happens:
set transaction isolation level read committed set implicit_transactions off
set implicit_transactions on
execute InsertToReproTableClr 'CLR, AC=true'
execute InsertToReproTableTsql 'TSQL, AC=true'
if @@trancount > 0 commit tran
This is, I think, the confusing bit. You set something to false which in turn sets something to ON.
Reading up on "SET IMPLICIT_TRANSACTIONS (Transact-SQL)"
"SET IMPLICIT_TRANSACTIONS (Transact-SQL)"
https://msdn.microsoft.com/en-us/library/ms187807.aspx
it shows that when ON, SET IMPLICIT_TRANSACTIONS sets the connection into implicit transaction mode (as indicates in the property name J)
When a connection is in implicit transaction mode and the connection is not currently in a transaction, executing for example INSERT starts a transaction.
Transactions that are automatically opened as the result of this setting being ON must be explicitly committed or rolled back by the user at the end of the transaction.
Otherwise, the transaction and all of the data changes it contains are rolled back when the user disconnects.
When set implicit_transactions is set to off (default) then connection is in autocommit mode, in autocommit mode, all individual statements are committed if they complete successfully.
This means then that the connection when using TSQL is is already in an open transaction (client -> server).
However, when using CLR then the inner connection (context connection) is currently not in a transaction (client -> server is, CLR to server is not) so it starts a transaction,
This transaction then needs to be explicitly committed or rolled back at the end of the transaction, otherwise it will automatically be rolled back, this is what happens when the rows are not inserted.
In short, we need to explicitly commit the INSERT in the CLR stored procedure. How do we do that? Enter the TransactionScope and its Complete method.
"TransactionScope.Complete Method"
https://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.complete.aspx
"Implementing an Implicit Transaction using Transaction Scope"
https://msdn.microsoft.com/en-us/library/ms172152.aspx
So back to our CLR stored procedure, just edit to look as follows (you have to reference the System.Transactions assembly):
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void InsertToReproTableClr(SqlString txtToInsert)
{
using (TransactionScope scope = new TransactionScope())
{
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO ReproTable VALUES (@insTxt);", connection);
cmd.Parameters.Add(new SqlParameter("@insTxt", txtToInsert));
cmd.ExecuteNonQuery();
connection.Close();
}
scope.Complete();
}
}
};
Rerun the client and check the table contents. Much better.
cid ctext
----------- --------------------
1 CLR, AC=true
2 TSQL, AC=true
3 CLR, AC=false
4 TSQL, AC=false
So to summarize, when running with setAutoCommit(false) then you have to explicitly commit the code in the CLR stored procdure, this is done using the TransactionScope.
I hope this helps.
"Understanding Transactions"
https://msdn.microsoft.com/en-us/library/ms378931(SQL.90).aspx
"Managed Data Access Inside SQL Server with ADO.NET and SQLCLR"
https://msdn.microsoft.com/en-us/library/ms345135(SQL.90).aspx#mandataac_topic10
"System.Transactions Integration with SQL Server (ADO.NET)"
https://msdn.microsoft.com/en-us/library/ms172070.aspx
"The server failed to resume the transaction..."
"Context Connection"
https://msdn.microsoft.com/en-us/library/ms131053.aspx
"CLR Integration and Transactions"