@@IDENTITY correct syntax?

Thread view:

Mike - 13 Dec 2003 15:44 GMT

Hi,
I am new to MS SQL and JSP and am having trouble finding documentation
or code examples for this feature.

I do an INSERT into a table with an IDENTITY column, then want to
retrieve the value from the incremented IDENTITY column in the row
I've just created. (Called "threadID".) I then insert "threadID"'s
value into another table.

Does the following appear correct? (And should I be using
SCOPE_IDENTITY instead of @@IDENTITY?)

// insert new thread
String threadQuery = "insert into threads (forumID, threadTitle,
views) values ('"+forumID+"','"+threadTitle+"','"+views+"') select
@@IDENTITY as 'Identity'";
myStatement.executeUpdate(threadQuery);
ResultSet rs = myStatement.GetResultSet();
int threadID = rs.getInt("Identity");

// insert new post
String postsQuery = "insert into posts (threadID, forumID, userName,
comments, submitDate, picURL, picCaption) values
('"+threadID+"','"+forumID+"','"+userName+"','"+comments+"','"+submitDate+"','"+imageURL+"','"+imageCaption+"')";
myStatement.executeUpdate(postsQuery);

Thanks,
Mike

Reply to this Message

gnazio - 13 Dec 2003 21:43 GMT

> Hi,
> I am new to MS SQL and JSP and am having trouble finding documentation
[quoted text clipped - 7 lines]
> Does the following appear correct? (And should I be using
> SCOPE_IDENTITY instead of @@IDENTITY?)

Does it works?

> // insert new thread
> String threadQuery = "insert into threads (forumID, threadTitle,
[quoted text clipped - 12 lines]
> Thanks,
> Mike

Try this and let me know...
...
// insert new thread
String threadQuery = "insert into threads (forumID, threadTitle,views)
values ('"+forumID+"','"+threadTitle+"','"+views+"')";
myStatement.executeUpdate(threadQuery);

ResultSet rs = myStatement.executeQuery("select @@IDENTITY");
rs.next();
int threadID = rs.getInt(1);
rs.close();

// insert new post
String postsQuery = "insert into posts (threadID, forumID, userName,
comments, submitDate, picURL, picCaption)
values('"+threadID+"','"+forumID+"','"+userName+"','"+comments+"','"+submitDate+"','"+imageURL+"','"+imageCaption+"')";
myStatement.executeUpdate(postsQuery);
...

Reply to this Message

Mike - 14 Dec 2003 09:54 GMT

> Try this and let me know...
> ...
[quoted text clipped - 14 lines]
> myStatement.executeUpdate(postsQuery);
> ...

Hi,
I can't test over the weekend, so I don't know if my original
suggestion works. I'll try both approaches on Monday.
M

Reply to this Message

Mike - 15 Dec 2003 21:59 GMT

> Try this and let me know...
> ...
[quoted text clipped - 14 lines]
> myStatement.executeUpdate(postsQuery);
> ...

Hi,
That seems to work well. However, is it possible that another user
could make an insert just after this one, so that the @@IDENTITY value
returned is that created by the second, wrong insert? Or will this
example only return the identity value created by myStatement's
executeUpdate?
Thanks,
Mike

Reply to this Message

Leo Gaggl - 15 Dec 2003 22:37 GMT

> Hi,
> That seems to work well. However, is it possible that another user
[quoted text clipped - 4 lines]
> Thanks,
> Mike

According to the docs it will return the identity produced by the
current statement if selected within the same statement. Although I seem
to recall from the good old days (SQL Server 6.x) that this was not
always the case in high load environments.

See SQL Docs:

@@IDENTITY
Returns the last-inserted identity value.

Syntax
@@IDENTITY

Return Types
numeric

Remarks
After an INSERT, SELECT INTO, or bulk copy statement completes,
@@IDENTITY contains the last identity value generated by the statement.
If the statement did not affect any tables with identity columns,
@@IDENTITY returns NULL. If multiple rows are inserted, generating
multiple identity values, @@IDENTITY returns the last identity value
generated. If the statement fires one or more triggers that perform
inserts that generate identity values, calling @@IDENTITY immediately
after the statement returns the last identity value generated by the
triggers. The @@IDENTITY value does not revert to a previous setting if
the INSERT or SELECT INTO statement or bulk copy fails, or if the
transaction is rolled back.

@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions in
that they return the last value inserted into the IDENTITY column of a
table.

@@IDENTITY and SCOPE_IDENTITY will return the last identity value
generated in any table in the current session. However, SCOPE_IDENTITY
returns the value only within the current scope; @@IDENTITY is not
limited to a specific scope.

IDENT_CURRENT is not limited by scope and session; it is limited to a
specified table. IDENT_CURRENT returns the identity value generated for
a specific table in any session and any scope. For more information, see
IDENT_CURRENT.

HTH,

---
Leo Gaggl
Adelaide, South Australia

Reply to this Message

Mike - 16 Dec 2003 09:11 GMT

> According to the docs it will return the identity produced by the
> current statement if selected within the same statement. Although I seem
> to recall from the good old days (SQL Server 6.x) that this was not
> always the case in high load environments.

Thanks!
M

Reply to this Message

@@IDENTITY correct syntax?

Free Magazines