1. Learn how to define an auto increment primary key in SQL Server. This data tutorial will explain basic table creation and information around using identity a.
2. Return auto generated key in table output fails on SQL Server 2005: 2011/03/29 12:15:06 - Table output.0 - ERROR (version 4.1.0-GA, build 14380 from 2010-11-09 17.25.17 by buildguy): Unable to release database transaction savepoint.
3. May 28, 2017  In this video you will learn How to get primary key value (auto-generated keys) from inserted queries in JDBC using a demo project.
4. Knowing the primary key value allows you to locate the row that contains it. Relational database engines, such as SQL Server, Oracle, and Microsoft Access/Jet support the creation of automatically incrementing columns that can be designated as primary keys. These values are generated by the server as rows are added to a table.
5. This example shows how to retrieve auto generated primary key by the database (via an insert statement). Following method of JdbcTemplate takes KeyHolder argument which will contain the generated key on the successful insert execution.

I'm trying to get a the key-value back after an INSERT-statement. Example: I've got a table with the attributes name and id. Id is a generated value. INSERT INTO table (name) VALUES('bob'); Now I want to get the id back in the same step. How is this done? We're using Microsoft SQL Server 2008.

Oracle Java Database Connectivity (JDBC) supports several different versions of JDBC, including JDBC 2.0 and 3.0. This chapter provides an overview of JDBC 2.0 and JDBC 3.0 support in the Oracle JDBC drivers. The chapter contains the following sections:

Introduction

The Oracle JDBC drivers support all JDBC 3.0 features. These features are provided through the oracle.jdbc and oracle.sql packages. These packages support all Java Development Kit (JDK) releases from 1.2 through 1.4. JDBC 3.0 features that depend on JDK1.4 are made available to earlier JDK versions through Oracle extensions.

JDBC 2.0 Support: JDK 1.2.x and Later Versions

Standard JDBC 2.0 features are supported by JDK 1.2 and later versions. There are three areas to consider:

• Support for data types, such as objects, arrays, and large objects (LOBs). This is handled through the standard java.sql package.

• Support for standard features, such as result set enhancements and update batching. This is handled through standard objects, such as Connection, ResultSet, and PreparedStatement, under JDK 1.2.x and later.

• Support for extended features, such as features of the JDBC 2.0 optional package, also known as the standard extension application programming interface (API), including data sources, connection pooling, and distributed transactions.

This section covers the following topics:

Note:

JDK1.1.x is no longer supported. The package oracle.jdbc2 has been removed.

Data Type Support

Oracle JDBC fully supports JDK 1.2.x, which includes standard JDBC 2.0 functionality through implementation of interfaces in the standard java.sql package. These interfaces are implemented as appropriate by classes in the oracle.sql and oracle.jdbc packages.

Standard Feature Support

In a JDK 1.2.x environment, using the JDBC classes in classes12.jar, JDBC 2.0 features, such as scrollable result sets, updatable result sets, and update batching, are supported through methods specified by standard JDBC 2.0 interfaces.

Extended Feature Support

Features of the JDBC 2.0 optional package, including data sources, connection pooling, and distributed transactions, are supported in a JDK 1.2.x or later environment.

The standard javax.sql package and classes that implement its interfaces are included in the Java Archive (JAR) files packaged with the Oracle Database.

Standard versus Oracle Performance Enhancement APIs

The following performance enhancements are available under JDBC 2.0, which had previously been available as Oracle extensions:

• Update batching

• Fetch size or row prefetching

In each case, you have the option of using the standard model or the Oracle model. Do not, however, try to mix usage of the standard model and Oracle model within a single application for either of these features.

See Also:

JDBC 3.0 Support: JDK 1.4 and Previous Releases

Table 4-1 lists the interfaces and classes added or extended to support specific JDBC features.

Table 4-1 JDBC 3.0 Feature Support

New feature	JDK1.4 implementation	Pre-JDK1.4 implementation
Savepoints (new class)	java.sql.Savepoint	oracle.jdbc.OracleSavepoint
Savepoints (connection extensions)	java.sql.connection	oracle.jdbc.OracleConnection
Querying parameter capacities (new class)	java.sql.ParameterMetaData	oracle.jdbc.OracleParameterMetaData
Querying parameter capacities (interface change)	Not applicable	oracle.jdbc.OraclePreparedStatement
Resource adapters	oracle.jdbc.connector	oracle.jdbc.connector
RowSets	oracle.jdbc.rowset	oracle.jdbc.rowset
LOB modification	Not applicable	oracle.sql.BLOBoracle.sql.CLOB
Retrieving auto-generated keys	oracle.sql.Statement	oracle.jdbc.OracleStatement oracle.jdbc.OracleConnection
Result set holdability	java.sql.Connection java.sql.DatabaseMetaData java.sql.Statement	oracle.jdbc.OracleConnection oracle.jdbc.OracleDatabaseMetadata oracle.jdbc.OracleStatement

