Since 'Stored Procedures' usually have database specific implementations, Telerik OpenAccess ORM enforces certain rules while using stored procedures, based on the database used.
Using Stored Procedures with Oracle
Usage of stored procedures for write operations with Oracle is the same as with any other backend except for a few restrictions.
- As the Oracle provider for .NET passes the procedure parameters by order, it is mandatory that the parameter mappings need to be specified in the same order as they are declared in the stored procedure, when using the ODP driver. The order of the parameter mapping is not important while using the ‘genericADO2’ driver.
Using Stored Procedures with MySql
By default, the name generator for Telerik OpenAccess ORM generates the table, column names from the persistent model by using the class, field names. However, the MySQL connector for .NET has a bug when it comes to handling underscores (‘_’) within table, column, procedure, parameters names. Due to this Telerik OpenAccess ORM generates the default names for schema objects in MySQL, using different settings.
This can be achieved for other backends by using the ‘Use model field names for column names’ property in the ‘Backend Configuration’ dialog, under the ‘Name Generation’ category.
Using Stored Procedures with SqlAnywhere
In order to perform Optimistic Concurrency while using stored procedures, Telerik OpenAccess ORM requires the count of the rows affected by the stored procedure execution.
The stored procedure code generated by OpenAccess expects the execution to stop whenever an error is encountered.This database specific error is then wrapped by an appropriate OpenAccess exception and thrown to the client application that uses the OpenAccess API. In SqlAnywhere the 'on_tsql_error' database option controls the error handling mechanism in stored procedures. In order to maintain the expected behavior irrespective of the 'on_tsql_error' option value, OpenAccess uses the rows affected count returned by SqlAnywhere instead of passing it via a 'RowsAffected' OUT parameter. Hence the 'RowsAffected' OUT parameter is not required or generated by OpenAccess while using stored procedures for CUD operations.
If a user defined stored procedure is to be used, it needs to ensure that execution stops immediately if an error is encountered.