External Object: Stored Procedure

Official Content
This documentation is valid for:

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.

EO Stored Procedure

Properties

External Object

EO Stored Procedure Prop

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.
Folder Folder where the EO is located.

 

Methods

EO Stored Procedure 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

EO Stored Procedure Parameters

Access Type It indicates whether the parameter is: 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 as defined in the SP.

 

Usage

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:

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 

  • 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:
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 mode 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

There are some complex data types which 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.



Was this page helpful?
What Is This?
Your feedback about this content is important. Let us know what you think.