On 11 Apr 2013 14:46, Dan wrote:
WRT Chuck's statement about not being able to call these SQL stored
procedures from a CL program, how are you invoking this? It appears
to not be returning anything to the program that invoked it. I'll
speculate that you interrogate the SPErrLogP table after your SQL
stored procedures have completed execution to determine whether an
error occurred.
I want to clarify that I did not say you would not be able to call
the routines, just that a /meaningful/ CL CALL of a SQL stored procedure
is not easily achieved, and possibly is just best avoided. First,
because it may be near impossible or just very difficult to make the
invocation functional [mostly for when parameters are involved; and even
if one were to make it work, will it still work in the future, given
there is no documented as-expected outcome?]. Second, because the CL
CALL is not the SQL CALL, it has no pre-defined means to get feedback
from the SQL procedure [which would, if it could, be done with
parameters; which, as noted, may make the routine less likely to be
functionally callable from CL]; effectively this is because the CL is
not a supported embedded SQL language for which SQLCODE, SQLSTATE, and
other of the SQLDA would be available to the program.
The referenced message [in the earlier quoted message; far below] had
its thread begin in the previous month; the archive link:
http://archive.midrange.com/midrange-l/201303/threads.html#00808
The next message in that thread, in April, indicated that the routine
was being used to return a RESULT SET to the CLIENT. One way to test is
using the iNav database Run SQL. This is what Kurt said was being done,
in this message:
http://archive.midrange.com/midrange-l/201303/msg00841.html
FWiW: A CLLE should be able to use the SQLCLI to consume a result
set, just as it can be used to perform the SQL CALL. What I recall
about using the SQLCLI, was only trying to get a INOUT parameter result
from a CALL. I honestly have no recollection of my progress in doing
that. In the given scenario, to avoid having to re-learn the CLI, I
would probably just write a /simple/ embedded SQLRPGLE to make a
zero-parameter SQL CALL request, and devise a method to inform the CL
CALL of that program [probably by sending the SQL#### message as an
escape] that the SQL CALL had encountered an error [per SQLCODE, or
possibly also a warning, per SQLSTATE?]. The routines possibly could be
left unchanged. See why, in my further comments; see "terminate anyway".
Also, since you're declaring an "EXIT HANDLER FOR SQLEXCEPTION,
SQLWARNING", do I presume correctly that encountering a warning will
cause the SQL stored procedure to abend at the point of the warning?
I'm not sure I'd want that, but I guess I'd have to review real-world
scenarios to know for certain.
As I understand it, and from what I recall in testing, having
included the SQLWARNING in the declared generic EXIT HANDLER will indeed
terminate the processing at that point just like any error not handled
specifically aside from the generic SQLEXCEPTION. The description of
that condition handler as a "MonMsg CPF0000" seems accurate, if not
somewhat understated just how thorough the condition handler is by
comparison; i.e. a CL command might log a diagnostic or informational
message, yet end with a completion or other message that can not be
monitored, so a MONMSG CPF0000 may not assist to detect just a logged
/warning/ in all cases.
I have the poor habit of mostly ignoring SQL warnings :-( with the
assumption that they will not occur. Given the SQL is coded correctly
and crafted carefully, that generally holds true, at least until the
definition of the files change, although just as likely due to a
different environment such as for a change in language\CCSID. For those
reasons I should probably code for SQLWARNING in addition to
SQLEXCEPTION, to be sure that any eventual nuances not considered
originally, that come about from simple changes, are made conspicuous by
a failure, instead of possibly the routine effecting something
undesirable [like truncating a string, or using substitution characters
in data].
In the following message from the same referenced thread, I give a
link to docs where it mentions that for when "the condition is a warning
and there is not a handler for the condition" then checking the SQLCODE
and SQLSTATE or GET DIAGNOSTICS can be used outside of a condition
handler, just after a statement. And if a declared EXIT handler would
not perform any particular action [like setting an output parameter that
indicates a failure], then the SQL routine would terminate anyway, with
no EXIT handler in effect, ending with the condition that caused the error.
http://archive.midrange.com/midrange-l/201303/msg00812.html
The routines shown in the current message thread do not have any
parameters, so they could remain without any condition handler for
errors, and the invoker by SQL CALL could just check the diagnostics
from the CALL to see what the failure or warning was in the procedure.
However as I have read, that is often considered undesirable, because
then the caller may become, improperly, expected to know what the
procedure does and how to react to its failures. If the caller is not
simply checking solely for SQLCODE=0, then likely the procedure should
set a specific error code such as a SQLSTATE to a known value as the
expected value by the caller, to be found as indication of
worked\failed. The caller should probably not be deciding what to do
between the granular failures of row already exists, file not found, and
a lock conflict which could come from varying statements in the routine,
and instead just look for a specific [set of] failure codes and react in
a defined manner; which means the routine must set the codes according
to what it is doing and when\where within the procedure, in condition
handlers.
Regards, Chuck
On Thu, Apr 11, 2013 at 3:34 PM, Anderson, Kurt wrote:
I recently was working on putting error handling into stored
procedures. My SQL version of "MonMsg CPF0000" can be found here:
http://archive.midrange.com/midrange-l/201304/msg00063.html
This IBM redbook on SQL procedures and functions has been helpful.
See chapter 8 for error handling. 8.2 gets into specifics.
http://www.redbooks.ibm.com/redbooks/pdfs/sg246503.pdf
midrange.com
