One of the little known features of m-Power is the ability to call your own functions (or native database functions) through calculated fields. I’m going to show you how to do it with a pretty powerful example that takes about five minutes to build.
First, some background
Every database has its own rules regarding functions and its own native functions. This page shows some DB2 functions, and if I click on one of them, it shows the analogous function in Oracle and Sequel Server.
In addition, every database has its own rules for what languages you can use to write your own User Defined Functions (UDFs). I am going to use SQL to write a function for DB2/400.
What I am going to do is build a function that sums the product of Price * Ordered Quantity of all the detail records of a given order.
At first glance, you might think that UDFs are just a finite subset of what we can do with external objects. Here’s where you are wrong. Because functions are called from the database, they can be used to do anything you might want to do in an SQL clause. This means you can display, sequence, and select records based on the results of functions. That’s a lot more power with less work than what we can do in an application servlet with external objects.
This function is useful, in that it can be called in myriad numbers of reports and inquiries about our orders. What you will see in the example is that I can sequence and select records in my servlets based on the results of this function. Let’s get started.
1. Build the Function:
To do this I entered SQL and typed the following code;
create function CBB2E/ORDERTOT1( ordnuminp DECIMAL(6,0) ) returns DECIMAL(11,2) language SQL is deterministic reads SQL DATA no external action BEGIN DECLARE ordertot DECIMAL(11,2); select sum(QTYORD * PRICE) into ordertot from CBB2E/ORDDETAIL where ORDNO = ordnuminp; return ordertot; END
It’s fairly simple and straightforward. The create function statement first names it and puts it somewhere. It tells it that we will be passing it one parameter which is numeric 6,0. The function returns an 11,2 field. Lastly, I encapsulate my SQL statement between a BEGIN and END statement. If I have entered everything correctly, SQL tells me that it has created the function ordertot1 in library CBB2E.
2. Create a multi-record web 2.0 retrieval
I create this over the Order header file linking to the customer master file to get the customer name. The fields I select are Customer Number, Name, Order Number, Customer PO #, Order Date, and Ship Date. Notice, that I never touch the order detail file in my definition of this retrieval. I’ll leave that to my UDF.
3. I create a calculation to call my function
First, I include the characters *SQL in the calculation field description. This tells m-Power not to edit check the calculation. That means you have to get the specification correct or you may run into compile or run time errors.
I make certain that my calculation field attributes match what the function is going to return – an 11 byte numeric field with 2 decimal places.
Lastly, I specify my function in the area for the calculation. It looks like this:
CBB2E.ordertot1(ORDNO)
The syntax is Library followed by a period followed by function name, then left parenthesis, then the fields or parms to pass to the function in a comma separated list, followed by a close parenthesis.
Nice things to know
Your data-base comes with pre-defined functions. To find out what they are, just Google the name of your data-base and the words database functions.
Different data-bases let you write your own user defined functions in a variety of languages. You are not just restricted to SQL. This means you can write code in the language you know and user it through m-Power.
Our performance tests indicate that this stuff is pretty darn fast. You are taking code out at the application level, and running it at the data-base level. Of course, like any other coding, you can build things that will be inefficient, but if you do things smartly, you are going to love the way this performs.