External Object: Stored Procedure

External Objects (EO) whose Type property = "Stored Procedure" (SP) 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).
• Datastore: 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 Knowledge Base.
• Description: Description of the method.
• Type: Data type returned by the method, if applicable.
• External Name: Name of the Stored Procedure 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 Stored Procedure.

Use

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

Then, in your code you can do the following:

Event 'Count'
  &mysps.get_count_university(&count) //&count is a variable based on the Numeric(8.0) data type. It is loaded with the number of universities in the DB University table
  msg(&count.ToString())
EndEvent

get_count_university is the Internal Name given to the Stored Procedure sp_get_count_university. 

Considerations

• Only variables and/or constants are accepted as parameters when calling Stored Procedures. Expressions are not supported. So, if necessary, you can assign an expression to a variable and pass the variable as a parameter.
• 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 'DeleteUser'
    &mysps.delete_user(10)
    commit
EndEvent

Deployment

When the application is deployed, take into account that if the SP(s) are running on 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_get_count_university',&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 True.
  • 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.