Microsoft KB Archive/894562

= 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 =

Article ID: 894562

Article Last Modified on 11/28/2005

-

APPLIES TO


 * Microsoft SQL Server 2000 Driver for JDBC

-





SYMPTOMS
Consider the following scenario:
 * You use the Microsoft SQL Server 2000 Driver for JDBC to connect your program to a Microsoft SQL Server 2000 database.
 * SQL Server 2000 generates a 3604 error code.
 * You use the SQLWarning.getErrorCode method to retrieve the error code.

In this scenario, the SQLWarning class only receives the warning message. Additionally, the SQLWarning class returns the error code as 0 (zero) and does not return the SQL state.

You expect the SQLWarning class to return the valid error code together with the correct SQL state.

Note In a similar scenario, the SQL Server 2000 ODBC driver returns this error as an SQL_ERROR result instead of as a warning. The SQL Server 2000 ODBC driver returns an error result because the severity level is 16.



RESOLUTION
To resolve this problem, install SQL Server 2000 Driver for JDBC Service Pack 3. To obtain this service pack, visit the following Microsoft Web site:

http://www.microsoft.com/downloads/details.aspx?familyid=07287b11-0502-461a-b138-2aa54bfdc03a



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the &quot;Applies to&quot; section. This problem was corrected in SQL Server 2000 Driver for JDBC Service Pack 3.



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(&quot;com.microsoft.jdbc.sqlserver.SQLServerDriver&quot;).newInstance;

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

conn = DriverManager.getConnection(connURL);

stmt = conn.createStatement;

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

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

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

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

updateCount = stmt.getUpdateCount;

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

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

// The driver returns the &quot;Duplicate key was ignored&quot; message.

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

try

{

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

updateCount = stmt.getUpdateCount;

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

SQLWarning sqlWarn = stmt.getWarnings;

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

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

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

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

}

catch(Exception e)

{

if (e instanceof SQLException)

{

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

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

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

}

System.out.println(&quot;Insert failed. &quot; + ((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:

824684 Description of the standard terminology that is used to describe Microsoft software updates

