Procedures for Sql Exec Statements

[jsplice]

I want to make some procedures that can accept parms and will do simple embedded sql functions such as open and close a cursor, and fetch. I want to do this so I can clean up the way my code looks. Because SQL errors don't always cause a program to end when it should, I had to incorporate some kind of error handling. I'm using a service program that was writte to handle SQL errors; the procedure looks up the SQLCOD value and send the error text in a program break message. I then force the program to quit, which must be done from the mainline (as far as I know):

Exec sql Open Generic_Cursor;
If CheckSQLErrors();
  *INLR = *ON;
  return;
EndIf;  


PCheckSQLErrors   B
D                 PI              N

 /free
  If SQLSTT <> '00000' and SQLSTT <> '02000';
    RegExcpHandler('SQL0000':*ON:*OFF);
    ThrowAndCatch ( SqlMsgid(SQLCOD): *OMIT: SQLERM );
    return *on;
  Else;
    return *off;
  EndIf;
 /end-free

P                 E

So what I want to do is create a procedure that can combine all of those functions. It will perform the cursor open, call CheckSQLErrors(), and exit the program if neccessary (if it's even possible to exit the program from within a procedure). I can see there being a problem with setting up the parms for the procedure that will accept the cursor name. Can you use passed-in values for a cursor in the open statement?

 

[Mercury2]

Can you use passed-in values for a cursor in the open statement?

I don't think so.

Not really sure what you mean, however in the service program I'd save first the SQLSTT variable to prevent the sql status from being altered by others subsequent calls to others procedures.

Exec sql Open Generic_Cursor;
pe_SQLSTT = SQLSTT;
If CheckSQLErrors(pe_SQLSTT);
  *INLR = *ON;
  return;
EndIf;  


PCheckSQLErrors   B
D                 PI              N
D  pe_SQLSTT                     5A    

D SQL_OK          C                  '00000'
D SQL_END_NF      C                  '02000' 

 /free
  If pe_SQLSTT <> SQL_OK and pe_SQLSTT <> SQL_END_NF;
    RegExcpHandler('SQL0000':*ON:*OFF);
    ThrowAndCatch ( SqlMsgid(SQLCOD): *OMIT: SQLERM );
    return *on;
  Else;
    return *off;
  EndIf;
 /end-free

P                 E

 

[jsplice]

Well my question from before is negligible if there is a way to exit the program from within a procedure. I know that "return" can be used in either the mainline or in a subroutine to exit the program; but if it's used in a procedure, it simply returns control to the caller. I don't really want to have both subroutines and procedures in the same program; I think that would look horrible. Ideally I'd like to have something like this:

Exec sql Open Generic_Cursor;
CheckSQLErrors();



PCheckSQLErrors   B
 /free
  If SQLSTT <> '00000' and SQLSTT <> '02000';
    RegExcpHandler('SQL0000':*ON:*OFF);
    ThrowAndCatch ( SqlMsgid(SQLCOD): *OMIT: SQLERM );
    *INLR = *ON
    return;   //**EXIT PROGRAM
  EndIf;
 /end-free 

P                 E

 

[Mercury2]

Look here and I think that you'll get all the responses you've been dreaming about (euh... hopefully

 

[jsplice]

Well I appreciate the link, but unfortunately I'm only an "associate member" of that site and cannot view the article.

 

[Mercury2]

I had this article stored in the folder Divers on my public server at

http://www.psoriano.freesurf.fr/Divers/program ending.doc

Enjoy !

Philippe

 

[jsplice]

Thanks for the link. I actually ended up going with the exit(0) method for ending the program and it seems to work pretty well. I also found it important to add the CLOSQLCSR(*ENDMOD) paramter to the CRTSQLRPGI compile command, otherwise it defaults to CLOSQLCSR(*ENDACTGRP).

 

[Mercury2]

Quoted from the SQL Guru Birgitta Hauser
When using *ENDMOD all OPDs opened in the module get completely closed. That means each time the module is used a full open or full optimization must be executed, which is the most time consuming action when executing an SQL statement.

With the full open the following steps must be performed:
1. An access plan must be created or at least validated
2. Statistics must be asked and all available access paths (either DDS described keyed logical files or SQL indexes) must be checked and valuated.
3. Temporary objects (such as hash tables, relative record lists, bit maps) needed to execute the SQL statement must be created
4. The temporary objects must be filled with data

When using the compile option CLOSQLCSR = *ENDMOD all temporary objects will be deleted as soon as the module ends.
When using the compile option CLOSQLCSR = *ENDACTGRP the temporary objects will only be deleted after the first execution. For all subsequent excutions only a soft close and a pseudo open will be performed. That means only the data in the temporary objects must be actualized. The ODPs (i.e. the temporary objects) will not be deleted before the activation group ends. If a program is executed in the default activation group, the ODP will not be deleted before the job ends.

Well if you really want to use *ENDMOD after reading this, it's better to use the SET OPTION sql stm as the first stm in your program so that you don't forget it when you compile again.

exec sql set option closqlcsr = *endmod

 

[jsplice]

I tried both ways and didn't notice a difference in performance between the two. It runs pretty quick using either method, so I think I'm going to stick with *ENDMOD. Thanks for the set option tip; it is much easier to do it that way than have to change the compile parameters during each compile.