[progress News] [progress Openedge Abl] Extend Your Sql With User-defined Functions In...

[Jyothi Sundaragiri]

With user-defined functions, OpenEdge database developers can extend their programming to validate business logic, use less bandwidth and more.

OpenEdge SQL 11.7 allows you as database developers to create your own routines. These routines, called User-Defined Functions (UDF), can accept parameters, perform custom actions and return results. These are required especially when it comes to business logic, complex algorithmic calculations and custom actions. They also help reduce network traffic.

UDFs help the user extend the SQL functionality. You can write UDFs in Java to provide functionality that is not available in SQL or SQL built-in functions by default.

UDFs can be added in an SQL statement to a SQL function or to an expression. For example, you can use the UDFs in the following cases:

• The select list of a SELECT statement
• The condition of a WHERE clause
• ON CLAUSE, ORDER BY, and GROUP BY clauses
• The VALUES clause of an INSERT statement
• The SET clause of an UPDATE and DELETE statement

With UDFs, you can express your business logic and execute the business logic in the SQL engine, from where it can be called in your SQL queries.

Prerequisites


You must create UDFs as top-level functions before they can be used within an SQL statement. To use a user function in an SQL expression, you must own or have EXECUTE privileges on the user function.

Benefits of UDFs

• Extends programming richness via user code - Your database can now do more than only process data. With UDFs, databases can now also validate business logic and perform custom actions.
• Optimizes network traffic - UDFs allow data manipulation such that you do not need a lot of interaction with the database and you can optimize your results based on your business logic within the database. This reduces network bandwidth utilization.
• Reuse code, which can be called akin to stored procedures with business logic.

Limitations of UDFs

• When called in SQL queries, UDFs are executed only in a single thread. They follow a serial execution plan. Hence, if the business logic inside UDFs would work better in a multi-threaded environment, it would be better to not implement it as a user-defined function, since it would lead to overall performance degradation.
• UDFs cannot change database state, so there might be usage constraints if you are attempting to change the database information inside your business logic.
• The OpenEdge 11.7 release only supports scalar UDF.

When to Choose a UDF?

• You want a single value as the result. For example, total cost of a business trip, total project cost in man-days, or the birth date of a person.
• The return type can be any valid SQL type except arrays and lobs.

Example of an OpenEdge SQL User-Defined Function


Now that you know the benefits and limitations of a UDF, let’s walk through an example. We’ll create a function called days_between and see how to use it in a SQL select statement. This UDF accepts two input parameters named shipdate and orderdate which are of type date, calculates difference between them and returns a bigint value as output from the UDF.

days_between (date, date): This function provides the difference between two dates.
OpenEdge SQL has a similar Months_Between scalar function that gives you days_between functionality.

create function days_between(shipdate date, orderdate date)
return bigint
import
import java.util.Date;
begin
try {
long diff = shipdate.getTime() - orderdate.getTime();
return diff / (24 * 60 * 60 * 1000);
} catch (Exception e) {
return -1L;
}
end

select ordernum, pub.order.custnum, substr(Name,1,20), substr(Address,1,20), Phone,
days_between(shipdate, pub.order.orderdate) as DAYS_DELAYED
from pub.order inner join pub.Customer
on pub.customer.custnum=pub.order.custnum
where days_between(shipdate, pub.order.orderdate) > 10
order by days_between(shipdate, pub.order.orderdate) DESC;
Ordernum CustNum substr(Name,1,20) substr(Address,1,20) Phone DAYS_DELAYED

174 64 Hamahakkimies Ky Tainionkoskenkatu 20 (50) 374 3131 325

157 42 Stall-turn Flying Hurricane Lane 0702 258 133 260



Save Time and Bandwidth


With user-defined functions in OpenEdge 11.7, you can extend your SQL functionality to save time, gain capabilities and reduce bandwidth use. You can read more about UDFs in our documentation, and don’t forget to check out everything that’s new in OpenEdge 11.7.

Continue reading...