Getting @@Identity from SQL Server via ODBC Driver 
Author Message
 Getting @@Identity from SQL Server via ODBC Driver

I need to access a SQL Server 6.5 via ODBC;
I have this problem on master - details Form;
I'd like to get ID of the inserted master record;

The following code is correct if I use native delphi Driver, but if I use
ODBC Driver the variable id returns 0!

Can you help me?

Thanks in advance.

Maurizio Baldi, Abel Key Scrl, Florence, Italy-

DBUPD : TUpdateSQL;
DBQ : TQuery;
DBQ_Exec : TQuery;
id : integer;
....
DBQ.UpdateObject := DBUPD;
DBQ.CachedUpdates := True;
DBQ.RequestLive := True;
DBUPD.Sql[ukInsert].Text := 'Insert into Books (title, author) values
(''1984'',''Orwell'')';

DBQ.Append;
[some field operations....]
DBQ.Post;

DBQ.Database.StartTransaction;
try
    DBUPD.Apply(ukInsert);
    with DBQ_Exec do begin
        if Active then Close;

        Open;
        id := FieldByName('ID').AsInteger;
    end;
    [operations with details...]
    DBQ.Database.Commit;
except
    DBQ.Database.Rollback;
end;

These are the SQL trace in the 2 cases...

NATIVE DRIVER:
-- 24/11/99 11:40:09,073 SQL (ID=141, SPID=17, User=sa(AK_PD\BALDMA),
App='', Host=''(ffc0a5b5) )
BEGIN TRAN
go
-- 24/11/99 11:40:18,206 SQL (ID=141, SPID=17, User=sa(AK_PD\BALDMA),
App='', Host=''(ffc0a5b5) )
Insert into Books (title, author) values ('1984','Orwell')
go
-- 24/11/99 11:40:20,390 SQL (ID=141, SPID=17, User=sa(AK_PD\BALDMA),
App='', Host=''(ffc0a5b5) )

go

ODBC DRIVER:
-- 24/11/99 11:29:07,653 SQL (ID=135, SPID=17, User=sa(AK_PD\BALDMA),
App='', Host='BALDMA'(ffc0aafd) )
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
go
-- 24/11/99 11:29:49,173 SQL (ID=135, SPID=17, User=sa(AK_PD\BALDMA),
App='', Host='BALDMA'(ffc0aafd) )
set implicit_transactions on
go
-- 24/11/99 11:29:49,173 SQL (ID=135, SPID=17, User=sa(AK_PD\BALDMA),
App='', Host='BALDMA'(ffc0aafd) )
go
-- 24/11/99 11:30:17,383 New connection (ID=136, SPID=20,
User=sa(AK_PD\BALDMA), App='', Host='BALDMA'(ffc0aafd) )
Insert into Books (title, author) values ('1984','Orwell')
-- 24/11/99 11:30:17,403 SQL (ID=136, SPID=20, User=sa(AK_PD\BALDMA),
App='', Host='BALDMA'(ffc0aafd) )
set quoted_identifier on use "GBG"
go
-- 24/11/99 11:30:17,603 SQL (ID=136, SPID=20, User=sa(AK_PD\BALDMA),
App='', Host='BALDMA'(ffc0aafd) )
exec sp_server_info 500 select 501,NULL,1 where 'a'='A' select
504,c.name,c.description,c.definition from master.dbo.syscharsets
c,master.dbo.syscharsets c1,master.dbo.sysconfigures f where f.config=1123
and f.value=c1.id and c1.csid=c.id set textsize 2147483647 set ansi_defaults
on set cursor_close_on_commit off set implicit_transactions off
go
-- 24/11/99 11:30:17,623 SQL (ID=136, SPID=20, User=sa(AK_PD\BALDMA),
App='', Host='BALDMA'(ffc0aafd) )
set implicit_transactions on
go
-- 24/11/99 11:30:17,633 SQL (ID=136, SPID=20, User=sa(AK_PD\BALDMA),
App='', Host='BALDMA'(ffc0aafd) )



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Problem with Identity field in Delphi 2, with ODBC SQL server 6.5 link

2. Help: Identity recovering and Disconnections with Delphi 2 and SQL Server and ODBC

3. SQL Date query via ODBC Access driver

4. SQL server 4.21 via ODBC in Delphi 1.02

5. SQL Server 6.5: Native driver vs. ODBC

6. Setting parameters for the ODBC-driver SQL-server

7. Delphi, 16bit Native SQL-Server 6.5 native/ ODBC drivers

8. ODBC SQL Server driver 3.5 and RAISERROR in Triggers

9. Sql Server 6 & Identity columns

10. using @@IDENTITY from SQL-Server 6.0

11. Identity (autincrement) primary key trouble with MS SQL Server

12. ms-sql-server identity field with delphi3

 

 
Powered by phpBB® Forum Software