Overview of Supported JDBC 3.0 Features

Table 4-2 lists the JDBC 3.0 features supported at this release and gives references to a detailed discussion of each feature.

Table 4-2 Key Areas of JDBC 3.0 Functionality

Feature	Comments and References
Transaction savepoints	See 'Transaction Savepoints' for information.
Statement caching	Reuse of prepared statements by connection pools. See Chapter 22, 'Statement Caching'.
Switching between local and global transactions	See 'Switching Between Global and Local Transactions'.
LOB modification	See 'JDBC 3.0 LOB Interface Methods' .
Named SQL parameters	See 'Interface oracle.jdbc.OracleCallableStatement' and 'Interface oracle.jdbc.OraclePreparedStatement' .
RowSets	See Chapter 20, 'JDBC RowSets'
Retrieving auto-generated keys	See 'Retrieval of Auto-Generated Keys'
Result set holdability	See 'Result Set Holdability'

Transaction Savepoints

The JDBC 3.0 specification supports savepoints, which offer finer demarcation within transactions. Applications can set a savepoint within a transaction and then roll back all work done after the savepoint. Savepoints relax the atomicity property of transactions. A transaction with a savepoint is atomic in the sense that it appears to be a single unit outside the context of the transaction, but code operating within the transaction can preserve partial states.

Note:

Savepoints are supported for local transactions only. Specifying a savepoint within a global transaction causes SQLException to be thrown.

JDK1.4 specifies a standard savepoint API. Oracle JDBC provides the following different savepoint interfaces:

• java.sql.Savepoint

  Works with JDK1.4

• oracle.jdbc.OracleSavepoint

  Works across all supported JDK versions.

JDK1.4 adds savepoint-related APIs to java.sql.Connection. The Oracle JDK version-independent interface, oracle.jdbc.OracleConnection, provides equivalent functionality.

Creating a Savepoint

You create a savepoint using either Connection.setSavepoint, which returns a java.sql.Savepoint instance, or OracleConnection.oracleSetSavepoint, which returns an oracle.jdbc.OracleSavepoint instance.

Return Auto Generated Key In Sql 2016

A savepoint is either named or unnamed. You specify the name of a savepoint by supplying a string to the setSavepoint method. If you do not specify a name, then the savepoint is assigned an integer ID. You retrieve a name using getSavepointName(). You retrieve an ID using getSavepointId().

Note:

Attempting to retrieve a name from an unnamed savepoint or attempting to retrieve an ID from a named savepoint throws an SQLException.

Rolling back to a Savepoint

You roll back to a savepoint using Connection.rollback(Savepoint svpt) or OracleConnection.oracleRollback(OracleSavepoint svpt). If you try to roll back to a savepoint that has been released, then SQLException is thrown.

Releasing a Savepoint

You remove a savepoint using one of the following methods:

• Connection.releaseSavepoint(Savepoint svpt)

• OracleConnection.oracleReleaseSavepoint(OracleSavepoint svpt)

Note:

As of Oracle Database 10g, releaseSavepoint and oracleReleaseSavepoint are not supported. If you call either of the methods, then SQLException is thrown with the message 'Unsupported feature'.

Checking Savepoint Support

You query whether savepoints are supported by your database by calling oracle.jdbc.OracleDatabaseMetaData.supportsSavepoints(), which returns true if savepoints are available, false otherwise.

Savepoint Notes

When using savepoints, you must consider the following:

Return Auto Generated Key In Sql File

• After a savepoint has been released, attempting to reference it in a rollback operation will cause an SQLException to be thrown.

• When a transaction is committed or rolled back, all savepoints created in that transaction are automatically released and become invalid.

• Rolling a transaction back to a savepoint automatically releases and makes invalid any savepoints created after the savepoint in question.

Savepoint Interfaces

The following methods are used to get information from savepoints. These methods are defined within both the java.sql.Connection and oracle.jdbc.OracleSavepoint interfaces:

• public int getSavepointId() throws SQLException;

  Returns the savepoint ID for an unnamed savepoint. Throws SQLException if self is a named savepoint.

• public String getSavepointName() throws SQLException;

  Returns the name of a named savepoint. Throws SQLException if self is an unnamed savepoint.

The following methods are defined within the java.sql.Connection interface:

• public Savepoint setSavepoint() throws SQLException;

  Creates an unnamed savepoint. Throws SQLException on database error or if connection is in the auto-commit mode or participating in a global transaction.

