Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved Returning Scope_identity() via ODBC

  1. Issue
0
Votes
Undo
  1. Angela Chen
    Angela Chen
  2. PowerBuilder
  3. Monday, 1 July 2019 22:38 PM UTC

We are having an issue on returning identity column using PB2017 R3 build 1858 connecting to MSSqlServer DB via ODBC

We have a table with insert trigger that insert into another table and we want the return identity value to be the id from the first table. Instead of @@Identity , we would like to use Scope_Identity() , but PB returns null with scope_identity()

A simple test is an insert into a table and a select @@identity(), scope_identity() in DB painter 

the select returns the id from the table inside the trigger and a null 

the same select returns valid ids in SqlServer management studio 

What is the suggested setting in PBODB170.ini for GetIdentity? 

 

WPF and Winforms on .net Core article
Is Windows Server 2019 a Supported OS for Runtime?
Responses (3)
Matthew Balent
Matthew Balent Accepted Answer Pending Moderation
  1. Wednesday, 3 July 2019 13:31 PM UTC
  2. PowerBuilder
  3. # 1

Is there a driver setting you need to set?  OLE DB with native driver uses "identity='scope_identity()'

Comment
There are no comments made yet.
Ashish Syal
Ashish Syal Accepted Answer Pending Moderation
  1. Tuesday, 2 July 2019 23:07 PM UTC
  2. PowerBuilder
  3. # 2

Hello Angela,

I believe this looks like a bug for PB Version (PB2017 R3 build 1858 connecting to MSSqlServer 2014 DB via ODBC) as i  faced the same problem while using scope_identity() to return the new id. The application just crashed out without any error message.

The same procedure works well for older version of PB. Lately i discontinued the procedure and opted the path as suggested by Chris. Luckily, i had used this logic only in one procedure and easily handled.

Regards.

Comment
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 2 July 2019 17:32 PM UTC
  2. PowerBuilder
  3. # 3

Hi Angela;

  Have you tried using the ReSelectRow() command after the insert DML to get the new Trigger set value?

Regards ... Chris

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.