question

VirgilYu-4851 avatar image
0 Votes"
VirgilYu-4851 asked ·

Execute multiple sql statements with no errors when the insert fails

 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?

sql-server-transact-sql
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Maybe the existing data were different in each experiment. Did you check the tables and try the same scripts using Management Studio?

0 Votes 0 ·
VirgilYu-4851 avatar image
0 Votes"
VirgilYu-4851 answered ·

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ·

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.

· 2 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @ErlandSommarskog,
Thank you very much for your answer. But I prefer not to change the database setting. And just like the solution you mentioned, the method execute() will only return the first result, for the other result the method`getMoreResults()` will help.

0 Votes 0 ·

For the record: SET NOCOUNT ON is not a database setting, but a session setting. And it is considered best practice to have it ON as long as you don't actually want these rowcounts. These counts causes extra chatter on the write and are not good for performance.

0 Votes 0 ·
CarrinWu-MSFT avatar image
1 Vote"
CarrinWu-MSFT answered ·

Hi @VirgilYu-4851,

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.

· 3 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @Carrin-msft,
Thank you for your information. I have changed my tag to sql-server-transact-sql, and what do you mean to post my issue in Java form, how to do that?

Best regards,
Virgil

0 Votes 0 ·

Sorry for the wrong expression. I mean you can put your question on other related communities.

0 Votes 0 ·

SQL server general tag is talking about the issues about SQL server. Your issue is not related to SQL server,

Carrin, what on Earth are you talking about? Virgil's question is certainly about SQL Server, and this is definitely the correct place to ask the question. And if you care to read the entire thread before posting inaccurate information, you would have seen that I had already answered. it. Yes, there are Java-specific details, but the general pattern is the same as with any other client API.

0 Votes 0 ·