• public Savepoint setSavepoint(String name) throws SQLException;

  Creates a named savepoint. If a savepoint by this name already exists, then this instance replaces it. Throws SQLException on database error or if connection is in the auto-commit mode or participating in a global transaction.

• public void rollback(Savepoint savepoint) throws SQLException;

  Removes specified savepoint from current transaction. Any references to the savepoint after it is removed cause an SQLException to be thrown. Throws SQLException on database error or if connection is in the auto-commit mode or participating in a global transaction.

• public void releaseSavepoint(Savepoint savepoint) throws SQLException;

  Not supported at this release. Always throws SQLException.

Pre-JDK1.4 Savepoint Support

The following methods are defined within the oracle.jdbc.OracleConnection interface. Except for using OracleSavepoint in the signatures, they are identical to the methods declared in the preceding section.

Retrieval of Auto-Generated Keys

Many database systems automatically generate a unique key field when a row is inserted. Oracle Database provides the same functionality with the help of sequences and triggers. JDBC 3.0 introduces the retrieval of auto-generated keys feature that enables you to retrieve such generated values. In JDBC 3.0, the following interfaces are enhanced to support the retrieval of auto-generated keys feature:

These interfaces provide methods that support retrieval of auto-generated keys. However, this feature is supported only when INSERT statements are processed. Other data manipulation language (DML) statements are processed, but without retrieving auto-generated keys.

Note:

The Oracle server-side internal driver does not support the retrieval of auto-generated keys feature.

java.sql.DatabaseMetaData

In JDBC 3.0, the java.sql.DatabaseMetaData interface provides the following method:

Return Auto Generated Key In Sql Tutorial

The method indicates whether retrieval of auto-generated keys is supported or not by the JDBC driver and the underlying data source.

java.sql.Statement

The java.sql.Statement interface is enhanced with the following methods:

This interface provides new methods for processing SQL statements and retrieving auto-generated keys. These methods take a String object that contains a SQL statement. They also take either the flag, Statement.RETURN_GENERATED_KEYS, indicating whether any generated columns are to be returned, or an array of column names or indexes specifying the columns that should be returned. The flag and the array values are considered only when an INSERT statement is processed. If an UPDATE or DELETE statement is processed, these values are ignored.

The getGeneratedKeys() method enables you to retrieve the auto-generated key fields. The auto-generated keys are returned as a ResultSet object.

If key columns are not explicitly indicated, then Oracle JDBC drivers cannot identify which columns need to be retrieved. When a column name or column index array is used, Oracle JDBC drivers can identify which columns contain auto-generated keys that you want to retrieve. However, when the Statement.RETURN_GENERATED_KEYS integer flag is used, Oracle JDBC drivers cannot identify these columns. When the integer flag is used to indicate that auto-generated keys are to be returned, the ROWID pseudo column is returned as key. The ROWID can be then fetched from the ResultSet object and can be used to retrieved other columns.

java.sql.Connection

The java.sql.Connection interface is enhanced with the following methods:

These methods enable you to create a PreparedStatement object that is capable of returning auto-generated keys.

Sample Code

The following code illustrates retrieval of auto-generated keys:

In the preceding example, a sequence, SEQ01, is created to generate values for the ORDER_ID column starting from 1000 and incrementing by 1 each time the sequence is processed to generated the next value. An OraclePreparedStatement object is created to insert a row in to the ORDERS table.

JDBC 3.0 LOB Interface Methods

Before Oracle Database 10g release 2 (10.2), Oracle provided proprietary interfaces for modification of LOB data. JDBC 3.0 adds methods for these operations. In Oracle9i Database release 2, the JDBC 3.0 methods were present in ojdbc14.jar, but were not functional. The JDBC 3.0 standard LOB methods differ slightly in name and function from the Oracle proprietary ones. In Oracle Database 10g release 2 (10.2), the JDBC 3.0 standard methods are implemented in both ojdbc14.jar and classes12.jar. In order to use these methods with JDK1.2 or 1.3, LOB variables must be typed as, or cast to, oracle.sql.BLOB or oracle.sql.CLOB as appropriate. With JDK1.4, LOB variables may be typed as java.sql.Blob or java.sql.Clob.

Table 4-3 and Table 4-4 show the conversions between Oracle proprietary methods and JDBC 3.0 standard methods.

Table 4-3 BLOB Method Equivalents

Oracle Proprietary Method	JDBC 3.0 Standard Method
putBytes(long pos, byte [] bytes)	setBytes(long pos, byte[] bytes)
putBytes(long pos, byte [] bytes, int length)	setBytes(long pos, byte[] bytes, int offset, int len)
getBinaryOutputStream(long pos)	setBinaryStream(long pos)
trim (long len)	truncate(long len)

Table 4-4 CLOB Method Equivalents

