Consultor Eletrônico

Status: Unverified

GOAL:

How to enable all users to call an SQL-92 Stored Procedure without having to fully pre-qualify the procedures name with its owner?s name?

FACT(s) (Environment):

Progress 9.x
OpenEdge 10.x
All Supported Operating Systems

FIX:

Use the SQL-92 "SET SCHEMA 'OwnerName';" statement. The following sample code performs the following tasks:

1. Sets the schema to the default schema, in this case 'PUB'.
2. Creates a stored procedure that will be owned by 'PUB' not the creator.
3. Grants execute privileges on this newly created procedure to every one.

SET SCHEMA 'PUB';
COMMIT WORK;
DROP PROCEDURE ListCustomers;
CREATE PROCEDURE ListCustomers()

RESULT (
name CHARACTER (60)
)

BEGIN
SQLCursor customerCursor = new SQLCursor("SELECT name FROM pub.Customer");
customerCursor.open();
do {
customerCursor.fetch();
if (customerCursor.found()) {
String cname = (String) customerCursor.getValue(1, CHARACTER);
SQLResultSet.set (1, cname );
SQLResultSet.insert ();
}
} while ( customerCursor.found() );
customerCursor.close ();
END

GRANT EXECUTE ON PUB.LISTCUSTOMERS TO PUBLIC;
COMMIT WORK;

The above SQL-92 code allows all users to call the above procedure without having to fully pre-qualify the procedure's name with its owner?s name as long as the default schema is set to 'PUB':

CALL LISTCUSTOMERS() ;

Therefore, one way to call SQL-92 Stored Procedures without having to fully pre-qualify their names with the owners' names is to make the default schema as the owner of all the stored procedures as illustrated in the above code sample.

Kindly note that the Progress ODBC driver connections support setting the default schema name without having to execute the "SET SCHEMA 'DefaultSchemaName';" statement. See solution P21008, "How to set the default SQL-92 Schema on Windows for ODBC Connection?" for details.