Consultor Eletrônico

Status: Unverified

GOAL:

Stored procedures are missing when using the SQLProcedures Catalog function in Progress 9.1B and 9.1C

FACT(s) (Environment):

Progress 9.1B

FACT(s) (Environment):

Progress 9.1C

FIX:

When using the SQLProcedures Catalog function to list database's stored procedures, some may be missing when using this function.   Due to a issue in the SQL-92 Engine, those stored procedures that are not passed arguments are omitted.

To demonstrate;
1.  Create a copy of the sports database.
2.  Add a table called t1 and add three integer fields called c1, c2, c3.  
3.  Connect to the Database via SQLExplorer or some other SQL tool.  
4.  Create and commit the Stored Procedures listed below.  

The following stored procedure will not be displayed when running the SQLProcedures Catalog function


@echo true
CREATE PROCEDURE TT1 ()
BEGIN
   SQLIStatement tt1 = new SQLIStatement ("insert into pub.t1(c1,c2,c3) values (4,5,6)");
   tt1.execute ();
END
COMMIT


However, the following one will:

@echo true
CREATE PROCEDURE TT2 (IN x INTEGER)     <--- Argument
BEGIN
   SQLIStatement tt2 = new SQLIStatement ("insert into pub.t1(c1,c2,c3) values (6,7,8)");
   tt2.execute ();
END
COMMIT


As long as a COMMIT statement is issued, you will be able to see all Stored Procedures with the following query:
select * from sysprogress.sysprocedures