Oracle Proprietary Method	JDBC 3.0 Standard Method
putString(long pos, String str)	setString(long pos, String str)
not applicable	setString(long pos, String str, int offset, int len)
getAsciiOutputStream(long pos)	setAsciiStream(long pos)
getCharacterOutputStream(long pos)	setCharacterStream(long pos)
trim (long len)	truncate(long len)

Result Set Holdability

Result set holdability is a new feature introduced in JDBC 3.0. This feature enables applications to decide whether the ResultSet objects should be open or closed, when a commit operation is performed. The commit operation could be either implicit or explicit.

The default holdability property of a ResultSet object is implementation defined. The default holdability of ResultSet objects returned by the underlying data source can be determined using the APIs provided by JDBC 3.0.

In JDBC 3.0, the following APIs have been modified to support this feature:

• java.sql.Connection

• java.sql.DatabaseMetaData

• java.sql.Statement

JDBC 3.0 also provides two new ResultSet constants:

• ResultSet.HOLD_CURSORS_OVER_COMMIT

• ResultSet.CLOSE_CURSORS_AT_COMMIT

Oracle Database only supports HOLD_CURSORS_OVER_COMMIT. Therefore, it is the default value for the Oracle JDBC drivers. Any attempt to change holdability will throw SQLException.

The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows:

Which returns:

No value was specified for the AUTO_INCREMENT column, so MySQL assigned sequence numbers automatically. You can also explicitly assign 0 to the column to generate sequence numbers, unless the NO_AUTO_VALUE_ON_ZERO SQL mode is enabled. For example:

If the column is declared NOT NULL, it is also possible to assign NULL to the column to generate sequence numbers. For example:

When you insert any other value into an AUTO_INCREMENT column, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the largest column value. For example:

Updating an existing AUTO_INCREMENT column value also resets the AUTO_INCREMENT sequence.

You can retrieve the most recent automatically generated AUTO_INCREMENT value with the LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function. These functions are connection-specific, so their return values are not affected by another connection which is also performing inserts.

Return Auto Generated Key In Sql Server

Use the smallest integer data type for the AUTO_INCREMENT column that is large enough to hold the maximum sequence value you will need. When the column reaches the upper limit of the data type, the next attempt to generate a sequence number fails. Use the UNSIGNED attribute if possible to allow a greater range. For example, if you use TINYINT, the maximum permissible sequence number is 127. For TINYINT UNSIGNED, the maximum is 255. See Section 11.1.2, “Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT” for the ranges of all the integer types.

For a multiple-row insert, LAST_INSERT_ID() and mysql_insert_id() actually return the AUTO_INCREMENT key from the first of the inserted rows. This enables multiple-row inserts to be reproduced correctly on other servers in a replication setup.

To start with an AUTO_INCREMENT value other than 1, set that value with CREATE TABLE or ALTER TABLE, like this:

For information about AUTO_INCREMENT usage specific to InnoDB, see Section 15.6.1.6, “AUTO_INCREMENT Handling in InnoDB”.

Return Auto Generated Key In Sql 2017

• For MyISAM tables, you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

  Which returns:

  In this case (when the AUTO_INCREMENT column is part of a multiple-column index), AUTO_INCREMENT values are reused if you delete the row with the biggest AUTO_INCREMENT value in any group. This happens even for MyISAM tables, for which AUTO_INCREMENT values normally are not reused.

• If the AUTO_INCREMENT column is part of multiple indexes, MySQL generates sequence values using the index that begins with the AUTO_INCREMENT column, if there is one. For example, if the animals table contained indexes PRIMARY KEY (grp, id) and INDEX (id), MySQL would ignore the PRIMARY KEY Generate cert pem key pem. for generating sequence values. As a result, the table would contain a single sequence, not a sequence per grp value.

More information about AUTO_INCREMENT is available here:

• How to assign the AUTO_INCREMENT attribute to a column: Section 13.1.20, “CREATE TABLE Statement”, and Section 13.1.9, “ALTER TABLE Statement”.

• How AUTO_INCREMENT behaves depending on the NO_AUTO_VALUE_ON_ZERO SQL mode: Section 5.1.11, “Server SQL Modes”.

• How to use the LAST_INSERT_ID() function to find the row that contains the most recent AUTO_INCREMENT value: Section 12.15, “Information Functions”.

• Setting the AUTO_INCREMENT value to be used: Section 5.1.8, “Server System Variables”.

• AUTO_INCREMENT and replication: Section 17.5.1.1, “Replication and AUTO_INCREMENT”.

• Server-system variables related to AUTO_INCREMENT (auto_increment_increment and auto_increment_offset) that can be used for replication: Section 5.1.8, “Server System Variables”.