Need to handle sql errors with "RUN STORED-PROC send-sq

Posted by jamali on 20-Jan-2017 09:20

We're using OpenEdge 10.2b on an HP-UX and are running some SQL statements on an external MS SQL DB (windows of course). See here for more info.

I thought NO-ERROR would suppress any SQL errors, but it seems it only suppresses the progress errors. If the program will be running in the background (no user/screen to output to) how can I suppress and then email these errors to a sysAdmin (basically, looking for similar functionality to the Progress ERROR-STATUS:ERROR)?

RUN STORED-PROCEDURE send-sql-statement IN-handle = PROC-HANDLE NO-ERROR ("Insert into tbl_name(col1, col2, col3) VALUES (data_designed_to_fail)").

OpenEdge General - Forum

All Replies

Posted by Brian K. Maher on 20-Jan-2017 09:30

jamali,
 
Can you provide more information like the error(s) you are seeing and exactly what the code is?  Using NO-ERROR should allow you to get at the error information for the ERROR-STATUS handle.
 
Brian

Posted by jamali on 20-Jan-2017 09:43

Hi Brian,

The error (intentionally generated as a test) shows up at the bottom of the screen:

SYSTEM ERROR: Assignment error. (6177)

22005: [Microsoft][SQL Server Native Client 10.0][SQL Server]Conversion failed when converting the varchar value 'fail' to data type bit.

According to the documentation on this command, it seems it only captures PROGRESS errors. This is an SQL error.(documentation.progress.com/.../index.html

Ideally, I need to:

1. Suppress the error (and have the program continue, as it currently just fails)

2. Get the error immediately after and email it (basically need the same functionality as ERROR-STATUS:ERROR and NUM-MESSAGES)

Is this possible?

Posted by Fernando Souza on 20-Jan-2017 09:49

The problem is that some errors, such as 6177, generate a STOP condition, and you will not be able to trap the errors with NO-ERROR. You can 'handle' the stop condition with a DO ON STOP, but the errors will still go to the current output device.

Posted by Brian K. Maher on 20-Jan-2017 09:58

jamali,
 
You may want to consider a DO ON STOP UNDO, RETRY block along with something like OUTPUT TO <blah> KEEP-MESSAGES or perhaps even using the LOG-MANAGER handle to turn on ABL logging of 4GLMessages then in the IF RETRY block inside the DO ON STOP block you could closed the logging, then read the log and do whatever you want with the information.
 
Brian
 
 

Posted by Rob Debbage on 23-Jan-2017 03:08

Hi Jamali,

Just some general advice but one point to consider is whether it is appropriate to use RUN STORED-PROCEDURE in this scenario. For the previous situation with DELETE FROM, there was a clear benefit of using it since it allowed you to a) run the command server-side and b) delete all records in a single operation rather than record-by-record.

I personally do not see the benefit of using it for INSERT statements though. You will be creating one record at a time anyway so the only thing that you are saving on is the translation from ABL to SQL, which should take a negigible amount of time. You can achieve the same using standard ABL calls, e.g. CREATE / ASSIGN, and therefore benefit from fuller ABL error handling.

In sum, RUN STORED-PROCEDURE can be very useful in some scenarios but maybe isn't appropriate to all so do consider your use-case for it.

Kind regards,

Rob

This thread is closed