Embedded SQL - How to force a program to fail due to error?
Last Post 22 Mar 2013 12:05 AM by B.Hauser. 7 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Bill Phillips
New Member
New Member
Posts:13

--
20 Mar 2013 11:34 AM

Hi,

I find myself facing a very "unusual"  request. I am working as consultant and the lead program has requested that I not monitor program errors and let the program fail so that a off iseries monitor can start a help ticket. In the other code I have written at this site I have monitor all operation, formatted the PSP and SQL Code, Status, Messages, identifying the code section and operation where the error occurred, and providing additional notes where needed. I sent them out via email to the on call program list and help desk. Apparently that is not their standard.

Ok no problem with standard RPG operations ... I don't agree with the approach but I can only advise them. 

The issues is programs has embedded SQL. Short of causing a divide by zero, set up another bogus error,  setting on Hxx indicators or letting a downstream operation fail, I cannot think of way to do this?

As stated,  I don't agree with this approach however I have to make sure that just stating my opinion. Comments, advise, solutions and points of view would be appreciated. 




B.Hauser
Veteran Member
Veteran Member
Posts:2048

--
20 Mar 2013 01:43 PM
Check the SQLCODE or SQLSTATE after each (embedded) SQL Statement.
If an error occurs, i.e. SQLCODE < *Zeros or Pos1-2 of the SQLSTATE is neiter 00 or 01 nor 02, retrieve the error message by using the GET DIAGNOSTICS SQL statement. Send an Escape Message using the QMNSNDPM (Send Program Message) API.

Birgitta
Bill Phillips
New Member
New Member
Posts:13

--
21 Mar 2013 10:13 AM
Birgitta,

Thanks for the tip. Question does Get Diagnostics SQL provide more complete information than the SQLCA? I am pulling the SQL Code, Status, and error directly for the SQL data structure.
Robert Clay
Veteran Member
Veteran Member
Posts:915

--
21 Mar 2013 11:29 AM

I'm confused by your phrase "let the program fail".

Do you want the program to stop and go into MSGW status? Just don't monitor anything and it will do that spectacularly by itself.

-OR- 

Do you want the program to end abruptly? Use the DUMP opcode maybe?

I guess my point is: how is any "off iseries monitor" going to know that a problem has occurred? What will it monitor for?

Have I missed the point entirely?

 

"Contrariwise, if it was so, it might be; and if it were so, it would be; but as it isn't, it ain't. That's logic."--Tweedledee
Lynne Noll
Senior Member
Senior Member
Posts:6567

--
21 Mar 2013 12:51 PM
A SQL error will not automatically cause a program error; you have to send the escape message. You may not actually want an abnormal termination.

For my web procedures, it is very important for the procedure not to hang up on an inquiry message. So if the procedure gets an unexpected SQL error, I usually make a descriptive message 'Sql Error ' + %char(sqlcod) + ' opening xx cursor') or something like that. This lets me know not only what the error was but what action it occurred on. The SQL code lets me get to what the error was better than the state, since there is a corresponding message. The consuming program then handles the error (or not) and puts the entries into its log.





Bill Phillips
New Member
New Member
Posts:13

--
21 Mar 2013 03:42 PM

to Robert Clay

Sorry for the lack of clarity.
They want the program to end abruptly. 

The monitor will start I help ticket stating that the job failed. 

Since I try to make programs end in a controllable and recoverable manner, with as much information as possible when thing go astray  I have lost my talent for causing program to just crash ;-)




Bill Phillips
New Member
New Member
Posts:13

--
21 Mar 2013 04:18 PM

to Lynne Noll

Lynne I agree and I have been writing code that come to a controlled end for some time now. In fact what you described is what I have done, formatted the SQL with the code, state , error message and included searchable markers in the program so that the program can find the very code that failed then emailing that to the programmer on call email. When the error occurs I am making sure the data is left in a recoverable state (roll back if necessary)

This was just thrown out as "new requirement" for some code that I was charged to change to embedded SQL.  

I am just making sure I understand all I know before I  discuss this subject with the client

Thanks

B.Hauser
Veteran Member
Veteran Member
Posts:2048

--
22 Mar 2013 12:05 AM
Posted By Bill Phillips on 21 Mar 2013 11:13 AM
Birgitta,

Thanks for the tip. Question does Get Diagnostics SQL provide more complete information than the SQLCA? I am pulling the SQL Code, Status, and error directly for the SQL data structure.

From within the SQLCA you can only get the SQLCODE, SQLSTATE and the variable message textes, but you can't get the complete error message. You have to retrieve it from the message file  QSQLMSG first be converting the SQLCODE into an SQL Message Id (SQL + absolute value of the SQLCODE:  -811 --> SQL0811).

With GET DIAGNOSTICS you can not only retrieve the complete error message directly, but also more detailed than using the SQLCA method. There are also much more information that can be returned with the GET DIAGNOSTICS statement.

To get more detailed information about GET DIAGNOSTICS please check the following link:

GET DIAGNOSTICS

Birgitta

You are not authorized to post a reply.

Acceptable Use Policy