How to Select @@IDENTITY w/o cursor library
Kaelene McElligott
Hi Ben,
I was wondering if you every found out the answer to this one, as I am
having the same problem. (the only difference is the I am using SQL Server
6.0)
I first tried using
pRecordSet->GetFieldValue("@@INDENTITY", sIdentity);
after the call to update, but that results in a DBException
AFX_SQL_ERROR_FIELD_NOT_FOUND
and then when I saw your posting, I tried what you where doing with your
CIdentity class, but when I tried that I got Invalid column name
'@@INDENTITY'
Did you get any further with this?
--
Adrian McElligott
a...@ezymail.com
http://link4free.ezymail.com/
Checkout our geographical IP address locater at
http://link4free.ezymail.com/GeoLocate.htm
_________________________________________________________-
Ben Witt <bw...@enteract.com> wrote in article
<01bca84c$0b56f3c0$0ac31ecb@www>...
> Using VC5.0, SQL Server 6.5, NT4.0
>
> We are attempting to eliminate the use of the ODBC cursor library by our
> application and have run into some difficulty in acquiring the
> @@IDENTITY value following an insert.
>
> We are using CRecordset to do the insert on the table with the identity
> value (using AddNew()/Update()) and then opening another CRecordset
> derived CIdentitySet (code below) to acquire the identity value for the
> record which was just inserted.
>
> CString CIdentitySet::GetDefaultSQL()
> {
> return( "select @@IDENTITY" );
> }
>
> void CIdentitySet::DoFieldExchange(CFieldExchange* pFX)
> {
> pFX->SetFieldType(CFieldExchange::outputColumn);
> RFX_Long(pFX, "@@IDENTITY", m_identity );
> }
>
> Without using the cursor library, the identity value that is returned is
> always NULL. When the cursor library is loaded, we get the correct
> identity value.
>
> Using the SQLTrace utility, we can see that sp_cursor is used to perform
> the insert when not using the cursor library. Is the @@IDENTITY value
> getting wiped out somewhere in the stored procedure?
>
> Any help here would be much appreciated.
>
> Ben Witt
> bw...@enteract.com
> Energy Connections
>
Ben Witt
Adrian,
I attempted to retrieve the identity value after an insert using the
same method that you presented (after correcting the typo @@IDENTITY)
and got the same field not found error when providing "@@IDENTITY" for
the column name in GetFieldValue().
I used the other method providing the column number of zero (0) to
GetFieldValue() and NULL is returned for the identity value when the
cursor library is not loaded. When the cursor library is loaded (which I
want to eliminate), the proper identity value is returned.
I have found that MFC will attempt to use SQLSetPos() to do the insert
when the cursor library is not loaded and the SQL driver supports
positioned operations. Using SQL trace, I see that the SQL driver is
using a internal server stored procedure, sp_cursor, to to the insert.
After the insert, the identity value that is returned is NULL.
What I have done is prevent MFC from using SQLSetPos() to do the insert
by changing its update options after connecting to the database via
OpenEx(). After calling OpenEx(), modify m_dwUpdateOptions to remove
the setting of the AFX_SQL_SETPOSUPDATES option (m_dwUpdateOptions &=
~AFX_SQL_SETPOSUPDATES). This will then force MFC to use positioned
update SQL to perform updates.
After doing this and using the same code from above, the correct
identity value will be returned.
Everything that I have read indicates that using SQLSetPos() to do
updates is more efficient than using positioned SQL but as yet I have
not found a way to retrieve the identity value after an insert when
using it.
If you or anyone else knows what is happening inside sp_cursor, I would
be most interested to hear about it.
Hope this helps you.
Darius Liktorius
I'm not sure if this is just a problem with MFC, I am having this problem
under Active Server Pages on IIS 3.0 which uses ADO (Active Data Objects)
for access to SQL server through ODBC. Please let me know if you all get
anywhere. From the looks of it, people are getting identity column
problems out of the blue. The only semi-obvious thing I can think of is if
you create a new table w/ an ident. column when using any version of a SQL
service pack after the original 6.50 release, it stops working.
Darius Liktorius
MCSE, MCPS
dar...@pchd.com
Ben Witt <bw...@enteract.com> wrote in article
Randy Baker
@@IDENTITY is misspelled in your code examples and error message below, but
this might just be a typo.
--
Randy Baker (remove Z from address in email replies)
Kaelene McElligott <a...@ezymail.com> wrote in article
<01bca935$75056560$0ac31ecb@www>...
Scott Turner
Darius Liktorius wrote:
> I'm not sure if this is just a problem with MFC, I am having this
> problem
> under Active Server Pages on IIS 3.0 which uses ADO (Active Data
> Objects)
> for access to SQL server through ODBC. Please let me know if you all
> get
> anywhere. From the looks of it, people are getting identity column
> problems out of the blue. The only semi-obvious thing I can think of
> is if
> you create a new table w/ an ident. column when using any version of a
> SQL
> service pack after the original 6.50 release, it stops working.
>
> Darius Liktorius
> MCSE, MCPS
> dar...@pchd.com
[ descfriptions of other hassles trying to retrieve @@identity after
insert snipped ]
I too have run into the same problem. I'm using SQL server 6.5 and
Visual Studio, with
the service pack [I'm not sure which one] but it was required to allow
VC++ to handle
database projects in the SQL server.
I am using straight C++, and after I do a successful insert into a
table with an identity,
I then issue a simple: SQLExecDirect(hstmt, "select @@identity",
SQL_NTS);
After which I fech the result set and call SQLGetData() ... I get a
null value also.
I have resulted to writing a stored procedure which does the insert,
then gets @@identity
into a single output parameter, and also returns this value as well. It
works in ISQL/W
but how can I get the return value from a sp via ODBC?
This is a genuine pain...
Luego,
scott