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)").
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?
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.
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.