External Object: Stored Procedure

External Objects (EO) of Stored Procedure (SP) type store 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.

Properties

External Object

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: folder where the EO is located.
Object Visibility: accessibility from other objects in different Modules.

Methods

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.

Parameters

Access Type: INOUT, IN only, or OUT only.
Internal Name: internal name to be given to the parameter.
Description: description of the parameter.
Type: data type of the parameter.
External Name: external name of the parameter defined in the SP.

Use

Suppose you have created an EO of SP type called MySQL_SP and you have defined a MySQL_SP variable called &mysps.

Then, in your code you can do the following:

Event 'Count'
    &mysps.get_count_universidad(&count)
    msg(&count.ToString())
EndEvent

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.

Notes

• 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. Therefore, 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:

Event 'Stored'
    &mysps.insertData()
    commit
EndEvent

Deployment

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.

Compatibility

It is possible to call Stored Procedures as it was done in previous versions; that is:

call('sp_count_universidad',&count)

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.'

Restrictions

Some complex data types are not supported. For example:

• Basic data types with the collection property set to "YES".
• Extended data types and Structured Data Types.
• Vector and Matrix.

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.