Microsoft KB Archive/313130

= How To Retrieve @@IDENTITY Value Using JDBC =

Article ID: 313130

Article Last Modified on 7/2/2004

-

APPLIES TO


 * Microsoft SQL Server 2000 Driver for JDBC
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2000 64-bit Edition

-



This article was previously published under Q313130



SUMMARY
When connected to a Microsoft SQL Server database, you can use the @@IDENTITY value to return the last-inserted identity value. This article contains a code sample that demonstrates how to retrieve this value in a Java application that uses the Microsoft SQL Server 2000 Driver for JDBC.



MORE INFORMATION
The code sample in this article illustrates two different methods to retrieve the @@IDENTITY value with JDBC.

 ==== Method 1 ====

Use a stored procedure that performs an INSERT into a table, and then returns the @@IDENTITY value as an output parameter. Output parameters from a stored procedure are not available until all of the resultsets have been processed. As soon as the resultset from the stored procedure has been fetched, the output value of this procedure will contain the @@IDENTITY value.  ==== Method 2 ====

Submit a batch query that contains a SELECT @@IDENTITY statement to retrieve the @@IDENTITY value. As the resultsets from the batch are processed, the @@IDENTITY values are available as a column of a resultset. 

NOTE: The code in this article is set up to use a stored procedure by default. See the comments in the code for the lines that have to be uncommented to use the batch statement instead.

Steps to Run the Sample Code
  Use the following SQL code to create the sample table and stored procedure in your SQL Server database: CREATE TABLE myIdentTable (col1 int NOT NULL IDENTITY(1,1), col2 varchar(20)) GO

CREATE PROCEDURE myIdentProc (@ident INT OUTPUT, @cvalue varchar(20)) AS INSERT INTO myIdentTable (col2) VALUES (@cvalue) SELECT @ident = @@IDENTITY GO   Copy the following sample code into a new Java source file: import java.sql.*; import java.io.*;

public class IdentitySample {   public static void main(String args[]) {       try {           String URL = &quot;jdbc:microsoft:sqlserver://yourServer:1433;databasename=pubs&quot;; String userName = &quot;yourUser&quot;; String password = &quot;yourPassword&quot;; System.out.println( &quot;Trying to connect to: &quot; + URL);

//Register JDBC Driver Class.forName(&quot;com.microsoft.jdbc.sqlserver.SQLServerDriver&quot;).newInstance;

//Connect to SQL Server Connection con = null; con = DriverManager.getConnection(URL,userName,password); System.out.println(&quot;Successfully connected to server&quot;); //Create statement and Execute using either a stored procecure or batch statement CallableStatement callstmt = null; //Using a stored procedure callstmt = con.prepareCall(&quot;{call myIdentProc(?,?)}&quot;); callstmt.registerOutParameter(1, java.sql.Types.INTEGER); callstmt.setString(2, &quot;testInputProc&quot;); System.out.println(&quot;Stored procedure successfully executed&quot;); callstmt.execute; //OR //Using a batch statement directly /*           callstmt = con.prepareCall(&quot;INSERT INTO myIdentTable (col2) VALUES (?);SELECT @@IDENTITY&quot;); callstmt.setString(1, &quot;testInputBatch&quot;); System.out.println(&quot;Batch statement successfully executed&quot;); callstmt.execute; */            int iUpdCount = callstmt.getUpdateCount; boolean bMoreResults = true; ResultSet rs = null; int myIdentVal = -1; //to store the @@IDENTITY //While there are still more results or update counts //available, continue processing resultsets while (bMoreResults || iUpdCount!=-1) {                          //NOTE: in order for output parameters to be available, //all resultsets must be processed rs = callstmt.getResultSet; //Use the following if using the batch statement instead of the stored procedure //if rs is not null, we know we can get the results from the SELECT @@IDENTITY /*               if (rs != null) {                   rs.next; myIdentVal = rs.getInt(1); }               */                 //Do something with the results here (not shown)

//get the next resultset, if there is one //this call also implicitly closes the previously obtained ResultSet bMoreResults = callstmt.getMoreResults; iUpdCount = callstmt.getUpdateCount; }           //Use the following if using the stored procedure //retrieve the @@IDENTITY here because we know all results have been processed, //comment out when using batch myIdentVal = callstmt.getInt(1); System.out.println( &quot;@@IDENTITY is: &quot; + myIdentVal); //Close statement and connection callstmt.close; con.close; }       catch (Exception ex) {           ex.printStackTrace; }       try {           System.out.println(&quot;Press any key to quit...&quot;); System.in.read; }       catch (Exception e)        { }   } }                     Change the URL, the username, and the password variables to reference appropriate connection information for your SQL Server. Compile and run the sample.</li> The output should look similar to the following:

Trying to connect to: jdbc:microsoft:sqlserver://yourServer:1433;databasename=pubs

Successfully connected to server

Stored procedure successfully executed

@@IDENTITY is: 1

Press any key to quit..

On subsequent executions, the identity value increments by one each time the program runs.</li></ol>

<div class="references_section">