Midrange News for the IBM i Community


Posted by: Chris Proctor
Programmer Analyst
Columbia Sports Company
Portland, OR
"Drop Procedure" issue
has no ratings.
Published: 21 Feb 2017
Revised: 17 Mar 2017 - 271 days ago
Last viewed on: 13 Dec 2017 (618 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

"Drop Procedure" issue Published by: Chris Proctor on 21 Feb 2017 view comments(4)

Good morning. I'm trying to create a command that can be used to drop stored procedures. In STRSQL, if I type DROP PROCEDURE LIBRARY.PROCNAME the stored procedure is deleted, but if I try to do the same thing in an RPGLE program it looks like it works, but the stored procedure still exists. I've checked my log and I'm not seeing anything to indicate an error. Also, once I've called my command, DROPSPROC, if I go into STRSQL and try to drop the procedure it won't find it. It's only after I sign off and back on that it will work in STRSQL. I'm at a loss as to what the issue could be. Does STRSQL do something behind the scenes that I'm not aware of that makes the command work properly? The code for the program is very simple. Here it is:

ctl-opt option(*nodebugio:*srcstmt) dftactgrp(*no);

 

// prototype definitions

dcl-pr dropSprocs extpgm('DROPSPROCR');

psproc char(75);

pslib char(10);

end-pr;

dcl-pi dropSprocs;

psproc char(75);

pslib char(10);

end-pi;

// global variable definitions

dcl-s sqlstmt char(100) inz;

if psproc <> ' ' and pslib <> ' ';

sqlstmt = 'drop procedure ' + %trim(pslib) + '.' +

%trim(psproc);

exec sql execute immediate :sqlstmt;

endif;

*inlr = *on;

Any suggestions would be greatly appreciated!

 

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: Ringer
Premium member *
Comment on: "Drop Procedure" issue
Posted: 9 months 21 days 6 hours 56 minutes ago

I suspect it's the SQL naming option. *SYS uses the / and *SQL uses the period . as the library & object separator.  

Do F13 then 1 in STRSQL to see how it is set there. Then compare that to the *PGM. 

QSYS/PRTSQLINF OBJ(DROPSPROCR) 

OPTION(....) 

Ringer 

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: "Drop Procedure" issue
Posted: 9 months 17 days 21 hours 36 minutes ago

Some stuff is cached until the last session that used it, ends.  So the sign off/on makes sense. 

Posted by: DaleB
Premium member *
Reading, PA
Comment on: "Drop Procedure" issue
Posted: 9 months 16 days 5 hours 22 minutes ago

After the execute immediate, you should check the SQLSTATE. Probably should also look in the job log.

Posted by: SteveCCNJ
Premium member *
Egg Harbor Twp., NJ
Comment on: "Drop Procedure" issue
Posted: 8 months 28 days 1 hours 15 minutes ago

I wonder if this might be an authorities issue, since the authority you have personally in STRSQL might be different than what the pgm has when executing.

From the manual:

To drop a procedure, the privileges held by the authorization ID of the statement
must include at least one of the following:

v The following system authorities:
– For SQL procedures, the system authority *OBJEXIST on the program object
associated with the procedure, and

– The DELETE privilege on the SYSPROCS, SYSPARMS, and SYSROUTINEDEP
catalog tables, and

– The system authority *EXECUTE on library QSYS2
v Database administrator authority