1. Tim Bruce
  2. PowerBuilder
  3. Monday, 23 October 2023 14:26 PM UTC

Power Builder 2019 R3

Windows 11 Enterprise

SQL Server 2019

 

I have a datawindow where I should be able to add a new record.  The Update fires and I can see the insert statement in the Sql Server Profiler but the data never makes it to the table.  Due to the sensitivity of the data I can't add it here but when i run the insert in the DB Profiler in PB and sql server management studio they both work

After the update the commit is fired (i stepped though the code to verify.

Can anyone provide anything to try?

 

Thanks

Arnd Schmidt Accepted Answer Pending Moderation
  1. Wednesday, 25 October 2023 22:16 PM UTC
  2. PowerBuilder
  3. # 1

There are two more options.

You insert and commit into

a.) another database.

b.) a table with the same name in another schema aka "user schema".

hth

Arnd

Comment
There are no comments made yet.
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Wednesday, 25 October 2023 07:15 AM UTC
  2. PowerBuilder
  3. # 2

Hello,

Here is the code snippet for update

In DBError event of dw_1

is_dberror = sqlsyntax
ib_dberror = True

Return 1

In Save button or save event

SQLCA.AutoCommit = False
ll_rc = Event ue_saveprocess_update()
If ll_rc = 1 Then
        SQLCA.commit
Else
	SQLCA.rollback
        // Display Error Process,
        // Error Message stored in variable ls_dberror
        Messagebox('Error', is_dberror)
End If
SQLCA.AutoCommit = True

Event ue_saveprocess_update

Long			ll_rc

ll_rc = dw_1.Update(True, False)
If (ll_rc <> 1 And ib_dberror = False) Then		// For dbError = True, Error captured at DBError Event which should not be overwritten here
        is_dberror = "Save Failed. Check DW update properties"
	Return ll_rc
ElseIf ib_dberror = True Then
	Return -1
End If

/* Added after Kim pointed out */
Return 1

IN the Save event, after rollback, messagebox will display, if there is any error that prevents the data being inserted.

HTH

Happiness Always
BKR Sivaprakash

 

Comment
  1. Kim Berghall
  2. Wednesday, 25 October 2023 20:17 PM UTC
Shouldn't you return ll_rc at the end of ue_saveprocess_update?
  1. Helpful
  1. Sivaprakash BKR
  2. Thursday, 26 October 2023 04:15 AM UTC
Thanks Kim for pointing out the missing Return. In the end

Return 1

to be there.

[ I've edited my reply ]

  1. Helpful
There are no comments made yet.
John Fauss Accepted Answer Pending Moderation
  1. Tuesday, 24 October 2023 13:22 PM UTC
  2. PowerBuilder
  3. # 3

Hi, Tim -

Have you tested the PB application by enabling the database communications trace in PB, by prefacing the SQLCA.DBMS value with the word "TRACE" (e.g., SQLCA.DBMS = "TRACE MSO")?

There are additional database trace options as well. Open PB Help, navigate to the "Search" tab, and search for "pbtrace".

Best regards, John

Comment
There are no comments made yet.
John Fauss Accepted Answer Pending Moderation
  1. Monday, 23 October 2023 20:52 PM UTC
  2. PowerBuilder
  3. # 4

Is there an INSERT trigger on the table? If so, what does it do?

Comment
  1. Tim Bruce
  2. Tuesday, 24 October 2023 10:43 AM UTC
There is an insert trigger on that table which adds the record to an audit table. When I run the SQL Server Trace I can see the insert stmt. when I copy out that insert stmt and run it in Management Studio the insert works
  1. Helpful
  1. Andreas Mykonios
  2. Tuesday, 24 October 2023 11:01 AM UTC
Are you sure you are not in a nested transaction?

Andreas.
  1. Helpful
  1. Tim Bruce
  2. Tuesday, 24 October 2023 13:09 PM UTC
Thanks - as a test I've disabled the triggers and still no go
  1. Helpful
There are no comments made yet.
Kevin Ridley Accepted Answer Pending Moderation
  1. Monday, 23 October 2023 16:38 PM UTC
  2. PowerBuilder
  3. # 5

I agree with all of the below:

Be sure you have a commit (sounds like you do)

Check the return code of the update statement

Check the transaction.sqlcode
Check transaction.sqlerrtext also

Comment
  1. Tim Bruce
  2. Monday, 23 October 2023 16:46 PM UTC
return for update is 1

sqlcode = 0

The sqlerrtext is blank
  1. Helpful
  1. Chris Pollach @Appeon
  2. Monday, 23 October 2023 20:47 PM UTC
Hi Tim.

Make sure that the DWO "update properties" are set correctly. Also, make sure that the DWO instance you're using is the one your expecting. That also means that you're not using another same DWO but from a different PBL that has different update settings. ;-)

Regards ... Chris
  1. Helpful
There are no comments made yet.
Tim Bruce Accepted Answer Pending Moderation
  1. Monday, 23 October 2023 16:07 PM UTC
  2. PowerBuilder
  3. # 6

Hey Chris,

See my responses below

  • Check the Update() commands return code 
    • ll_rv = dw_1.update using sqlca 
  • Have the DS/DC control "DBError" event coded 
    • if ll_rv = 1 then
      messagebox("test","commit");
      COMMIT Using SQLCA;
  • Check the return code on the "Commit" command
    • messagebox("test",string(SQLCA.SQLCode)); shows 0
  • Verify that you're looking at the same DB instance
    • it is the same instance as it' s the only one we have
Comment
  1. Chris Pollach @Appeon
  2. Monday, 23 October 2023 20:44 PM UTC
You seem to have the Update() & DBError event processing mixed up based on your answer.
  1. Helpful
  1. Tim Bruce
  2. Tuesday, 24 October 2023 13:12 PM UTC
I'm not sure i understand. Can you provide an example of update & dberror event processing?
  1. Helpful
  1. Chris Pollach @Appeon
  2. Tuesday, 24 October 2023 17:35 PM UTC
There are two DBError events:

1) Transaction Object: https://docs.appeon.com/pb2022/powerscript_reference/dBError_event.html

2) DW Control Object: https://docs.appeon.com/pb2022/datawindow_reference/dwevt_DBError.html

Also in the DataStore as well.

You just need to make sure that these events are always coded to that you App catches any DBMS errors.

Personally, I always log these so that any developer or support person can view them when debugging.

  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Monday, 23 October 2023 15:09 PM UTC
  2. PowerBuilder
  3. # 7

Hi Tim;

  Did your app...

  • Check the Update() commands return code
  • Have the DS/DC control "DBError" event coded
  • Check the return code on the "Commit" command
  • Verify that you're looking at the same DB instance

Regards ... Chris 

Comment
There are no comments made yet.
mike S Accepted Answer Pending Moderation
  1. Monday, 23 October 2023 14:44 PM UTC
  2. PowerBuilder
  3. # 8

commit

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.
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.