Execute multiple sql statements with no errors when the insert fails

Question

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class jdbcTest {
    public static void main(String[] arg) throws ClassNotFoundException, SQLException {

        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        String connectionUrl = "#############";
        Connection con = DriverManager.getConnection(connectionUrl, "###", "###");

        Statement stmt = con.createStatement();
        String sql1 = "INSERT INTO [tuj_dev].[dbo].[TLT_ORG2]\n" +
                "VALUES ('', '', '')\n" +
                "UPDATE [tuj_dev].[dbo].[TLT_ORG2]\n" +
                "SET [LVL_2_NM] = [TLT_NTID]";
        String sql2 = "UPDATE [tuj_dev].[dbo].[TLT_ORG2]\n" +
                "SET [LVL_2_NM] = [TLT_NTID]\n" +
                "INSERt INTO [tuj_dev].[dbo].[TLT_ORG2]\n" +
                "VALUES ('', '', '')";
        stmt.execute(sql2);
        stmt.close();
        con.close();
    }
}

When I try to execute the SQL statements(sql1, sql2), I got a different result.
For sql1, I got an exception "Violation of PRIMARY KEY constraint" and the update statement executes successfully.
For sql2, I got no errors and the update statement executes successfully as well.
Here I have a question, why the sql2 won't throw an exception or if I miss some settings?

Answer 1

https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/Statement.html#execute(java.lang.String).

The method getMoreResults() must be called after execute the query if more results needed to be retrieved.

For more information, please check the link above.

Answer 2

Hi @Virgil Yu ,

SQL server general tag is talking about the issues about SQL server. Your issue is not related to SQL server, suggest you add the correct tag for your issue and also post your issue in Java form. You can find all products that supported on Q&A forum from this link.

Best regards,
Carrin

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

I don't work with Java, so I cannot give the exact details, but the issue have been seen in other environments as well.

One remedy is to add this command to the SQL batch: SET NOCOUNT ON. This suppresses the "rows affected" messages from SQL Server.

The better solution is to iterate over all result sets returned from SQL Server. The first result set is the row count from SQL Server, but you need to move further to have the exception raised.

The reason the latter is better is that there are situations where SET NOCOUNT ON is not going to help.