The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

SQL Server SCOPE_IDENTITY()

Suppose that Table1 has an IDENTITY primary key, and that after inserting a new row into this table, I'd like to get the ID that was just generated.

My understanding is that the last IDENTITY value generated by SQL Server can be returned via a call to SCOPE_IDENTITY().  When I run the following query

BEGIN TRANSACTION
INSERT....
SELECT SCOPE_IDENTITY()
COMMIT;


In Query Analyzer, it returns a single row with a single column containing the id.

In my program, I'm using ODBC, and I'd like to receive the last ID in a program variable. I'm sort of new to ODBC, but what I've done is create a statement, prepare it, bind a long integer to column 1, and then call execute.  When I call SQLFetch, though, the "function sequence error" message is returned.

I'm in manual commit mode.  I'm calling SQLEndTrans to commit the tran before I call SQL Fetch.

What are some of the things I could be doing wrong?  What would be a better way to achieve this?  Stored procedure?

Thanks,
ODBC Rookie Send private email
Monday, March 12, 2007
 
 
This won't work because SCOPE_IDENTITY() returns the last ID assigned in the current scope (sp, trigger, function, or batch).  Since your INSERT wasn't in the same scope as the function, the function returns an error.

You could use IDENT_CURRENT('tablename').  But I wouldn't.

Because the bigger problem is one of concurrency.  What if some other process did an INSERT between your last INSERT and your call to IDENT_CURRENT()?  The ID you get back won't be the ID of the record you inserted.

To control for this, you should use a stored proc to do the INSERT, and have the SP return the ID.  There are other ways to accomplish this, but that's the one that will work in all circumstances.
Robert Rossney Send private email
Monday, March 12, 2007
 
 
"Since your INSERT wasn't in the same scope as the function, the function returns an error."

Why isn't it in the same scope? When I do the same thing using the sqloledb provider for ADO, it works. Does ODBC handle such things differently?
clcr
Monday, March 12, 2007
 
 
Actually, this statement doesn't fail to _execute_, so I'm not sure if the second poster's comment was entirely accurate.  It looks as though ODBC just can't get at the results.

As I mentioned, when the query is run in the query analyzer, it succeeds and returns a result set containing one datum (the correct value of the identity column last inserted.)

I know that I shouldn't use @@IDENTITY because that suffers from the problem the prev poster mentioned.  I thought that SCOPE_IDENTITY was safe because the documentation mentioned that things done in a single statement were in the same scope, and in my application, each thread of execution will have its own connection and statement handle(s).

As for writing a stored procedure, I'm inclined to think this might be the way to go, but in writing the SP, I basically just wrote the same SQL, so I fail to understand why that would work and the previous thing wouldn't.  I guess I'll RTFM some more.  I'm really a SQL newbie, I guess.

>> Does ODBC handle such things differently?

I bet it does.  I saw some posts on the net about .NET people doing this and not having problems.
ODBC Rookie Send private email
Monday, March 12, 2007
 
 
I should read more carefully.  If you're inside a transaction, SCOPE_IDENTITY() should be returning the right value.

What I'd try:  I'd take the whole transaction and table out of play and see if I could get ODBC to return a result set from, say,

  SELECT db_id()

ODBC can handle result sets containing computed columns with no name, or at least the documentation claims it can.
Robert Rossney Send private email
Monday, March 12, 2007
 
 
I think that eliminating the variables in this equation is a great place to start.  Thanks!

I'll post back with what I find.
ODBC Rookie
Monday, March 12, 2007
 
 
Got it!

My first clue was that after executing the stored procedure a call so SQLNumResultCols() returned 0 columns-- not what I expected at all.

I changed my stored procedure to do a "SET NOCOUNT ON" as the first statement.

After that, it seems to work.  For those who might be trying to solve the same problem, my exact sequence of calls is as follows:

(Connection is in manual commit mode)

SQLAllocStmt( ... )
SQLExecDirect( ... )
SQLBindCol( ... )
SQLFetch( ... )
SQLEndTran( ... )

If any gurus are out there and think this is wrong, please let me know. 

Could the last poster recommend a good book on T-SQL that would provide a decent introduction but be detailed enough to cover stored procedures?

I've been programming for years but still don't feel I have the level of knowledge I should have when it comes to SQL.
ODBC Rookie
Monday, March 12, 2007
 
 
I'm curious, why did you choose ODBC?

ODBC is still supported and Microsoft did bother to write 64-bit ODBC components for Windows, but it is telling that there is no 64-bit OLEDB Provider for ODBC.

ODBC has been stagnant for 12 years now, but three generations of newer technologies have been invented since then.  Any of the newer technologies would have made this job easier, with ADO.Net reducing it to one method call, ExecuteScalar() to return the identity value.
JSmith Send private email
Monday, March 12, 2007
 
 
Partially because I am somewhat familiar with it.  But no particular reason.  One thing: can you use ADO.NET from an _unmanaged_ C++ app?
ODBC Rookie
Monday, March 12, 2007
 
 
I'm not sure how complicated it it, but COM interop wrappers are, by definition, unmanged programs that access .Net classes.  Those are built on the fly by the IDE.  So, yes you can.  But, I'm not sure if it is worth it or not.
JSmith Send private email
Tuesday, March 13, 2007
 
 
Building a COM wrapper around ADO.NET is a non-trivial project.  Hell, just deciding which ADO.NET features you wouldn't implement in your COM wrapper is a non-trivial project.
Robert Rossney Send private email
Tuesday, March 13, 2007
 
 
So, build a COM interop wrapper for a .NET assembly and call it from a C++ program?  Sounds like a lot of work with near zero return on my investment when I could just use ODBC.
ODBC Rookie Send private email
Tuesday, March 13, 2007
 
 
You could use ADO with a minimum of pain, but I'm not sure what gains it would bring you vs. ODBC.
clcr
Tuesday, March 13, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz