A Stored Procedure (SP) of External object (EO) type 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 of the stored procedure object.
||Description of the stored procedure object.
||Type of EO: Stored Procedure.
||Data Store that points to the DBMS where the stored procedures are defined.
||Folder where the EO is located.
||Internal name to be given to the method in the KB.
||Description of the method.
||Type of data returned by the method, if applicable.
||Name of the SP as defined in the DBMS.
||It indicates whether the parameter is: INOUT, IN only or OUT only.
||Internal name to be given to the parameter.
||Description of the parameter.
||Data type of the parameter.
||External name of the parameter as defined in the SP.
Suppose we’ve created a SP of EO type called MySQL_SP and we’ve defined a MySQL_SP variable called &mysps.
Then, in our code we 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 mode 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.