1. John Raghanti
  2. PowerBuilder
  3. Tuesday, 22 October 2024 18:14 PM UTC

Hi! I'm running PB 2022 R3 Build 3391 with MS SQL Server databases. We just moved from R2 to R3 recently.

I've got a datawindow with an Identity Column specified in the update properties:

I call dw.Update() in code and then (since 2017 at least) I grab transaction_id for the inserted row to set the foreign key for other relevant tables.

I started getting FK errors that it didn't exist. When I step through the code, I am getting a value in the identity column, but it is not the value of the inserted row. There is a trigger on this table that inserts some auditing data (and has since at least 2017), and I am getting the identity value for the auditing entry.

If I insert a row in SSMS, and do a select scope_identity(), it does come back with the correct data for transaction_id.

Did something change in R3? Looking for assistance.

Thanks!

 

Accepted Answer
Peter Pang @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 29 October 2024 07:07 AM UTC
  2. PowerBuilder
  3. # Permalink

Hi John,

Starting from PB2022 R2 we refactored the ADO.NET driver, using a new dll file (such as Appeon.DB.SQLServer.dll), for details please refer to:
https://docs.appeon.com/pb/whats_new/ADONET_driver_upgrade.html

PB2022 R3 build3391 can use MSOLEDBSQL Microsoft OLE DB Driver and ass Identity=’SCOPE_IDENTITY()’ in DBParm to solve the problem. You can refer to: https://docs.appeon.com/pb2022r3/connection_reference/Identity.html

The usage is similar to SNC, Database Profiles Setup -> Syntax -> DataWindow Identity Value.

Best Regards,
Peter

Comment
  1. John Raghanti
  2. Wednesday, 30 October 2024 15:31 PM UTC
Thank you Peter! I put Identity='SCOPE_IDENTITY()' into our DBPARM and our software started acting the way it had in the past. I appreciate your response. Glad we don't have to refactor everything. :)
  1. Helpful
There are no comments made yet.
Mark Goldsmith Accepted Answer Pending Moderation
  1. Saturday, 26 October 2024 18:56 PM UTC
  2. PowerBuilder
  3. # 1

Hi John,

If this is your bug that you submitted (https://www.appeon.com/standardsupport/bugfixes/view?id=1232) maybe you've provided more info in it but since it's locked I can't tell.

When you say "...I grab transaction_id...", how are you "grabbing it"? As well, are you able to provide the definition of your trigger (hiding or substituting any confidential information)?

Have you changed database drivers since moving from R2 to R3? Looking at your follow-up post after you spun up a clean machine, the config file provided means you're using an ADO.Net connection as I'm pretty sure non-ADO.Net connections can't use a .NET configuration file. Is this how you're connecting to the database, both in R2 and R3? If you're still using an ADO.Net connection approach, maybe this was an undocumented change for R3. I could have missed it but the information at this link: https://docs.appeon.com/pb2022/connecting_to_your_database/XREF_67762_Defining_the_ADO.html doesn't seem to be available anywhere in the PB R3 Help.

Have you tried doing the insert without the trigger just to see if PB will obtain the correct identity value back without a Reset()/ Retrieve() and rule out anything else going on? Any chance the wrong identity value you are getting, for the initial insert, would match the identity value of any of the newly inserted record(s) for the auditing data succeeded?

@Jeff: "...I think this is normal behavior for the identity attribute..."; I can appreciate the solution you came up with because as far as using @@identity, scope_identity or ident_current(table_name), I tend to stay away from the first two due to the potential for these issues when using triggers and use a different approach.

Maybe you're aware of this...there are two virtual tables that are made available when an insert, update or delete cause a trigger to fire. They are called the INSERTED and DELETED tables (I think it was OLD and NEW in some other DBMSs). If you're not familiar with these tables and how to take advantage of them, you may wish to read up on it as this use case is one of the main advantages with these tables. Using this approach, the FK value will not be dependent on either of @@identity, scope_indentity or ident_current(table_name), but what is written to the INSERTED table which is made available to the trigger, including the identity value.

As to your specific issue, not getting the correct identity value on your initial insert, maybe for some reason you're now coming up against a timing issue that wasn't surfacing before, causing the wrong scope_identity value to be obtained (maybe try and change it to ident_current(table_name) but if it is a timing issue it may still fail, but I'd really look into using the virtual tables mentioned above).  Not sure how much this helps and I might have other suggestions but it would be good to understand some of the questions posed above.

Regards,

Mark

Comment
There are no comments made yet.
John Raghanti Accepted Answer Pending Moderation
  1. Friday, 25 October 2024 17:31 PM UTC
  2. PowerBuilder
  3. # 2

I've got a little more information. We spun up a clean machine and installed PB 2022 R2 build 1892. We have the following section in the PB220.exe.config file, which causes our product to work like it's worked for years.

<configuration>

  <dbConfiguration>

     <myconfig getIdentity="select scope_identity()" />

  </dbConfiguration>

</configuration>

In PB 2022 R3 build 3391 it no longer works. Was this section reformatted or made obsolete?

If I remove this section from R2, I'm seeing the same behavior that I see in R3.

 

Any thoughts?

Comment
  1. Arnd Schmidt
  2. Friday, 25 October 2024 22:57 PM UTC
I do not have this entries using PB 2022 R2 2828.

But that seems to be your solution or to use the identity dbparm.

https://docs.appeon.com/pb2022/connection_reference/Identity.html

It all depends on the DBMS you are using (ODBC vs. MSOLEDBSQL vs ...).

The "old" way was to Patch/Review the pbodb.ini.

hth

Arnd
  1. Helpful 1
There are no comments made yet.
Jeff Gibson Accepted Answer Pending Moderation
  1. Wednesday, 23 October 2024 21:40 PM UTC
  2. PowerBuilder
  3. # 3

I'm uncertain if this will help.

We did something similar to this in a piece of software we had written for the transportation industry.

We would trigger an insert, and then that insert would fire a trigger or triggers that would insert additional rows into detail tables. This would always cause issues with the identity.

We ended up defining connection level variables that associated with each table which had the sole job of just grabbing the identity value.

I think if your identity was working on a table with triggers, you may have just gotten lucky at that moment that you were getting the value before the triggered inserts wrote over the identity value. Someone keep me honest on this. I think this is normal behavior for the identity attribute if a triggered insert is occurring will always get overridden in a situation like this.

We would create our connection level variables on the connect for that user to the database. 

We would call a function to immediately call a remote procedure to grab the identity value.

Not sure if this helps, but this felt very similar to what we had ran into back in the day.

Comment
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 22 October 2024 20:11 PM UTC
  2. PowerBuilder
  3. # 4

Hi John;

  Since the Trigger seems to be the culprit here. Have you tried using the ReSelectRow() command after the update() command to refresh the DWO's primary buffer with the actual value of the transaction_id column on that parent table?

Regards .. Chris

Comment
  1. John Raghanti
  2. Wednesday, 23 October 2024 11:39 AM UTC
I did try to call ReSelectRow() and had the same results. If I Reset() the datawindow and Retrieve() it again, I do get the proper transaction_id. It's very strange. I'm trying to spin up a PB 2022 R2 virtual machine to see what that shows me.
  1. Helpful
  1. Chris Pollach @Appeon
  2. Wednesday, 23 October 2024 20:01 PM UTC
That is very weird John - I would not have anticipated that result.

I wonder if another Trigger is also interfering on the ReSelectRow?

At least you now have a workaround with the Reset / Retrieve though.
  1. Helpful
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.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.