-
Erwin Anema
- PowerBuilder
- Friday, 18 April 2025 12:26 PM UTC
Hi all,
Context: PowerBuilder 2022R3 Build 3356. MSOLEDBSQL connecting against SQL Server.
I ran into the issue of using DisableBind=0 in combination with Identity='SCOPE_IDENTITY()'.
When the application performs an insert, the executed SCOPE_IDENTITY() returns NULL.
By tracing the executed SQL Statements on SQL Server, I can see that PowerBuilder executes:
exec [sys].sp_describe_undeclared_parameters N'INSERT INTO "dbo"."MYTABLE" ....
exec sp_executesql N'INSERT INTO "dbo"."MYTABLE" ( "userIdent", "keyName", "valueText", "lastModified" ) VALUES ( ....'
select SCOPE_IDENTITY()
This shows the problem, because SCOPE_IDENTITY returns the identity from the "values inserted only within the current scope".
With sp_executesql the insert statement is executed in a different scope.
@@identity doesn't have this issue, because this "@@identity is not limited to a specific scope", and this does return an issue.
That's why @@identity works with this pattern, but scope_identity currently not.
What probably should be changed is to run the SELECT SCOPE_IDENTITY() within the sp_executesql command where the row is inserted.
(See also here, and the answer from Dan Guzman: https://learn.microsoft.com/en-us/answers/questions/574204/how-to-use-scope-identity()-with-sp-executesql)
Kind regards,
Erwin
Find Questions by Tag
Helpful?
If a reply or comment is helpful for you, please don’t hesitate to click the Helpful button. This action is further confirmation of their invaluable contribution to the Appeon Community.