External Objects (EO) of type Stored Procedure (SP) stores all the information (name, parameters, etc.) about how to access a set of stored procedures located in a DBMS.
In this type of EO several methods with their corresponding parameters can be defined. Each method maps to a Stored Procedure defined in the DBMS.
Name: Name of the stored procedure object.
Description: Description of the stored procedure object.
Type: Type of EO (Stored Procedure).
Data Store: Data Store that points to the DBMS where the stored procedures are defined.
Module/Folder: The folder where the EO is located.
Object Visibility: accessibility from other objects in different Modules.
Internal Name: Internal name to be given to the method in the KB.
Description: Description of the method.
Type: Type of data returned by the method, if applicable.
External Name: Name of the SP as defined in the DBMS.
Access Type: INOUT, IN only or OUT only.
Internal Name: Internal name to be given to the parameter.
Description: Description of the parameter.
Type: The data type of the parameter.
External Name: External name of the parameter defined in the SP.
Suppose you have created an EO of type SP called MySQL_SP and you have defined a MySQL_SP variable called &mysps.
Then, in your code you can do the following:
count_universidad is the internal name given to the stored procedure sp_count_universidad; in the parameter, it loads the entry number of the University table of my DB.
- In order to generate all the necessary code to call the stored procedure, GeneXus will map the method's Internal Name with its External Name, and the parameters' Internal Name with their corresponding External Name, taking into account the parameter type and their Access Types. Therefor external names must be correctly defined; otherwise, errors such as "PLS-00306: wrong number or types of arguments in call to ' ...' " may appear.
- If the Stored Procedure modifies the database, like an insert, update or delete, you have to explicitly use a commit command. For example:
When the application is deployed, take into account that if the SP(s) are running in an iSeries DB2, the gx400dcl.exe (.net) or crtjdbccalls.class (java) files must be executed to declare them. See SAC #26620 for more details.
It is possible to call Stored Procedures as it was done in previous versions; that is:
In addition, declaring the list of Stored Procedures used in the property List of external stored procedures at the Data Store level is also possible.
This calling method is deprecated, and a warning like this will appear: spc0145, 'Calling stored procedure ''%1'' in data store ''%2'' via the call keyword is deprecated.'
There are some complex data types that are not supported. For example:
When the Stored Procedure returns a Recordset data type, GeneXus doesn't provide a compatible data type to map it.
Null value as an argument for a stored procedure method is not supported.
Stored Functions (Oracle) are not supported; use Stored Procedures with InOut or Out parameters.