Stored Procedures

www.altova.com Print this Topic Previous Page Up One Level Next page

Home >  Data Sources and Targets > Databases and MapForce >

Stored Procedures

Stored procedures are programs that are hosted and run on a database server. Stored procedures can be called by client applications and they are often written in some extended dialect of SQL. Some databases support also implementations in Java, .NET CLR, or other programming languages.

 

Typical uses of stored procedures include querying a database and returning data to the calling client, or performing modifications to the database after additional validation of input parameters. Stored procedures can also perform other actions outside the database, such as sending e-mails.

 

A stored procedure may have zero or more input and output parameters, and may optionally return zero or more recordsets, in addition to the default return value. Consequently, in MapForce, you can call a stored procedure in various ways:

 

Call a stored procedure in order to retrieve data, as if it were a source component on the mapping. This is applicable for procedures that do not take input parameters. When the mapping runs, the procedure is called, and it returns some recordset or output parameters. You can map the recordset, or the output parameters, or both, to any other data type supported by MapForce. For an example, see Stored Procedures as Data Source.
Call a stored procedure as a function-like call, with parameters. In this case, you supply all required input parameters from the mapping, and you can also map the returned recordset, or the output parameters, or both, to some other target supported by MapForce. For an example, see Stored Procedures with Input and Output.
Call a stored procedure as if it were a target component on the mapping. The typical use case is calling a stored procedure with parameters in order to modify the database (for example, insert a record). This approach is suitable if you do not need any output from the stored procedure. Also, in this approach you can execute the stored procedure within a database transaction that can be rolled back in case of an error. For an example, see Stored Procedures in Target Components.

 

There are also cases where you may need to call stored procedures or perform actions on database tables in a specific order (first insert, then update, and so on). For example, you may need to pass the output parameter of a stored procedure to another stored procedure. Or you may need to combine data returned by a stored procedure with data from a table. Such actions are possible with the help of local relations defined in MapForce, even when the underlying database does not enforce primary/foreign key relationships between tables. For more information, see Stored Procedures and Local Relations.

 

Note:To illustrate how MapForce implements stored procedures, this chapter uses Microsoft SQL Server 2016 and the "AdventureWorks 2016" database. The latter can be downloaded from https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks.

 

Support notes

Stored procedures can be used only in the BUILT-IN execution engine. Code generation in C++, C#, or Java is not supported.
User-defined types, cursor types, variant types and many "exotic" database-specific data types (such as arrays, geometry, CLR types) are generally not supported as input or output parameter types.
Procedure and function overloading (multiple definitions of routines with the same name and different parameters) is not supported.
Some databases support default values on input parameters, this is currently not supported. You cannot omit input parameters in the mapping to use the default value.
Stored procedures returning multiple recordsets are supported depending on the combination of driver and database API (ODBC/ADO/ADO.NET/JDBC). Only procedures that return the same number of recordsets with a fixed column structure are supported.
Whenever possible, use the latest version of the database native driver maintained by the database vendor. Avoid using bridge drivers, such as ODBC to ADO Bridge, or ODBC to JDBC Bridge.
You can optionally enable database transactions for stored procedures that are called as data target, see Stored Procedures in Target Components. Transactions are not supported for stored procedures that are called as a data source (without input parameters), or those that are called like a function (with both input and output).

 

The following table lists the database-specific support notes.

 

Database

Support notes

Access

Stored procedures in Microsoft Access databases have very limited functionality and are not supported in MapForce.

DB2

Supported in MapForce: stored procedures, scalar functions, table-valued functions.
Return values from DB2 stored procedures are not supported because they cannot be read via the database APIs used in MapForce.
Row-valued functions (RETURNS ROW) are not supported.
It is recommended to install at minimum "IBM_DB2 9.7 Fix Pack 3a" to avoid a confirmed JDBC driver issue when reading errors/warnings after execution. This also fixes an issue with the ADO provider that causes one missing result set row.

Firebird

Supported in MapForce: stored procedures, table-valued functions

Informix

Supported in MapForce: stored procedures, table-valued functions.

MariaDB

Supported in MapForce: stored procedures, scalar functions

MySQL

Supported in MapForce: stored procedures, scalar functions
MySQL includes complete support for stored procedures and functions starting with version 5.5. If you are using an earlier version, functionality in MapForce is limited.

Oracle

Supported in MapForce: stored procedures, scalar functions, table-valued functions.
It is recommended to use a native Oracle driver instead of the Microsoft OLE DB Provider for Oracle.
Oracle has a special way to return result sets to the client by using output parameters of type REF CURSOR. This is supported by MapForce for stored procedures, but not for functions. The names and number of recordsets is therefore always fixed for Oracle stored procedures.

PostgreSQL

Supported in MapForce: scalar functions, row-valued functions, table-valued functions.
In PostgreSQL, any output parameters defined in a function describe the columns of the result set. This information is automatically used by MapForce - no detection by execution or manual input of recordsets is needed. Parameters of type refcursor are not supported.

Progress OpenEdge

Supported in MapForce: stored procedures.

SQL Server

Supported in MapForce: stored procedures, scalar functions, table-valued functions.
It is recommended to use the latest SQL Server Native Client driver instead of the Microsoft OLE DB Provider for SQL Server.
The ADO API has limited support for some data types introduced with SQL Server 2008 (datetime2, datetimeoffset). If you encounter data truncation issues with these temporal types when using ADO with the SQL Server Native Client, you can set the connection string argument DataTypeCompatibility=80 or use ODBC.
SQL Server Procedures have an implicit return parameter of type int null, which is available for mapping. If the procedure omits a RETURN statement, the resulting value is 0.

SQLite

SQLite does not use stored procedures.

Teradata

Supported in MapForce: stored procedures, macros.
Scalar functions, aggregate functions and table functions are not supported
Known issue: The Teradata ODBC driver refuses to populate output parameter values after a procedure call.

© 2019 Altova GmbH