DOCUMENT:Q313130  06-AUG-2002  [odbc]
TITLE   :HOWTO: Retrieve @@IDENTITY Value Using JDBC
PRODUCT :Open Database Connectivity (ODBC)
PROD/VER::
OPER/SYS:
KEYWORDS:kbJDBC kbDSupport kbSQLServ2000 kbSQLServSearch kbAudDeveloper kbHOWTOmaster kbSystemDa

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft SQL Server 2000 Driver for JDBC 
 - Microsoft SQL Server 2000 (all editions) 
-------------------------------------------------------------------------------

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

1. 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

2. 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 = "jdbc:microsoft:sqlserver://yourServer:1433;databasename=pubs";
   			String userName = "yourUser";
   			String password = "yourPassword";
   		
   			System.out.println( "Trying to connect to: " + URL); 

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

   			//Connect to SQL Server
   			Connection con = null;
   			con = DriverManager.getConnection(URL,userName,password);
   			System.out.println("Successfully connected to server"); 
   			
   			//Create statement and Execute using either a stored procecure or batch statement
   			CallableStatement callstmt = null;
   			
   			//Using a stored procedure
   			callstmt = con.prepareCall("{call myIdentProc(?,?)}");
   			callstmt.registerOutParameter(1, java.sql.Types.INTEGER);
   			callstmt.setString(2, "testInputProc");
   			System.out.println("Stored procedure successfully executed");
   			callstmt.execute();
   			
   			//OR 
   			
   			//Using a batch statement directly
   			/*
   			callstmt = con.prepareCall("INSERT INTO myIdentTable (col2) VALUES (?);SELECT @@IDENTITY");
   			callstmt.setString(1, "testInputBatch");
   			System.out.println("Batch statement successfully executed"); 
   			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( "@@IDENTITY is: " + myIdentVal);		
   			
   			//Close statement and connection 
   			callstmt.close();
   			con.close();
   		}
   		catch (Exception ex)
   		{
   			ex.printStackTrace();
   		}
   		
   		try
   		{
   			System.out.println("Press any key to quit...");
   			System.in.read();
   		}
   		catch (Exception e)
   		{
   		}
   	}
   }

3. Change the URL, the username, and the password variables to reference
   appropriate connection information for your SQL Server.

4. Compile and run the sample.

5. 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.

REFERENCES
==========

For more information about the @@IDENTITY value, see SQL Server Books Online.

Additional query words: autonumber autoincrement

======================================================================
Keywords          : kbJDBC kbDSupport kbSQLServ2000 kbSQLServSearch kbAudDeveloper kbHOWTOmaster kbSystemData 
Technology        : kbSQLServSearch kbAudDeveloper kbSQLServ2000Search kbSQLServ2000 kbSQLServJDBC
Version           : :
Issue type        : kbhowto

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 2002.