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

Resolved Capture Thrown Error In Datastore

  1. How-to
0
Votes
Undo
  1. Joe Hess
    Joe Hess
  2. PowerBuilder
  3. Friday, 15 October 2021 20:02 PM UTC

Hi,

I'm trying to capture a thrown error from a delete trigger while using a datastore. The datastore seems to ignore it.

 

The trigger is simple:

IF EXISTS (SELECT 1 FROM table, deleted WHERE table.key=deleted.key) THROW 60000, 'Record may not be deleted.', 1

 

If I attempt to delete the record using a statement in SSMS, the error is thrown properly. If I use an SQL DELETE statement in PowerScript, I can see/capture the error information in SQLCA. However, if I try to use ds.Delete(row) followed by ds.Update(), the error does not appear. There is nothing in SQLCA. The DBError event does not fire. I tried wrapping the update with TRY - CATCH. Nothing seems to work. The record does not delete as it shouldn't. I just have no message that the delete failed.

Is this possible, or do I need to stick with using the delete statement in PowerScript?

 

Thanks,

Joe

Rich Text with Hyperlinks
Open Source code Software for PB/PFC code scan. Wh...
Responses (3)
Joe Hess
Joe Hess Accepted Answer Pending Moderation
  1. Monday, 18 October 2021 16:51 PM UTC
  2. PowerBuilder
  3. # 1

Thanks for the responses. This came down to an ID 10T error. I had packaged the JSON, and I inadvertently used GetValueString instead of GetValue to extract the JSON object.

 

Joe

Comment
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Monday, 18 October 2021 17:15 PM UTC
Thanks for sharing!
  1. Helpful
mike S
  1. mike S
  2. Tuesday, 19 October 2021 19:14 PM UTC
lol
  1. Helpful
There are no comments made yet.
mike S
mike S Accepted Answer Pending Moderation
  1. Sunday, 17 October 2021 17:36 PM UTC
  2. PowerBuilder
  3. # 2

It works fine with a datastore.    dbcode will be the number you entered (60000).

 

what database driver are you using?  MSOLEDBSQL, OLEDB (the old one), and native client 11 all work fine.

 

Is your datastore part of a share?  The error will only show on the shared 'original' 

 

Comment
Joe Hess
  1. Joe Hess
  2. Monday, 18 October 2021 16:14 PM UTC
Hi, Mike.



Good to know it should work. Now I need to figure out why it does not work. I'm using SNC11. The datastore is not part of a share. I am exporting the datawindow contents to a Datawindow JSON and importing it into a datastore if that makes a difference. No messages. My error code does not show up.



Joe
  1. Helpful
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Saturday, 16 October 2021 14:39 PM UTC
  2. PowerBuilder
  3. # 3

Hi,

Sorry can't be of much help here.

It sounds like some kind of a bug to me, but it's been a while I haven't worked with sqlserver.

You should definitely be able to do this and in my opinion, it's a lot better than doing an embedded sql delete statement in powerscript.
Is the datawindow object assigned to your datastore updatable?

To make sure your trigger throwing an exception, try adding another exception for the case in which it doesn't find the record you look for with the deleted.key.
Just for testing purposes.

Could it be that an implicit rollback is executed when you raise the error which clears the error? (I've read something about SET XACT_ABORT to ON or OFF making a difference).
Have you tried using RAISERROR instead of THROW?

regards.

Comment
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Saturday, 16 October 2021 14:56 PM UTC
What if you use ERROR_.... instead of throwing an exception?

To see what I mean: https://docs.microsoft.com/en-us/sql/t-sql/functions/error-message-transact-sql?view=sql-server-ver15

Maybe that will populate the sqlca values.
  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.