Howard,
This is the code I have used in the past to drop a procedure in Visual Basic:

Dim mCon As ADODB.Connection
Set mCon = New ADODB.Connection
mCon.Open "DSN=BOB;UID=BUILDER;PWD=SECRET;"
Dim mCmd As New ADODB.Command
mCmd.ActiveConnection = mCon
mCmd.CommandText = "DROP PROCEDURE BOBLIB.TEMPPROC"
mCmd.Execute
mCon.Close

I know the procedure exists because I’m able to call it fine and pass parameters with no problem. But when I try and delete it or DROP it I get an error message. Any ideas?  Bob-

Dear Bob,

Ah, the elusive procedure name... But it must be there because you can call it right? The problem is procedure names versus specific names. Lets take a look:

Once upon a time, the As/400 only allowed a procedure to have a unique name. In order to better support user defined functions and procedures with optional parameters you can now have several procedures with the same name. The AS/400 tells them apart by their SPECIFIC NAME which is also stored in the system catalogue when you create a procedure. When you attempt to call a procedure, the procedure that gets executed by your AS/400 is determined by the number of parameters and the data types of parameters passed to the procedure. For example, you could create a procedure called CALCME that takes two integer parameters. You could then create another procedure called CALCME that takes two integers and a character string. The AS/400 will store both procedures with the procedure name CALCME, but will give the first procedure the specific name CALCME and the second procedure will receive the specific name CALCM00001

Lets do this to the system so we can observe the behavior. Create the following procedure using Client Access Express or my SQLThing Enterprise Edition tool:

CREATE PROCEDURE WEBOE.CALCME
( P1 INOUT INTEGER,
P2 INOUT INTEGER)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
BEGIN
SET P2 = P1*4;
END

The procedure takes two arguments and returns argument two as argument 1 multiplied by 4. If you use the following SQL statement, you will see that the procedure is now registered on your AS/400.

SELECT * FROM QSYS2.PROCEDURES WHERE PROCNAME LIKE ‘CALC%’

Ok, now lets create another procedure:

CREATE PROCEDURE WEBOE.CALCME
( P1 INOUT INTEGER,
P2 INOUT INTEGER,
P3 INOUT INTEGER)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
BEGIN
SET P2 = P1*P3;
END

This procedure takes three arguments and returns the second argument as argument 1 multiplied by argument 3. Now, do the following select statement and you will see that you have 2 CALCME procedures:

SELECT * FROM QSYS2.PROCEDURES WHERE PROCNAME LIKE ‘CALC%’

Now, if you were to issue the following statement, which procedure would the AS/400 drop?

DROP PROCEDURE CALCME;

Neither, as it cannot tell which one you want to dismiss. So, how to we get rid of them?

The AS/400 has a physical file in the QSYS2 library called SYSROUTINES, which is where the AS/400 keeps the definitions of all your procedures and user defined functions. Try the following SQL statement:

SELECT ROUTINE_NAME,SPECIFIC_NAME,SPECIFIC_SCHEMA,
IN_PARMS, OUT_PARMS, INOUT_PARMS
FROM QSYS2.SYSROUTINES WHERE ROUTINE_NAME LIKE 'CALC%'

You should see results like the following:
RoutineName SpecificName SpecificSchema InParms OutParms InoutParms
CALCME CALCME WEBOE 0 0 2
CALCME CALCM0001 WEBOE 0 0 3

See, both procedures have the name CALCME but both procedures have a unique specific name. Now, try the following SQL statement:

DROP SPECIFC PROCEDURE CALCME;

This instructs the AS/400 to drop the procedure with the specific name CALCME. My advice is to always use the DROP SPECIFIC grammar as you are then sure the AS/400 knows exactly which procedure you wish to delete.

Howard F. Arner, Jr. is a writer and consultant with Client Server Development, Inc. and author of the book iSeries and AS/400 SQL at Work. You can purchase a copy of Howard’s book at www.sqlthing.com/books.htm Send your SQL questions to harner@sqlthing.com or visit our website to find out more about stored procedures.


 

Sample Topic Header: