Questions? Feedback?powered byOlark live chat software
Bug 3009

Get Identity from Sqlserver2012 via ODBC 11 July, 2019

Angela Chen
09 July, 2019
Product: PowerBuilder Category: DataWindow
Version: 2017 R3 Publishing: Public
Status: Verifying Priority: P3
Classification: Resolution:
Ken Guo 11 July, 2019
Hi Angela,

Thanks for providing this test case! I can reproduce the issue locally. With ODBC, SCOPE_IDENTITY() function indeed returns null. 
This issue also exists in other programming languages. You can reference this link: 
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a8ff34e6-5bb9-494e-a790-2a50cc53abfd/scopeidentity-via-an-odbc-connection?forum=sqldataaccess

Therefore, I suggest that you use other DBMS to work around this issue, such as SQL Native Client (recommended), OLE DB or ADO.Net. I tested these DBMS and they all work fine. 

If you have to use ODBC, you can also work around it by modifying the trigger. For example, write the Manager table id to a column in Employee table id in a trigger, and you can read this value from the column later when you need it: 

create trigger MgrTrigger on Employee after insert
as
if (select emptype from inserted) = 1
begin
             declare @id int
              set @id = (select id from inserted)
              insert into Manager(empid) values(@id)
              update Employee set Employee.manager_id = SCOPE_IDENTITY() where Employee.id = @id
end

Regards,
Ken
Angela Chen 10 July, 2019
I have not tried OLEDB or Native Client, but using ADO.net with both system.data.odbc and system.data.sqlclient works.  However, ADO.net has different dehavior that we do NOT want to switch to.
Here's how you can test it 
in SqlServer manager create the tables and the trigger

create table Employee (id int identity, empname nvarchar(100), emptype int);
insert into Employee values ('John', 0);
insert into Employee values ('Paul', 0);
insert into Employee values ('Jane', 0);
insert into Employee values ('Mike', 1);
insert into Employee values ('Mary', 1);
create table Manager (id int identity, empid int);
go 

create trigger MgrTrigger on Employee after insert

as
if (select emptype from inserted) = 1
begin
             declare @id int
              set @id = (select id from inserted)
              insert into Manager(empid) values(@id)
end
go 

Then in PB database painter run the following two statements

insert into Employee values ('Test', 1);

select '2', @@IDENTITY, SCOPE_IDENTITY(); 

@@identity returns 1 -- the identity from the manager table and scope_identity() returns null
Ken Guo 10 July, 2019
Hi Angela,

Please confirm if you also have the issue when connecting to the database using OLE or SQL Native Client?
In order to reproducible and better analyze the issue, I need you to provide a simple complete test case including PBL, DB Table, DB Trigger, etc. 

Regards,
Ken
Govinda Lopez 09 July, 2019
Hi Angela,

I will now transfer your ticket to our engineering team for further analysis. We will keep you posted on the results here.


Regards,
Govinda Lopez 09 July, 2019
Hi Angela,

I will be working on analyzing/reproducing your case. We will keep you posted on the results here.


Regards,
Angela Chen 09 July, 2019
*Phenomenon:
Scope_identity() returns null via ODBC

*Reproduce Steps:
//in PB17 DB painter
Insert into Table A with trigger insert into Table B 
select Scope_identity()
Same behavior inserting a row in datawindow painter, the id column returns null
Remarks:
We need to get the identity value back from Table A, so we need to change the default @@identity() to Scope_identity().  We cannot use Ident_curr("table_name") because it might return value from another insert
OS:
Windows 10 
Platform:
All 
Database Type:
Microsoft SQL Server 
Database Version:
2012