Work with SQL Custom Functions

In some integration engine processing contexts, you might need to retrieve information to use in DML expressions from an external DBMS such as Oracle or MySQL. One way to achieve this is to write a function using the C programming language in connection with your DBMS access libraries.

This can be both complex and risky, and might require a large amount of obscure code. For very simple queries, the integration engine provides a quick solution: SQL Custom Functions.

SQL Custom Functions perform SQL selection queries. To create an SQL Custom Function you begin by creating a new Custom Function object in Axway Mapping Services.

Unlike Custom Functions implemented in C, SQL Custom Functions are defined and declared in Axway Mapping Services: no third-party toolset is required. As a result, when you select SQL as the Custom Function category, Axway Mapping Services displays the Implementation tab.

In the Editor view of the function, enter the SQL statement that you want to perform. The statement must be a simple SELECT statement that returns only one column from one row. The maximum length of the SELECT statement is 5,000 characters.

The value that your SQL Custom Function returns must be compatible with the class you select for it. If the statement returns none or more than one row, you receive an error. In addition, when a statement returns an SQL null value, your function returns a symbolic null.

The parameters of your Custom Function can be referred to in the SELECT statement. To do this you enclose the parameter names between hashes #. For example, imagine a table ZIP containing two columns: one for city names (CITY) and the second for zip codes (CODE). To implement the GetZipCode Custom Function that returns the code of a supplied city, use the following query:

SELECT CODE FROM ZIP WHERE CITY = #city_name#

When the integration engine executes the Custom Function, it replaces #city_name# with the value of the function parameter city_name enclosed in double quotes, and returns the value of the code from the table.

The following graphic illustrates the content of the Implementation tab:

Implementation tab

For such functions to be successfully executed, you must define a JDBC Channel in Axway Mapping Services that addresses the appropriate DBMS.

Related Links