PreparedStatement batch exception: missing trigger message

[NicolaIsotta]

Driver version or jar name

6.2.1.jre7

SQL Server version

Microsoft SQL Server
13.00.4001

Client operating system

Windows 10

Java/JVM version

java version "1.8.0_144"
Java(TM) SE Runtime Environment (build 1.8.0_144-b01)
Java HotSpot(TM) 64-Bit Server VM (build 25.144-b01, mixed mode)

Problem description

The exception thrown when a batch update fails because of a trigger is different based on the Statement type.
If it's a normal Statement, the trigger message is correctly shown.
Instead, if it's a PreparedStatement, the exception doen't contain the trigger message, so the issue is hard to find. Calling getCause() or getNextException() doesn't help.

Repro code

https://gist.github.com/NicolaIsotta/f10f9162c9dc89f903adc0df4644e662

The result of running the code is:

java.sql.BatchUpdateException: [Riga Insesistente] - Impossibile inserire, non esiste alcun legame tra la riga inserita e lariga dell'ordine acquisto!!
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeBatch(SQLServerStatement.java:1740)
	at it.emmeduei.Test.testStatement(Test.java:40)
	at it.emmeduei.Test.main(Test.java:67)
com.microsoft.sqlserver.jdbc.SQLServerException: Transazione terminata nel trigger. Il batch è stato interrotto.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1535)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.processResults(SQLServerStatement.java:1210)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.processBatch(SQLServerStatement.java:1201)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatementBatch(SQLServerPreparedStatement.java:2511)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtBatchExecCmd.doExecute(SQLServerPreparedStatement.java:2383)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2478)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:219)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:199)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:2294)
	at it.emmeduei.Test.testPreparedStatement(Test.java:58)
	at it.emmeduei.Test.main(Test.java:68)

[AfsanehR-zz]

Hi @NicolaIsotta . I tried running the sample you provided and was not able to produce any error messages. In order to help you better, is it possible to post the English error message in here?
Thank you!

[NicolaIsotta]

Hello @v-afrafi . Are you talking about about the first exception or the second? Because the first one has a custom message returned by the trigger. The second exception message in English is The transaction ended in the trigger. The batch has heen aborted.

Unfortunately I'm not the DBA and I do not have access to the trigger definition.
I'll download SQL Server Express and try to create a stand-alone reproducer, I guess it can help you track down the issue.

[NicolaIsotta]

I think I worked out something near to a reproducer.
First, I run this:

create table test_table (
        test_field int
)

Then this:

create trigger test_trigger
on test_table
for insert
as
begin
if (select test_field from inserted) > 10
begin
return
end
RAISERROR ('Custom error message, you should see me. test_field should be higher than 10', 16, 0)
rollback transaction
end

Then I run this class: https://gist.github.com/NicolaIsotta/792c47791511dd918ebc74ebcc576c42
The result is:

Statement exception:
java.sql.BatchUpdateException: Custom error message, you should see me. test_field should be higher than 10
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeBatch(SQLServerStatement.java:1740)
	at it.emmeduei.Test.testStatement(Test.java:39)
	at it.emmeduei.Test.main(Test.java:65)

PreparedStatement exception:
com.microsoft.sqlserver.jdbc.SQLServerException: Transazione terminata nel trigger. Il batch è stato interrotto.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1535)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.processResults(SQLServerStatement.java:1210)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.processBatch(SQLServerStatement.java:1201)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatementBatch(SQLServerPreparedStatement.java:2511)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtBatchExecCmd.doExecute(SQLServerPreparedStatement.java:2383)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2478)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:219)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:199)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:2294)
	at it.emmeduei.Test.testPreparedStatement(Test.java:55)
	at it.emmeduei.Test.main(Test.java:68)

As you can see, the PreparedStatement exception does not contain the custom trigger message.

[AfsanehR-zz]

Thank you @NicolaIsotta. I was able to reproduce it. We will get back to you soon.

[AfsanehR-zz]

Thank you @NicolaIsotta for creating this issue. I created pr #458 as a fix. Could you please test it as well and see if it resolves your issue?
Also, can we add the test case you provided in the same pr?

[NicolaIsotta]

I tested and approved the fix, thanks @v-afrafi .
Feel free to use the test case.