FIX: The SQLWarning class does not return an error code or a valid SQL state for a 3604 error when you use the SQL Server 2000 Driver for JDBC (894562)

MORE INFORMATION

Steps to reproduce the problem

To reproduce this problem, compile and then run the following code.

import java.util.*;

import java.sql.*;

 

public class DupKeyError

{

     public static void main (String[] args) throws Exception

     {

            Connection conn         = null;

            Statement stmt          = null;

            CallableStatement cs    = null;

            ResultSet rs            = null;

            String sqlCmd; 

            String connURL;

            int updateCount;

            

            try 

            {

                 Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();

            

                    connURL = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=pubs;User=sa;Password=password1;";

                    conn = DriverManager.getConnection(connURL);

                    stmt = conn.createStatement();

                    stmt.execute("CREATE TABLE #DupKeyErr (ObjId bigint NOT NULL, RcvObjId bigint NULL, PayObjId bigint NULL)");

                    stmt.execute("ALTER TABLE #DupKeyErr WITH NOCHECK ADD CONSTRAINT DupKeyErrPK PRIMARY KEY CLUSTERED (ObjId)");

                    stmt.execute("CREATE UNIQUE INDEX DupKeyErrUX1 ON #DupKeyErr(RcvObjId, PayObjId) WITH IGNORE_DUP_KEY");

 

                    stmt.execute("INSERT INTO #DupKeyErr VALUES (1,101,201)");

                    updateCount = stmt.getUpdateCount();

                    System.out.println("Update count is " + updateCount);

                    

                    // This insert should cause the driver to return SQLWarning 3604 "Duplicate key was ignored."

                    // The driver returns the "Duplicate key was ignored" message.

                    // However, the error code is 0 instead of 3604.

                    try

                    {

                            stmt.execute("INSERT INTO #DupKeyErr VALUES (2,101,201)");

                            updateCount = stmt.getUpdateCount();

                            System.out.println("Update count is " + updateCount);

                            SQLWarning sqlWarn = stmt.getWarnings();

                            System.out.println("Expected SQL Error Code is:   3604");

                            System.out.println("Actual   SQL Error Code is:   " + sqlWarn.getErrorCode());

                            System.out.println("Actual   SQL State is     :   " + sqlWarn.getSQLState());

                            System.out.println("SQL Message Code is: " + sqlWarn.getMessage());

                    }

                    catch(Exception e)

                    {

                            if (e instanceof SQLException)

                            {

                                   System.out.println("SQL Error Code is:  " + ((SQLException)e).getErrorCode());

                                   System.out.println("SQL Message Code is: " + e.getMessage());

                                   System.out.println("SQL State is: " + ((SQLException)e).getSQLState());

                            }

                            System.out.println("Insert failed. " + ((SQLException)e).getMessage());

                            return;

                    }

            }

         catch (Exception e)

            { 

             e.printStackTrace(); 

         }

         finally

            {

                    if (conn != null)

                            conn.close();

            }

     }

}

When you run this code, the following output is generated: Update count is 1
Update count is 0
Expected SQL Error Code is: 3604
Actual SQL Error Code is: 0
Actual SQL State is :
SQL Message Code is : [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Duplicate key was ignored. For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base: